April 1, 2010 at 8:37 am
I have a query that I am testing in SQL Server 2005.
In that query, I have a varchar field that I declare that is 8000 characters long. When I run the query manually in SQL Server Management Studio by selecting the database and clicking on ‘New Query’, the full field (upto 8000 characters) is displayed in the query results window.
However, if I set up a SQL Server Agent Job (SQL Server Management Studio-->SQL Server Agent-->Jobs), identify the Type as T-SQL, copy the same query into the Command box, indicate that the output is to be written directly to a file, and then run it as a scheduled job, only the first 2048 characters of the field are written.
Is there a setting somewhere that I can set so the full 8000 characters are written when writing directly to a file?
April 1, 2010 at 9:29 am
Are you writing the output to a named file or to job history?
IIRC the job history table has the 2048 limit.
April 1, 2010 at 9:34 am
I am writing the output to a named file.
April 1, 2010 at 9:42 am
Two suggestions, then:
1. Write to job history instead - see http://www.mssqltips.com/tip.asp?tip=1394 for details; or
2. Wrap the thing in a call to SQLCMD instead and use the output option. Use step type CmdExec and a proxy.
April 1, 2010 at 11:26 am
I write the results of the query that I am running to a named file because the named file, in turn, is then used as input to a job running on a separate system outside of my system.
With this additional information, is writing to the job history still a viable option? I confess that I am still a relative newcomer to SQL Server, but it doesn't seem that that would be an option.
Please let me know what you think.
Thanks!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply