OLEDB error while processing Analysis service database

Hi,
I migrated AS 2000 database to AS 2005 thorugh migration wizard and started processing the database. But I am getting error like "OLE DB error: OLE DB or ODBC error: Query (5, 19) Parser: The syntax for 'AS' is incorrect..". After migrating I didn't modified any settings.
If anybody having info, please share.
ThanksIt looks the SQL query sent by Analysis Services for processing is bad. You can examine the query in the processing dialog and see what's wrong (wrong syntax for AS clause?).

OLE+XML+ASP Fails After Migration to 2003

Hi,
We have moved our site from a Windows Server 2000 and SLQ Server 2000
environment to Windows Server 2003 and SQL 2000 and everything is working
fine except an app that uses XML and OLE.
The script was working fine up to a recent point and the only way we can
resolve the problem is by restarting IIS. The script just streams the xml
many successful times and then out of nowhere just sits there and appears to
be timing out but the process has been sitting on the server for over 4 hour
s
without timing out.
Has there been anything that has changed in Windows Server 2003 that would
cause this?
Thanks,
FredHi Fred,
Can you give a little more detail on what the script is doing? Which XML
interfaces/objects are you using in the script?
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"FredPope" <FredPope@.discussions.microsoft.com> wrote in message
news:1C900FAD-0510-4C50-9DCC-BF7A84902BA4@.microsoft.com...
> Hi,
> We have moved our site from a Windows Server 2000 and SLQ Server 2000
> environment to Windows Server 2003 and SQL 2000 and everything is working
> fine except an app that uses XML and OLE.
> The script was working fine up to a recent point and the only way we can
> resolve the problem is by restarting IIS. The script just streams the xml
> many successful times and then out of nowhere just sits there and appears
to
> be timing out but the process has been sitting on the server for over 4
hours
> without timing out.
> Has there been anything that has changed in Windows Server 2003 that would
> cause this?
> Thanks,
> Fred
>

OLE+XML+ASP Fails After Migration to 2003

Hi,
We have moved our site from a Windows Server 2000 and SLQ Server 2000
environment to Windows Server 2003 and SQL 2000 and everything is working
fine except an app that uses XML and OLE.
The script was working fine up to a recent point and the only way we can
resolve the problem is by restarting IIS. The script just streams the xml
many successful times and then out of nowhere just sits there and appears to
be timing out but the process has been sitting on the server for over 4 hours
without timing out.
Has there been anything that has changed in Windows Server 2003 that would
cause this?
Thanks,
Fred
Hi Fred,
Can you give a little more detail on what the script is doing? Which XML
interfaces/objects are you using in the script?
Thanks,
Adam Wiener [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"FredPope" <FredPope@.discussions.microsoft.com> wrote in message
news:1C900FAD-0510-4C50-9DCC-BF7A84902BA4@.microsoft.com...
> Hi,
> We have moved our site from a Windows Server 2000 and SLQ Server 2000
> environment to Windows Server 2003 and SQL 2000 and everything is working
> fine except an app that uses XML and OLE.
> The script was working fine up to a recent point and the only way we can
> resolve the problem is by restarting IIS. The script just streams the xml
> many successful times and then out of nowhere just sits there and appears
to
> be timing out but the process has been sitting on the server for over 4
hours
> without timing out.
> Has there been anything that has changed in Windows Server 2003 that would
> cause this?
> Thanks,
> Fred
>

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..

Old Excel files after SQL2000 > SQL2005 migration

Hello there!

We are having some problems over here after our SQL2005 upgrade.

If we try to use an excel file that previously accessed the SQL2000 database (same DSN name and credentials), and then try to refresh the data after the 2005 upgrade, we get the errormessage "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON."

We have tried to delete and recreate the DSN file.. Tried to use SA user instead (with offcourse full access)..

An even bigger problem is that we can not even get into msquery to copy the SQL query to make a new file, the same problem appears.

Please note that if we try to make a new external database query it works fine!

Any suggestions?

We have now tried to make the user member of the sysadmin rule. And that works. But that is not a workable solution in the long run for us.

So our hopes are still left to you!

|||

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

|||

Thank you for your answer!

Is there any known way to force MsQuery not to use this backwards compability when connecting to the source? Maybe in the dsn or in msquery itself?

We have not at any time used qouted identifiers, so that would not be a problem.

|||We're talking this over internally now, I'll see what I can find out.|||

I suspect the problem is how the old datasource stored off the application name in it's internal connection string. If the string contains the words "Microsoft Query" then our driver will send this dbcc traceon statement. So new queries must not be adding this to the connection string.

Unfortunately I don't see a way to modify the connection string that Excel is using it appears to be embedded in the spreadsheet somewhere.

|||

I talked to one of my Office gurus and he said:

Assuming you used Excel's Import External Data, the sheet should have a QueryTable object which contains a Connection string:

'sample VBA macro

Sub Test()

Dim q as QueryTable

Set q = ActiveSheet.QueryTables(1) 'assumes active sheet has the data and only 1 query table on the sheet

q.Connection = "<your new connection>"

q.Refresh

End Sub

So potentially you could fix this by reading the Connection property and removing the string Microsoft Query from the connection string.

|||Also note I filed a bug for this internally so we will get this fixed.|||

Thank you very much!

That actually worked well

Have a nice weekend!

|||

How do you get to this VBA macro? I can't seem to find it under macros or VBA editor.

|||There is not a pre-existing macro to do this, you have to write your own macro (code) to do this. In Excel this is under Tools|Macro|Macros.|||

I was assuming this was added when you used Excel's Import External Data per an earlier post. If I just used the Excel front end (no custom macro) is there a way to fix this?

|||New imports should work, I verified this with Excel 2003 at least. If they don't work for you let me know what version of Excel you are using.|||

Any news on a fix for this issue

The drivers with sp1 do they have a fix or is there a hotfix available

|||

SP1 did not change anything for us..

But the script that someone provided earlier fixes the problem client side..