SQLCMD Select Stmt Does Not Create Report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQLCMD Select Stmt Does Not Create Report

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-20 : 14:17:56

hello everyone,
This is 2005

I'm building a new procedure to generate a simple report.

No errors but no report is produced.

Can anyone help please? Thanks, 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;
Print @sqlcmd;
EXEC xp_cmdshell @sqlcmd,no_output;
END

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\FailedLogins.txt

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

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-20 : 14:25:39

Assuming you do not get an EXECUTE permission denied error message when you run this from SQL, have you tried running the command from the command prompt in windows? Does it work?

If it does and you are not getting an error, maybe you are expecting the file to be produced locally (on your client system), when in fact the xp_cmdshell is running on the server and producing the file relative to the server?

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-20 : 14:43:43

Thanks for your reply, Lazareth.

No permissions errors. I checked server and my desktop. The report was not generated. But it WILL produce a report when I exclude the date criteria in my Select stmt. I'm doing something wrong there but I don't know what....

John

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-20 : 15:39:23

Ah, what is the datatype of LoginDate?

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-20 : 16:07:31

lazerath,

In the table, the loginDate column is smalldatetime

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\FailedLogins.txt

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

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

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-20 : 17:35:30

John,

Something isn't adding up. You were able to run this command from the command line and you got results in the file C:\AuditReports\FailedLogins.txt?

sqlcmd -Q "SELECT ServerName as Server, LoginName as FailedLogin, ClientName as ComputerName, LoginDate FROM DBA.dbo.AuditDatabaseLogins WHERE AuditType = 'Login Failed' ORDER BY LoginDate" -o C:\AuditReports\FailedLogins.txt

Note, I removed the line "AND LoginDate between '20100331' and '20100430'".

I noticed you don't specify the server and connection type. Try adding this:
-S [SERVER NAME] -E

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-21 : 14:44:56

Lazerath, sorry for the delay.

I WAS able to get it to work. You are correct, something was wrong yesterday. I don't know why but I was getting varying and unpredictable results yesterday.

This is what I have now and it is working consistently:

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\LoginFailuresReport.txt'
SET @sqlcmd = @sqlcmd + @cmd;
Print @sqlcmd;
EXEC xp_cmdshell @sqlcmd,no_output;
END

Now I need to work on the aesthetics, reducing the lenght of the printed columns and things like that. I may start a new thread for that.

Thanks for your help and your ideas.
John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources