locked
agent job to create a store procedure RRS feed

  • Question

  • I'd like to schedule a sql agent job to drop a exiting procedure and create a new one based on the previous procedure.
    For example, the procedure name is spTest, in my script which has the following steps:
    1. drop procedure spTest
    2. Select [text] from sys.syscomments where text like 'spTest'

    So I am able to retrieve the content of the procedure and save it to the string variable like varchar(max). What could I do to get the content of sp executed in the script.

    Thanks for the help

     

     

     

    Wednesday, August 19, 2009 2:45 PM

Answers

  • You can do this too, which is safer

    DECLARE	@code VARCHAR(MAX)
    
    SELECT	@code = [definition],
    FROM	sys.sql_modules
    WHERE	OBJECT_NAME(object_id) = 'spTest'
    
    DROP PROCEDURE spTest
    
    EXEC(@code)
    


    But it is still just patching the symptoms of the problem...
    • Marked as answer by happycoding Wednesday, August 19, 2009 4:55 PM
    Wednesday, August 19, 2009 3:25 PM

All replies

  • sory, I could to

    select [text] from sys.syscomments where text like 'spTest' first, then drop procedure sptest. Could save the result to .sql file, then use osql to execute the script?

     

    Wednesday, August 19, 2009 3:21 PM


  • Follow steps

    1. Save the text from syscomments into a variable
    2. drop procedure
    3. execute save text file as sp_execute


    but i don't understand what is its need to do.
    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Wednesday, August 19, 2009 3:41 PM
  • I used Peso's idea, working as I expect. This is the temp fix for problem procedure. I will post my code on the other thread.

     

    Thank everybody

    Wednesday, August 19, 2009 4:50 PM
  • You are right. sys.sql_mdules is very good system view to know. Thanks.
    Thursday, August 20, 2009 2:23 PM