Truncation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Truncation problem

Author

Topic

yuriy
Starting Member

4 Posts

Posted - 2002-09-24 : 04:22:03

Hello
I have a stored procedure which returns varchar(2000) value. When I execute it, it works pretty well. But when I call this procedure from another one, the string is truncated. Why this happens?
Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-24 : 04:46:36

Is the string in the calling SP 2000 chars?
Are you sure it's not the way you are displaying the return?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yuriy
Starting Member

4 Posts

Posted - 2002-09-24 : 05:02:36

quote:

Is the string in the calling SP 2000 chars?
Are you sure it's not the way you are displaying the return?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

The string in the colling of SP is of the same length as the one in the SP.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-09-24 : 05:36:26

post sample code (both calling + called SP's...you can take out the non-relevent/private bits)...and you may get the problem solved faster.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-24 : 05:43:03

try this after correcting any typing mistakes

create proc nr1
@s varchar(2000) output
as
select @s = replicate('a',2000)
go
create proc nr2
as
declare @s varchar(2000)
exec nr1 @s output
select StringReturned = len(@s)
go
exec nr2
go
drop proc nr1
go
drop proc nr2
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yuriy
Starting Member

4 Posts

Posted - 2002-09-25 : 03:37:32

quote:

post sample code (both calling + called SP's...you can take out the non-relevent/private bits)...and you may get the problem solved faster.

That's how my troublesome procedure look like:

create procedure prcGetPresentItems
@id int, @items varchar(2000) output as ...

When I execute it, it returns correct result. But when I execute it from another SP, it returns truncated string. Thats how I call it from both Query Analiser and another procedure:

declare @id int; declare @items varchar(2000); set @id = 72
exec prcGetPresentItems @id, @items output
select @items
print len(@items)

The fun thing is that the length of the result is the same in both instances, but the string is obviousely shorter in the truncated version. That's what puzzles me most.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-25 : 05:19:00

In query analyser the display string length defaults to 256 chars.
Go to Query, current connection options, advanced and change the max chars per column to something big.
(that's where the option is in v7 anyway)

You say it's only when calling from another sp that the problem occurs?
Can you show how it's called from the other sp and how you detect the truncation.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yuriy
Starting Member

4 Posts

Posted - 2002-09-26 : 02:20:14

quote:

In query analyser the display string length defaults to 256 chars.
Go to Query, current connection options, advanced and change the max chars per column to something big.
(that's where the option is in v7 anyway)

You say it's only when calling from another sp that the problem occurs?
Can you show how it's called from the other sp and how you detect the truncation.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

My anabridged string is showed well in query analyser, so I think problem is not with it.

That's how procedure is called from another SP:

exec prcGetPresentItems @id, @items output

After calling the procedure, I insert @items into a table. And in this table I found the truncated result. The field in the table is of appropriate length, so I inserted

select @items

between procedure call and insertion into table, and found out my problem.

Excuse mistakes in my posts, I am not that illiterate, English is just not my mother tongue.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-26 : 06:17:51

This is a bit weird.
What length is it truncated to?
Did you run the code I posted (try it with the additions below)?
You say select len(@items) gives the correct length?

try
select len(@items)
select right(@items,len(@items - 200))
select replicate('a',2000)

this will show if it is a display problem (still my favourite but losing ground rapidly)

Is there anything strange about the way you call the SP - e.g. is it on the same server as the calling SP?

Which version / service pack.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources