Migrating a database from SQL 2005 to SQL 2008

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.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Migrating a database from SQL 2005 to SQL 2008

Author

Topic

jford
Starting Member

19 Posts

Posted - 2010-05-14 : 11:27:21

I have been having an issue with migrating a website with a backend of sql 2005 database to a backend of a sql 2008 database. I've tried making using copy wizard, backup and restore and detach, copy reattach. The first symptom I receive is the user account(dbo) that the website accesses can't access the database. I reset the password and I start getting
Exception Details: System.Data.OleDb.OleDbException: Could not find stored procedure 'rsp_GetSetting'.

but I can still point the website to the old sql server and have it work. This is a major hold up in my project.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-14 : 12:01:32

In the restored database have you tried sp_change_db_owner?

No, you're never too old to Yak'n'Roll if you're too young to die.

jford
Starting Member

19 Posts

Posted - 2010-05-14 : 12:17:42

why would the database owner change when doing a detach and attach?

Kristen
Test

22859 Posts

Posted - 2010-05-14 : 13:01:18

If you moved the database to a different machine then you have to re-synchronise the IDs for teh Database Users with the System Logins (which are part of SQL Server, not part of the database itself)

If you restore / re-attach to the original SQL Server then the IDs will match, and thus the problem does not arise.

jford
Starting Member

19 Posts

Posted - 2010-05-14 : 13:54:05

is there a difference than making the users the owner using the gui? We also encountered a similar problem trying to reattach the database to the original server.

jford
Starting Member

19 Posts

Posted - 2010-05-14 : 14:06:41

I did this command
USE database
GO
EXEC sp_changedbowner 'account'
GO

and this didn't resolve the issue

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-14 : 15:01:10

[code]
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
GO

DECLARE @SQL varchar(100)

DECLARE curSQL CURSOR FOR
select "exec sp_change_users_login 'AUTO_FIX','" + name + "'"
from sysusers
where issqluser = 1 and name NOT IN ('dbo', 'guest')

OPEN curSQL

FETCH curSQL into @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
--EXEC (@SQL)
PRINT @SQL
FETCH curSQL into @SQL
END

CLOSE curSQL
DEALLOCATE curSQL
go

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jford
Starting Member

19 Posts

Posted - 2010-05-14 : 15:09:45

what does that do? I tried it but it didn't help.

Here is the actual error.

Server Error in '/H' Application.
--------------------------------------------------------------------------------

Could not find stored procedure 'rsp_GetSetting'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Could not find stored procedure 'rsp_GetSetting'.

Source Error:

[No relevant source lines]

Source File: c:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files\honeywell\5d399f22\3dddc5b3\App_Web_iwfkbcnu.1.cs Line: 0

Stack Trace:

[OleDbException (0x80040e14): Could not find stored procedure 'rsp_GetSetting'.]
Prezza.Web.PageBase.AppPage.OnError(EventArgs e) in C:\Prezza\Survey305Solution\UltimateSurvey\Base\AppPage.cs:425
System.Web.UI.Page.HandleError(Exception e) +109
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +11041499
System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +11041050
System.Web.UI.Page.ProcessRequest() +91
System.Web.UI.Page.ProcessRequest(HttpContext context) +240
ASP.default_aspx.ProcessRequest(HttpContext context) in c:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files\honeywell\5d399f22\3dddc5b3\App_Web_iwfkbcnu.1.cs:0
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +599
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +171

jford
Starting Member

19 Posts

Posted - 2010-05-14 : 15:38:39

I executed
exec sp_change_users_login 'AUTO_FIX','INFORMATION_SCHEMA'
exec sp_change_users_login 'AUTO_FIX','sys'
exec sp_change_users_login 'AUTO_FIX','user'
exec sp_change_users_login 'AUTO_FIX','surveyDev'

and it fixed user but got this error.
Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 35
Terminating this procedure. 'INFORMATION_SCHEMA' is a forbidden value for the login name parameter in this procedure.
Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 35
Terminating this procedure. 'sys' is a forbidden value for the login name parameter in this procedure.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 1.

The problem still occurs though.

jford
Starting Member

19 Posts

Posted - 2010-05-14 : 16:53:01

I'm very new to the sql administration stuff. I'm not sure what the next step would be.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources