How long a SQL Agent job takes to run

USE msdb
select
j.job_id
,j.name as job_name
, s.step_name
, s.step_id
-- , s.command
,dbo.agent_datetime(run_date, 0) as step_start_date
,dbo.agent_datetime(run_date, run_time) as step_start_date_time
--,h.instance_id
,h.run_duration
,run_duration/10000 as run_duration_hours_only --hours
,run_duration/100%100 as run_duration_minutes_only --minutes
,run_duration%100 as run_duration_second --seconds
,(run_duration/10000 * 60 * 60) + -- hours as seconds
(run_duration/100%100 * 60) + --minutes as seconds
(run_duration%100 ) as run_duration_total_seconds --seconds
-- , h.*
from msdb.dbo.sysjobs j inner join
msdb.dbo.sysjobsteps s on j.job_id = s.job_id inner join
msdb.dbo.sysjobhistory h on s.job_id = h.job_id and s.step_id = h.step_id
where
1=1
and j.name like  'My Job Name'
--order by h.instance_id desc

Create a "LIKE " type filter for a string parameter in SSRS report

You can create a simple user defined filter parameter by applying a filter to the recordset and writing a bit of VB code

The VB code is as follows:

Public Function fnLike(ByVal val as string, ByVal Filter as string) AS Boolean

val = lcase(val)
Filter = lcase(Filter)

If len(trim(filter)) = 0 then
    Return True
End If

If val.contains(filter) Then
    Return True
Else
    Return False
End If
End Function

To implement simply put the following in to the Filter Expression for the recordset

=code.fnLike(Fields!EMPLOYEE_NAME.Value, Parameters!PractitionerName.Value)

For the other parameters for the filter set

• Type - Boolean
• Operator "="
• Value - True

Get date format sql is using

select dateformat from sys.syslanguages   where name = @@LANGUAGE

SSRS - Report Run History

Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function

SSRS - Report Run History

Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function

Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function

Option Explicit

Dim objConnection, objCommand, objRootDSE, strDNSDomain, strQuery
Dim objRecordSet, strDN, objGroup
Dim sEmpID, aManager, sManager
Dim objDB
dim strODBC, oRS

'this constant is used to define ODBC Connection
strODBC  = "Data Source=CMS;Initial Catalog=CMS;Persist Security Info=True;User ID=cms;Password=cms"

' Use ADO to search Active Directory.
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

' Search.
strQuery = ";(&(objectCategory=person)(objectClass=user)(name=*));displayName,Manager,employeeID,employeeNumber;subtree"
objCommand.CommandText = strQuery
objCommand.Properties("Page Size") = 100
objCommand.Properties("Timeout") = 30
objCommand.Properties("Cache Results") = False
objCommand.Properties("Sort On") = "sAMAccountName"

Set objRecordSet = objCommand.Execute
If objRecordSet.EOF Then
  Wscript.Echo "No users found"
  objConnection.Close
  Set objRootDSE = Nothing
  Set objConnection = Nothing
  Set objCommand = Nothing
  Set objRecordSet = Nothing
  Wscript.Quit
End If

set objDB = Nothing
set oRS = Nothing

'Connect to database then run the SQL to generate load
Set objDB = DBConnect()

'Wscript.Echo "Before loop"

Do Until objRecordSet.EOF
    'On Error Resume Next

    err.Clear
    sEmpID = objRecordSet.Fields("employeeID")
    aManager = ""
    sManager = ""

    If err.number = 0 Then
        If sEmpID <> "" Then

      on error resume next 'nulls causing problems here
            aManager = split(objRecordSet.Fields("Manager"), ",", -1)

            If len(aManager(0)) > 3 Then
                sManager = right(aManager(0), len(aManager(0)) -3)
            End If

      on error goto 0
      'the replace on displayName is to get rid of the comma in irish names like Louise O'Hanlon which kill the SQL
      WScript.Echo "EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'"
            'Set oRS = objDB.Execute("EXEC SET_ADUser '" & Replace(objRecordSet.Fields("displayName"),"'","") & "'," & objRecordSet.Fields("employeeID") & ",'" & sManager & "','" & objRecordSet.Fields("employeeNumber") & "'")
            '   oRS.MoveFirst 'this forces load of total recordset


        End If
    End If

    objRecordSet.MoveNext
