October 8, 2012 at 6:17 am
Generating code / scripts I run into problems with the length of strings.
I use management studio to generate code / scripts.
Often the total length of the scripts is longer than 8000.
But I run in to several problems when my scripts go over 8000.
The results get chopped off.
I use the REPLACE funtion a lot. (Is or can the REPLACE be a problem?)
And I do use Print @string or Select @string to display the result.
A workaround I have used before is generating the scripts in parts which are under the 8000 characters. But this limits what I can do and is very difficult to generalise.
Is there a webpage which describes this problem and comes with suggestions ?
Is there a solution or workaround for this ?
I thought the varchar(max) would solve this old problem for me, I still hope it will or am I being naive?
Thanks for any help,
Ben Brugman
Some further information:
I am trying to build a script / code repository.
Where I can store snippets. (execute,show,print those).
and use replace to replace parts of the snippets, with other snippets or for example a table name.
Snippet:
'
-- Start_timing_code
Select count(*) as tel_TTTTTT from TTTTTT
-- End_timing_code
'
In this snippet the TTTTTT can be replaced by a tablename.
'-- Start_timing_code' can be replaced with a timing code snippet.
'-- End_timing_code' can be replaced with a timing code snippet, (which does the endtiming).
October 8, 2012 at 6:54 am
The problem will be the UI, not varchar(max) nor replace().
SSMS, for example, will cut off at 8k characters, when it's displaying the data.
Try this:
USE ProofOfConcept;
GO
DECLARE @Cmd NVARCHAR(MAX);
SET @Cmd = CAST('/*' AS NVARCHAR(MAX)) + REPLICATE(CAST('x' AS NVARCHAR(MAX)),50000) + CAST('*/ print ''Hello World!''' AS NVARCHAR(MAX));
PRINT @Cmd;
EXEC sp_executesql @Cmd;
The Print command will truncate, showing "/*" and then a bunch of "x"s, cut off before the command, but the Hello World! will be printed by executing the string.
So the string isn't being truncated by anything inside SQL Server. It executes correctly. If it were being truncated, it would generate an error because it would have an unterminated comment block, and that's all it would do. Wouldn't get to Print.
You can slightly modify how this works by playing with the Options in SSMS (Tools - Options - Query Results), but that only goes so far. It's not really the right tool for dealing with large text strings. You'll want to build an application that is designed for dealing with that kind of thing, or see if someone has already built one that you can use (Bing/Google should find that, if it exists, and you can then decide if one of them will do what you need and want).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 8, 2012 at 8:01 am
GSquared (10/8/2012)
The problem will be the UI, not varchar(max) nor replace().SSMS, for example, will cut off at 8k characters, when it's displaying the data.
Thanks,
Now I only have to build a print which does do the printing in chuncks.
I build the code below, this does the printing in chucks of at most.
Assumed is that linelength does not go beyond a 1000.
A <CR/LF> is searched between the characters 3000 and 4000.
The chuck up to that cr/lf is printed. If not found the chunck up to 4000 is printed.
Thanks again for your advise,
Ben Brugman
-- Ben Brugman 20121008
-- Print @Longstring
-- Search a new line starting from position 3000
-- Print up to that position or to a maximum off 4000 characters.
-- Process the remainder.
declare @Longstring varchar(max) = 'any long string with crlf between the lines'
declare @RemainingString varchar(max)
DECLARE @NL VARCHAR(4)
declare @plek int = 0
declare @chunklength int = 4000
set @NL = '%'+char(13)+char(10)+'%'
set @RemainingString = @Longstring
declare @restlengte int = datalength(@RemainingString)
while DATALENGTH(@RemainingString) > @chunklength
begin
-- print '-- restlength = '+convert(varchar(6),@restlengte)
set @plek = @chunklength
set @plek = @chunklength-1000+PATINDEX(@NL, substring(@RemainingString,@chunklength-1000,datalength(@RemainingString)))
IF @plek > @chunklength begin set @plek = @chunklength end
-- print '-- location found '+ convert(varchar(6),@plek)
print substring(@RemainingString,1,@plek -2)
set @restlengte = @restlengte - (@plek+1)
set @RemainingString = substring(@RemainingString,(@plek+1), datalength(@RemainingString)) -- eindlengte is meer
end
print @RemainingString
October 8, 2012 at 8:34 am
Hi,
If you cast your VARCHAR(MAX) to XML then SSMS can display the entire contents in an XML editor window.
DECLARE @Cmd NVARCHAR(MAX);
SET @Cmd = CAST('/*' AS NVARCHAR(MAX)) + REPLICATE(CAST('x' AS NVARCHAR(MAX)),50000) + CAST('*/ print ''Hello World!''' AS NVARCHAR(MAX));
SELECT CAST(@Cmd AS XML);
October 8, 2012 at 8:37 am
Artoo22 (10/8/2012)
Hi,If you cast your VARCHAR(MAX) to XML then SSMS can display the entire contents in an XML editor window.
DECLARE @Cmd NVARCHAR(MAX);
SET @Cmd = CAST('/*' AS NVARCHAR(MAX)) + REPLICATE(CAST('x' AS NVARCHAR(MAX)),50000) + CAST('*/ print ''Hello World!''' AS NVARCHAR(MAX));
SELECT CAST(@Cmd AS XML);
Works if you don't have any special characters in the data. Try it with <, >, &, /, and so on. Can be a problem with code, because a lot of Where clauses (for example), will have less-than and greater-than logic in them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 8, 2012 at 8:52 am
Thanks Artoo22 and GSquared,
Tried the XML but this result in error, as described by GSquared. (this looked promising to me).
If there are no problems with functions and operations like REPLACE, using the 'longprint' routine is sufficient for me.
Thanx,
ben
October 8, 2012 at 12:05 pm
ben.brugman (10/8/2012)
Generating code / scripts I run into problems with the length of strings.I use management studio to generate code / scripts.
Often the total length of the scripts is longer than 8000.
But I run in to several problems when my scripts go over 8000.
The results get chopped off.
I use the REPLACE funtion a lot. (Is or can the REPLACE be a problem?)
And I do use Print @string or Select @string to display the result.
A workaround I have used before is generating the scripts in parts which are under the 8000 characters. But this limits what I can do and is very difficult to generalise.
Is there a webpage which describes this problem and comes with suggestions ?
Is there a solution or workaround for this ?
I thought the varchar(max) would solve this old problem for me, I still hope it will or am I being naive?
Thanks for any help,
Ben Brugman
Some further information:
I am trying to build a script / code repository.
Where I can store snippets. (execute,show,print those).
and use replace to replace parts of the snippets, with other snippets or for example a table name.
Snippet:
'
-- Start_timing_code
Select count(*) as tel_TTTTTT from TTTTTT
-- End_timing_code
'
In this snippet the TTTTTT can be replaced by a tablename.
'-- Start_timing_code' can be replaced with a timing code snippet.
'-- End_timing_code' can be replaced with a timing code snippet, (which does the endtiming).
Why don't you just use sp_helptext and call it a day?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply