SQLCMD and SQL Variables

I recently worked on a little project that involved outputting some workflow user stats from a database every hour. The scripts must produce an output file that will be transferred via SFTP to a separate database in another domain. The main bulk of the process was contained within a stored procedure and so the only thing left to do was to put the query results in an output file ready for transfer. Rather than create an SSIS package and use the tools available in this, I wanted to use SQLCMD as this would reduce the amount of code and the overhead of supporting an extra SSIS package for something I deemed fairly small. The Stored Procedure was set up with input parameters because although it was generally going to be run as an automated process with the same parameters, there is always going to be a need to run it ad-hoc with different date parameters.

And that was the problem, using SQLCMD with SQL Variables especially when those parameters are not character strings.

So the two problems I had to overcome was not using SQLCMD variables and using datetime and bit data types. Basically this involved a hack, of sorts.

You cannot use SQL variables in the SQLCMD as firstly, the “EXEC” statement in the SQLCMD is in a different session as to where I have declared my sql variables so they will not be recognised so I need to be able to pass the values into the command dynamically. This wouldn’t normally be too much of a problem but with this SQLCMD also using double-quotes in the “EXEC usp_storedProcedure” statement, it becomes a little more difficult. Add to this, that you cannot pass non character-based strings makes it even more tricky.

I probably should have just given up on this and gone back to my original plan of creating a SSIS package but once I’d started on the SQLCMD solution, I had to persevere.

So here is the method I used. I am probably documenting this more for my own benefit than anyone elses as I don’t want to forget how I solved this, as you never know when this might come in handy again…

DECLARE @startDate datetime, @endDate datetime, @filePath VARCHAR(100), @fileName VARCHAR(100), @ms bit, @fs VARCHAR(3)
SET @filePath = N'D:\FileOutput$\'; -- Location you want your file to output to.
SET @fileName = N'Filename'+ CONVERT(VARCHAR, GETDATE(), 112) 
+ '_' + CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt' -- The filename includes the current date as well as hours and minutes.
set @ms = 1; -- boolean value which will also need converting
set @fs = 'ALL' --a varchar value that doesn't need converting
SET @startDate = CAST(dateadd(DAY,datediff(DAY,1,GETDATE()),0) + CAST('19:00:00' AS TIME) AS datetime) -- Yesterdays date 7pm
SET @endDate = sysdatetime() -- Time Now
DECLARE @SQL VARCHAR(1000) -- variable that holds the SQLCMD and SQL statement 
SET @SQL = 'SQLCMD -S (local) -E -d Images -q "EXEC usp_eg_Ctax_Data_Extract ''' + CONVERT(VARCHAR(23), @startDate, 121) + ''',''' + CONVERT(VARCHAR(23), @endDate, 121) +''',''' + CAST(@ms AS CHAR(1)) + ''',''' + @fs + '''" -o "' + @filePath + @fileName + '" -h -1'
PRINT @cmd
EXEC master ..xp_cmdshell @cmd