Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLCMD Gives Error Must declare the scalar variable


SQLCMD Gives Error Must declare the scalar variable

Author
Message
dbaforever
dbaforever
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 538
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
Ben Teraberry
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1105 Visits: 1199
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
dbaforever
dbaforever
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 538
Thanks bteraberry. you are so right. I will try your idea tomorrow and reply with the results.

John
dbaforever
dbaforever
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 538
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
Adam Gojdas
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 1429
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search