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.

     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)
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
Definition LIKE '%linkedsrv1%'
Definition Like '%linkedsrv2%'
 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 AS JobName,js.command
      FROM msdb.dbo.sysjobsteps jsteps
         INNER JOIN msdb.dbo.sysjobs jobs
            ON jobs.job_id = jsteps.job_id
 jsteps.command LIKE   '%linkedsrv1%'
jssteps.command Like  '%linkedsrv2%'
 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


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


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

-- Declare new variables

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)
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


SET @tableHTML =

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)) +
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
EXEC MSDB.dbo.sp_send_dbmail @profile_name='Testreport',
@copy_recipients = @accountemail,
@recipients = '',
@subject='Test message',
@body= @tableHTML,
@body_format = 'HTML' ;

SET @count= @count +1



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 SQ...