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

• Ben Teraberry

  SSChampion

  Points: 11845

  More actions

  May 19, 2010 at 6:04 pm

  #1169822

  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

  May 19, 2010 at 7:07 pm

  #1169827

  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

  May 20, 2010 at 8:40 am

  #1170167

  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

  May 21, 2010 at 2:23 pm

  #1171101

  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.