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
latexfoamAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
here the procedure you are using, as you know:
http://msdn.microsoft.com/en-us/library/ms190479.aspx

so, you need to run something like:
EXEC sp_addlinkedserver    N'ServerC',   N'SQL Server'; 

Open in new window


after that, you might need to check the security stuff of the linked servers, compare ServerB with serverC
you best do that in the SQL Management Studio connected to ServerA, and check the linked server folder in server objects
0
 
pjamCommented:
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
0
 
latexfoamAuthor Commented:
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
0
 
hyphenpipeCommented:
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?
0
 
latexfoamAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.