How to retrieve a large text from inside a table?

Question

for my own records I have a table in one of my servers where I like to save my activities and scripts

the table definition is:

IF OBJECT_ID('[dbo].[activity]') IS NOT NULL 
DROP TABLE [dbo].[activity] 
GO
CREATE TABLE [dbo].[activity] ( 
[dt]        DATE                             NOT NULL,
[i]         SMALLINT                         NOT NULL,
[activity]  NVARCHAR(max)                     NOT NULL,
[script]    NVARCHAR(max)                     NULL  
           CONSTRAINT [DF__activity__script__6E01572D] DEFAULT (NULL))

GO
CREATE CLUSTERED INDEX [pk_activity] 
   ON [dbo].[activity] ([dt] desc, [i] desc)

as this table is located in the DBA database on my DBA_SERVER server, from my local machine I created a linked server to the DBA_SERVER so that I can insert scripts into my table.

this is how I insert a script into my table, the script is located on my local machine:

    INSERT INTO [MY_DBA_SERVER].[DBA].[dbo].[activity]
               ([dt]
               ,[i]
               ,[activity]
               ,[script])
    SELECT
               DT=cast ('3 may 2018' as date)
               ,I=1100
               ,ACTIVITY='MOVE APPLICATION ROWS - the Ids work'
               ,[script]=BulkColumn 
    FROM Openrowset( Bulk 'C:\Users\MMiorelli\Downloads\applicationID consolidated.sql', 
                     Single_Blob) as [the script]

and this seems to be working fine, because when I run the select below:

SELECT [dt]
      ,[i]
      ,[activity]
      ,[script]
  FROM [MY_DBA_SERVER].[DBA].[dbo].[activity]
GO

I can see that the stuff I have just inserted is on my table.

and when I run the following script I find out how much data is in each field:

SELECT [dt]
      ,[i]
      ,[activity]
      ,[script]
      ,len_activity=DATALENGTH( [activity])
      ,len_script=DATALENGTH( [script])
  FROM [MY_DBA_SERVER].[DBA].[dbo].[activity]
GO

and that gives me this:

My problem is that I cannot retrieve the whole content of my script. I have tried to copy it using the mouse as you can see on the picture below:

But I have some hairy scripts that are very long, and copying like that cuts them short of their whole.

my question is:

How to retrieve this script from my table?

it can be via select or even saving it to a file, I would prefer a T-SQL way of doing it if possible.

Just a curiosity would that be the same way for images?

Answer 1

You should be able to accomplish this with the Stored Procedure I provided in the following answer (here on DBA.SE):

How to output more than 4000 characters in sqlcmd

Since you are using NVARCHAR(MAX), please see the note towards the bottom of that answer that has a link to the NVARCHAR version of that Stored Procedure on PasteBin.

In either case, the Stored Procedures print the output in chunks to get around the 4000 or 8000 character limitation (depending on NVARCHAR or VARCHAR, respectively) of PRINT, hence you shouldn't notice any truncation and you should still get newlines, etc working as expected, which won't happen when copying / pasting from the results grid.

Answer 2

There's another way similar to Aaron's, using the little-documented processing-instruction... thing.

Using this gives you unchecked XML, which will won't replace predefined entities.

Also, the column will have XML identifying marks at the beginning and end, which may be another drawback depending on how you want to use the output.

But it'll be clicky and show you the whole thing!

CREATE TABLE dbo.LongText ( 
    Id INT IDENTITY, 
    LongText_N NVARCHAR(MAX), 
    LongText_X XML  
    );

INSERT dbo.LongText ( LongText_N, LongText_X )
SELECT asm.definition, (SELECT asm.definition AS [processing-instruction(_)] FOR XML PATH(''))
FROM sys.all_sql_modules AS asm

SELECT * FROM dbo.LongText AS lt

Answer 3

You can't copy from text output, because it truncates the data.

You can't copy from grid output, not only because it truncates the data, but it also mangles carriage returns, line feeds, and other control characters.

You might be able to convert that column to XML, then double-click to view the data in a new tab. I describe this here, but for a different purpose:

• Validate the contents of large dynamic SQL strings

Basically, SSMS wasn't made to do the task you want to do. You can extract to a file, but it's quite likely that similar truncation will happen during that process.

Your best bet is to pull the data from Powershell or C# and make that write the output to a file.