SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server SQL 2000 - SQL 2012 Issues


Linked Server SQL 2000 - SQL 2012 Issues

Author
Message
Leeland
Leeland
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2042 Visits: 1331
I have successfully created a linked server from SQL 2012 to a SQL 2000 server.

It isn't supported to the best of my knowledge but it has been working so far.



The notes I wrote up...


Since we are using a x64 bit 2012 server we need to add the DSN in the odbc32.exe as well as the x64 odbc.exe menu.

The odbc for 32 bit isn’t easy to find

C:\windows\System32

Right click and run as administrator, then system, then add...

Need to pick the non-version 11…it won’t work…pick the 6.01 version…in my example I had SQLSRV32.DLL (11/20/2010)

Then configure as you see fit (i.e. give it a name and point it at a server)


When an x64 bit connects to a 32 bit 2000 server you need to run the x64 to 32bit conversion script.

Instructions listed here

http://support.microsoft.com/kb/906954


Once you have that part done for both x64 ODBC and 32bit ODBC you can take the script below and configure as you like…hopefully it makes enough sense…



USE [master]
GO

EXEC sp_addlinkedserver
@server = '</Server name you put in the DSN/>', -- Name of the Linked Server, when it is created.
@srvproduct = 'Microsoft OLE DB Provider for ODBC', -- OLE DB Provider.
@catalog = '', -- Catalog Is Optional For ODBC Connections.
@provider = 'MSDASQL', -- Provider_name.
@datasrc = '</The actual server name/>', -- DSN Name of the ODBC Data Source.
@provstr = 'DRIVER={SQL Server};SERVER=</The actual server name/>;UID= enter login;PWD=enter password;' -- ODBC Connection String.


EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'</Server name you put in the DSN/>',
@locallogin = N'enter a local login',
@useself = N'False',
@rmtuser = N'enter a remote login',
@rmtpassword = N'enter a remote password'
GO






Then when done and no errors...log into your 2012 server with your local login account and try a 4 part named query across...should work.
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 501
Hi Leeland thanks a lot for detailed information...
Leeland
Leeland
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2042 Visits: 1331
Sorry for the confusion...

In my example I configured both the 32 bit and x64 ODBC

the 32 bit ODBC.exe is located in that path I listed above C:\windows\System32 directory.

right click on the ODBC.exe run as admin and then create a new DSN (which will point to the 2000 server).


When you are configuring it asks what driver to use...I just listed the driver I used and tried to be descriptive.

When you configure obviously make note of the name of the DSN...because that is going to be the name of the linked server.


when you use the code I posted below you will replace the "</Server name you put in the DSN/>" with the name of the DSN you just created...


@datasrc = '</The actual server name/>',

you will replace that part with the actual server name


in this part you need to configure the login and password

@provstr = 'DRIVER={SQL Server};SERVER=</The actual server name/>;UID= enter login;PWD=enter password;' -- ODBC Connection String.


for example you could use the sa account and password



this code is for adding the login mapping on that particular linked server


EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'</Server name you put in the DSN/>',
@locallogin = N'enter a local login',
@useself = N'False',
@rmtuser = N'enter a remote login',
@rmtpassword = N'enter a remote password'
GO

hope it helps...
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 501
Leeland I get this error:


Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TEST2".
OLE DB provider "MSDASQL" for linked server "TEST2" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)


Any idea why am am I getting this error... I created a new ODBC connection "DSNTEST1" which connects with no issue... I followed your suggestion.... I used this driver to create the ODBC "SQLSRV32.DLL (11/20/2010)". Also, I forgot to mention that SQL 2012 is installed in Virtual Machine...

