Post reply

Calling a stored procedure within a cursor loop

  • September 2, 2002 at 11:38 pm

    #435813

    I hop I'm understandin the problem...

    When I try it, the USE statement seems to run OK but then revert back to the original database after the execute has run. If I append the T-sql to the end of the Use statement and execute the lot it works fine.

    It's messier as it makes it harder to write/debug so if there's another way I'd be interested.

    Cheers,

    Mike

  • September 6, 2002 at 12:26 am

    #436046

    An Alternative is not to use the USE statement and fully qualify the objects you are refrencing. That works for me.

  • September 6, 2002 at 4:46 am

    #436064

    ifmanish the use only remains in effect for the duration of the EXECUTE process. Any code outside this will remain in the same DB context.

    However to save soem developement hassels and to get to work as you need try something like this.

    EXEC sp_MSForEachDB '

    USE ?

    GO

    yourcodehere, --keep in mind double all single quotes to work properly.'

    Also you may want to use the fully qualified name if possible which would look sorta like this.

    EXEC sp_MSForEachDB '

    DROP TABLE ?.dbo.tempimp'

    The key is the ? with the system SP is replace with the database name and the code is executed. There is also an sp_MSForEachTable that works similar but they cannot be used together.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 3 posts - 16 through 17 (of 17 total)

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