I've recently moved the SQL Server 2000 to a new server. SQL Server is unchanged but the OS is changed. The OS version of the old box is Win 2000 and the new box is Win 2003. I am wondering if this is the reason why I am no longer be able to see the status, start or stop SQL Server Agent from my computer. I have no problem with that if I do it from the server either in Enterprise Manager or from the Services. Another thing is broken after the move is that the store procedure that references the linked server returned the error.
Error Diagnostic Information
ODBC Error Code = 42000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
SQL = "exec GetContentInfo @SiteID=379, @ContentID=7601"
Data Source = "COMMONSITEINFO"
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (25:3) to (25:48).
The error occurred while processing an element with a general identifier of (CFRETHROW), occupying document position (246:11) to (246:21).
Date/Time: 05/21/08 10:49:11
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1)
Remote Address: 220.127.116.11
This is the code of the store procedure:
IF @Status = 1
SET Hits = Hits+1
WHERE ContentID = @ContentID AND SiteID = @SiteID
If I remember correctly, these are the outline of what I dd to migrate the data after the new OS (Wind 2003( has been installed on the new server
1. Install SQL Server on the new server
2. Apply the patch and hot fix on the new server so that the versions are the same on both old and new servers.
3. Shutdown SQL services on the both servers
4. Copy all data files and log files
5. Have the system admin to name the new server to be the same as the old server.
6. Restart SQL Server services on the new server.
Everything is working fine except for the above two problems. Could it be because the security is more tighten in Win 2003? This is not the first time this SQL Server is moved. The first time it was moved the OS version was unchanged and everything was working fine after the migration.
I am stuck and this is the only place I think I can get help from.