January 12, 2007 at 2:15 pm
I am experiencing a very strange issue with SQL Server. I am still kinda new with the company, so I will not be able to explain the "why" behind somethings as you will soon see.
We have a staging and production environment;both clustered, SQL Server 2000, SP4, and on Windows 2003. Until yesterday both of the sql instances were even named the same. Obviously, this caused problems when trying to connect from one server to the other in order to migrate data.
Well, today I changed the staging server name via the following commands (followed by a restart of the SQL service):
sp_dropserver 'OLDSERVER'
sp_addserver 'NEWSERVER', 'local'
UPDATE sysjobs SET originating_server = 'NEWSERVER' where originating_server = 'OLDSERVER'
Everything looks good. I verifed the name change with "SELECT @@SERVERNAME", and the linked servers correct, but I am still having a strange issue with DTS.
When I get to the "Specify Table Copy or Query" step I usually like to select the "Copy objects and data between SQL Server databases" radio button because I little bit more flexibility regarding the copy, but I still get an error that reads "You cannot copy objects when the source and destination databases are the same". Although, if I click "OK" here, and choose the "Copy table(s) and view(s) from the source database" I have no problem copying or appending the tables or views to the destination.
I appreciate anyone's thoughts on this.
January 15, 2007 at 3:32 am
you might want to check the net name , you could have just changed the netname to allow the links to work using sp_setnetname. Could be the connections are still within the packages - renaming servers is not a task to be taken lightly, especially on a cluster.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply