varchar issue - 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)

varchar issue

Author

Topic

jai2808
Starting Member

27 Posts

Posted - 2008-04-21 : 08:27:32

Hi,
I am SP which creates the query dynamically and assigned to it an variable with datatype varchar(8000).

When i try to run the query it gives me an error.
But when i printed the query i found that the query is of length 4000 even though the datatype is varchar with length 8000.

Can some one please guide me on this.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-21 : 08:33:37

Post the code... (or at least an abridged version of it)

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-21 : 08:37:00

What error you got?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-21 : 08:38:43

quote:
Originally posted by jai2808

Hi,
I am SP which creates the query dynamically and assigned to it an variable with datatype varchar(8000).

When i try to run the query it gives me an error.
But when i printed the query i found that the query is of length 4000 even though the datatype is varchar with length 8000.

Can some one please guide me on this.

Do you think you have given enough information to help you?
Post procedure code and the error you got

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-21 : 08:39:29

Double

Madhivanan

Failing to plan is Planning to fail

jai2808
Starting Member

27 Posts

Posted - 2008-04-21 : 09:42:32

Sorry,
PFA the code
declare @sql varchar(8000)
set @sql=''

Select @sql = @sql + ', Max(Case when Parameter = ''' +Parameter + ''' Then SelectedValue End ) AS ''' + Parameter + '''' From (Select Parameter From MstQCParameterMaster where queueid=2)a

--print @sql

DECLARE @FSTR AS Varchar(8000)

SET @FSTR=REPLACE(@sql,',,','')

set @FSTR= 'Select'+ @FSTR

SET @FSTR=REPLACE(@FSTR,'Select,','Select')

--print len(@sql)
select @FSTR,len(@FSTR)
--exec(@FSTR)

jai2808
Starting Member

27 Posts

Posted - 2008-04-21 : 09:44:30

the lenght is showing as 4006

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-21 : 11:52:10

What is the error that you're getting?

jai2808
Starting Member

27 Posts

Posted - 2008-04-22 : 00:21:19

i am not getting any error but @FSTR is showing only 4006 characters
and its truncating the query which results in syntax error.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 03:38:50

> i am not getting any error...results in syntax error

Huh? Okay - I take your point. You are getting an error, but something is going wrong before it.

Try changing

Select Parameter From MstQCParameterMaster where queueid=2

Select cast(Parameter as varchar(8000)) as Parameter From MstQCParameterMaster where queueid=2

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

jai2808
Starting Member

27 Posts

Posted - 2008-04-22 : 05:26:16

Hi RyanRandall,
You are genius, this solved my problem.
Can you please explaim me the logic of statement
Select cast(Parameter as varchar(8000)) as Parameter From MstQCParameterMaster where queueid=2

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 07:29:04

I guess then that the Parameter column is an nvarchar. When you append a varchar to an nvarchar, the implicit conversion takes it to an nvarchar. The maximum default size for an nvarchar is 4000 - hence the problem. Our fix was to convert the nvarchar to a varchar.

You can use this code to play around with the idea...

declare @t table (x nvarchar(100))
while (select 50 * count(*) from @t) < 6000
    insert @t select replicate('x', 49) + '|'

select * from @t

declare @s varchar(8000)
set @s = ''
select @s = @s + x from @t
select len(@s), @s

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

jai2808
Starting Member

27 Posts

Posted - 2008-04-23 : 02:06:08

Thanks RyanRandall

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources