Link Server Issue
-
On ServerA (a 2000 SQL Server running sp4) I created a link server ServerB which is a 2005 SQL Server, I have both SQL logins & windows logins set up on both servers with permissions, and "Be made using login's current security context" set. When I run a query from ServerB I receive
"Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "AFDCSQL3". The provider supports the interface, but returns a failure code when it is used."
But when I run a query from ServerA I receive:
"Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'ServerB' does not contain table '"DBName"."Test"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ServerB', TableName='"DBName"."Test"'].
Which is not true because I was able to query the database an table "Test" with that login.
What am I missing?
-
When you select the option "Be made using this security context" what login are you using? If you using windows login it will not work since there are couple more things which you need to change at windows level like delegation. Using sql login is best. Can you try that and let us know?
-
I'm still receiving the 7314 error message And I ran: osql -E -S linkservername -i "\\Path\instcat.sql" on both servers.
-
I still get my answer;-). What type of login are you using?
-
I've tried a windows login and a sql login.
-
you had previously said you had this setting "Be made using login's current security context" set."
The typical issues for linked servers tend to be related to which security context you are using, and sometimes what is the default database of the remote user you are using, can you just confirm that this is the setting you are using? we really need to know EXACTLY how you set this portion of the screen up:
can you show us the exact command that you ran that raised the error?
was it really select * from SeverB.DBName.Test?
a linked server needs to be a 4 part naming convention, like ServerB.Databasename.dbo.Test, for example, in order to go to the correct object...i think it's a syntax issue and not a linked server issue.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
My statements is:
Select * from LinkingServer..Databasename.TableName
Since I'm going from a 32 bit SQL Server 2000 machine to a 64 bit SQL 2005 Server I believe I need to create or use a 32 bit DSN? I was using the OLE DB which seem not to work. Is this correct?
-
the SQL statement is wrong.
if you are going to use double dots, which tells the linked server to get the default object, it is like this:
it has to be Select * from LinkingServer.Databasename..TableName
or
Select * from LinkingServer.Databasename.dbo.TableName
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply