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
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
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?