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.

Tuesday, July 04, 2017

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%'

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.

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





Wednesday, February 26, 2014

Getting the first date of the month in SQL without any functions

One of the date columns in my database is an integer as it is a dimension key
I was trying to get some results grouped for the whole month by this date integer column.

This is how I went about getting it in an easier manner without using any DATEADD and DATDIFF functions

Let us say that the date column name is dim_date_key which is an integer data type

So I used the following SQL code to get the sum of the amount for the month

select sum(amount), dim_date_key/100*100 + 1 month_start_Date from table_name
where dim_date_key/100*100 + 1 >= 20130401
group by dim_date_key/100*100 + 1

Here if we just look at the expression dim_process_date_key/100*100 +1 in mathematical terms it is confusing as to how this can get the start of the month.

But the key here is the integer data type of the date column.


  • When the expression is evaluated, the expression dim_process_date_key/100 gets evaluated first.  This gives the answer as 201304 since the data type is an integer.
  • Then the expression 201304 * 100 is evaluated which yeilds the result as 20130400 which is an integer.  
  • Then the expression 20130400 + 1 is evaluated which yeilds the start date of the month which is 20130401


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