Date Comparison Not Selecting Any Rows

Question

Post reply

Date Comparison Not Selecting Any Rows

dbaforever

Hall of Fame

Points: 3740
More actions
May 20, 2010 at 8:53 am

#140464

hello experts,
The code (below) does not give an error, but it also does not retrieve any records/generate a report.
I suspect that conversion of @PriorMonthStart (within the SELECT stmt) may be the problem but I'm not sure.
Print @PriorMonthStart shows Mar 31 2010 11:59PM
Print @PriorMonthEnd shows Apr 30 2010 11:59PM
Again, records DO exist within this date range, but none are selected.
Can anyone find my mistake?
Thank you. John
----------------------------------------
SET NOCOUNT ON;
BEGIN
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))
Print @PriorMonthEnd
-- derive first day of prior month
SET @PriorMonthStart = dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )-1, 0))
Print @PriorMonthStart
SET @sqlcmd = 'sqlcmd -Q '
SET @cmd = '"SELECT ServerName as Server, LoginName as FailedLogin, ClientName as ComputerName, LoginDate FROM DBA.dbo.AuditDatabaseLogins WHERE AuditType = ''Login Failed''
AND LoginDate between ''' + convert(varchar(10), @PriorMonthStart, 112) + ''' and ''' + convert(varchar(10), @PriorMonthEnd, 112) + '''
ORDER BY LoginDate" -o C:\AuditReports\FailedLogins.txt'
SET @sqlcmd = @sqlcmd + @cmd;
EXEC xp_cmdshell @sqlcmd,no_output;
END

Viewing 7 posts - 1 through 6 (of 6 total)

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

patel_mayur Valued Member Points: 72 More actions · Answer 1

patel_mayur

Valued Member

Points: 72

May 20, 2010 at 9:54 am

#1170262

Print your @sqlcmd and you will come to know

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

Thank you, patel_mayur.

Print @sqlcmd; shows ....

sqlcmd -Q "SELECT ServerName as Server, LoginName as FailedLogin, ClientName as ComputerName, LoginDate FROM DBA.dbo.AuditDatabaseLogins WHERE AuditType = 'Login Failed'

AND LoginDate between '20100331' and '20100430'

ORDER BY LoginDate" -o C:\AuditReports\Testsqclcmd10.txt

And when I run this Select stmt (without the output to report) it returns the requested records.

I'm sorry, I do not see what the problem is.

Thanks, John

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

... to review the situation:

If I remove the WHERE clause from the Select stmt, it generates a report - even when I run it in SQLCMD.

Does SQLCMD not like something about my WHERE clause .....?

WHERE AuditType = ''Login Failed''

What oh what have I done wrong?

thanks, John

LutzM SSC Guru Points: 107049 More actions · Answer 4

Your query itself seems to be valid in terms of syntax.

You should narrow the problem down in SSMS and build the dynamic string later.

My guess would be there are either no rows with AuditType = 'Login Failed' or at least there is a different spelling/addtl. information in that filed.

I would run the following two queries:

SELECT ServerName as Server, LoginName as FailedLogin, ClientName as ComputerName, LoginDate

FROM DBA.dbo.AuditDatabaseLogins

WHERE LoginDate between '20100331' and '20100430' ORDER BY LoginDate

SELECT ServerName as Server, LoginName as FailedLogin, ClientName as ComputerName, LoginDate

FROM DBA.dbo.AuditDatabaseLogins

WHERE AuditType = 'Login Failed' ORDER BY LoginDate

and check if both will return results that should match the complete query (e.g. the first one should include 'Login Failed' rows and the second one should have rows in the requested range.

Btw: what's the data type of your LoginDate column? Pot shot: if it's character and stored as '2010-03-31' then you won't find any matching rows... Just guessing though.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

Thanks for the reply Hall of Famer!

The data type of column LoginDate is smalldatetime

Yes, there are qualifying rows in the table. I have confirmed that (more than once 🙂

You could probably write a book about what I dont know about using SQLCMD 🙂

Let's call it a day. I'll post back tomorrow.

Thanks for your interest.

John - Memphis TN USA

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

I was able to get it to work. Thanks to everyone who replied with ideas.

this is the final code to generate the report:

DECLARE @curDate datetime

DECLARE @PriorMonthStart datetime

DECLARE @PriorMonthEnd datetime

DECLARE @sqlcmd varchar(1000);

DECLARE @cmd varchar(1000);

SET NOCOUNT ON;

BEGIN

SELECT @curDate = GETDate()

-- derive last day of the prior month

SET @PriorMonthEnd = dateadd(ms,+1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

Print @PriorMonthEnd

-- derive first day of prior month

SET @PriorMonthStart = dateadd(ms,+1,DATEADD(mm, DATEDIFF(m,0,getdate() ) -1, 0))

Print @PriorMonthStart

SET @sqlcmd = 'sqlcmd -Q '

SET @cmd = '"Select ServerName as Server, LoginName as FailedLogin, ClientName as ComputerName, LoginDate from DBA.dbo.AuditDatabaseLogins WHERE AuditType = ''Login Failed'' AND LoginDate between ''' + convert(varchar(10), @PriorMonthStart, 112) + ''' and ''' + convert(varchar(10), @PriorMonthEnd, 112) + ''' " -o C:\AuditReports\TestSqlCmd31.txt'

SET @sqlcmd = @sqlcmd + @cmd;

Print @sqlcmd;

EXEC xp_cmdshell @sqlcmd,no_output;

END