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]
Thanks to all of the answers to question below I can retrieve my scripts from the table.
I am very thankful, that is working fine. Sometimes though, I need to re-run a script that I have run in the past, or I want to attach it to an email. It would be really helpful if I could save my script to a file.
so these above are all working as you can see the scripts below:
Please not that you need to temporarily enable xp_cmdshell first.
--Temporarily enable xp_cmdshell sp_configure 'show advanced options',1; RECONFIGURE GO sp_configure 'xp_cmdshell',1; RECONFIGURE GO
d:\ is available on my machine, so I used it below:
DECLARE @Text AS VARCHAR(100) DECLARE @Cmd AS VARCHAR(100) SET @Text = 'Hello world^| ' SET @Cmd ='echo ' + @Text + ' > d:\AppTextFile.txt' EXECUTE Master.dbo.xp_CmdShell @Cmd go DECLARE @cmd varchar(256), @var varchar(50),@var1 varchar(50) SET @var = 'Hello world!' set @var1 = 'This is second line' SET @cmd = '(echo '+@var+' && echo '+@var1+') >d:\SprintTask.txt' EXEC master..xp_cmdshell @cmd, no_output go
that is all working. But when I try to apply the link below to save my varchar(max) to a file, I can't make it work.
following the procedure shared by Phil Factor, he shows in that link an example as how to save a
varchar(max) to a file, but that is not working for me, it does not show any error message nor exception though. the code that comes out to be executed in sqlcmd is:
bcp ”select BulkCol from [##temp52890]” queryout d:\testing.txt -w -T -S myservername\myinstancename
select BulkCol from [##temp52890] has my varchar(max) in full.
The question remains:
How to save a varchar(max) to a file?
Now running the following code I get the error message below:
EXECUTE master.dbo.xp_cmdshell 'bcp "select BulkCol from [##temp464501]" queryout d:\testing.txt -c -T ' --SQLState = 08001, NativeError = 2 --Error = [Microsoft][ODBC Driver 13 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server . --SQLState = 08001, NativeError = 2 --Error = [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config --ured to allow remote connections. For more information see SQL Server Books Online. --SQLState = S1T00, NativeError = 0 --Error = [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired --NULL