April 12, 2006 at 4:02 pm
I was setting up a new job in Enterprise Manager that uses SQL code. When I browsed to get the SQL code I got a message that said there wasn't enough available free space to open the SQL file. It asks do you want to continue Y/N, if yes, the code will be truncated to 3200 characters. Is there a way to change the size so that you can bring in bigger SQL code files?
April 12, 2006 at 5:02 pm
The command text of a job step is limited to 3200 chars.
[@command =] 'command'
Is the command(s) to be executed by SQLServerAgent service through subsystem. command is nvarchar(3200), with a default of NULL.
When you say you want to bring in bigger SQL code files, the key word here may be "file". If you saved the SQL code in a script file, the job step to run it would be a simple Operating System Command step that uses osql.exe (or sqlcmd.exe on SQL 2005) to execute it. Pro: you can edit the file to modify the job without going through all the dialog boxes. Con: the file could be moved or deleted and kill the job.
Alternatively, put the code in a stored proc and call that. Stored procs over 4000 chars are stored in multiple syscomments records, so you wouldn't run into the size limitation.
April 13, 2006 at 11:29 am
Thanks, Scott. I was wondering about a stored procedure. I will give that a try.
April 13, 2006 at 2:20 pm
Sometimes SPs just won't do - especially if there is a lot of DDL involved. For these 'special' cases I'd use CmdExec and OSQL instead !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply