September 17, 2010 at 3:27 am
Hi all,
Not sure if anyone else has run into this but we appear to be hitting a limit on the amount of characters logged to an output file from SQL Agent.
Basically we have a procedure which dynamically creates a SQL command and executes it. This procedure is executed within a SQL Agent job which logs its output to a file. Within this procedure we use a variable of type VARCHAR(max) to caputre the dynamic SQL, the content of which is printed at some stage in the procedure.
We have found that it only captures the first 2047 characters within the log file whenever this variable is printed. Further more in a simulated test I have found that selecting a VARCHAR(max) variable you will only get the first 1024 characters placed in your output logfile.
Not that much of a problem really as there are ways of getting around this, just wondering if anyone else has run into this issue?
Furthermore after looking at a different scenario it would appear that VARCHAR(max) and VARCHAR(n) are logged in different ways. Same test I ran above for VARCHAR(max) using VARCHAR(n), if you select the variable not only does it execute quicker and the output stored in the log file with less leading empty spaces, you get the entire n characters returned. However if you print the variable you still only get 2047 characters.
Very odd...
September 17, 2010 at 3:41 am
You get the same kind of limitation if you do a select of your varchar(max) in the management studio.
One way to get around it is to use a bcp out to get your varchar(max) in a file (put your variable in a global temp table ##).
You could also build a clr to write it to a file for you (might be overkill though).
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply