asked on

Changing Servers Names stored in a stored Procedure

I am not a DBA, i am a systems administrator and i usually only deal with Windows Servers and hardware, Recently our only programmer left the company. We have a very important script that runs every day. Last weekend we moved our maion database toa new server with a new server name. I can see in his stored procedures where he names the server i the select statement. When i change the server name i get the following message
"Error 7202" Could not find Server 'NewServerName' in sysservers. Execute sp_addlinkedserver to add teh server to sysservers.
I ran a query "sp_addlinkedservers NewServerName" and it said it completed successfully but when i go back to change teh stored procedure it comes up with the same messsage

pjam

I'm with you, DB is not my bailywick either. I do know though that when our old programmer left and we changed server anmes our new programmer was able to use an alias name until he re-wrote the program. Not sure how to do that but there may be something at Microsoft explaining it. I found this fairly easily and I am sure there is more:
http://support.microsoft.com/kb/265808

latexfoam

ASKER

Thanks for the response but in this situation the old database server and name are still active. That server has to stay up for our old ERP systems database, i moved only 1 database to the new server. If i create an alias then other stored procedures that reference the old server will not work

hyphenpipe

Is this stored procedure part of the database at the new location or on one you didn't move that was pointing to the old location?

latexfoam

ASKER

The stored procedure is actually on a seperate server all together. It actually pulls data from 3 other database server. So its running on a SQL server that is in our DMZ because it relays information to our bank and other customers. Lets call it ServerA. The Stored Procedure runs from ServerA and used to pull data From ServerB. I moved 1 database onto ServerC and need to figure out how to change the pointers that use to have ServerB in them to now say ServerC. When i change the server name in the stored procedure i get the message Error 7202" Could not find Server 'ServerC' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial