July 29, 2009 at 11:27 am
You can actually specify a length larger than the total string and substring will work fine, so the following will work too:
declare @STR varchar(max), @strlen int, @startIndex int, @blockSize int
select @STR = 'some long sql string',
@strlen = len(@str), @startIndex = 1, @blockSize = 4000
while(@startIndex <= @strlen)
begin
print substring(@str,@startIndex,@blockSize)
set @startIndex = @startIndex + @blockSize
end
July 29, 2009 at 11:30 am
I too have faced this problem. The easiest method I have found is to set the string variable to an xml variable. You can select the variable and use for xml path clause to dynamical convert the string into "valid" xml text. You can then select the xml coumn and click the link to launch the code in a new window.
SET NOCOUNT ON;
GO
DECLARE @t TABLE(
ID INT,
Str1 VARCHAR(1500),
Str2 VARCHAR(1500),
Str3 VARCHAR(1500),
Str4 VARCHAR(1500)
)
INSERT INTO @t
VALUES
(
1,
REPLICATE('A',1500),
REPLICATE('B',1500),
REPLICATE('C',1500),
REPLICATE('D',1500)
);
DECLARE @sql VARCHAR(MAX),
@x XML,
@Id INT
SET @Id = 1
SET @sql =
(SELECT
'SELECT ' + CHAR(13)
+ '''' + Str1 +'''' + ',' + CHAR(13)
+ '''' + Str2 +'''' + ',' + CHAR(13)
+ '''' + Str3 +'''' + ',' + CHAR(13)
+ '''' + Str4 +'''' + CHAR(13)
+ 'FROM t' + CHAR(13)
+ '--WHERE something = something'
FROM @t
WHERE id = 1)
SET @x = (SELECT @sql AS [text()] FOR XML PATH(''))
SELECT dataLength(@sql) AS SQL_Length
SELECT @x
July 29, 2009 at 1:01 pm
Referring to the last post about the XML FOR PATH functionality, this is very interesting, but it has one issue: when you have the "" characters in the string, it displays them as "<" and ">" respectively. Do you know how to get past this?
Thanks,
SB
July 29, 2009 at 2:59 pm
Referring to the last post about the XML FOR PATH functionality, this is very interesting, but it has one issue: when you have the "" characters in the string, it displays them as "" respectively. Do you know how to get past this?
There is probably a way to do this, but I dont know it. I have never had a need to devise a solution. Typically, I just do a search/replace, if I need to change the HTML tags. It should also be known that other characters may not come across correctly, such as &. I would be interested to hear if anyone has a work around for this problem.
July 29, 2009 at 5:44 pm
A very nice article.
I ran into a similar problem a little over a year ago and posted my solution at the time to the site. It is at: http://www.sqlservercentral.com/scripts/Print/63240/
It is always interesting to see how different people solve similar problems. I also found some of the XML-based solutions mentioned in the comments very interesting.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
July 30, 2009 at 7:25 am
I faced the same problem sometime back... the way around for me was to insert the dynamic query in one of the tables i've created with varchar(max) field and then selecting the query from that table...:-D
July 30, 2009 at 8:52 am
Regarding the reply by aschoch: I'm not sure how your suggestion works...you mentioned that you ran this query:
SELECT CONVERT(xml, '') AS DataXML FROM MyTable
However, I don't see a column reference in that query. Can you explain how this works in a bit more detail? I'm very interested in seeing if XML technology can replace what I'm doing with a simpler script.
Thanks,
SB
July 30, 2009 at 9:21 am
To Sam Bendayan,
I couldn't get my SQL to appear correctly--the HTML editor won't display the CDATA section right.
Here's the first part:SELECT CONVERT(xml, '' + column + '') AS ColumnXML FROM TABLE
That part works if your string doesn't have any XML reserved characters like < or &. To be safe, you need to account for those, so you can include a CDATA section inside the x tag, and put your column inside the CDATA. Unfortunately, there's no way to show you the actual syntax--nothing lets me actually display a CDATA section here. This is as close as I can get: SELECT CONVERT(xml, '*![CDATA[' + column + ']]*') AS ColumnXML FROM TABLE
Replace the asterisks with open and close tags, and you're good.
July 30, 2009 at 9:15 pm
I am not sure if the procedure or script is really needed for this. May be you should try following
Go to Options and Query results one of the option has a setting that allows you to control the maximum length of the string that is displayed.
I use results to grid and increase the text sice to 64 K or something
August 12, 2009 at 11:06 am
This is a very useful idea, but it seems that there is a bug in the code. If you run the "usage" example at the top of the code, you will see only 3,999 "b" characters: in place of the 4,000th "b" a "C" gets written. This continues with each succeeding string of characters being only 3,999 instead of 4,000. I changed one of my procs to call this code (in place of its current use of a loop with READTEXT) and I ended up with a mess.
August 18, 2009 at 11:04 am
I experienced the same problem of printing nvarchar(max)/varchar(max), with truncation at 4000/8000 characters. I strongly agree: it's a bug (while Microsoft insists it's a feature). I found a very simple/effective workaround. I don't know how/why my workaround works, but it works, bullet-proof, all the times. Try it.
After bulding a @LongString longer than 8000 chars, as varchar(max), create a small temp table with two fields, insert the long string @LongString you created through your code into the varchar(max) field, and then retrieve it from the table, as shown below:
CREATE TABLE [dbo].[TEMP](
[RecNo] [smallint] IDENTITY(1,1) NOT NULL,
[LongString] [varchar](max) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[TEMP] ([LongString])
SELECT @LongString
SELECT LongString FROM [dbo].[TEMP] WHERE RecNo = 1
--the above SELECT returns the entire unchopped string,
--which you can copy and paste into a text editor for analysis
DROP TABLE [dbo].[TEMP]
That's all there is to it.
Enjoy!
Mike Vassalotti
Herndon, Virginia
August 19, 2009 at 12:24 am
why to do so many homework.........(creating tamp table take value in it then copy)
you simply select @variablename (As previously I have given )
and then take it to text editor.........
August 19, 2009 at 6:47 am
Sorry for not replying sooner...
thakur_samir: The option you mentioned DOES affect the length of the string that is displayed, but it has a max value of 8192. Try to put something bigger in there and you'll see that it sets it back down to 8192.
mjarsaniya: SELECT @sql doesn't show the whole string. It only shows up to 8,192 characters, so it has the same problem as PRINT.
Steve McRoberts: I wasn't seeing the bug because I had changed my 'usage' example to add a CHAR(10) to the end of each line. If you do that then the string prints out correctly. However, when I remove the CHAR(10) I see the bug. Will fix and repost. Thanks for the feedback.
Mike Vassalotti: I have tried the table solution before and it only returned 8,192 characters. Nonetheless, I tried your specific code but it only returned 8,192 characters as well. Does this work for you on strings longer than 8,192 characters?
SB
August 19, 2009 at 8:36 am
mjarsaniya (8/19/2009)
why to do so many homework.........(creating tamp table take value in it then copy)you simply select @variablename (As previously I have given )
and then take it to text editor.........
For me, using a select and copying the command out is not very efficient. When copying/pasting the command into an editor, you lose all formatting. If you have a code formatter, selecting the command is probably the best option because you can copy the command into a new window and click the format button. For those who do not have a formatter, this option is going to be worse because you have to reformat a huge tsql statement. I believe in the scenario, where a code formatter is not available, the XML method may be the easiest to implement even though it has character limitations.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply