June 16, 2011 at 7:03 am
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
June 16, 2011 at 12:30 pm
Have you tried changing the provider to "Microsoft OLE DB Provider for SQL Server"
June 16, 2011 at 12:57 pm
Yes, I did. I still get 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".
June 16, 2011 at 1:25 pm
How about:
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER_2005', @srvproduct=N'SQL Server'
CEWII
June 16, 2011 at 2:28 pm
Tried it - same 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".
This still works after:
select top 10 * from OPENQUERY(SERVER_2005, 'SELECT * from [Test2005Database].[dbo].[Test2005Table]')
I log in to both servers (SERVER_2005 and SERVER_2008) using Windows authentication. I have admin rights on the 2008 box, and I own
Test2005Database on the 2005 box.
I have also tried creating a specific user (same name and password) on both boxes.
I'm at a loss. Anyone have an idea?
Thanks.
June 16, 2011 at 3:22 pm
It might be worth checking permissions by running this in the Test2005Database:
EXECUTE AS user = 'TEST_USER';
SELECT * FROM fn_my_permissions('dbo.Test2005Table', 'OBJECT')
REVERT;
Just to see exactly what the user can do.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 16, 2011 at 4:25 pm
entity_name permission_name
dbo.Test2005Table SELECT
dbo.Test2005Table UPDATE
dbo.Test2005Table REFERENCES
dbo.Test2005Table INSERT
dbo.Test2005Table DELETE
dbo.Test2005Table VIEW DEFINITION
dbo.Test2005Table ALTER
dbo.Test2005Table TAKE OWNERSHIP
dbo.Test2005Table CONTROL
June 16, 2011 at 4:27 pm
well that looks ok, can you do the same for the database permissions?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 16, 2011 at 4:35 pm
Just out of interest, what happens if you try to expand the Linked Server Catalogs in SSMS Object Explorer?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 17, 2011 at 8:07 am
>Just out of interest, what happens if you try to expand the Linked Server Catalogs in SSMS Object Explorer?
I can see all of the tables in the schema.
June 17, 2011 at 8:25 am
also out of mild interest, can you query ok from the command line on the 2008 server to the 2005 server using that user?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 28, 2011 at 10:41 am
I have the same problem trying to run a remote query from 2005 to a 2000 server:
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.
Is there some backward compatibility software I need to install?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply