Post reply

'Use DB' option in Stored procedures

  • July 13, 2009 at 5:58 pm

    #132044

    I have some of the commands in my SP like below but not able to use 'Use Database' option , any workaround?

    sp_helpfile

    sp_start_job

  • July 13, 2009 at 6:07 pm

    #1024192

    Hi,

    Just want to make sure that I got you question right.

    Do you mean you are getting some kind of error message when you are trying to execute sp_helpfile and sp_start_job. If yes, then can you please give the error message.

  • July 13, 2009 at 6:10 pm

    #1024194

    If I read your question right you are trying to do a USE statement in a sproc and it won't let you. MY guess is that you are doing a USE msdb so you can execute the start job.. You can reference the sproc in the database directly..

    EXEC databasename.dbo.sprocname

    or

    EXEC databasename..sprocname

    I recommend the first way, it performas faster, or at least MS says it does..

    CEWII

  • July 13, 2009 at 6:26 pm

    #1024202

    You can try like this...

    DECLARE @SQL NVARCHAR(1000)

    DECLARE @DBNAME VARCHAR(40)

    SELECT @DBNAME = 'MSDB'

    SELECT @SQL = 'EXEC'

    SELECT @SQL = @SQL + ' '+@DBNAME +'..sp_helpfile'

    EXEC SP_EXECUTESQL @SQL

    Thanks
    Jay
    http://www.sqldbops.com

  • July 13, 2009 at 7:23 pm

    #1024211

    Guys you rock ,thnx

  • July 17, 2009 at 12:44 am

    #1026029

    it looks like i am stuck with dbcc shrinkfile , any idea how i can use the database name in the stored procedure for dbcc shrinkfile with specific db?

  • July 19, 2009 at 9:20 pm

    #1026701

    DECLARE @varname varchar(255)

    SELECT @varname = 'DBCC SHRINKFILE( ' + @FileNameWeGotSomewhereElse + ' ) '

    EXEC ( @varname )

    Comes to mind..

    CEWII

Viewing 7 posts - 1 through 6 (of 6 total)

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