[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2003-11-28
5
Medium Priority
?
529 Views
Last Modified: 2008-02-01

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.




0
Comment
Question by:PascuaD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 750 total points
ID: 9838285
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9839454

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
 

Expert Comment

by:joselopesdacruz
ID: 9847112
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
 

Author Comment

by:PascuaD
ID: 9847341
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9946463
Hi Pascua D,

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

Regards.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question