EXEC sp_addlinkedserver
@server = 'TEST2', -- Name of the Linked Server, when it is created.
@srvproduct = 'Microsoft OLE DB Provider for ODBC', -- OLE DB Provider.
@catalog = '', -- Catalog Is Optional For ODBC Connections.
@provider = 'MSDASQL', -- Provider_name.
@datasrc = 'DSNTEST1', -- DSN Name of the ODBC Data Source.
@provstr = 'DRIVER={SQL Server};SERVER=DSNTEST1;UID= TestUser;PWD=TEST;' -- ODBC Connection String.
Leeland
Leeland
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2042 Visits: 1331
Laura_SqlNovice (7/26/2012)
Leeland I get this error:


Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TEST2".
OLE DB provider "MSDASQL" for linked server "TEST2" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)


Any idea why am am I getting this error... I created a new ODBC connection "DSNTEST1" which connects with no issue... I followed your suggestion.... I used this driver to create the ODBC "SQLSRV32.DLL (11/20/2010)". Also, I forgot to mention that SQL 2012 is installed in Virtual Machine...

EXEC sp_addlinkedserver
@server = 'TEST2', -- Name of the Linked Server, when it is created.
@srvproduct = 'Microsoft OLE DB Provider for ODBC', -- OLE DB Provider.
@catalog = '', -- Catalog Is Optional For ODBC Connections.
@provider = 'MSDASQL', -- Provider_name.
@datasrc = 'DSNTEST1', -- DSN Name of the ODBC Data Source.
@provstr = 'DRIVER={SQL Server};SERVER=DSNTEST1;UID= TestUser;PWD=TEST;' -- ODBC Connection String.



You created the DSN both 32 bit and x64? (not sure if it was totally needed...I did it anyway)...

One other thing...you made them as System DSN's vs User DSN's correct?

When inside the Create New data source menu make sure you choose the 6.01.xxx version named "SQL Server"


You tested that account login and password on the 2000 server? case sensitive?

I seem to recall getting a similar error and I basically wiped everything off in regards to DSN's and Linked servers and started from scratch with the steps listed above...

I would say to start from square 1, won't take that long to go in and recreate the dsn's (are you using that account listed above when you are configuring the DSN?).

Then configure the script to add the linked server and then the linked server login...

let me know if it helps
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 501
Leeland thanks a lot for your help... there is some progress and I think I am very close in solving this issue with your help...

I created a SYSTEM DSN "TEST_ODBC" using the SQL Server Drive 6.01.7601... in C:\Windows\System32\odbcad.32.exe

I created the Linked Server using the Script below:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'TestLinkedServer', @srvproduct=N'TEST_ODBC', @provider=N'MSDASQL',
@datasrc=N'TEST_ODBC',
@provstr=N'DNS=TEST_ODBC;UID=MYUSERNAME;PWD=MYPASSWORD'

GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TestLinkedServer', @optname=N'use remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'TestLinkedServer',
@locallogin = N'MyDOMAIN\USERNAME', ---2012 Box account
@useself = N'False',
@rmtuser = N'MYUSERNAME', --remote server login
@rmtpassword = N'MYPASSWORD' --remote server pwd
GO



When I tested the connection of the linked server "TestLinkedServer" it is working fine and I can see the catalogs...
However, when I run this select below:

Select * from [TestLinkedServer].MYDB.dbo.TBLNAME

I get this error now:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "TestLinkedServer" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "TestLinkedServer". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.



HOWEVER when I tried running this instead:
Select * from [TestLinkedServer]...TBLNAME

I CAN SEE THE DATA... seems like configuration issue????



Thanks for all your help Leeland...

Laura
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 501
When I enabled "Allow Inprocess" in the "MSDASQL" from Providers folder... the 4 part naming is working fine too... I will restart the server and test again...
Leeland
Leeland
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2042 Visits: 1331
So it worked?
Laura_SqlNovice
Laura_SqlNovice
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 501
Yes it is working. Thanks a lot Leeland!

For linked server provider "MSDASQL" I have enabled only "Allow Inprocess" and it is working fine.... I wanted to check with you which ones you have enabled?

Thanks!
Leeland
Leeland
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2042 Visits: 1331
On my example...that was the only one enabled...


Glad it is working.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search