Post reply

Linked server error

  • March 21, 2011 at 11:24 am

    #147613

    Hi,

    I am working on sql server 8.0.818 workgroup edition..

    I want to join a table from it to a different server having sql server 2005 enterprise edition.

    I created a linked server on the workgroup edition ...

    When i try to run hte query i get the following error:-

    Msg 17, Level 16, State 1, Line 1

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • March 21, 2011 at 12:58 pm

    #1301498

    ANY thoughts?

    Regards
    Sushant Kumar
    MCTS,MCP

  • March 21, 2011 at 1:02 pm

    #1301501

    diagnose the linked server before using it in your code;

    if you run EXEC sp_tables_ex 'LinkedServerName' do you get any results? in theory, it will return all available tables fo rthe default database of the login being used.

    If No results/error: let us know what the error is...did you set up your linked server to use a specified login(ie exec sp_addlinkedsrvlogin....)

    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!

  • March 21, 2011 at 1:17 pm

    #1301519

    Lowell (3/21/2011)

    diagnose the linked server before using it in your code;

    if you run EXEC sp_tables_ex 'LinkedServerName' do you get any results? in theory, it will return all available tables fo rthe default database of the login being used.

    If No results/error: let us know what the error is...did you set up your linked server to use a specified login(ie exec sp_addlinkedsrvlogin....)

    When I run exec sp_tables_ex'Linkedservername'

    I get error:-

    Msg 17, Level 16, State 1, Procedure sp_tables_ex, Line 20

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    The security in the linked server is:-

    local login : sa

    remote user :sa

    remote pasword: .....

    Connections will be made using the login;s current security context

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • March 21, 2011 at 1:38 pm

    #1301544

    you can set up the linked server so if a specific local user tries to use the linked server, it uses a different set of remote credentials instead:

    the way you describe it now, you have this set: that means pass my credentials on tis server over to the other: that works great for domain users(if they are on the same domain), but might not work on the other server(my SQL login 'bob' is not the same as your SQl login 'bob'

    you probably want to use the bottom option for your linked server; change it to look like this and try to run sp_tables_ex again.

    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!

  • March 21, 2011 at 2:12 pm

    #1301567

    I changed it to the bottom option and then run:-

    EXEC sp_tables_ex 'LinkedServerName'

    Msg 7202, Level 11, State 2, Procedure sp_tables_ex, Line 20

    Could not find server 'LinkedServerName' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

    (0 row(s) affected)

    Then i ran:-

    Exec sp_addlinkedserver [LINKEDSERVERNAME]

    i got:-

    Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79

    The server 'LINKEDSERVERNAME' already exists.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • March 21, 2011 at 2:20 pm

    #1301575

    'LinkedServerName' was the example...you never mentioned the name you gave that server, so all i could post was examples i thought would be intuitive enough that you knew you'd need to tweak the code

    what is your specific linked server name?

    exec sp_tables_ex [YourLinkedServer]

    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!

  • March 21, 2011 at 2:30 pm

    #1301586

    Lowell (3/21/2011)

    'LinkedServerName' was the example...you never mentioned the name you gave that server, so all i could post was examples i thought would be intuitive enough that you knew you'd need to tweak the code

    what is your specific linked server name?

    exec sp_tables_ex [YourLinkedServer]

    I wrote my server name in 'LINKEDSERVERNAME'...

    When i run :--

    exec sp_tables_ex [mylinkedservername]

    The query runs for 00:01:41 and then i get error:-

    Msg 17, Level 16, State 1, Procedure sp_tables_ex, Line 20

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • March 21, 2011 at 2:48 pm

    #1301592

    i think you are abstracting stuff out, and leaving out critical details.

    show us the EXACT command you ran. it really sounds like you say you did one thing, but actually did something else.

    for example, you can see on this picutre form my SSMS list of linked servers:

    these are the exact, working commands for me to query any of my linked servers:

    EXEC sp_tables_ex AccessDb

    EXEC sp_tables_ex DBSQL2K5

    EXEC sp_tables_ex DEV223

    EXEC sp_tables_ex HENNXLS

    EXEC sp_tables_ex MyLinkedServer

    EXEC sp_tables_ex MyOracle

    EXEC sp_tables_ex PRODUCTION

    EXEC sp_tables_ex TxtSvr

    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!

  • March 21, 2011 at 3:32 pm

    #1301614

    Attached.

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • March 22, 2011 at 4:39 am

    #1301774

    Hi Sushant,

    Did you add the login credentials you have in the server which you want to use it as linked server?

    Only issues that we generally face with the linked servers is the access.

    when you create a linked server using the script, first of all, add ur credentials in that server directly under security tab and give the login details and give the required server roles.

    You might have done this, but cross check once again.

  • March 22, 2011 at 7:07 am

    #1301853

    vishnubhotla.uday (3/22/2011)

    Hi Sushant,

    Did you add the login credentials you have in the server which you want to use it as linked server?

    Only issues that we generally face with the linked servers is the access.

    when you create a linked server using the script, first of all, add ur credentials in that server directly under security tab and give the login details and give the required server roles.

    You might have done this, but cross check once again.

    I am connecting it thru sa login..

    In the security tab of hte linked server , i have chekced for last option:-

    Be made using this security context :

    remote login : sa

    password : ....

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • March 22, 2011 at 10:29 am

    #1301980

    I meant to say that, directly connect to the server box and add the required credentials there itself, but not in the linked server created.

    Hope you have done this.

    If the problem still exists, then we need to dig it more to root out.

Viewing 13 posts - 1 through 12 (of 12 total)

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