oledb to ibm db2 - no tables?

Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
--
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'AS400USERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL
> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
> Here is my linked server script, hope it will help you:
[...]

oledb to ibm db2 - no tables?

Hi.
I am upgrading a datawarehouse from sql2000 to sql2005.
Unfortunately, there is something wrong when I try to get the list of
available tables/views to import from ibm as400.
It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
there is no such driver available on sql2005. The only available driver is
the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the list
of all the available tables / libraries on the remote system. The message
is: "The source database you have selected contains no visible tables or
views." On sql2000 I was able to fetch the list of all the available tables
in all the libraries, put a tick against some of them and just import them
to the sql server.
I noticed that I can use the .Net driver for ODBC which allows me to import
data from any odbc source (defined in control panel -> administrative tools
-> odbc...), but this approach requires typing the whole sql statement for
each source table. This could be OK for a single table, but there are more
than 50 tables I need to import and writing separate sql statement for each
of them is something I would like to avoid.
Is there anybody with good experience regarding this issue? Please help...
PLI'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
When selecting from libraries files I simply do " SELECT * FROM
AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too!)
Here is my linked server script, hope it will help you:
(our AS/400 system name is.. AS400 :-) ).
/****** Object: LinkedServer [AS400OLE] Script Date: 04/16/2007 16:58:00
******/
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400OLE',
@.srvproduct=N'AS400', @.provider=N'IBMDA400', @.datasrc=N'AS400',
@.catalog=N'AS400'
/* For security reasons the linked server remote logins password is changed
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@.rmtsrvname=N'AS400OLE',@.useself=N'False
',@.locallogin=NULL,@.rmtuser=N'AS400U
SERID',@.rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'dist',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'pub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'sub',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'lazy schema
validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'AS400OLE', @.optname=N'use remote
collation', @.optvalue=N'true'
"Piotr Lipski" <piotr.lipski@.nie.p0czta.spamuj.bo.onet.w.ryj.pl> wrote in
message news:1rjimvs4uyts7$.3ctxiz7tm0uv.dlg@.40tude.net...
> Hi.
> I am upgrading a datawarehouse from sql2000 to sql2005.
> Unfortunately, there is something wrong when I try to get the list of
> available tables/views to import from ibm as400.
> It worked fine on sql2000 (I was using "iSeries Access ODBC Driver") but
> there is no such driver available on sql2005. The only available driver is
> the "IBM DB2 UDB for iSeries OLE DB Provider" which cannot display the
> list
> of all the available tables / libraries on the remote system. The message
> is: "The source database you have selected contains no visible tables or
> views." On sql2000 I was able to fetch the list of all the available
> tables
> in all the libraries, put a tick against some of them and just import them
> to the sql server.
> I noticed that I can use the .Net driver for ODBC which allows me to
> import
> data from any odbc source (defined in control panel -> administrative
> tools
> -> odbc...), but this approach requires typing the whole sql statement for
> each source table. This could be OK for a single table, but there are more
> than 50 tables I need to import and writing separate sql statement for
> each
> of them is something I would like to avoid.
> Is there anybody with good experience regarding this issue? Please help...
> --
> PL|||On Mon, 16 Apr 2007 17:00:42 -0400, Rafael Lenartowicz wrote:
Thanks for hint. In the meantime I've re-installed the whole as400 software
suite, choosing "full" option this time - and it definitely works better.
At least, I can get the list of all the tables on remote system.
Unfortunately, I still cannot get the full list of all the columns in a
particular table - I have to run "select * from [as400table]" to do it.
Anyway, I'm happy enough. I can import data (and schedule the import
packages). Next step will be learning how to fire ssis packages on demand.
Cheers,
PL

> I'm succesfuly accesing AS/400 files via OLEDB from SQL Server 2005 SP2.
> When selecting from libraries files I simply do " SELECT * FROM
> AS400OLE.AS400.LIBRARY.FILE " and it works fine (IMHO faster than ODBC too
!)
> Here is my linked server script, hope it will help you:
[...]sql

OleDb Source Error - Connecting to Oracle 9i

Hi,
I have my datasource in Oracle9i and destination is Sql2005. I am connecting to Oracle 9i through OLEDb provider and when I connect to my DataSource using OLE DB Data Source, I get the following error:

Warning at "guid code": Cannot retreive the column code page info from the oledb provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale Id will be used.

Could anyone please help me trouble shoot this problem.

Regards,
Sudhakara.T.P.
sudhakaratp@.hotmail.com

Hi,

Click on the OLE DB Source component and check for the properties, you have to keep the property "Alwaysusedefaultcodepage" to true.

Hope this will help you.

Regards,

Raju

|||

Hi Raju,
Thank you very much for your help.
Well, I was not aware of this option and it helped me a lot to finish my work.

Regards,
Sudhakara.T.P.
sudhakaratp@.hotmail.com

OLE DB for DB2 Built on Dev run on Standard

I have built a simple package using the Microsoft OLE DB provider for DB2 with SSIS Developer. If I schedule this on a Standard SQL2005 machine the package will not run. The error is blank, just says it had an error. Can I not run this package because the provider is not installed on this machine? If so is there a work around for this?you probably have to run your package on the box that has DB2OLEDB physically installed.|||

I believe that the ole db for DB2 has to be used on enterprise edition (if i read the licesne right from the ssis extension site). I am not sure if the ms one works very well. The IBM one sucked very badly when dealing with decimals. Even they recommended not using it (IBM) as part of the Client access express in their redbooks. If you are not going to fork out the extra few thousand bucks for enterprise, try the ODBC connect from DataDirect. It is a bazillion times faster than the CLAE ODBC, and i could actually get it to work (on db2 400).

Dev edition has all rights/ features as enterprise.

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..