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] 
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))

   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=cast ('3 may 2018' as date)
           ,ACTIVITY='MOVE APPLICATION ROWS - the Ids work'
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.

How to retrieve a large text from inside a 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.

I have tried a few things, all using bcp and xp_cmdshell.

Write to a text file from a stored procedure

Exporting Data Programatically with bcp and xp_cmdshell

How to create text file using sql script with text “|”

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;

sp_configure 'xp_cmdshell',1;

the d:\ is available on my machine, so I used it below:

SET @Text = 'Hello world^| '
SET @Cmd ='echo ' +  @Text + ' > d:\AppTextFile.txt'
EXECUTE Master.dbo.xp_CmdShell  @Cmd

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

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.

Using BCP to export the contents of MAX datatypes to a file

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

where the 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 [2]. 
--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

enter image description here

  • what can't you make work? Or what error message do you get? – Max Vernon May 9 '18 at 15:55
  • Basically I would like to save a varchar(max) into a file. I have edited the question for clarification – Marcello Miorelli May 9 '18 at 16:06
  • 1
    @marcellomiorelli - I noticed your example that is failing with the odbc errors (immediately after your statement "The question remains") did not include the -S parameter for the BCP command. When the -S parameter is not specified, a connection to the default instance will be done. Did you intentionally leave off the -S parameter? – Scott Hodgin May 9 '18 at 16:51
  • 1
    @marcellomiorelli see the xp_cmdshell to call bcp in my answer here . This will help you. You are not putting in the server name and databaseName – Kin Shah May 9 '18 at 18:43
  • 1
    Hi @marcellomiorelli 1) Why are you using VARCHAR instead of NVARCHAR since the column you are retrieving the value(s) from is NVARCHAR(MAX). 2) Why are you attempting the execution from within SQL Server when it seems like you won't be accessing the output from SQL Server? Meaning, why not just create a CMD script file that you can execute whenever? You could even have it prompt you for an "activity" to pass in and filter on. It just seems like this isn't the best scenario for xp_cmdshell. – Solomon Rutzky May 9 '18 at 20:57

The queryout parameters look weird to me.

The link uses:

 + '” queryout ' + @Filename + ' -w -T -S' + @@ServerName;

I use these:

+ @filepath +  @filename + @filetype + ' -t " - " -c -T -d DBA''

Not sure what they all do, to be honest, but I think the -d one is important. It's specifying the database [DBA].

Here's my big bad script. Only run it if you hate your SAN administrator - and make sure you've got a new printer cartridge:

declare @filepath varchar(255),
@filename varchar(255),
@filetype varchar(255),
@table varchar(255),
@sql nvarchar(max)

set @filepath = 'C:\users\jmay\documents\'
set @filetype = '.txt'

declare c cursor local for
select name from sys.tables st with (nolock)

open c
fetch from c into @table

while @@FETCH_STATUS = 0

set @filename = @table

set @sql = N'declare @bcp varchar(4000)
set @bcp = ''bcp " select * from ' + @table + ' " queryout ' 
+ @filepath +  @filename + @filetype + ' -t " - " -c -T -d DBA''
EXECUTE master.dbo.xp_cmdshell @BCP'

exec sp_executesql @sql

set @sql = N'declare @bcp varchar(4000)
set @bcp = ''START /MIN NOTEPAD /P ' + @filepath +  @filename +  @filetype + '''
EXECUTE master.dbo.xp_cmdshell @BCP'

print @sql
exec sp_executesql @sql

fetch next from c into @table

close C
Deallocate C
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.