4

SQL Server Agent has a 512 characters limit on texts fields like nvarchar(max). I found this out after seeing my stored procedure is working correctly when I run it in SSMS but not working when is being run by a SQL Server job.

To fix this I know I can use SET TEXTSIZE { number } in my stored procedure to increase this number. However I don't want to add this line in each and every stored procedure that are being run by SQL Server jobs. Is there a way that I can set this value for SQL Server agent itself?

Bahman
  • 1,034
  • 12
  • 28
  • 1
    Unfortunately I don't think it is possible. – Jan W Sep 02 '15 at 13:07
  • Thanks Jan, I ended up adding it to all sps. – Bahman Sep 03 '15 at 22:57
  • 1
    I might be a little too late. I hope that this helps someone. You can set the textsize In your sql agent step before executing the store procedure. Simply add the textize. Updating stored procedures is a overkill. e.g. SET TEXTSIZE 15000 EXEC dbo.yourspname param1, param2... Hope this will help someone. – Abe May 06 '16 at 13:51

1 Answers1

0

Hear my plea MS. Be kind to our eyes and allow us to increase the SQL Agent font size.

I use a sysinternals tool called ZoomIt. Pressing [Control] + [1] takes a snapshot of the screen and zooms in to various magnifications. It's a far cry from increasing the Agent job & steps, but works in a pinch.

Another work around is to return the job or it's steps in a query window.

select subsystem, step_id, step_name, command from sysjobsteps where job_id in 
(select job_id from sysjobs where name like '%Some Job with tiny screen letters%')
rmulkey
  • 1