Linked server error
-
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 -
ANY thoughts?
Regards
Sushant Kumar
MCTS,MCP -
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! -
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 -
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! -
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 -
'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! -
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 codewhat 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 -
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! -
Attached.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP -
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.
-
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 -
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