executing sp_changeownerdb

29 Sep 2005 2:21 PM

Jason

Hi,

I've a problem executing sp_changedbowner in my stored procedure which
is saved in the master's stored procedures.

The code fails after executing @proc1. The errors says that i could not
find proc2.

Here's the code:

CREATE PROC usp_RestoreDB(
@db_name varchar(10),
@backup_location varchar(255),
@login varchar(20)
)
AS

DECLARE @proc1 varchar(100), @proc2 varchar(100)
SET @proc1 = @db_name + '..sp_fixusers'
SET @proc2 = @db_name + '..sp_changedbowner @loginame = ' + @login

RESTORE DATABASE @db_name
FROM DISK = @backup_location
WITH REPLACE

Begin
EXEC @proc1
EXEC @proc2
END

29 Sep 2005 2:39 PM

Alejandro Mesa

Try,

....
exec (@proc1)
exec (@proc2)
....

AMB

Show quote

"Jason" wrote:

> Hi,
>
> I've a problem executing sp_changedbowner in my stored procedure which
> is saved in the master's stored procedures.
>
> The code fails after executing @proc1. The errors says that i could not
> find proc2.
>
> Here's the code:
>
> CREATE PROC usp_RestoreDB(
> @db_name varchar(10),
> @backup_location varchar(255),
> @login varchar(20)
> )
> AS
>
> DECLARE @proc1 varchar(100), @proc2 varchar(100)
> SET @proc1 = @db_name + '..sp_fixusers'
> SET @proc2 = @db_name + '..sp_changedbowner @loginame = ' + @login
>
>
> RESTORE DATABASE @db_name
> FROM DISK = @backup_location
> WITH REPLACE
>
> Begin
> EXEC @proc1
> EXEC @proc2
> END
>

29 Sep 2005 10:40 PM

Hugo Kornelis

On Thu, 29 Sep 2005 16:21:18 +0200, Jason wrote:

Show quote

>Hi,
>
>I've a problem executing sp_changedbowner in my stored procedure which
>is saved in the master's stored procedures.
>
>The code fails after executing @proc1. The errors says that i could not
>find proc2.
>
>Here's the code:
>
>CREATE PROC usp_RestoreDB(
>@db_name varchar(10),
>@backup_location varchar(255),
>@login varchar(20)
>)
>AS
>
>DECLARE @proc1 varchar(100), @proc2 varchar(100)
>SET @proc1 = @db_name + '..sp_fixusers'
>SET @proc2 = @db_name + '..sp_changedbowner @loginame = ' + @login
>
>
>RESTORE DATABASE @db_name
> FROM DISK = @backup_location
> WITH REPLACE
>
>Begin
>EXEC @proc1
>EXEC @proc2
>END

Hi Jason,

Try changing the logic for proc2 to

(...)
SET @proc2 = @db_name + '..sp_changedbowner'
(...)
EXEC @proc2 @loginame = @login
(...)

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)