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
