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 

Wednesday, August 30, 2017

Node.js MSSQL ConnectionError: Failed to connect to localhost:1433 - connect ECONNREFUSED

Today I was trying Node.js to connect to SQL Server, the connection to the sql server failed with the below error.

name: 'ConnectionError',

  message: 'Failed to conncet to localhost

Below are the steps I have followed to resolve this. Step 1 Ensure that the TCP/IP protocol is Enabled as shown below



Step 2

Ensure that the sql server browser is running as shown below.










Step 3

Node.js connections only supports authenticating with SQL Server accounts.

This is not that clear in the documentation.  It took me a while to figure this.

To ensure that the SQL SERVER is configured for sql server authentication do the following
·       In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
·      On the Security page, under Server authentication, select the new server authentication mode, and then click OK.


You might be prompted to restart the sql server. Right click the sql server and restart.  The SQL Server Agent should also be restarted.


 

Thursday, August 24, 2017

SQL Server was unable to communicate with the LaunchPad service.

Today I was trying to run some R scripts against SQL Server.

All of a sudden the "R" scripts no longer worked, they returned the below error:

SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.


To fix this error, I went to Administrative Tools -- Services.  Looked whether the LaunchPad Service is running.  I had to start this service as shown below.



This fixed the error.


Friday, July 21, 2017

Step By Step Guide to resolve SQL server connection issues when connecting from R Studio

Yesterday I tried using a couple of  RevolveScaleR functions -- RxSqlServerData and RxImport.in Rstudio.  These are my learnings as part of this exercise.

The following are the steps that need to be taken for successful connection to the SQLExpress database


  1. Ensure that the SQL Server database has permission for the user that you are using as shown below.

  2. Ensure that the NamedPipes and TCP/IP protocols are Enabled using the SQL Server Configuration Manager. 


  3. Otherwise you will receive an error as -- 
    • [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
  4. When you are enabling the TCP/IP protocol ensure that you specify the port 1433 at the appropriate location.  Otherwise you will receive an error as -- 
    • [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied. 
    • [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
  5. Restart the SQL SERVER service after configuring the above step.
  6. Ensure that the firewall is open to the port 1433.
  7. Ensure that you can run a Telnet session to your sql server IP address.
  8. There is a default user group created named SQLRUserGroupSQLEXPRESS when you install SQLEXPRESS with R   Ensure that the user connecting to the SQL server from R Studio belongs to this group.  Otherwise you will receive an error as --  Error in doTryCatch(return(expr), name, parentenv, handler) :   Could not open data source.

Now coming to the code I have used in R Studio to import the data from the SQL Server Database into R is as follows:



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
#Connection string to connect to SQL Express
connStr <- font=""> paste("Driver=SQL Server; Server=", "SQLEXPRESS",
                 ";Database=", "TestDB", ";Trusted_Connection=true;", sep = "");

#Get the data from the Table
SQL_testdata <- font=""> RxSqlServerData(table = "dbo.rental_data",
                                  connectionString = connStr, returnDataFrame = TRUE);

#Import the data into a data frame
testdata <- font=""> rxImport(SQL_testdata);

#See the structure of the data 
head(testdata);

#See the top rows
str(testdata);



Hope this is useful for those who would like to connect to sql server using RStudio.

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/R...