Seek Predicates (Execution Plans)

Leave a comment

I was researching seek predicate information while working through an execution plan looking to increase query performance. Came across this great article by Craig Freedman that explains seek predicates how they function within single column indexes, multiple column indexes and also how functions muck things up a bit for Index seeks.

Craig Freedman’s SQL Server Blog – Seek Predicates

 

SSRS Subscription emails are not being delivered (SharePoint Integration Mode)

Leave a comment

For the past few days we have had issues with subscriptions not being emailed to our end users. I had not experienced this issue before so I googled and found some posts on Dean Kalanquin’s blog that while helpful did not resolve the issue. They did, however, lead me in the right direction shich is why I posted them below.

Monitoring and Troubleshooting Subscriptions

Troubleshooting Subscriptions: Part II, Using the Reporting Trace Log File

Following the first blog I checked the SQL Agent job and all appeared to be fine according to the job’s history. Next, I looked in to the Events table and the Notifications table. To test the event I grabbed the execution code from the job properties, executed the code then queried the Events table repeatedly until I could see the entry. Crude, yes but I was in a pinch. 🙂

exec [SharePoint_ReportServer].dbo.AddEvent @EventType=’TimedSubscription’, @EventData=’ed209f73-953a-4fe8-96b6-3569bd2151aa’

SELECT * FROM Events

Following the execution I found that records were indeed being placed into both the Events table and the Notifications table as well. This proved that the event processing service was functioning correctly.

My next step was found in Dean’s second blog that lead me to the Trace Log file.

$:\Program Files\Microsoft SQL Server\MSRS10_50.MNPSRS11\Reporting Services\LogFiles\

There I found the error that was returned . .

Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.

This is when the lightbulb above my head lit up. 🙂  I had run across this issue before and it was permissions related. Below is my checklist that will quide you through fixing this issue without blindly applying sysadmin rights to you Report Server service account. 😉

  • Log in to your SQL Reporting Server server and view the configuration options under SQL Server Configuration manager. Once opened click on the SQL Services node and you will see the login used to run the service.
  • Navigate to the login in SQL Server, this under Security>Logins and add sysadmin permissions in Server Roles.
  • Run the subscription via SharePoint, Native Report Server or manually via execution code or SQL Agent job. If the subscription is sent, then the problem is permissions related. If not, hit the Back button and try another blog. 😀
  • Run the following code that grant execute permissions to several objects in the master and msdb databases. Simply Find/Replace [yourlogin] with your login.

GRANT EXECUTE ON msdb.dbo.sp_help_category TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_category TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_job TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_delete_job  TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_help_job TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule  TO [yourlogin]

GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO [yourlogin]

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO [yourlogin]

  • Add the public server role to the MSDB and Master databases under Login properties>User Mappings
  • In addition to the above you will need to GRANT SELECT permissions on ..

msdb.dbo.sysjobs
msdb.dbo.syscategories

Now all that is left is to remove the sysadmin server role and test your subscriptions again. All should be well, if not . . . well I can’t help you then. 😉

 

 

Converting Time data type columns in sysschedules table

Leave a comment

Working on a report that will provide a schedule similar to an Outlook calendar for SQL job scheduling.

From Book Online. . .
“Time on any day between active_start_date and active_end_date that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.”

So, taking into consideration the time is formatted as HHMMSS I used the following script to parse and concatenate the time.

Partial Script
SELECT
LEFT(RIGHT('0' + CAST(active_start_time AS VARCHAR), 6), 2) + ':' + SUBSTRING(RIGHT('0'
+ CAST(active_start_time AS VARCHAR),6), 3, 2) + ':' + RIGHT(CAST(active_start_time AS VARCHAR), 2) 'Start Time'
FROM dbo.sysjobs
INNER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules
ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

I might post the report once completed. 😉

CAST SQL Server system table/view duration as Time

Leave a comment

Parse to your heart’s content . .

SELECT CAST(SUBSTRING(CAST(last_run_duration + 1000000 AS CHAR(7)), 2, 2) AS INT) AS ‘hours’
,CAST(SUBSTRING(CAST(last_run_duration + 1000000 AS CHAR(7)), 4, 2) AS INT) AS ‘mins’
,CAST(SUBSTRING(CAST(last_run_duration + 1000000 AS CHAR(7)), 6, 2) AS INT) AS ‘secs’
FROM msdb..sysjobsteps

CAST SQL Server system table/view dates as Date

Leave a comment

Another weird one . . why does SQL Server store these as integers? :/

SELECT CASE WHEN last_run_date = 0 THEN ‘Missing Date Information’
ELSE SUBSTRING(CAST(last_run_date AS VARCHAR), 5, 2) + ‘/’ + SUBSTRING(CAST(last_run_date AS VARCHAR),
7, 2) + ‘/’ + CAST(last_run_date / 10000 AS VARCHAR)
END AS ‘Last Run Date’
FROM msdb..sysjobsteps

Cast SQL Server system table/view times as DateTime (like)

Leave a comment

Funny lil snag I ran into earlier today with converting the last_run_time in the sysjobsteps system view in SQL Server 2008 R2.

Solution


SELECT last_run_date
,last_run_time
,CAST(CAST(NULLIF(last_run_date, 0) AS VARCHAR(8)) AS DATETIME) + STUFF(STUFF(RIGHT(‘00000’
+ CAST(last_run_time AS VARCHAR(6)), 6), 3, 0, ‘:’), 6, 0, ‘:’) AS [DateTime]
FROM msdb..sysjobsteps


			

%d bloggers like this: