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
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. ­čśë


Custom Report Viewer Permission Level

Leave a comment

An issue that we have run into a few times with SSRS running in SharePoint Integration mode is that we would like for the end users to have access to the Report Services features without having god status or Full Control permissions in the library. To accomplish this a new permission level needs to be created specific to Reporting Services.

There are two ways to go about doing this at the permissions level an I am not a SharePoint guru so take this with a grain of salt. The first option is to create a permission level that only addresses the report viewing permissions. In this case you will need to couple this level with an additional standard SharePoint permission level for additional permissions. (Ex. Add Report Viewer and Design permissions, so the user can not only have Report Viewer permissions but also have Design permissions in the library as well. The second option is to combine two roles into one which is the approach I took. To accomplish this you will need to create the Report Viewer permission level and into it also add the features of another level. This is better illustrated in the below walkthrough.

On the Site Actions menu , click Site Settings.

1. On the Site Settings page, in the Users and Permissions section, click Advanced permissions.

2. If the Settings menu is not available, select Manage Permissions of Parent from the Actions menu. Otherwise, skip to step 4.

Note: The Settings menu is not available on the Permissions page if your site is inheriting permissions from its parent site.

3. On the Permissions page, on the Settings menu, click Permission Levels.

Click permission levels

4. On the Permission Levels page, click Add a Permission Level.

Add permission level

5. On the Add a Permission Level page, in the Name and Description section, type a name and optionally a description.

Add name description and permissions levels

In my case, I added the Report Viewer permissions as well as the View Only permission level permissions. To grab another roles permissions to copy them in to the new permission level simply click the permission level from the Permission Level page and copy the permissions to your new permission level.

*To add report subscription features simply check “Create Alerts” and “Manage Alerts” from within the permissions selection page.

SSRS – Repeating Header Row

Leave a comment

Took me awhile to crack this one . .

In Report Builder ensure that you have Advanced Mode selected in the Row/Column Group section.

select Advanced Options

Once selected you should have several static row and column groups. Select the Header Row group in the Row Groups section. If

you are unsure which static group you are selecting look into the report pane. Every time a group is selected its corresponding cell is

highlighted as well. Once the header group is found, look at the properties and set them as follows; Fixed Data = True, RepeatOnNewPage=True.

Make the proper selections

Now you’re all set.

*If you cannot see the properties of the static group head to the view Tab at the top of the application and ensure the properties check box is checked.

Cannot preview Excel spreadsheet data in BIDS 2008

Leave a comment

Simple error returned when trying to view sheet data in Excel from within BIDS.

“There was an error displaying the preview. (Microsoft Visual Studio)

Index and length must refer to a location within the string.

Parameter name: length (mscorlib)”


BIDS doesn’t do well with spaces in the Excel sheet name. Remove the spaces or replace them with underscores lazy programmer. ­čśë

%d bloggers like this: