Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Linked Server SQL 2000 - SQL 2012 Issues Expand / Collapse
Author
Message
Posted Wednesday, July 25, 2012 10:05 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 417, Visits: 1,105
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.
Post #1335256
Posted Wednesday, July 25, 2012 2:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
Hi Leeland thanks a lot for detailed information...
Post #1335439
Posted Wednesday, July 25, 2012 3:36 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 417, Visits: 1,105
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...
Post #1335458
Posted Thursday, July 26, 2012 3:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
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.
Post #1336146
Posted Thursday, July 26, 2012 4:41 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 417, Visits: 1,105
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

Post #1336173
Posted Friday, July 27, 2012 10:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
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
Post #1336634
Posted Friday, July 27, 2012 11:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
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...
Post #1336682
Posted Friday, July 27, 2012 1:10 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 417, Visits: 1,105
So it worked?
Post #1336727
Posted Friday, July 27, 2012 1:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:40 PM
Points: 103, Visits: 500
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!
Post #1336749
Posted Friday, July 27, 2012 1:59 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 417, Visits: 1,105
On my example...that was the only one enabled...


Glad it is working.
Post #1336767
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse