I am trying to help a customer with the following error. Is there an issue with SQL Server 2005 running on a 64 bit Windows 2008 server? Or is there a flaw in his approach to migrating the dbs? If it matters, the apps that access the dbs are MS Great Plains V 10 and Softrax operations 9.0 SP1.
They have applied SQL SP 2, but are waiting for confirmation that Softrax will run on SP 3 before applying.
"I tried using the backup tool in sql and then restoring to the new machine. This seemed to work great until I got to the master database…it said I had to go into single user mode. This I did and was able to get a backup.
All seemed to restore on the new box. SQL would start will all databases copied over to the new box except the master DB. When I would copy that DB and try to start the service it would start/stop immediately
So I tried to restore all DB’s except master…I renamed the new box to “SQLServer" like the production box and tried to login with Great Plains. It would not authenticate to the new SQLServer. I can’t remember the message, but it was very similar to what you would get with an incorrect username and/or password.
Then the consultant came in and using SQL Management studio he connected to both SQLServers. Both could be seen in the management studio, but when he would try to run the database copy wizard….it would appear to transfer the data, but then error out near the end.. I did a test run of one of our non essential databases and attached the error.
Failed with the following error: "The procedure 'sys.sp_procoption' cannot be executed within a transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."