Loop


Wscript.Echo "After loop"

' Clean up.
objConnection.Close
Set objRootDSE = Nothing
Set objGroup = Nothing
Set objConnection = Nothing
Set objCommand = Nothing
Set objRecordSet = Nothing


'This function sets up DB Connection using specified DSN
  Function DBConnect
  Set objDB = CreateObject("ADODB.Connection")
  objDB.Open strODBC 'change this for each server'
  Set DBConnect = objDB
End Function

SELECT    ReportName,[Path]
             ,Runs
             , DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 AS DaysHistoryPresent
             ,CASE
                WHEN  DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 = 0 OR Runs = 0 THEN 0
                ELSE  Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)
             END AS ApproxRunsPerDay
             , Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)*30  AS ApproxRunsPerMonth
       FROM
       (
          SELECT
             c.Name AS ReportName
             ,c.Path
             ,Count(1) AS Runs
             ,MIN(el.TimeStart) AS FirstDay
             ,MAX(el.TimeStart) AS LastDay
          FROM ExecutionLogStorage EL WITH(NOLOCK)
             LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
          WHERE   el.Source <> 6
          AND [Name] NOT IN ('Global', 'Default','dsRptFmt','dsRptFmt_Internal','Dummy')
          GROUP BY c.Name,c.Path
       ) AS a
       ORDER BY
       CASE
          WHEN DATEDIFF(dd, FirstDay,LastDay)+1 = 0.000 OR Runs = 0 THEN 0.000
          ELSE (Runs/ DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1)
       END DESC

sp_helptext to get sql behind INFORMATION_SCHEMA views

To see the SQL behind information_schema views

exec sp_helptext  'INFORMATION_SCHEMA.COLUMNS'

Code for a View / Function comination to gernerate random numbers in tSQL

The RAND function does not behave as you would expect in SQL Server. If it is included in a select statement then it will generate a single number and populate every row with that value.

Here is a bit of a complex way of getting around this problem. Wrapping the RAND function in a view and calling via a UDF.

Modify as required but remember be careful of resource usage calling functions in select statement

CREATE VIEW vw_RandNumber_tempDEVOnly
AS

--gerard 28/10 this is a temporary setup to generate random data to allow me to develop a report in a system where we haven't put the required grouping data
SELECT RAND() as RandNumber

ALTER FUNCTION fRandNumber_tempDEVOnly(@Min int, @Max int)
RETURNS float
AS
--gerard 28/10 this is a temporary setup to generate random data to allow me to develop a report in a system where we haven't put the required grouping data

 BEGIN
 RETURN ROUND(@Min + (select RandNumber from vw_RandNumber_tempDEVOnly) * (@Max-@Min),0)
 END


--call like this
 SELECT dbo.fRandNumber_tempDEVOnly(1,3)

SSRS subreports inserting pagebreaks.

It can be frustrating when a subreport (or any report item) inserts pagebreaks a and you have to go through and get the KeepTogether and KeepWithGroup properties correct AND correct at the right level.

KeepWithGroup in the parent report is the key property and the key level is at the static row group the sub report is in for the Parent Report.

If you are having pagebreaks inserted a rule of thumb is

• Make sure KeepTogether is set to False everywhere it is not explicitly required
• KeepWithGroup = After for the report item that is creating the pagebreaks (in this case the sub-report)

SSRS - Add a label listing items selected from a multi select parameter

The following code in ds_Vendor_Type_SelectedList returns a string that can be used in the report heading to describe the values selected in a multi select parameter.

A simple alternative might be to use the VB code
=Join(Parameters!ParameterName.Label, ",")
But in large parameter selections this does not indicate to the user if ALL the parameters have been selected.

