SQL Server Exporting a Table to a Text File

 in SQL Server  tagged sql / bcp / xp_cmdshell by

This post describes how to export a SQL Server table to a text file using a stored procedure.

xp_cmdshell

To accomplish this task we will be using xp_cmdshell. It spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text. This means that you can run DOS commands from SQL Server.

Extended stored procedures are stored procedures that call functions from DLL files. However, as Microsoft has said…”This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead.”

An Example

  • Database name: myDatabase
  • Table name: dbo.Authors
  • Outputted file name and directory: C:\temp stuff\authorsout.txt
  • Stored procedure name: AuthorsToTextFile
-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO

Run the above commands in a new query window if you get the following error message: 

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 2]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
Return Value
------------
0

(1 row(s) affected)


USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Authors2TextFile] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @sql varchar(1000)
	SELECT @sql = 'bcp "select * from myDatabase.dbo.Authors" '
			+ 'queryout "c:\temp stuff\authorsout.txt" -c -T -S'
			+ @@servername
	EXEC master..xp_cmdshell @sql
END
GO

bcp

In the above example the directory \temp stuff\ has a space in it. You need to surround the path and file with double quotes whenever any directory has a space in it, otherwise no double quotes are needed.

-c
Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n (newline character) as the row terminator. -c is not compatible with -w.

-T
Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

-S server_name [\instance_name] Specifies the instance of SQL Server to which to connect. If no server is specified, the bcp utility connects to the default instance of SQL Server on the local computer. This option is required when a bcp command is run from a remote computer on the network or a local named instance. To connect to the default instance of SQL Server on a server, specify only server_name. To connect to a named instance of SQL Server, specify server_name\instance_name.