CREATE PROCEDURE [dbo].[WriteRecordToCSVFile]

@ServerAddress varchar(128), — Server full address
@Username varchar(128), — DB server user name
@Password varchar(128), — DB server password
@Database varchar(128), — Database name where have to perform query
@Query varchar(3000), — Query which has to perform and whose data needs to save in CSV file
@DestPath varchar(128), — Destination path relative to DB server. Path must be absolute
@DestFileName varchar(128) — CSV file name.

As

EXEC sp_configure ‘show advanced options’, 1

— To update the currently configured value for advanced options.

RECONFIGURE

— To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

— To update the currently configured value for this feature.

RECONFIGURE

DECLARE @bcpCommand varchar(4000)

select @ServerAddress = replace(replace(replace(@ServerAddress, ‘|’, ‘^|’),'<‘,’^<‘),’>’,’^>’)
select @Username = replace(replace(replace(@Username, ‘|’, ‘^|’),'<‘,’^<‘),’>’,’^>’)
select @Password = replace(replace(replace(@Password, ‘|’, ‘^|’),'<‘,’^<‘),’>’,’^>’)
select @Database = replace(replace(replace(@Database, ‘|’, ‘^|’),'<‘,’^<‘),’>’,’^>’)
select @DestPath = replace(replace(replace(@DestPath, ‘|’, ‘^|’),'<‘,’^<‘),’>’,’^>’)
IF RIGHT(@DestPath, 1) <> ‘\’ SET @DestPath = @DestPath + ‘\’

SET @bcpCommand = ‘sqlcmd -k2 -s, -W -S “‘ + @ServerAddress + ‘” -U “‘ + @Username + ‘” -P “‘ + @Password + ‘” -d “‘ + @Database + ‘” -Q “‘ + @Query + ‘” -o “‘ + @DestPath + @DestFileName + ‘”‘

EXEC master..xp_cmdshell @bcpCommand

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s