If all parameters are selected then the following structure returns 'ALL' or alternatively, if a subset is selected then it lists the actual items. This is a more intuitive and user friendly approach.

The second data set called ds_Vendor_Type_SelectedList is populated based on the user selection of the parameter (@VendorTypes parameter variable)



Parameter Name = @VendorTypes

List populated by following Dataset
ds_VendorType_List

SELECT     VENDTYP_CODE,VENDTYP_DESC
FROM APL_VENDTYP
WHERE INACTIVE = 'N'
ORDER BY VENDTYP_DESC


ds_Vendor_Type_SelectedList

DECLARE @CountSelected AS INT
DECLARE @CountAvailable AS INT
DECLARE @ReturnString AS VARCHAR(MAX)

SET @CountAvailable =
    (SELECT COUNT(VENDTYP_CODE)
    FROM APL_VENDTYP
    WHERE INACTIVE = 'N')
SET @CountSelected =
    (SELECT COUNT(VENDTYP_CODE)
    FROM APL_VENDTYP
    WHERE VENDTYP_CODE  IN (@VendorTypes))

IF @CountAvailable = @CountSelected
    BEGIN
        SET @ReturnString = 'All Vendors Selected'
    END
ELSE
    BEGIN
        SET @ReturnString = 'Vendors Selected : ' +
        (STUFF(( SELECT ', ' + VENDTYP_DESC
        FROM APL_VENDTYP WHERE VENDTYP_CODE IN (@VendorTypes)
        FOR XML PATH('')), 1, 2, ''))
    END
   
SELECT  @ReturnString AS VendorsSelected  

Handy code snippits for exploring the ssrs database

/*
    Ran instances oof reports with the parameters
*/
SELECT    x.TimeStart
        ,c.Name
        ,x.USERNAME
        ,x.Parameters
        ,c.Type
        ,x.TimeDataRetrieval
        ,x.TimeProcessing
        ,x.TimeRendering
        ,x.ByteCount
        ,x.[RowCount]
        ,c.Path
       
               ,'XX' as 'Catelog_Fields_Start_Here'
               ,c.*
               ,'XX' as 'ExecutionLog_Fields_Start_Here'
               ,x.*
          FROM Catalog c
               INNER JOIN
               ExecutionLog x
                 ON c.ItemID = x.ReportID
         WHERE Type = 2 AND x.USERNAME like '%gerard%'
         order by x.TimeStart DESC
/*
    code to get xml of report from content field
*/
        
--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
    SELECT
    ItemID,Name,[Type]
        ,CASE Type
        WHEN 2 THEN 'Report'
        WHEN 5 THEN 'Data Source'
        WHEN 7 THEN 'Report Part'
        WHEN 8 THEN 'Shared Dataset'
        ELSE 'Other'
    END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
    FROM ReportServer.dbo.Catalog
    WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
    SELECT
    ItemID,Name,[Type],TypeDescription
    ,CASE
        WHEN LEFT(Content,3) = 0xEFBBBF
        THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
    ELSE
            Content
        END AS Content
    FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
    SELECT
    ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
    FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
    ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

-- dsMain
SELECT
ReportID,
Name AS ReportName,
InstanceName,
COALESCE(C.Path, 'Unknown') AS ItemPath,
UserName,
ExecutionId,
CASE(RequestType)
  WHEN 0 THEN 'Interactive'
  WHEN 1 THEN 'Subscription'
  WHEN 2 THEN 'Refresh Cache'
  ELSE 'Unknown'
END AS RequestType, -- SubscriptionId,
Format, Parameters,
CASE(ReportAction) WHEN 1 THEN 'Render'
  WHEN 2 THEN 'BookmarkNavigation'
  WHEN 3 THEN 'DocumentMapNavigation'
  WHEN 4 THEN 'DrillThrough'
  WHEN 5 THEN 'FindString'
  WHEN 6 THEN 'GetDocumentMap'
  WHEN 7 THEN 'Toggle'
  WHEN 8 THEN 'Sort'
  WHEN 9 THEN 'Execute'
  ELSE 'Unknown'
END AS ItemAction,
TimeStart,
TimeEnd,
TimeDataRetrieval,
TimeProcessing,
TimeRendering,
CASE(Source)
  WHEN 1 THEN 'Live'
  WHEN 2 THEN 'Cache'
  WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History'
  WHEN 5 THEN 'AdHoc'
  WHEN 6 THEN 'Session'
  WHEN 7 THEN 'Rdce'
  ELSE 'Unknown'
END AS Source,
CASE (Status)

  WHEN 'rsHttpRuntimeClientDisconnectionError' THEN 'HttpRuntimeClientDisconnectionError'
  WHEN 'rsReportServerDatabaseError' THEN 'ReportServerDatabaseError'
  WHEN 'rsProcessingAborted' THEN 'Processing Aborted'
  WHEN 'rsSuccess' THEN 'Success'
  WHEN 'rrRenderingError' THEN 'Rendering Error'
  ELSE 'Unknown'
END AS Status,
ByteCount,
[RowCount],
AdditionalInfo
FROM ExecutionLogStorage EL WITH(NOLOCK)
LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
WHERE ReportID = @ReportList
;

--dsSSRSReportList
Select    ItemID
        ,[Name]  
From    [Catalog] c
Where  [Type] = 2
AND [Hidden] <> 1
AND SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1))  = @FolderList
ORDER BY c.[Name]

--dsSSRSFolderList
--Returns a list of the folder structure on SSRS Server
SELECT DISTINCT SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1)) As [Path] From    [Catalog] c



create VIEW [dbo].[vw_REPORT_JWS_SSRS_ReportUsage]
AS
       SELECT    ReportName,[Path]
             ,Runs
             , DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 AS DaysHistoryPresent
             ,CASE
                WHEN  DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 = 0 OR Runs = 0 THEN 0
                ELSE  Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)
             END AS ApproxRunsPerDay
             , Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)*30  AS ApproxRunsPerMonth
       FROM
       (
          SELECT
             c.Name AS ReportName
             ,c.Path
             ,Count(1) AS Runs
             ,MIN(el.TimeStart) AS FirstDay
             ,MAX(el.TimeStart) AS LastDay
          FROM ExecutionLogStorage EL WITH(NOLOCK)
             LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
          WHERE   el.Source <> 6
          AND [Name] NOT IN ('Global', 'Default','dsRptFmt','dsRptFmt_Internal','Dummy')
          GROUP BY c.Name,c.Path
       ) AS a
       ORDER BY
       CASE
          WHEN DATEDIFF(dd, FirstDay,LastDay)+1 = 0.000 OR Runs = 0 THEN 0.000
          ELSE (Runs/ DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1)
       END DESC
GO
        

SSRS get the run history and parameters used for individual report

USE [ReportServer]
/*
useful code for getting the run history and parameters used of a report
*/
DECLARE @ReportName AS VARCHAR(MAX)
SET @ReportName = '%Report1%'--report name hers surrounded by wildcards

SELECT * FROM
(
    SELECT
    InstanceName,
    COALESCE(C.Path, 'Unknown') AS ItemPath,
    UserName,
    ExecutionId,
    CASE(RequestType)
      WHEN 0 THEN 'Interactive'
      WHEN 1 THEN 'Subscription'
      WHEN 2 THEN 'Refresh Cache'
      ELSE 'Unknown'
    END AS RequestType, -- SubscriptionId,
    Format, Parameters,
    CASE(ReportAction) WHEN 1 THEN 'Render'
      WHEN 2 THEN 'BookmarkNavigation'
      WHEN 3 THEN 'DocumentMapNavigation'
      WHEN 4 THEN 'DrillThrough'
      WHEN 5 THEN 'FindString'
      WHEN 6 THEN 'GetDocumentMap'
      WHEN 7 THEN 'Toggle'
      WHEN 8 THEN 'Sort'
      WHEN 9 THEN 'Execute'
      ELSE 'Unknown'
    END AS ItemAction,
    TimeStart,
    TimeEnd,
    TimeDataRetrieval,
    TimeProcessing,
    TimeRendering,
    CASE(Source)
      WHEN 1 THEN 'Live'
      WHEN 2 THEN 'Cache'
      WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History'
      WHEN 5 THEN 'AdHoc'
      WHEN 6 THEN 'Session'
      WHEN 7 THEN 'Rdce'
      ELSE 'Unknown'
    END AS Source,
    Status,
    ByteCount,
    [RowCount],
    AdditionalInfo
    FROM ExecutionLogStorage EL WITH(NOLOCK)
    LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
)AS a
WHERE ItemPath like @ReportName   

