July 27, 2012 at 10:26 am
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
July 27, 2012 at 11:44 am
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...
July 27, 2012 at 1:10 pm
So it worked?
July 27, 2012 at 1:31 pm
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!
July 27, 2012 at 1:59 pm
On my example...that was the only one enabled...
Glad it is working.
November 12, 2012 at 9:11 am
Sorry to resurrect an old thread but I am hitting the same problem, that is trying to link SQL 2012 to SQL 2000, and was just wondering how this solution has worked out for those who have used it. Is it still working as required or did you hit issues with it?
I need to update and consolidate 8 SQL 2000 servers to one SQL 2012 cluster. I was hoping to do this piece by piece so would need to have links to the old 2000 servers in place for the migrated databases to see the data on the non-migrated systems and so far this thread is the only solution I have found!
If not it'll be a move to SQL 2008 R2 instead of 2012, which I really would rather not do.
Cheers,
Luke
November 14, 2012 at 11:47 am
When I was working with the SQL 2000 to SQL 2012, and we got it configured...it worked just fine...
I am not at that position anymore so I cannot comment on the longevity, performance...etc...kind of use at your own risk type deal
November 15, 2012 at 1:34 am
Cheers Leeland (and thanks for the original solution too!). I only need this to work for the length of time it takes to get the last server up to 2012 once the first server is moved to 2012 (if that makes sense!) so fingers crossed it won't be more than a month or two......but then that could be a very long time if things aren't working properly 😉
November 15, 2012 at 9:31 pm
Hope it works out...when I came up with that it was for a similar purpose...and it worked just fine!
March 13, 2013 at 1:43 pm
SQL Server Driver 6.01.7601
Where do I get this driver from? What installs it?
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply