Tuesday, June 27, 2017

Rerunning all subscriptions for a particular report that have failed.

Today I had a situation where a few of the subscriptions for a particular report have failed. I needed a script to run only these subscriptions manually. Basically I am extending my previous blog post to achieve this by passing the result of my query into a cursor and executing the subscriptions using the sp_start_job stored procedure as follows:

Do not forget to deallocate the cursor.

use ReportServer
go
DECLARE @ScheduledReportName varchar(200)
DECLARE @JobID uniqueidentifier
DECLARE @LastRunTime datetime
Declare @JobStatus Varchar(100)
--------------------------------------------------------
DECLARE @RunAllReport CURSOR
SET @RunAllReport = CURSOR FAST_FORWARD
FOR
SELECT 
CAT.[Name] AS RptName
, res.ScheduleID AS JobID
, sub.LastRuntime
, LastStatus
FROM
dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON CAT.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON CAT.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN dbo.Users U
ON U.UserID = sub.OwnerID
where res.ReportID ='3AA8BDD0456' 
AND  LastRunTime > GETDATE() - 1 AND LastStatus not like 'Mail sent%' AND LastStatus not like '%has been saved to%' AND LastStatus not like '%0 errors%' --AND LastStatus not like 'Pending%' 

OPEN @RunAllReport
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
WHILE @@FETCH_STATUS = 0
BEGIN
Print @ScheduledReportName --&' ' & @JobID
EXEC msdb.dbo.sp_start_job @job_name =@JobID
FETCH NEXT FROM @RunAllReport
INTO @ScheduledReportName,@JobID,@LastRunTime,@JobStatus
END
CLOSE @RunAllReport
DEALLOCATE @RunAllReport

Thursday, June 22, 2017

Rerunning a SSRS subscription report

Yesterday I had a situation where a couple of SSRS reports failed due to a network upgrade.

The SSRS report was not able to access the SQL server that was being used in the stored procedure.

So I had to rerun the report subscription manually.

To re-run the SSRS report subsciption I followed two steps.

Step 1:

I have used the below query to identify the job id of the failed reports.

SELECT
     S.ScheduleID AS SQLAgent_Job_Name, Sub.LastStatus, sub.LastRunTime, LastRunStatus
     ,SUB.Description AS Sub_Desc
     ,SUB.DeliveryExtension AS Sub_Del_Extension
     ,C.Name AS ReportName
     ,C.Path AS ReportPath

FROM ReportSchedule RS
     INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
     INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
     INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE
sub.LastRunTime > GETDATE() - 1 and  C.Name LIKE 'Reportname'

Step 2:

I have copied the scheduleid and used a filter in the Jobs of the SSRS server as shown below.

Then Right click the Job and click 'Start Job at Step'

There are several other practices to achieve the same result.but I find that this is easier to follow.

Monday, June 12, 2017

Find the stored procedures and agents that use Linked Server

Today I had a scenario where I had to identify all the stored procedures and agents that used a particular Linked Server. We are thinking of getting rid of these multiple Linked Servers and combining them into one server.

So I have used the sys.sql_modules table to find out what stored procedures are using these linked servers.

The code I have written is as follows.

SELECT OBJECT_NAME(object_id) object , *
FROM sys.sql_modules
WHERE
Definition LIKE '%linkedsrv1%'
OR
Definition Like '%linkedsrv2%'
OR
Definition Like '%linkedsrv3%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;

I have also used the sysjobsteps and sysjobs tables from msdb database to find out what jobs are using these Linked Servers,

The code I have written is as follows:

SELECT j.name AS JobName,js.command
FROM msdb.dbo.sysjobsteps jsteps
INNER JOIN msdb.dbo.sysjobs jobs
ON jobs.job_id = jsteps.job_id
WHERE
jsteps.command LIKE '%linkedsrv1%'
OR
jssteps.command Like '%linkedsrv2%'
OR
jsteps.command Like '%linkedsrv3%'

Next question that I am pondering is if we consolidate these servers into one server, what are the considerations I need to be aware of.

I will be covering this in a future post. Meanwhile appreciate some feedback from the readers.

Tuesday, June 06, 2017

Sending Automatic Emails from SQL server

I am excited to reactivate my blog after 2.5 years.

I have written a stored procedure last week to send automated emails to customers based on certain criteria.

To identify this there is a stored procedure that requires some parameters.

The steps followed for creating the stored procedure are as follows:

There is already an existing stored procedure that creates a temp table and inserts data into that temp table.
In the new stored procedure define a new temp table.
Insert data into this temp table by executing the existing stored procedure
Declare new variables to hold the needed for the email.
In a while loop assign the data to the above variables for each record and send the email one by one using sp_send_dbmail built in stored procedure.

Here is the skeleton code I came up with for the procedure. I have configured a HTML email

[code]

Declare @temp_table table
(
ID INT IDENTITY(1, 1) primary key ,
field1 varchar(60) null,
AccountID VARCHAR(25) null,
AccountName varchar(200) null,
AccountEMail varchar(128) null
)

--
-- Insert into the temp table variable -- the list to be emailed by running the stored procedure Proc1
--

Insert into @temp_table
(

field1,
AccountID,
AccountName,
AccountEMail

)
Exec [Proc1]
@Parameter1 = 'NZIO',
@Parameter2 = 1,
@Parameter3=0

--
-- Declare new variables
--

DECLARE @count INT
DECLARE @accountemail varchar(60)
DECLARE @accountname varchar(60)
DECLARE @rowcount INT
DECLARE @href1 varchar(256)

--setting count to start from first row
SET @count = 1
SET @rowcount = (SELECT count(*) FROM @temp_table)
--Print @rowcount

--
--loop to send email from temp table
--

WHILE(@count < @rowcount)
BEGIN
SET @accountemail = (SELECT AccountEMail FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')
SET @accountname = (SELECT AccountName FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')
SET @href1 = (SELECT CASE When ClientMode = 'UNATTENDED (HANDS-FREE)' then 'http://link1.pdf' else 'http://link.pdf' END FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')

--
--Configure Email body
--

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'

Automatic Notification -- Please do not reply

' +
N'Good Morning ' +
cast(@accountname as nvarchar(max)) +
N' We are contacting you to let you know that blah blah (EDI: ' +
cast(@field1 as nvarchar(max)) +
N')
is not working. Please have a look at your account+ cast(@accountname as nvarchar(max)) +
N' Please click on the below link to view user guide on starting
' + N' Userguide for Windows ' +
N' Regards BI Support '

;

--
-- Send email using Database Mail
--

--USE msdb
--GO
EXEC MSDB.dbo.sp_send_dbmail @profile_name='Testreport',
@copy_recipients = @accountemail,
@recipients = 'iemail@emailadd.com',
@subject='Test message',
@body= @tableHTML,
@body_format = 'HTML' ;

SET @count= @count +1

END

[/code]

My Tech Learning