?
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
?
536 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
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

809 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