Solved

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

Posted on 2003-11-28
5
524 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 250 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 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