Archive

Posts Tagged ‘sql cmdshell’

SQL Server – Add Date/Time to output file of BCP / SQLCMD (2)

12.04.2013 2 comments

Last time I posted about How you can add date/time to output file name, in which I used xp_cmdshell to execute the BCP/SQLCMD command using TSQL, which means we need to have xp_cmdshell server feature enabled for that solution work. There is a workaround available to that solution when xp_cmdshell is not enabled.

You can also run BCP/SQLCMD command on command prompt (cmd.exe) and append date/time to output file name. You can use below commands:

 

SQLCMD command to add date/time to output filename:

SQLCMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" -o MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt

BCP command to add date/time to output filename:

bcp "EXEC ExportData" queryout MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt -S (local) -T -d SqlAndMe -c

 

You can use ECHO command to verify the file name:

ECHO MyFile_%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.txt

 

How this works:

On command prompt %date% return current date in short format. The ":~6,4" part is like a SUBSTRING function which returns 4 characters starting from position 6, which returns year. Similarly, we are retrieving month, day, hour, minutes using same function and appending all of this together to generate the file name in format "MyFile_YYYYMMDD_HHMM.txt"

Hope This Helps!

Vishal

SQL Server – Add Date/Time to output file of BCP / SQLCMD

09.04.2013 No comments

You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic file names when exporting data. For generating dynamic file names you can use solution provided below. In the examples below I have appended date/time to exported files. You can modify the logic to suit your requirement.

Step 1: First let us create a stored procedure which will provide the data to be exported:

CREATE PROCEDURE ExportData

AS

SET NOCOUNT ON

SELECT 'Vishal', 'SqlAndMe'

GO

 

EXEC dbo.ExportData

GO

Result Set:

—— ——–

Vishal SqlAndMe

I have selected string here to keep things simple. You can specify any query in stored procedure which produces required data.

 

Step 2: Now, we will write the T-SQL code to export data returned from this stored procedure. Here we will use SQLCMD (you can also use BCP) to export data. We will execute SQLCMD using xp_cmdshell extended stored procedure.

DECLARE       @sqlCommand   VARCHAR(1000)

DECLARE       @filePath     VARCHAR(100)

DECLARE       @fileName     VARCHAR(100)

 

SET    @filePath = 'C:Temp'

 

SET    @fileName = 'MyFile_' +

       + CONVERT(VARCHAR, GETDATE(), 112) + '_' +

         CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +

         CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt'

 

SET    @sqlCommand =

       'SQLCMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" -o "' +

       @filePath + @fileName +

       '" -h-1'

 

–Uncomment if you want to use BCP

–SET  @sqlCommand =

—     'bcp "EXEC ExportData" queryout "' +

—     @filePath + @fileName +

—     ' " -S (local) -T -d SqlAndMe -c'

 

–PRINT       @sqlCommand

 

EXEC   master..xp_cmdshell @sqlCommand

GO

The above code will create the required file as "MyFile_YYYYMMDD_HH_MM.txt".

You can verify the command generated by uncommenting the PRINT statement in the code above. Also, you can uncomment the fourth SET statement in case you want to use BCP command to export the data.

Hope This Helps!

Vishal