Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQLCMD Gives Error Must declare the scalar variable Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2010 4:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:58 PM
Points: 150, Visits: 498
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

Post #924722
Posted Wednesday, May 19, 2010 6:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 1:49 PM
Points: 1,104, Visits: 1,174
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
Post #924760
Posted Wednesday, May 19, 2010 7:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:58 PM
Points: 150, Visits: 498
Thanks bteraberry. you are so right. I will try your idea tomorrow and reply with the results.

John

Post #924766
Posted Thursday, May 20, 2010 8:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:58 PM
Points: 150, Visits: 498
Your idea worked perfectly, bteraberry. Thank you.

Now I have a different issue but I will start a new thread for that.
John
Post #925186
Posted Friday, May 21, 2010 2:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
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.
Post #926275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse