SET NOCOUNT ON;
BEGIN

SET @cmd = 
	'"SELECT ServerName as Server, 
		LoginName as FailedLogin, 
		ClientName as ComputerName, 
		LoginDate 
	FROM DBA.dbo.AuditDatabaseLogins 
	WHERE AuditType = ''Login Failed'' 
		AND LoginDate between ''' + @PriorMonthStart + ''' and ''' +  @PriorMonthEnd + '''
	ORDER BY LoginDate" -o C:\AuditReports\FailedLogins.txt'

DECLARE @curDate datetime
DECLARE @PriorMonthStart datetime
DECLARE @PriorMonthEnd datetime
DECLARE @sqlcmd varchar(1000);
DECLARE @cmd varchar(1000);
SELECT @curDate = GETDate()

--create a variable to use for new line
DECLARE @carReturnLineFeed varchar(10);
SET @carReturnLineFeed = char(13) + char(10); /*carriage return & line feed*/

-- derive last day of the prior month
SET @PriorMonthEnd = dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

-- derive first day of prior month
SET @PriorMonthStart = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )-1, 0))

SET NOCOUNT ON;
BEGIN
SET @sqlcmd = 'sqlcmd -Q -v PriorMonthStart=' + CONVERT(VARCHAR(20),@PriorMonthStart,101) + ' PriorMonthEnd=' + CONVERT(VARCHAR(20),@PriorMonthEnd,101)

--** error occurs here
SET @cmd = @carReturnLineFeed
SET @cmd = @cmd + '"DECLARE @PriorMonthStart datetime' + @carReturnLineFeed
SET @cmd = @cmd + 'DECLARE @PriorMonthEnd datetime' + @carReturnLineFeed
SET @cmd = @cmd + 'SET @PriorMonthStart = CAST(''$(PriorMonthStart)'' AS datetime)' + @carReturnLineFeed
SET @cmd = @cmd + 'SET @PriorMonthEnd = CAST(''$(PriorMonthEnd)'' AS datetime)' + @carReturnLineFeed
SET @cmd = @cmd + 'SELECT ServerName as Server, LoginName as FailedLogin, ClientName as ComputerName, LoginDate FROM DBA.dbo.AuditDatabaseLogins WHERE AuditType = ''Login Failed'' AND LoginDate between @PriorMonthStart and @PriorMonthEnd ORDER BY LoginDate" -o C:\AuditReports\FailedLogins.txt'
-- The above Statement gives error - Must declare the scalar variable "@PriorMonthStart"

SET @sqlcmd = @sqlcmd + @cmd;

EXEC xp_cmdshell @sqlcmd,no_output;
--PRINT @sqlcmd
END