Search and Locate report items the SSRS database (ie which reports did I use the SQL function i am modifying in etc)

Following code will list report objects in a Reporting Server Database. It can be modified to serarch for specific items in specific reports (ie which reports contain reference to a certain data set or paramater list etc)



--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
  where Name like '%%' --add filter here
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML

--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

SSRS Troubleshooting notes for Data connection Errors

Datasets unable to refresh fields

  The size necessary to buffer the XML content exceeded the buffer quota

  This often occurs when building a report based on a previous report
  Often it is because you are referencing fields that are not in the existing data scope,
  You are probably referencing a dataset or fields that have not been included in the report yet

  An item with the same key has already been added.

      Duplicate field names in your SQL

  Must declare the scalar variable "xx". (when this Parameter already exists)

      1) Add the Parameters Manually in the Parameters Tab of the Dataset Properties
    
      2) Report xml is scrambled - Multiple reasons can cause this but I had a big problem with this occurring when I was converting crystal reports to SSRS and using an online conversion tool to do the basics (and set up all the positioning and formatting as per the original which some of these tools are very good at)

      I have never put the time and effort into working out what the problem was. Instead I have opened a fresh report and quickly rebuilt it which has always worked.

       Open a blank report and rebuild the report across the tip
        Copy parameters from the xml and add
        Copy and Paste all the actual graphical interface of the report to the new report
        Build the datasets manually in 
     

SSRS Displaying column headers

It is easy to forget what is required to display column headers

There are actually 3 settings need to be set      



    Tablix Properties  
        Repeat Header Row on each page needs to be checked
    Group -> Select Advanced _> Select the following 2 properties  
        RepeatOnNewPage to True
        KeepwithGroup to After


The same applies for column headers

Using HTML markup to format text in SSRS

You have a number of options for formatting text in SSRS. Here is a good article on formatting options for text boxes. The step by step HTML section is below. Thanks SQLchick,

http://www.sqlchick.com/entries/2010/10/31/using-different-formats-within-a-single-textbox-in-ssrs.html

HTML Tags

This option delivers a bit more flexibility, particularly if you have a complex expression in your textbox.  Using HTML tags will work if you have a complex expression (i.e., shown in the design view).
First, you do need to define Placeholder Properties on the Expression:

Within the Placeholder Properties, change the default Markup type to be “HTML – Interpret HTML tags as styles.”  If you forget to change this radio button, then the html tags will be rendered as literal text.

Then within your expression, insert the HTML tags as needed.

Only a subset of HTML tags are supported within SSRS, such as bold, italics, and underline.  The MSDN page on Formatting Text and Importing HTML specifies the valid HTML tags you may use within an SSRS textbox.  If you use an HTML tag that isn’t supported in SSRS, it will be ignored.
As a sidenote, using HTML tags within a simple expression will work as well:

However, in a real situation, I would reserve using HTML tags to situations when I have a complex expression.  With a simple expression, I’d opt to keep the formatting options simpler.

Passing Multi-Value Parameters In Reporting Services

Here is a quick and efficient way to pass and use multi value parameters in SSRS and your stored procedures or SQL.

Concept

