Knowledgebase
MS SQL 8 upgrade to 10
| Posted by lazat, 03-25-2009, 04:54 PM | | Hi
I try to relocate a database from my MS-sql8 server to a MS-sql 10. I can't get it to work as it should on the new host.
I have used the deattach / atttach method and after that i can login to the db in the new server and make selects. However stored procedures or views does not work. If i try to access them from a vbscript it get an error telling me that the stored procedure does not exicst. If I use the admin tool I can verify that it is there.. Can someone explain what to do? the db is owned by the user and should have all user rights thats needed, but apparently i guess that something is missing.
|
| Posted by plumsauce, 03-25-2009, 07:02 PM | | You have orphaned users.
There is a stored procedure you can use to fix this.
You can verify this by trying to login as the web user in the admin tool. It will throw an error.
|
| Posted by domainworldaccess, 03-25-2009, 11:11 PM | | A colleague just went through this. Here was one good piece of advice: "I would recommend the free Upgrade Advisor Tool for SQL Server. Search for this tool on www.microsoft.com/sql. It will look at your code in stored procedures and tell you what will not work. You can also run a trace to capture any dynamic sql coming from your app and run that through this tool as well."
Which was followed with: "Another question you should consider is “how much SQL code is in the application”. If the answer is none then the SQL Upgrade Advisor will fit your needs. If you have embedded and/or dynamic SQL then SUA will not help."
Good luck,
Joe
|
| Posted by RBBOT, 03-26-2009, 04:13 PM | | Your issue is very probably the orphaned users that plumsauce suggested. See:
http://support.microsoft.com/kb/274188
However, after restoring a database to a newer version of sql, you should run DBCC CHECKDB as the newer verison often finds and corrects minor errors in the database file that the older verison didn't spot. I regularly find this when restoring SQL 2000 databases to SQL 2008.
|
| Posted by lazat, 03-26-2009, 04:59 PM | | ok I did as plumsauce said but the web user is not allowed to run that command.. grr It works if i use "sa" acccount to some extend but it just runs and runs and its never comopleted so i stop the process. I have also tried Upgrade Advisor Tool for SQL Server and i can run that applicaton on the ms 8 server with some errors that they say that i should fix. but i still have problems.. I don't know where to start, all seams to end up in a dead end.
|
|
 Add to Favourites
 Print this Article
|
Also Read
