Linked Server or query

[Billw]

I have two servers registered Server1 and Server2. I want to create a stored procedure on Server2 to update a table in Server1 when a view in Server2 is updated.

Should I set up a Linked Server for Server2 or can I do this all in the Stored Procedure.

I have tryed to create a Linked Server on Server1 but always get an error

Server: Msg 6, Level 16, State 1, Line 1
Specified SQL server not found.

Here are the commands I ran in query analyzer

sp_addlinkedserver
'Server2',
N'SQL Server'


sp_addlinkedsrvlogin
@rmtsrvname = Server2
, @useself = false
, @locallogin = NULL
, @rmtuser = BILLW
, @rmtpassword = PWSRV2

THEN:
select * from
openquery (Server2, 'select @@serverName, user_name(), db_name(), @@spid')

And I get


Server: Msg 6, Level 16, State 1, Line 1
Specified SQL server not found.

Any thoughts

Thanks

[rmiao]

I think you should create linked server on server 2 that points to server 1.

[Billw]

Thanks for your response.

I just tried that and my issue now is the Error 6 Specified SQL server not found. This is in Enterprise Mgr after adding linked server1 as a SQL server using They will be mapped to RemoteLogin,RemotePassword.

These servers are on two different domains but I can register both in EntMgr and login using query analyzer. What am I missing on the linked server?

Thanks

[JBane]

Am I completely misreading this, or did you name your linked server 'Server2' and your openquery is using a server named PSOFT?

Jeff

[Billw]

I typed it wrong I used Server2 in the openquery. I have corrected my post thank you

[rmiao]

Did you see any linked server in em under server2?

[Billw]

Yes, I have added using query analyzer with the above script and have also added within EM. after adding if I refresh and click on the Linked Server I get the Error 6 Specified SQL server not found in EM, any thoughts?

Thanks

[rmiao]

Try run 'select @@servername' on both servers to see what do you get.

[Billw]

using @@servername on each server I get the respective server

Server1
Server2

[rmiao]

What do you get from sp_helpserver then?

[Billw]

The same except for when I add the linked server then I get the linked server name also

sp_helplinkedsrvlogin
I get the linked server and the remote login

sp_linkedservers

I get the local server plus the linked
server when it is added

Note - on this server I have a linked Oracle server that works fine.

thanks for your help.

Bill

[rmiao]

I never have problem to add linked sql server yet. Did you try the query again? How about query it with four part name?

[Billw]

No luck on the query I will try some more. If I can't get it to work I am going to set it aside and come back to it. I will let you know what happens.

Thanks for your help

[bannor]

I've just finished beating my head against a wall with linked servers on SQL7, and here's what I found I had to do each time

1) Open the Client Network Utility

Create and alias for the linked server, and use TCP/IP. Enter the TCP/IP address for the linked server. I made the alias the same name as what the linked server was called. After that all worked fine.


I was trying to connect from SQL7 to a SQL2000 server on the same domain. Linking from 2000 to 7.0 worked fine, going the other way the 7.0 db kept telling me the 2000 server did not exist. Perhaps this will work for you too...

[Billw]

I worked on this again yesterday, and you are correct. I did not have a set up in the client network utility on the server. Not thinking I just set it up on the workstation I was working from. Thanks for the reply.

Thanks rmiao for all your help last week also, I should have relized this earlier. Oh well on to the next issue.

Thanks again