SQLCMD Gives Error Must declare the scalar variable

Question

Post reply

SQLCMD Gives Error Must declare the scalar variable

dbaforever

Hall of Fame

Points: 3740
More actions
May 19, 2010 at 4:00 pm

#139087

hi experts,
This is 2005.
I'm trying to run a Select stmt via sqlcmd, to generate a simple report. After overcoming a few syntax errors, I am almost there but I get
error "Msg 137 Must declare the scalar variable "@PriorMonthStart" at the SET @cmd statement.
Thanks for any tips.
John
DECLARE @curDate datetime
DECLARE @PriorMonthStart datetime
DECLARE @PriorMonthEnd datetime
DECLARE @sqlcmd varchar(1000);
DECLARE @cmd varchar(1000);
SELECT @curDate = GETDate()
-- 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 '
--** error occurs here
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'
-- The above Statement gives error - Must declare the scalar variable "@PriorMonthStart"
SET @sqlcmd = @sqlcmd + @cmd;
EXEC xp_cmdshell @sqlcmd,no_output;
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

Couple of things:

SET NOCOUNT ON;

BEGIN

This needs to be at the beginning before you declare your variables.

Also, you're passing a string into xp_cmdshell that has "@PriorMonthStart" and "@PriorMonthEnd". You need to build the string in such a way that the values you want are passed, not the variable names:

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'

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

dbaforever Hall of Fame Points: 3740 More actions · Answer 2

Thanks bteraberry. you are so right. I will try your idea tomorrow and reply with the results.

John

dbaforever Hall of Fame Points: 3740 More actions · Answer 3

Your idea worked perfectly, bteraberry. Thank you.

Now I have a different issue but I will start a new thread for that.

John

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 4

Additionally you might try something like the following utilizing sqlcmd variables. It allows you to leave the query the same. You just need to add the variables to be used. I think it may allow the query plan to be cached since it actually uses the variables within the sql statement similar to dynamic sql when using sp_executesql.:

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

EDIT: for the CONVERT of datetime I changed to use 101 - CONVERT(VARCHAR(20),@PriorMonthStart,101) - you may need to choose the specific one that you need.