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.

Finding Parameter values for subscriptions in a SSRS report

Today I had to look at about 100 subscriptions for a SSRS report, and find if a particular region has been used as a parameter in the subscription,

I tried opening each subscription to see the parameter.  After opening a couple I thought this parameter should be saved in the database so started writing a query to find out all the values of the parameter used in all the subscirptions in the subscriptions database.

The following is the query that I came up with to find out what values were used for the parameters in the subscriptions for  a report.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

WITH [CParameters] AS ( SELECT [SubscriptionID], [Parameters] = CONVERT(XML,a.[Parameters]) FROM [Subscriptions] a ), [MySubscriptions] AS ( SELECT DISTINCT [SubscriptionID], [ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')), [ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)') FROM [CParameters] a CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p) ), [SubscriptionsAnalysis] AS ( SELECT a.[SubscriptionID], a.[ParameterName], [ParameterValue] = (SELECT STUFF(( SELECT [ParameterValue] + ', ' as [text()] FROM [MySubscriptions] WHERE [SubscriptionID] = a.[SubscriptionID] AND [ParameterName] = a.[ParameterName] FOR XML PATH('') ),1, 0, '') +'') FROM [MySubscriptions] a GROUP BY a.[SubscriptionID],a.[ParameterName] ) SELECT a.[SubscriptionID], c.[UserName] AS Owner, b.Name, b.Path, a.[Locale], a.[InactiveFlags], d.[UserName] AS Modified_by, a.[ModifiedDate], a.[Description], a.[LastStatus], a.[EventType], a.[LastRunTime], a.[DeliveryExtension], a.[Version], e.[ParameterName], LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue], SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName FROM [Subscriptions] a INNER JOIN [Catalog] AS b ON a.[Report_OID] = b.[ItemID] LEFT OUTER JOIN [Users] AS c ON a.[OwnerID] = c.[UserID] LEFT OUTER JOIN [Users] AS d ON a.MODIFIEDBYID = d.Userid LEFT OUTER JOIN [SubscriptionsAnalysis] AS e ON a.SubscriptionID = e.SubscriptionID where name like '%Report%'