Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

NTEXT data truncated when run from an Server Agent Job Expand / Collapse
Author
Message
Posted Thursday, January 15, 2009 3:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:30 AM
Points: 1,049, Visits: 1,117
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
Post #636939
Posted Thursday, January 15, 2009 7:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
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

Post #637138
Posted Thursday, January 15, 2009 8:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:30 AM
Points: 1,049, Visits: 1,117
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

Andy
Post #637169
Posted Thursday, January 15, 2009 8:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
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

Andy


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


--Ramesh

Post #637188
Posted Thursday, December 5, 2013 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:11 AM
Points: 7, Visits: 29
Thank you Ramesh. Did the trick.
Post #1519966
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse