Link Server Issue

  • kd11

    Hall of Fame

    Points: 3447

    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?

  • curious_sqldba

    SSC-Dedicated

    Points: 36283

    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?

  • kd11

    Hall of Fame

    Points: 3447

    I'm still receiving the 7314 error message And I ran: osql -E -S linkservername -i "\\Path\instcat.sql" on both servers.

  • curious_sqldba

    SSC-Dedicated

    Points: 36283

    I still get my answer;-). What type of login are you using?

  • kd11

    Hall of Fame

    Points: 3447

    I've tried a windows login and a sql login.

  • Lowell

    SSC Guru

    Points: 323444

    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!

  • kd11

    Hall of Fame

    Points: 3447

    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?

  • Lowell

    SSC Guru

    Points: 323444

    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!

  • paul Arscott

    Mr or Mrs. 500

    Points: 504

    I have experienced Msg 7311 "Cannot obtain the schema rowset ..." before querying a SQL 2000 database via a linked server from SQL 2008. For some reason I has to apply SQL 2000 SP4 procedures again. Don't know why but this fixed it.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply