Link 2008 Server to 2005 Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Link 2008 Server to 2005 Server

Author

Topic

seth2011
Starting Member

10 Posts

Posted - 2011-06-13 : 17:59:53

Hello,

My problem:
I am trying to link a SQL SERVER 2005 box to a SQL SERVER 2008 box and I can only get the link to work one way - from 2005 to 2008.

My environment:
- 2005 SQL Server, call it SERVER_2005
- 2008 SQL Server, call it SERVER_2008
- SQL Server Management Studio

I have admin rights on the 2008 server and am working with an admin on the 2005 server.

The 2005 administrator created the TEST_USER user (server AND particular database logins) on SERVER_2005.
I did the same on the 2008 server.
The 2005 administrator created a link to SERVER_2008.

We were successfully able to query from SERVER_2005 to SERVER_2008:
select top 10 * from [SERVER_2008].[Test2008Database].[dbo].[Test2008Table]

I ran these two commands on SERVER_2008 to set up the link to SERVER_2005.

sp_addlinkedserver
@server='SERVER_2005',
@srvproduct='Any',
@provider='SQLNCLI10',
@datasrc='SERVER_2005',
@provstr='Data Source=SERVER_2005;Initial Catalog=Test2005Database;User Id=TEST_USER;Password=xxx;'

sp_addlinkedsrvlogin @rmtsrvname='SERVER_2005', @locallogin='TEST_USER', @rmtuser='TEST_USER', @rmtpassword='xxx'

When I try this:
select top 10 * from [SERVER_2005].[Test2005Database].[dbo].[Test2005Table]

I get this error:
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "SERVER_2005".

But this works:
select top 10 * from OPENQUERY(SERVER_2005, 'SELECT * from [Test2005Database].[dbo].[Test2005Table]')

Does anyone know of a step-by-step process to get this working?

It doesn't seem like a Windows/networking issue because OPENQUERY works. Right?

Thank you kindly,
Seth

mikgri
Starting Member

39 Posts

Posted - 2011-06-14 : 14:42:05

It is easy to do it if you will use SSMS and check option 'SQL Server' on General tab and 'By made using this security context' on Security tab. Or execute this two likes of code:
exec sp_addlinkedserver @server='SERVER_2005', @srvproduct='SQL Server' and
exec sp_addlinkedsrvlogin @rmtsrvname='SERVER_2005', @locallogin=NULL, @useself='False', @rmtuser='TEST_USER', @rmtpassword='xxx'

Hope this will resolve your problem.

seth2011
Starting Member

10 Posts

Posted - 2011-06-15 : 10:16:48

Thanks very much for responding. Even after following your instructions, when I log in to SERVER_2008 as TEST_USER and try to run a query against SERVER_2005, I am still getting the error message:

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "SERVER_2005".

seth2011
Starting Member

10 Posts

Posted - 2011-06-28 : 12:34:29

I discovered that I am seeing the exact same error when I'm logged into the 2005 Server through SSMS and am trying to query a table in a SQL Server 2000 instance:

select * from SERVER_2000.some_db.dbo.table_2000

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server SERVER_2000 does not contain the table ""some_db"."dbo"."table_2000"". The table either does not exist or the current user does not have permissions on that table.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-28 : 12:42:55

test_user is mapped to dbo schema? And has permissions?

data access is enabled in linked server?

seth2011
Starting Member

10 Posts

Posted - 2011-06-28 : 13:21:34

test_user is mapped to dbo schema? YES
And has permissions? YES

data access is enabled in linked server? YES

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources