February 24, 2009 at 3:34 am
Hi Pals,
Need some help.
I have big sql which is more than 4000 characters.
Basically the sql is formed by UNION operator.
For that reason i have declared the datatype as VARCHAR(MAX).
and then tried to assign the value to a variable.
and when i am trying to print the SQL using PRINT / SELECT , the sql is getting truncated.
What i have done is instead of printing i have used SELECT LEN('SELECT ..........'). Then i am getting
output as 8000 chars which can easily be accomodated by VARCHAR(MAX) datatype (i.e 2^31 chars.)
Why is the sql is getting truncated? What could be the problem?
How could we resolve this problem?
Do we need to take 2 string variables and do that or else do we have any other solution for this.
Basically i am inserting the result fetched by the big SELECT stmt inside my stored procedure.
Any Thoughts?
Any suggestions will be greatly appreciated.
Thanks in Advance.
February 24, 2009 at 4:22 am
From just your input it's hard to say what's the problem. Can you post table description, sample data and the sql you're trying to make?
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
February 24, 2009 at 6:40 am
In SQL Server Mgmt Studio, the maximum lengths of strings to be returned by SELECT or PRINT statements are options you can override. The defaults may be too low for your purposes.
From the dropdown menus at the top see:
Tools/Options/Query Execution/SQL Server/General/Set Textsize
and
Tools/Options/Query Results/SQL Server/Results to Grid
and
Tools/Options/Query Results/SQL Server/Results to Text
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 24, 2009 at 9:58 am
Two smallish comments:
- the PRINT may truncate the output, but executing the variable should execute the whole thing.
- You'd be better off using NVARCHAR, since both EXEC and sp_ExecuteSQL expect the code to be passed in as NVARCHAR(max).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 24, 2009 at 1:25 pm
If you want to review your generated sql in the interface you can cast him as xml, see the example
declare @sql nvarchar(max)
select @sql = replicate('0123456789'+char(13)+char(10),400), @sql = @sql+@sql
select len(@sql),cast(' ' as xml)
the code will be executed anyway, even is not printed (if your intention is to use exec(@sql) or something like that)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply