This Export is achieved using xp_cmdshell commands.
Step1:
To enable xp_cmdshell in sqlserver execute the below query:
EXEC sp_configure 'show advanced options', 1
GO
reconfigure
EXEC sp_configure 'xp_cmdshell', 1
GO
reconfigure
Step2:
Configure other details to connect sqlserver in the stored procedure variable initialization part and
Execute below stored procedure which takes four input parameter(as per my requirement).
---INPUT PARAMETERS
@tObjectName AS nVARCHAR(255)--View/Table Name , “DBName..ViewName”
,@tColumnName AS nVARCHAR(255)--Column Name for Filtering
,@tDateValue AS DATETIME --Value for Filtering
,@tOutputFileName AS nVARCHAR(255)--Output File Name
--STORED PROCEDURE
--=============Testing==========================
--EXEC [Sproc_TPS_ExportDataToFlatFile] 'DBName..ViewName','View Column Name','Data Value','FileName'
--EXEC [Sproc_TPS_ExportDataToFlatFile] 'tlmain..view_tblcase','dcreatedat','12/27/2011','dump.txt'
--===============================================
--Note:Folder Path has to be created
GO
if exists (SELECT * from dbo.sysobjects where id = object_id(N'[dbo].[Sproc_TPS_ExportDataToFlatFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Sproc_TPS_ExportDataToFlatFile]
GO
CREATE PROCEDURE [dbo].[Sproc_TPS_ExportDataToFlatFile]
@tObjectName AS nVARCHAR(255)
,@tColumnName AS nVARCHAR(255)
,@tDateValue AS DATETIME
,@tOutputFileName AS nVARCHAR(255)
AS
BEGIN
DECLARE @nObjectID AS INT
DECLARE @tsql AS varchar(8000)
DECLARE @tServerInstanceName AS nVarchar(255),@tUSerID AS nVarchar(255),@tPassword AS nVarchar(255)
,@tHeaderDelimiter AS nVarchar(10),@tViewName AS nVarchar(255),@tOutputFilePath AS nVarchar(MAX)
,@tTempFilePath AS nVarchar(MAX),@tTempString AS nVarchar(255),@tColumnList AS nVarchar(MAX)
,@tDelimiter AS nVarchar(10)
DECLARE @dtCurrentDate As DateTime
SET @tServerInstanceName='ServerName/InstanceName'
SET @tUSerID='UserID'
SET @tPassword='Password'
SET @tHeaderDelimiter='^|'-- ^ character is prefixed since pipe is an internal command for cmd
SET @tDelimiter='|'
SET @tOutputFilePath='C:\'+@tOutputFileName--Output Path
SET @tTempFilePath='C:\Temp.txt'--This file gets deleted at the end of export
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA .COLUMNS WHERE TABLE_NAME =@tViewName AND COLUMN_NAME =@tColumnName)
BEGIN--2
SELECT @tColumnList=COALESCE(@tColumnList + @tHeaderDelimiter, '')+ COLUMN_NAME FROM INFORMATION_SCHEMA .COLUMNS WHERE TABLE_NAME =@tViewName
--print @tColumnList
SELECT @tsql ='echo ' + @tColumnList + ' > ' + @tOutputFilePath--Copy Header columns to output Flatfile
exec xp_cmdshell @tsql
--export Data to temp file
SELECT @tsql = 'bcp "SELECT * FROM '+@tObjectName+' WHERE '+@tColumnName+' > '''+CAST(@tDateValue AS nVarchar(255))+'''" queryout '+@tTempFilePath+' -c -t"'+@tDelimiter+'" -r\n -U'+@tUSerID+' -P'+@tPassword+' -S'+@tServerInstanceName
--print @tsql
exec xp_CmdShell @tsql
--Copy Data to Output File
SELECT @tsql ='type ' + @tTempFilePath+ ' >> ' + @tOutputFilePath
exec xp_cmdshell @tsql
--Delete Temp File
SELECT @tsql ='del '+@tTempFilePath
exec xp_cmdshell @tsql
END--2
END
GO
