April 5, 2005 at 8:03 am
What is the best way to execute a SQL script in batch?
I have a dynamically changing script --- which needs to run every nite. Each nite the script contents will be different. I am very familiar w/ DTS, XP_CMDSHELL, SQL Stored Procs but I've never had this request before.
Unfortunately, I cannot stored proceduralize the script because it fluctuates from day to day. XP_CMDSHELL invokes DOS commands. DTS invokes inline "static" scripts (via EXEC SQL Task) or invokes canned stored procs.
SIMPLE Samples of the changing SQL Script:
Monday nite: INSERT INTO TBL_A SELECT * FROM TBL_B
Tuesday nite: CREATE TBL_X .....
Wed nite: DROP INDEX abcd
--And I'm a DBA - not really a developer. I'd prefer to do this thru one of the options listed above. - any idea's?
April 5, 2005 at 9:01 am
Who or what manages the changes to the script? Is Monday always the same? If monday, Tues, etc... are always the same (but different from each other) then you'll need to setup a job for each of them.
If all you need to do is execute a script file each night and don't care what the contents of the script are then there are several ways to do that. Probably the most direct and simplest is to create a job that uses xp_cmdshell to execute kick off osql...
If you need to manage the contents of the script (what it does) dynamically then you will have to define parameters and conditions before I could be much more specific in my recommendation.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 5, 2005 at 11:47 am
how about using datename(dw, getdate())
I use this for my backup scripts. For my dev and stage servers if the result is Sunday then it does a full backup, otherwise it's a differential. This way you could have a single proc that just checks the day of the week and does the according work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply