Post reply

can''t run sp_changedbowner from sproc

  • August 16, 2007 at 8:58 am

    #373279

    I am trying to run 'sp_changedbowner' inside a sproc that loops through a list of tables.

    The code for the sproc looks like this:

    set @SQLString = N'sp_changeobjectowner ''User1.'+ @ObjectName + ''', ''User2'''

    PRINT @SQLString

    EXECUTE @SQLString

    The "PRINT" statement above generates the following SQL statement:

    sp_changeobjectowner 'User1.PRI_0_Refresh_JXG', 'User2'

    However, when I run the sproc, I get the following error:

    Msg 2812, Level 16, State 62, Procedure P_NPM_ChangeSubpoolOwnerUser2, Line 57

    Could not find stored procedure 'sp_changeobjectowner 'User1.PRI_0_Refresh_JXG', 'User2''

     

    The most peculiar thing is that if I run the EXACT statement generated by the PRINT statement in the sproc (sp_changeobjectowner 'User1.PRI_0_Refresh_JXG', 'User2') in the EXACT same query window that I run the sproc - it runs fine.

    What am I missing?  Please help - I can't afford any more gray hairs!

     

     

     

     

    Have a good day,

    Norene Malaney

  • August 16, 2007 at 9:32 am

    #727399

    Norene,

    Try putting the string variable that is executed in parens e.g.

    EXECUTE (@SQLString)

    Greg

     

     

    Greg

  • August 16, 2007 at 9:37 am

    #727409

    Thank you!!!!!   I can't believe that it was that simple (just a pair of parens). 

    Have a good day,

    Norene Malaney

  • August 16, 2007 at 9:43 am

    #727416

    It's always the simple things that are the most maddening!

    Greg

    Greg

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply