Printing Strings Longer Than 8000 Characters

Question

Printing Strings Longer Than 8000 Characters

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

Jeff Moden wrote:

Michael L John wrote:
I'm with Phil. Why are you doing this?
I can't speak for Ron but sometimes it's necessary to see, for example, the full monty for dynamic SQL.

Presumably you have your DBs in source control, Jeff? If so, you always have whatever version of the code you are interested in at your fingertips.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

That's true, Phil... but I don't keep the results of Dynamic SQL in source control. I don't keep the code that gets stored in cache that I interrogate with my sp_WhatsRunning or sp_ShowWorst code. There are a thousand reasons why someone might want or need to display way more than 8000 characters. It's even good for looking at LOBs that you want to parse or whatever that are unfortunately stored in a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

Jeff Moden wrote:

That's true, Phil... but I don't keep the results of Dynamic SQL in source control. I don't keep the code that gets stored in cache that I interrogate with my sp_WhatsRunning or sp_ShowWorst code. There are a thousand reasons why someone might want or need to display way more than 8000 characters. It's even good for looking at LOBs that you want to parse or whatever that are unfortunately stored in a table.

There has been a miscommunication, I think.

I was not referring to the 8,000 character limit. I was questioning the generation of DDL for the objects in a database.

This reply was modified 2 years, 5 months ago by Phil Parkin.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Agreed. I was only reading what you quoted and that was my comment about Dynamic SQL. I totally agree DDL should be in source control. Sometimes, though, source control and what's actually in a prod box are different. It, of course, should never get that on the trunk until a branch is committed but I've seen people screw things up pretty badly on source control.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 5

This is a stored procedure that I created a to print strings that exceed the 8K limit of the PRINT command.

USE master;
GO 
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE OR ALTER PROCEDURE dbo.sp_LongPrint
/* ===================================================================================================
2017-01-05 Jason Long, Allows the printing of strings that are greater that the 4K limit of the PRINT commant.
=================================================================================================== *//*
DECLARE @x NVARCHAR(MAX) = (SELECT REPLICATE(CONVERT(NVARCHAR(MAX), 'abcdefghijklmnop' + char(13) + char(10)), 10000) );
EXEC dbo.sp_LongPrint @x;
*/@_string NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;

IF LEN(@_string) <= 4000
BEGIN
PRINT(@_string);
END;
ELSE 
BEGIN
DECLARE @string_part NVARCHAR(4000);

WHILE 1 = 1
BEGIN
SELECT 
@string_part = SUBSTRING(@_string, 1, ISNULL(NULLIF(se.string_end, 4000) - 1, 4000)),
@_string = STUFF(@_string, 1, ISNULL(NULLIF(se.string_end, 4000) + 1, 4000), N'')
FROM 
( VALUES ( 4000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@_string, 1, 4000)))) ) se (string_end)

IF @string_part = N''
BEGIN
BREAK;
END;
ELSE 
BEGIN
PRINT(@string_part);
END;
END;
END;
END;
GO

EXEC sys.sp_MS_marksystemobject N'dbo.sp_LongPrint';
GO

RonMexico Hall of Fame Points: 3474 More actions · Answer 6

To expand on the reason I'm not using other DDL scripting methods is because I'm looping through hundreds of tables and running tests on converting data types and changing data in some columns. So one time through I might change all decimal(10,4) fields to decimal(10,2) and next time through to an int. There will also be functions manipulating data in other fields so scripting and doing a find and replace would be more work in my opinion.

I decided to select the strings into a table because this gave me the best solution to copying and pasting the DDLs I need.

Jeff - your function is very handy and I'll have to keep that in my back pocket

Jason - that does print out more than 8,000 characters but it breaks it up into different lines which was my initial problem

Michael L John One Orange Chip Points: 27038 More actions · Answer 7

RonMexico wrote:

To expand on the reason I'm not using other DDL scripting methods is because I'm looping through hundreds of tables and running tests on converting data types and changing data in some columns. So one time through I might change all decimal(10,4) fields to decimal(10,2) and next time through to an int. There will also be functions manipulating data in other fields so scripting and doing a find and replace would be more work in my opinion.
I decided to select the strings into a table because this gave me the best solution to copying and pasting the DDLs I need.
Jeff - your function is very handy and I'll have to keep that in my back pocket
Jason - that does print out more than 8,000 characters but it breaks it up into different lines which was my initial problem

Ahhhhhh. Fun stuff.

As long as you are going to use the "Print" statement, it's never going to give you more than 8096 characters. That's the limit for SSMS in an output window.

Have you tried sending the results to a file??? That worked for me when I was faced with a similar situation

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

RonMexico Hall of Fame Points: 3474 More actions · Answer 8

No luck with the results to file. Thanks for the help though

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

RonMexico wrote:

No luck with the results to file. Thanks for the help though

Perhaps BCP from a LOB column to a file?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)