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
Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

840 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