July 23, 2007 at 7:32 pm
I wanted to have one master stored procedure that will create some 20 other stored procedures. I know I can combine all the source of the 20 stored procedures into one master file to do this.
But what I really wanted is to keep the "Create Procedure" code in their separate file and have the master stored procedure invoke them to create the procedures. Is this possible?
Note: I am not talking about executing the stored procedures but creating them.
Tony
July 23, 2007 at 10:51 pm
You can't do this in SQL server. You are expecting something equivalent to ORACLE package which is not available in sql. The best option would be to create a batch file to call the 20 SPS 1 by 1.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 24, 2007 at 12:37 am
Possibly, but it is not simple. Would require that your master call out to the command prompt and run SQLCMD, passing it the files to run. Something like this might work in the master proc.
EXEC master.sys.xp_cmdshell 'SQLCMD -S <servername> -E -i <filename>"
Replace the stuff in angled brackets with actual values.
This, of course, asumes that you have xp_cmdshell enabled on you server and that you've set up any necessary proxy accounts for it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2007 at 7:18 am
You need not have xp_cmdshell enabled. You need to run that from the DOS and not from sql server.
put all those as a batch script and call the batch script to create each of your procedure.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 24, 2007 at 8:20 pm
Thank you all!
Tony
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply