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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.