Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2005-12-01 : 02:32:08
|
| How do I schedule a script?I thought I would just COPY it into Job, but the COMMAND field doesn't seem to take very much text.It can't be run as an SProc 'coz there's lots of GOs in there and I need to avoid distributed transactions.I suppose I could schedule an OSQL session - and I could bung the output to a file, and then stick that somewhere / import it - is that the way to go?Kristen |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2005-12-01 : 02:39:21
|
quote:
I thought I would just COPY it into Job, but the COMMAND field doesn't seem to take very much text.
then create multiple steps in that job-----------------[KH] |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-01 : 03:27:56
|
| "then create multiple steps in that job"I started doing that, but each one is a really small amount of text.My script is 3,800 lines long (185K). Looks like it will only allow me to enter around 3K per step. Is there a way around that?[later] BoL says "... the command(s) to be executed by SQLServerAgent service through subsystem. command is nvarchar(3200) ..." - that's pretty tiny for what I need to do, and will be a nightmare if I need to modify the script in the future (although I like that idea that a single step can abort the whole process if it fails)Kristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-01 : 09:43:33
|
| 3,800 lines of a query. What is that you are doing?1. Do you have email configured for your SQL Server? Would it be possible to email the query to the server and have the PROCESS_MAIL running to catch the mail and then process it as a dynamic query? (Just a wild thought.)2. Could you store the query in a table, and then have a job that reads the query and executes it as a dynamic query? I know you couldn't declare a TEXT variable, but you could perhaps use READTEXT in chunks and place 8000 bytes in each of several variables, and then do an sp_executesql (@var1 + @var2 + @var3 etc). According to BOL sp_executesql inputs have to combine to an NTEXT so this might be possible. (Altnernative just break up the string into 8000 byte chunks in the table where you store it so you can just read them all back and not have to waste time with READTEXT and pulling the one string apart.)3. Why not have some coder on staff write a simple little .EXE that will run the command, and simply schedule a job that kicks off that program?So many options you'd think you were in a candy store |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-01 : 09:59:13
|
"What is that you are doing?"I get given a couple of tables full of Product/Price/Stock Level. This has to be "massaged" into the appropriate data for a whole series of "more normalised" tables in our application. Unfortunately there is no "This changed on THIS date" type column, so lots of processing is done to find the relatively few bits of actual changed data; then just the relevant rows gets copied to a linked server.The script itself doesn't change - so doesn't need to be "dynamic" as such.I like (2) - executing it from a TABLE is a nice idea. I only have SQL access to the server (its a client box, not one of ours) so anything COMMAND LINE will be hard work, and this would solve that problem too.I doubt [but would have to check] that any individual statement exceeds 8,000 bytes.AND [unlike OSQL etc.] I could easily abort if one statement failed.I like (3) less because it adds more things to fail, and the client's coding style lacks anything that I would remotely refer to as "defensive programming"  Where's that MunchingCandy emoticon?! TBH I had just assumed that you could shovel a massive script into the SQL Agent's JOBs thingie - never occurred to me that it was a sawn-off sort of a tool  Kristen |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-12-01 : 15:26:59
|
| Could you put it into a DTS - the EXECUTE SQL understands GOsteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-01 : 15:42:38
|
| I'd put it in a sql file, exceute the sql file via osql.exe, call osql.exe from a CmdExec job step. If you wanted to execute multiple sql files, then i'd call each with osql.exe, wrap those calls into a cmd file, then call the cmd file from a CmdExec job step.Tara Kizeraka tduggan |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-12-01 : 16:47:50
|
| If the script doesn't change, why not make the part between each GO into a stored procedure? It may be more work in the short term, but it would give you a lot more control for things like flow of control and error checking.To make it easier to set up in a job, you could create a master proc that calls all the others.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Schedule a long script