Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Daniel_Buchholz
Starting Member
28 Posts |
 Posted - 2008-02-11 : 03:28:44
|
Hi everyone!I have one big problem after we moved one of our server to a new data center. After the move the IP address of the server changed as well.We have several BCP commands in our stored procedures which are now failing. Obviously the server is not able to access itself with @@servername. To test I created the following:DECLARE @sServer varchar(100)DECLARE @sBcpCommand varchar(1000)SET @sServer = @@ServernameSET @sBcpCommand = 'bcp "SELECT * from master..sysdatabases" queryout "c:\test.txt" -S' + @sServer + ' -U[User] -P[Password] -c -Craw'EXEC master..xp_cmdshell @sBcpCommand This is the code that fails. The response is "Server does not exist or access denied". However if I place the servers IP into @sServer or even a DNS CNAME of the server everything is OK and I get the results.@@Servername resolves to "MFMDB02" which is correct and that correctly resolves to the server's IP address. So I don't really understand what is going on.Additionally, the server stopped logging Sql Server Agent job activity to the sysjobhistory table on the day the server moved. I saw in some system procedures that access to the log is as well done using @@servername so I think this might be the same problem here.What can I do? Do we have to rename the server and try again?Daniel |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-11 : 23:05:37
|
| Did you rename server? |
 |
|
|
Daniel_Buchholz
Starting Member
28 Posts |
Posted - 2008-02-12 : 02:31:32
|
| No, the server was not renamed. Only the IP address changed.Daniel |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2008-02-15 : 11:26:20
|
| Have you tried restarting the services?Have you tried flushing the DNS cache on the local machine (cmd line "ipconfig /flushdns")I dont know if the sql services store dns or ip internally for any period of time. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-16 : 00:43:44
|
| Did you create server alias on the machine? |
|
|
|
|
|
|
Access to server with @@servername fails