Using SQLCMD - Can Output File Name be a Variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Using SQLCMD - Can Output File Name be a Variable?

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-24 : 15:19:25

hi all,

I'm creating a report via SQLCMD and the report name needs to contain the current date so I chose to store the complete path and file name in a variable.

**But when I specify the variable name in sqlcmd, the report file is not generated. **

Print @ReportPath shows C:\AuditReports\RamcoFailedLogins2010524.txt
which looks valid. The folder AuditReports does exist so I don't know why the report is not being created.

Any ideas, please?
Thanks, John

DECLARE @curDate datetime
DECLARE @PriorMonthStart datetime
DECLARE @PriorMonthEnd datetime
DECLARE @sqlcmd varchar(1000);
DECLARE @cmd varchar(1000);
DECLARE @ReportPath varchar(200);
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
-- Construct report path and name
Set @ReportPath = 'C:\AuditReports\' + 'RamcoFailedLogins' + Convert(nvarchar(4),DATEPART(yyyy, getdate()))+ Convert(nvarchar(2),DATEPART(mm, getdate()))+ Convert(nvarchar(2),DATEPART(dd, getdate())) + '.txt'
--Set @ReportPath = 'C:\AuditReports\' + 'RamcoFailedLogins' + @PriorMonthEnd + '.txt' get conversion error
print @ReportPath
SET @sqlcmd = 'sqlcmd -Q '
SET @cmd = '"SELECT CAST(ServerName as char(15)) as Server, CAST(LoginName as char(22)) as FailedLogin, CAST(ClientName as char(20)) 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 @ReportPath'
SET @sqlcmd = @sqlcmd + @cmd;
Print @sqlcmd;
EXEC xp_cmdshell @sqlcmd,no_output;
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-24 : 15:28:28

Could you show us the output of PRINT @sqlcmd? Could you remove the NO_OUTPUT option and show us the output of xp_cmdshell?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-24 : 16:04:27

Thanks for your reply, Tara.
Print @ReportPath shows:
C:\AuditReports\RamcoFailedLogins2010524.txt
?? I don't understand why the month is not 2 digits 05 rather than 5

sqlcmd -Q "SELECT CAST(ServerName as char(15)) as Server, CAST(LoginName as char(22)) as FailedLogin, CAST(ClientName as char(20)) as ComputerName, LoginDate from DBA.dbo.AuditDatabaseLogins WHERE AuditType = 'Login Failed' AND LoginDate between '20100401' and '20100501' " -o @ReportPath

Thanks, John

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-24 : 16:14:57

I fixed @ReportPath as well as @sqlcmd.

DECLARE @curDate datetime
DECLARE @PriorMonthStart datetime
DECLARE @PriorMonthEnd datetime
DECLARE @sqlcmd varchar(1000);
DECLARE @cmd varchar(1000);
DECLARE @ReportPath varchar(200);
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
-- Construct report path and name
Set @ReportPath = 'C:\AuditReports\' + 'RamcoFailedLogins' + Convert(varchar(8), GETDATE(), 112) + '.txt'
--Set @ReportPath = 'C:\AuditReports\' + 'RamcoFailedLogins' + @PriorMonthEnd + '.txt' get conversion error
print @ReportPath
SET @sqlcmd = 'sqlcmd -Q '
SET @cmd = '"SELECT CAST(ServerName as char(15)) as Server, CAST(LoginName as char(22)) as FailedLogin, CAST(ClientName as char(20)) 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 ' + @ReportPath
SET @sqlcmd = @sqlcmd + @cmd;
Print @sqlcmd;
--EXEC xp_cmdshell @sqlcmd,no_output;
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2010-05-24 : 16:39:01

ah, yours is a much cleaner way of stringing the date.

Set @ReportPath = 'C:\AuditReports\' + 'RamcoFailedLogins' + Convert(varchar(8), GETDATE(), 112) + '.txt'

Thanks very much, TKizer!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-24 : 16:45:28

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

binsel
Starting Member

5 Posts

Posted - 2010-06-30 : 08:25:31

Tara,

I have script running against multiple SQL Servers. It creates one output file per sql instance and the instance names are in the file names. Do you know how I can add MACHINE NAMES to the output files?

I tried using %COMPUTERNAME% but it gave me the same comp. name I ran the script from.

set dt=none
set tm=none
for /F "tokens=2-4 delims=/ " %%i in ('date /t') do set dt=%%i%%j%%k
for /F "tokens=5-6 delims=:. " %%i in ('echo.^| time ^| find "current" ') do set tm=%%i%%j
for /F %%a in (SQL2005servers.txt) do sqlcmd -E -S %%a -d master -i c:\SEC\SEC-SQLSr_2005_2008_201006220000.sql -Y30 -s "|" -o c:\SEC\OUT2005\SQL_Server_%COMPUTERNAME%_%%a_%dt%%tm%.out

Thanks,

Burhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources