SQL Server – How to Retrieve Large Text from Table

blobbulk-insertopenrowsetsql serversql-server-2016

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.
enter image description here

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:

enter image description here

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:

enter image description here

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?

Best Answer

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.

Related Question