March 5, 2008 at 8:55 am
I'm not sure this is the right forum but it sounded best for my question.
I want to be able, while running an update script for a client, to look at a value in the database and decide whether or not I should replace an existing stored procedure. If the result is yes, replace it, I want to execute an external script to replace the stored procedure. For example:
update.sql containts
--
-- create new table
-- grant permissions
-- add column to existing table
-- add stored procedure for new table
exec somefunction "c:ew_table_sp.sql"
-- replace existing stored proc
if exists(select 'x' from control_table where table_name = 'existing_table' and replace_sp = 'Y')
begin
exec somefunction "c:\existing_table_sp.sql"
end
Does the "somefunction" exist?
Thanks in advance for your help
Jay Falck, CISSP, CHSS
Unicorn Computing
sqlservercentral@unicorncomputing.com
March 8, 2008 at 2:52 pm
Well you could use [font="Courier New"]xp_CmdShell[/font] to run [font="Courier New"]osql[/font] that could run your script, though frankly I wouldn't recommend it.
You really ought to be able to use a combination of dynamic SQL and stored procedures to accomplish the same result and I think that you would be much better off if you did. This is how I always do it and the improved error-handling alone makes it worth it (not to mention all of the security headaches of trying to route through DOS and back into SQL Server).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2008 at 4:41 pm
It appears what I was looking for was :r but that won't really get me where I need to be.
Jay Falck, CISSP, CHSS
Unicorn Computing
sqlservercentral@unicorncomputing.com
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply