June 11, 2008 at 8:07 am
Comments posted to this topic are about the item LongPrint
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 4, 2009 at 9:10 am
Thanks for the LongPrint. Works... I use it for debugging. It is a tool needed in SQL Server for long print statements. :-):-)
September 5, 2009 at 2:17 pm
Hello, Pete. Glad its helping you out.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 7, 2013 at 2:18 am
Thanks for the wonderful script.
keep posting such good scripts..
Neeraj Prasad Sharma
Sql Server Tutorials
April 10, 2015 at 3:13 pm
Hello Timothy,
Question where did you get the figure 1073741822?
SET @sSQL = SUBSTRING(@sSQL, @CurrentEnd+@Offset, 1073741822)
The pain of Discipline is far better than the pain of Regret!
May 5, 2016 at 3:28 pm
Thank you so much for this script!! I was going crazy trying to figure out why I was printing something that seemed to be getting truncated in the output.
Much appreciated.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 13, 2019 at 1:05 pm
I noticed it was a bit slow, and had a quick look.
Here is a version that is faster, if you have a lot of spaces.
--USE master
GO
BEGIN TRY
DROP PROCEDURE dbo.LongPrint
END TRY
BEGIN CATCH
END CATCH
go
CREATE --or alter
PROCEDURE dbo.LongPrint
@String NVARCHAR(MAX)
AS
/*
Example:
exec LongPrint @string =
'This String
Exists to test
the system.'
another method is
SELECT CAST('<root><![CDATA[' + @MyLongString + ']]></root>' AS XML)
from --https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
*/
/*
from: http://www.sqlservercentral.com/scripts/Print/63240/
This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).
It will print the text passed to it in substrings smaller than 4000
characters. If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.
If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.
If it is passed a null value, it will do virtually nothing.
NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
*/
DECLARE
@CurrentEnd BIGINT, /* track the length of the next substring */
@offset tinyint /*tracks the amount of offset needed */
set @string = replace( replace(@string, char(13) + char(10), char(10)) , char(13), char(10))
WHILE LEN(@String) > 1
BEGIN
IF CHARINDEX(CHAR(10), @String, 3000) between 1 AND 4000
BEGIN
SET @CurrentEnd = CHARINDEX(char(10), @String, 3000) -1
set @offset = 2
END
ELSE IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
BEGIN
SET @CurrentEnd = CHARINDEX(char(10), @String) -1
set @offset = 2
END
ELSE
BEGIN
SET @CurrentEnd = 4000
set @offset = 1
END
PRINT SUBSTRING(@String, 1, @CurrentEnd)
set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)
END /*End While loop*/
June 14, 2019 at 2:56 am
I used to display long strings in a similar matter and even wrote a similar article (didn't know Timothy had already written one back then). A fellow by the name of Orlando Colamatteo showed me a trick and the following function is what I wrote from Orlando's great tip. Details are in the comments and, yes, I use this in production.
CREATE FUNCTION [dbo].[ShowLongString]
/**********************************************************************************************************************
Purpose:
Display a string of more than 8000 characters. The string can be Dynamic SQL, XML, or just about anything else.
Note that this function does use XML and, while normally quite faithful, there are characters (I've not taken the time
to identify them because they're edge cases that I don't deal with) that it just won't handle. It will, however,
handle most control-characters below ASCII 32.
-----------------------------------------------------------------------------------------------------------------------
Usage:
--===== Example with Dynamic SQL
DECLARE @SQL VARCHAR(MAX);
SELECT @SQL = '
SELECT somecolumnlist
FROM some table with joins
;'
;
SELECT LongString
FROM dbo.ShowLongString(@SQL)
;
--===== Example with a call to a table or view
SELECT sm.Object_ID, Definition = ls.LongString
FROM sys.SQL_Modules sm
CROSS APPLY dbo.ShowLongString(sm.Definition) ls
;
-----------------------------------------------------------------------------------------------------------------------
Credits:
1. I learned this trick from a post by Orlando Colamatteo at the following link. It has served me very well since
then. Thanks, Orlando.
https://www.sqlservercentral.com/Forums/FindPost1468782.aspx
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
**********************************************************************************************************************/
--===== Declare the I/O for this function
(@pLongString VARCHAR(MAX))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT LongString =
(
SELECT REPLACE(
CAST(
'--' + CHAR(10) + @pLongString + CHAR(10)
AS VARCHAR(MAX))
,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
AS [processing-instruction(LongString)]
FOR XML PATH(''), TYPE
)
;
GO
GRANT SELECT ON [dbo].[ShowLongString] TO [public] AS [dbo]
;
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2019 at 9:56 am
hi Jeff,
That is going to be useful, at least for me. But I could not get it to work on SQL Server 2008 R2 (not that it is relevant for much longer)
Thank you,
Henrik
June 14, 2019 at 1:19 pm
hi Jeff, That is going to be useful, at least for me. But I could not get it to work on SQL Server 2008 R2 (not that it is relevant for much longer) Thank you, Henrik
I don't have 2008 R2 to test against but, for me, it works just fine in 2008, 2012, and 2016. I wonder if the forum code may have changed some of the code in the function (it's happened before).
Also, when you say you couldn't get it to work in 2008 R2, I can't help there without you posting the code that you used that didn't work. It would also be helpful if you were to post any errors returned if any are returned but, at least post the code where it's not working for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2019 at 1:37 pm
SQL Server 2008 R2 is going out of support, and we're moving the last database to Azure SQL DB today, so it doesn't really matter, but here goes:
Your function works when I run it on Azure SQL DB. The same code installs and run (and run and run) on SQL Server 2008 R2 with this @@version : Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
When running SP_Whoisactive, it shows that it is just burning CPU cycles. Your first example has not stopped yet, after 04:22.
June 14, 2019 at 11:34 pm
Thanks for taking the time, Henrik. A lot of people will continue to be "stuck" in 2008 R2 for one reason or another and so I wanted to know what's going on there. I don't have that version to test on but it's good to know that it goes haywire on that version so that we can warn people if they ask.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2019 at 1:41 pm
version - Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Jeff
this worked well for me. the version i had been using cut the output into 4k lines, but always lost the first character of the next line. Really a pain to put it back together.
declare @t varchar(max) = cast(replicate('1', 8000) as varchar(max)) + replicate('2', 8000) + replicate('3', 8000) + 'z'
print len(@t)
select @t
print @t
SELECT LongString
FROM dbo.ShowLongString(@t) July 1, 2019 at 1:52 pm
Jeff
declare @t varchar(max) = cast(replicate('1', 8000) as varchar(max)) + char(13) + char(10) + replicate('2', 8000) + char(13) + char(10) + replicate('3', 8000) + char(13) + char(10) +'z'Another nice feature of your function is that it respects CHAR(13) + CHAR(10), which does not usually happen in the SQL 2008 or SQL 2008 R2 SSMS result sets. The function that i mentioned that i had been using would force a CRLF at 4000 if the line was longer than 4k. This is what would lose the first character on the next line.
I think you really ought to promote this function, especially since SSMS v18 will not have a debugger. This function will become a crucial part of my toolbox.
Vote to add debugger back
October 16, 2020 at 7:07 pm
Jeff Moden's function ShowLongString is just what I was looking for. I use it as a better alternative for sp_helptext to quickly view the code of a procedure or function.
Adding the following line of code to a query shortcut will make it even faster:
EXEC sys.sp_executesql N'SELECT ls.longstring FROM sys.sql_modules sm CROSS APPLY dbo.ShowLongString (sm.[definition]) ls WHERE OBJECT_NAME(sm.[object_id]) = @O', N'@O SYSNAME', @O =
Selecting the name of a procedure (double click or CTRL + W), pressing the assigned query shortcut, click on XML result.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply