Sunday, September 17, 2017

Could not load folder contents - SSRS Report Manager

When I tried to access the report manager after a fresh installation of Sql Server Reporting Serivces 2016 using the url http://localhost/Reports , I received the below error.



By default the installation gives access to Builtin\Administrators. In order to resolve this error below are the steps that you can follow.
Step 1 Run internet explorer as Administrator and open the Report Manager URL http://localhost/Reports



Step 2 Click on Site Settings icon and then click on Security as shown.  You can see only one assignment of roles here for Builtin\Administrators

Step 3 Click on Add group or user assign the role assignment as shown below. 



Re-open internet explorer (non-admin) and recheck the url. You will not get the error anymore.

Friday, September 08, 2017

SSIS Error -- Exception deserializing the package "Access to the path ...... denied'

I was trying to build an SSIS package that one of my colleagues has created.

The first error that was thrown was  as below:
Exception deserializing the package "Access to the path 'C:\BACKup\Test\Test\bin\Development\Tcopy.ispac' is denied.".

Here are the steps that I have followed to resolve this.

Step 1  Check whether the package user has access to the path of the solution.
Step 2. Close the solution.
Step 3  Open Task Manager and end any process of DTS.  as shown below.







Step 4  Delete the .ispac file that is created in the bin/Development folder
Step 5  Reopen the solution and rebuild the package.

Hope these steps will help you resolve the build issues 

Monday, September 04, 2017

My presentation at SQL Saturday Auckland #655

I had an awesome day presenting at #sqlSaturday Auckland on 2nd September 2017.  My topic was on Predictive Analytics using  R  in SQL Server.



Link for my presentation below

Predicitve Analytics using R in SQL Server 2016




Friday, September 01, 2017

Rerun failed SSRS subscriptions

Below is the script I have used to rerun the SSRS Report subscriptions that have failed today.



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
, CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus

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
sub.LastRunTime > GETDATE() - 1 and  Cat.Name LIKE 'reportname%' 
>convert(date,GETDATE()-2)
ORDER BY U.UserName, RptName 
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 

Deploy the Azure Machine Learning Model

In the previous post I have discussed how to create an Azure Machine Model.  In this post I will be discussing how to Deploy this model. Pre...