The WordPress.com stats helper monkeys prepared a 2011 annual report for this blog.
Here’s an excerpt:
A San Francisco cable car holds 60 people. This blog was viewed about 1,700 times in 2011. If it were a cable car, it would take about 28 trips to carry that many people.
One of our customer would like to email result of a SQL Agent job to email id. related thread. You can achieve this by enabling job step output to a file, add a new job step in current job that calls sp_send_dbmail stored procedure to emails with file attachments
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name
FROM msdb.dbo.syscategories
WHERE name = N’[Uncategorized (Local)]‘
AND category_class = 1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category
@class=N’JOB’,
@type=N’LOCAL’,
@name=N’[Uncategorized (Local)]‘
IF ( @@ERROR <> 0
OR @ReturnCode <> 0 )
GOTO quitwithrollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job
@job_name=N’DBCC IndexDEFRAG’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@job_id = @jobId OUTPUT
IF ( @@ERROR <> 0
OR @ReturnCode <> 0 )
GOTO quitwithrollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id=@jobId,
@step_name=N’DBCC index defrag on sysjobhistory’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N’TSQL’,
@command=N’DBCC INDEXDEFRAG(msdb, ”sysjobhistory”)’,
@database_name=N’msdb’,
@output_file_name=N’c:\temp\dbccindexdefrag.txt’,
@flags=0
IF ( @@ERROR <> 0
OR @ReturnCode <> 0 )
GOTO quitwithrollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id=@jobId,
@step_name=N’send email’,
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N’TSQL’,
@command=N’EXEC sp_send_dbmail
@profile_name=”msft”,
@recipients=”seths@microsoft.com”,
@subject=”Index Defrag job output”,
@body=”Index Defrag report”,
@file_attachments=”C:\temp\dbccindexdefrag.txt”
’,
@database_name=N’msdb’,
@flags=0
IF ( @@ERROR <> 0
OR @ReturnCode <> 0 )
GOTO quitwithrollback
EXEC @ReturnCode = msdb.dbo.sp_update_job
@job_id = @jobId,
@start_step_id = 1
IF ( @@ERROR <> 0
OR @ReturnCode <> 0 )
GOTO quitwithrollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @jobId,
@server_name = N’(local)’
IF ( @@ERROR <> 0
OR @ReturnCode <> 0 )
GOTO quitwithrollback
COMMIT TRANSACTION
GOTO endsave
QUITWITHROLLBACK:
IF ( @@TRANCOUNT > 0 )
ROLLBACK TRANSACTION
ENDSAVE:
GO
T-SQL Script:
Emailing job output as a file attachment
Note: You can download SQL 2012 Developer edition if you are a MSDN subscriber
OR
Download SQL 2012 Evaluation:
1) To download evaluation edition Goto Download
for x86, Download following files to local folder on your machine
ENU\x86\SQLFULL_x86_ENU_Core.box
ENU\x86\SQLFULL_x86_ENU_Install.exe
ENU\x86\SQLFULL_x86_ENU_Lang.box
2) Open command window, extract downladed files to local folder
CD c:\SQL2012\
SQLFULL_x86_ENU_Install.exe
You will see extracted files under C:\SQL2012\SQLFULL_x86_ENU
Installation:
1) Open Command window as Elevated Administrator
2) CD C:\SQL2012\SQLFULL_x86_ENU
Unattended setup command line
Setup /QS
/ACTION=install
/INSTANCEID="SQL2012EVAL"
/INSTANCENAME="MSSQLSERVER"
/IACCEPTSQLSERVERLICENSETERMS=1
/FEATURES=SQL,Tools
/SQLSYSADMINACCOUNTS="machine-name\Administrators"
/BROWSERSVCSTARTUPTYPE=AUTOMATIC
/SECURITYMODE=SQL
/SAPWD="Password#1234$"
/INDICATEPROGRESS
/TCPENABLED=1
/AGTSVCSTARTUPTYPE="Automatic"
Above command does the following
- Installs SQL Server & Management tools
- Grants SQL sysadmin privilege for localgroup “Administrators”
- Enables TCP protocol for SQL instance
- Autostarts SQL Browser service
- Autostarts SQL Server Agent
5) Wait for setup.exe to complete
Theme: Rubric. Blog at WordPress.com.