•  Convert the parameter array to CSV
•  In the SSRS report, on the parameters tab of the query definition, set the parameter value to:
• =join(Parameters!YourParamName.Value,",")
• Create a Table Valued function to convert the CSV string to a table that can be addressed as per normal (See function below - there are many variation on the web)
• Join or or use this function as per a normal sub SELECT statement (See example sp below

You can also use a simple text box to pass a  comma separated string to SQL server directly. The user simply enters a comma separated list of values. This is useful where the user may need to select multiple values from a list that is too large to be displayed as a multi select drop-down (ie a company might have many thousands of customers which is too many to display in a drop down - but entering half a dozen client codes directly into a text box is a viable solution)

Gerard

User defined function for numeric strings

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
======================================================================================================================================
Author:            Gerard
Create date:   
Description:    Returns a recordset from SSRS multi select parameter that has been passed into a stored procedure
                (turns a comma seperated string into a records set)
Requirements:    Paramater variable must be formed up into a comma deliminated string (or some other delimiter if the data
                already contains commas)
                Parameters can be passed in directly from SSRS often but some data types need to be massaged in SSRS
                    This is achieved in the SSRS dataset that is calling the sp by using the VB JOIN function
                        DS calling SP -> Properties -> Parameters
                        Add Parameter that is multi select -> add JOIN script to concat selected values with commas (or other char if data contains commas)
                        EG Param=-ResponsiblePartner   Expr=JOIN(Parameters!ResponsiblePartner.Value,",")
               
               
               
                Example of usage in SP
                WHERE EMPLOYEE_CODE IN (SELECT * FROM DelimitedSplit8K(@RespPartners, ','))
                INNER JOIN (SELECT * FROM DelimitedSplit8K(@RespPartners, ',')) AS t ON t.Item = s.CUST_CODE


NOTE:                a useful bit of code to create a comma delimited string from a recordset
                    is below (useful place to leave it)
                    SELECT @RespPartners = COALESCE(@RespPartners + ',','') +  EMPLOYEE_CODE
                    FROM _JWS_Responsible_Partners
               
Modifications:    ALL MODIFICATIONS MUST BE DETAILED BELOW SHOWING DATE

Modified By:    Gerard   
Date:           
Modification:    modified DelimitedSplit8K to return a Integer recordset where multi select field is numeric

======================================================================================================================================



*/
ALTER FUNCTION [dbo].[DelimitedSplit8K_Int]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = CAST(SUBSTRING(@pString, l.N1, l.L1) AS INT)
   FROM cteLen l
   WHERE ISNUMERIC(SUBSTRING(@pString, l.N1, l.L1))=1
;

User defined functionfor text strings

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
======================================================================================================================================
Author:            Unknown
Create date:   
Description:    Returns a recordset from SSRS multi select parameter that has been passed into a stored procedure
                (turns a comma seperated string into a records set)
Requirements:    Paramater variable must be formed up into a comma deliminated string (or some other delimiter if the data
                already contains commas)
                Parameters can be passed in directly from SSRS often but some data types need to be massaged in SSRS
                    This is achieved in the SSRS dataset that is calling the sp by using the VB JOIN function
                        DS calling SP -> Properties -> Parameters
                        Add Parameter that is multi select -> add JOIN script to concat selected values with commas (or other char if data contains commas)
                        EG Param=-ResponsiblePartner   Expr=JOIN(Parameters!ResponsiblePartner.Value,",")
               
               
               
                Example of usage in SP
                WHERE EMPLOYEE_CODE IN (SELECT * FROM DelimitedSplit8K(@RespPartners, ','))
                INNER JOIN (SELECT * FROM DelimitedSplit8K(@RespPartners, ',')) AS t ON t.Item = s.CUST_CODE


NOTE:                a useful bit of code to create a comma delimited string from a recordset
                    is below (useful place to leave it)
                    SELECT @RespPartners = COALESCE(@RespPartners + ',','') +  EMPLOYEE_CODE
                    FROM _JWS_Responsible_Partners
               
Modifications:    ALL MODIFICATIONS MUST BE DETAILED BELOW SHOWING DATE

Modified By:    Gerard       
Date:            28/08/15
Modification:    Space in comma seperated string prevents match ie 'an1, dhp1' returns an1 while 'an1,dhp1' returns an1 and dhp1


Modified By:           
Date:           
Modification:   
======================================================================================================================================



*/
ALTER FUNCTION [dbo].[DelimitedSplit8K]
    (@pString VARCHAR(8000), @pDelimiter CHAR(1))


RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
   --SET @pString =REPLACE(@pString, ' ', '')

--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
    WITH
        E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
        )--10E+1 or 10 rows
        ,E2(N) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows
        ,E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
        ,cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
            SELECT TOP (ISNULL(DATALENGTH(REPLACE(@pString, ' ', '')),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
        )
        ,cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
            SELECT 1 UNION ALL
            SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(REPLACE(@pString, ' ', ''),t.N,1) = @pDelimiter
        )
        ,cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
            SELECT s.N1,
            ISNULL(NULLIF(CHARINDEX(@pDelimiter,REPLACE(@pString, ' ', ''),s.N1),0)-s.N1,8000)
            FROM cteStart s
        )
       
        SELECT ItemNumber,Item FROM
        (
            --Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
            SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
                Item= SUBSTRING(REPLACE(@pString, ' ', ''), l.N1, l.L1)
            FROM cteLen l
        ) as a
        WHERE REPLACE(Item, ' ', '') <> ''
       
;
;

Example sp
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
======================================================================================================================================
TestOnly
======================================================================================================================================
Example Call
exec udspr_REPORT_Gerard_Test2 '1010,1011,2010,3013,4012'

*/
CREATE PROC [dbo].[sp_Gerard_Test2]
(


    @CSVIn VARCHAR(8000) = NULL
   
)
AS
BEGIN
SELECT * FROM DelimitedSplit8K(@CSVIn, ',') AS wt

/*
Example of use in Inner Join
INNER JOIN (SELECT Item FROM DelimitedSplit8K(@PracticeGroup, ',')) AS wt ON (SomeTable.SomeField = wt.Item)
Example of most common use - IN
WHERE x.x IN((SELECT Item FROM DelimitedSplit8K(@PracticeGroup, ',')
*/

END

SQL Server Db stuck in restore state

It is possible to end up with a database stuck in a restoring state either you have specified the restore with no recovery or the client has disconnected whilst the restore is running or you have run out of disk space.

If the situation is that the database has fully restored but has not not recieved the recover command the following tSQL will put it back to an operational state

RESTORE DATABASE WITH RECOVERY

DLink DNS 323

The DNS 323 is a relatively cheap ~$150 Network Attached Storage device (NAS) that I use at home to store all the important stuff (family photos etc), back and synchronize my work directories on all my computers and to stream movies to our PS3 (unfortunately cannot stream microsoft's TV format - .wtv)

It has a little 200mhz ARM processor running a cut down version in Linux and a web interface to make all setup and changes.

Here is the link to its homepage
http://www.dlink.com/products/?pid=DNS-323
Basic services offered are:

• NAS disk share
• AV server for streaming media
• USB port to attach a network printer (see notes on SharePort utility below- don't even bother trying to use it as a print server as DLink advertises)
• ftp server
• iTunes server
• + a few others
  
• + you can install other Linux packages if you are technical
  

Overall I am very happy with it for the last 4 years. It has gone through a series of drives and a series of RAID configurations as I have filled it up and run out of space. There are few things to both know and be aware of with it though - here is a list of what I am aware of

Disk Size - 2TB now supported
With firmware v1.8 or v1.9 installed it is capable of holding 2 X 2TB disks. Yay. Be careful of the disks you buy though. DLink has tested only Western Digital WD20EADS 2TB drives. There are potential problems with (most) 2TB disks on the market using the newer 4K sectors and AFT technology. Look into it very carefully before you buy a disk. I just went with the DLink recommended WD disks - be careful they are the exact ones though - the WD20EARS 2TB drives do not work (1 character different - beware)

Speed
The 323 is very slow in file transfers to windows machines. A fair bit of this is due to the SAMBA configuration on the 323, a fair bit due to the limited CPU and a fair but due to the windows configurations and network card used. Basically this device is not up to much with its little ARM processor and the SAMBA configuration as set up is very CPU intensive with 64K buffers. In addition file transfers will be slower if the windows machine has a low powered CPU. Still - it streams HD no problems for me.

Use FTP. If you are doing a lot of large file transfers turn on the built in ftp server and use ftp to transfer the files. You will find it will be double the speed. Filezilla is a good ftp client for windows.

SharePort Utility
This DLink utility shares the USB port on the back of the 323 across the network. I use it to share a Lexmark 3200 series printer. The concept is great - apparently if you attach a powered USB hub you can share multiple devices.

I couldn't get SharePort working but eventually struck on the magic combination. I had initially installed v1.17 addon and on the 323 and was using 1.17 windows utility software on my PC. It could not see any in USB device inserted into the 323

The magic combination (for me) was SharePort v1.17 addon on the 323 and SharePort v3.0 windows utility software on my PC.

I will update this post if I find anything else to be aware of about this great little box. Feel free to add comments if you know of any other "gotchya's"

Listing MySQL functions and Procedures

Maintaining and updating stored procedures and functions in MySQL often will require root access which in practice means that you might need to have some interaction with your hosting provider if you wish to use them.

Operating the the standard phpMyAdmin interface gives no support for them other then the ability to create them through script.

The following bits of SQL are useful for viewing stored procedures and functions in a hosted database accessed through phpMyAdmin

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="FUNCTION"
AND ROUTINE_SCHEMA="database"

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="PROCEDURE"
AND ROUTINE_SCHEMA="database"

Also the "SHOW PROCEDURE STATUS" will give details of stored procedures and functions

Dealing with MySQL Function

Newer Versions of MySQL have the binary log which will generate te following error

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

To fix this you need incude the following keywords

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SET @x = 1 or DO RELEASE_LOCK('abc'), which execute but neither read nor write data.

• NO SQL indicates that the routine contains no SQL statements.

• READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT), but not statements that write data.

• MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

Ther referenced artical indicates using
SET GLOBAL log_bin_trust_function_creators = 1;

References
http://mvnrepository.blogspot.com/2009/08/this-function-has-none-of-deterministic.html


Example
DELIMITER $$

DROP FUNCTION IF EXISTS `adadmin_adem`.`fDeleteTheme` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `fDeleteTheme`(id_in INTEGER) RETURNS int(11)
DETERMINISTIC
READS SQL DATA
MODIFIES SQL DATA
BEGIN


DECLARE counter INTEGER;
DECLARE parent_id INTEGER;


SELECT count(id_theme) FROM s_themes where parent = id_in INTO counter;


IF counter = 0 THEN

SELECT parent FROM s_themes where id_theme = id_in INTO parent_id;
DELETE FROM adem.s_themes WHERE id_theme = id_in;
END IF;

RETURN parent_id;

END $$

DELIMITER ;

DLink 323 NAS box under Windows 7 or Vista

My D Link 323 NAS box worked perfectly under xp but with a dual upgrade to a Billton 7300 router and Windows 7 I have had continual problems.

After applying all the latest firmware to both devices I got it sorted

The final niggle was the 323 could be set up with mapped drives but I was randomly loosing my mapped drives when I shut it down. I could ping the NAS box and read the administration page and see the media server but could not access the file system via my mapped drives or device name ie \\NAS\Volume_1\

The solution turned out to be to use the ip address rather then the divice name in all mapping or accessing the device directly ie \\192.168.0.5\Volume_1\

This has been such a pain in the bottom I thought I would past it here - if someone is searching for the same problem they might pick it up.