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,
create procedure aaaNtextSelect
select cast(replicate('123456789 ',250) as ntext)
create procedure aaaNtextInsert
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;
When the SP is run from a Query Window
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.