Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Export Data in Text File Using SQLCMD in SQL SERVER 2005

By Manish Mathur,

Introduction : Sql Server 2005 provides the new utility which is sqlcmd command utility. By using this utility we can execute the T-sql command as well as we can export the data in text file from any sql server table. SQLCMD using OLEDB provider to execute the T-Sql commands.

Script Description : This script comprises of one stored procedure script which have three input paramters and two output parameters as follows :-


@TableName -- To Get Table Name
@FilePath -- To Get Valid FilePath with FileName
@Separator -- To Get Field Separator
@Result -- To get error no if any error ocurred during execution
@ErrDesc -- To get error description if any error ocurred during execution

I have seen lot of examples on which data in exported in text file with field headers but field headers also containing the "-" characters in the text file but in this script i have removed these characters "-" from the exported text file so that we can easily exported the data in required format.

Note : In the above example i have used the xp_cmdshell stored procedure. This feature required to be enable by using SQL Server Surface area configuration or you can use the following script to enable the feature.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO

 

Total article views: 4556 | Views in the last 30 days: 18
 
Related Articles
FORUM

URGENT: Error executing scripts......

URGENT: Error executing scripts......

FORUM

SSIS error while executing in command line

SSIS error while executing in command line

FORUM

DBCC command execution History

When did a DBCC command executed in a DB

FORUM

Replication issue Error executing a batch of commands. Retrying individual commands.

My Tran. Replication is working on other server, but when I crete new Subscription on new server I a...

FORUM

script errors

script errors

Tags
sqlcmd    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones