Why I can't store more than 256 characters in a variable defined as varchar(8000)?


I want to store a query into a variable and when I execute it, it truncates the value.

Ex.
Declare @QryStatement varchar(8000)

...
Set @QryStatement = "Select ...."  -- very long statement (less than 8000 chars)

-- display the content of @QryStatement
Select @QryStatement    --- This returns a truncated string up to 256 characters.

Exec (@QryStatement)   --- This returns an invalid function. It would because it tries to execute an invalid statement.




PascuaDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
if you're executing in query analyzer the default is only to display 255 characters for any column...

go to query options and specify the column size you want to display....

then see what error you actually having in your constructed statement...

of course if you want to execute a large dynamic sql statement
then you should use nvarchar and sp_executesql... which will allow the maximum
possible sql statement length (64K ?)


hth    
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
namasi_navaretnamCommented:

This works for me. I think there must be something wrong with your querry.

Declare @QryStatement varchar(8000)

Set @QryStatement = 'Select ''1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'', ''1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'' '


Select len(@QryStatement)    

Exec (@QryStatement)  


If you execute
If you Execute Select @QryStatement  

Sql will be trucated at 256 chars. But when you execeute  a sql that exceeds 256 characters using EXEC that should work fine as illustrated above.

So, check your sql and see if the sql is correct.

Namasi
0
joselopesdacruzCommented:
Are you using " or ' around the string in the SET ?
If you use " query analyzer interpret that has a identifier like a column name ou table name.

Use this :

Set @QryStatement = 'your query string goes here!'

José Cruz
0
PascuaDAuthor Commented:
LowFatSpread and Namasi have pointed me to the right directions. It is sorted now. Thanks.

It was me being stupid did not check the query that I was trying to execute.





0
namasi_navaretnamCommented:
Hi Pascua D,

Would you need further help. If not, why not close the issue? :)

Regards.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.