NTEXT data truncated when run from an Server Agent Job

Question

NTEXT data truncated when run from an Server Agent Job

Andy sql

SSCrazy Eights

Points: 9481
More actions
January 15, 2009 at 3:29 am

#198484

I am mystified by a problem when handling NTEXT data in SQL Server 2005 SP3. I often have to query 3rd-party stored procedures (which I cannot edit). I usually grab data from these SPs using an INSERT-EXEC.
In this case, the select statement returns an NTEXT field. Usually I insert this straight into nvarchar(max) without a problem. This works fine from a Query window.
BUT..... when the exact same code is run as an SQL Server Agent Job, the NTEXT data is truncated to 512 characters (which I guess equals 1024 bytes). To work around this, I changed nvarchar(max) to NTEXT, but the same problem occurs. This code sets up an example:
create table aaaNtextLog(
LogID int identity(1,1) primary key not null,
Param01 int,
Param02 nvarchar(max)
)
go
create procedure aaaNtextSelect
as
select cast(replicate('123456789 ',250) as ntext)
go
create procedure aaaNtextInsert
as
declare @tblNtext table (Param05 ntext);
insert into @tblNtext(Param05) exec aaaNtextSelect;
insert into aaaNtextLog(Param01, Param02) select len(cast(Param05 as nvarchar(max))), Param05 from @tblNtext;
go
When the SP is run from a Query Window
exec aaaNtextInsert;
select * from aaaNtextLog;
the resulting data is 2499 characters in length. But, when the exact same code is run as a step in an Agent Job, the data is truncated to 512 characters.
Any ideas?????? I have run the code as the same user which SQL Server Agent runs as, so I am fairly sure this is not the problem.
Andy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

I've verified your sample and its indeed truncating the result to 512 characters. I think SQL Agent's default TEXTSIZE is 1024, whereas SSMS' default size is 2147483647. So, you should try setting the TEXTSIZE option in the procedure itself. I tried myself and its working.

--Ramesh

Andy sql SSCrazy Eights Points: 9481 More actions · Answer 2

Hi Ramesh,

First off, thanks very much for investigating! And you are exactly right, specifying

SET TEXTSIZE { number }

within the Step of the Agent Job solves the problem. Fantastic, many thanks :hehe:

Andy

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

AndyD (1/15/2009)
Hi Ramesh,
First off, thanks very much for investigating! And you are exactly right, specifying
SET TEXTSIZE { number }
within the Step of the Agent Job solves the problem. Fantastic, many thanks :hehe:
Andy

Thanks for the feedback and I'm glad I could help you...:):)

--Ramesh

karsten.jacques SSC Journeyman Points: 93 More actions · Answer 4

karsten.jacques

SSC Journeyman

Points: 93

December 5, 2013 at 4:50 am

#1670999

Thank you Ramesh. Did the trick.