INVENTORY | LOG SHIPPING

INVENTORY SCRIPTS :  LOG SHIPPING
- Seel also parent script for databases with flag for log shipping
- The script can be run against SQL 2008 R2, 2008, 2005, and 2000.  The script is run as a dynamic query so SQL 2000 boxes will not error out on the tables it cannot find.


SELECT [Primary_Details].primary_id,
       [Monitor_Details].backup_job_id,
       [Primary_Details].primary_server,
       [Primary_Details].primary_database,
       [Secondary_Details].secondary_server,
       [Secondary_Details].secondary_database,
       [Monitor_Details].monitor_server,
       [Primary_Details].last_backup_date_utc,
       [LogShipping_JOBS].last_run_outcome       AS job_last_run_outcome,
       [LogShipping_JOBS].[last_outcome_message] AS [Job_last_outcome_message],
       [Monitor_Details].backup_directory        physical_path,
       [Monitor_Details].backup_share            shared_path,
       [Primary_Details].last_backup_file,
       [Primary_Details].backup_threshold,
       [Primary_Details].threshold_alert,
       [Primary_Details].threshold_alert_enabled,
       [Primary_Details].history_retention_period,
       [Monitor_Details].backup_retention_period,
       [Monitor_Details].user_specified_monitor,
       [LogShipping_JOBS].name                   AS job_name,
       [LogShipping_JOBS].[Enabled]              AS job_enabled,
       [LogShipping_JOBS].command                AS job_command,
       [LogShipping_JOBS].last_run_datetime      AS job_last_run_datetime,
       [LogShipping_JOBS].last_run_duration      AS job_last_run_duration,
       [LogShipping_JOBS].[description]
FROM   msdb.dbo.log_shipping_monitor_primary [Primary_Details]
       INNER JOIN msdb.dbo.log_shipping_primary_secondaries [Secondary_Details]
         ON ( [Primary_Details].primary_id = [Secondary_Details].primary_id )
       INNER JOIN msdb.dbo.log_shipping_primary_databases [Monitor_Details]
         ON ( [Primary_Details].primary_id = [Monitor_Details].primary_id
              AND [Secondary_Details].primary_id =
            [Monitor_Details].primary_id )
       LEFT OUTER JOIN (SELECT jobs.job_id,
                               jobs.name,
                               jobs.[enabled],
                               jobs.[description],
                               [JOBSteps].command,
                               CASE
                                 WHEN [JOBSteps].[last_run_date] > 0 THEN
                                 Substring(CAST([JOBSteps].[last_run_date] AS
                                                CHAR(8)),
                                 5, 2) + '/' +
                                 RIGHT(CAST([JOBSteps].[last_run_date]
                                            AS CHAR(8)), 2) + '/' + LEFT(CAST(
                                 [JOBSteps].[last_run_date] AS
                                 CHAR(8)), 4) + ' ' +
                                 LEFT(RIGHT('000000' + CAST(
                                            [JOBSteps].[last_run_time]
                                            AS VARCHAR(10)),
                                      6), 2)
                               + ':' + Substring(RIGHT('000000' + CAST(
                                       [JOBSteps].[last_run_time] AS
                                       VARCHAR(10)), 6),
                                               3, 2) + ':' + RIGHT(RIGHT(
                                                             '000000' + CAST(
                                 [JOBSteps].[last_run_time] AS
                                 VARCHAR(
                                 10)), 6), 2)
                                 ELSE NULL
                               END
              [Last_Run_DateTime],
                               LEFT(RIGHT('000000' + CAST(
                                          [SysJobServers].[last_run_duration] AS
                                          VARCHAR(10)),
                                    6), 2) + ':' + Substring(RIGHT(
                                                   '000000' + CAST(
                               [SysJobServers].[last_run_duration]
                               AS
                               VARCHAR(
                                                   10)), 6)
                               , 3, 2) + ':' + RIGHT(
                               RIGHT('000000' + CAST(
                                     [SysJobServers].[last_run_duration]
                                     AS VARCHAR(10)),
                                                   6)
                                     , 2) AS [Last_Run_Duration],
                               [SysJobServers].last_run_outcome,
                               [SysJobServers].[last_outcome_message]
                        FROM   msdb.dbo.sysjobs [Jobs]
                               INNER JOIN msdb.dbo.sysjobsteps [JOBSteps]
                                 ON ( jobs.job_id = [JOBSteps].job_id )
                               LEFT OUTER JOIN msdb.dbo.sysjobservers
                                               [SysJobServers]
                                 ON [Jobs].job_id = [SysJobServers].job_id
                        WHERE  [Jobs].category_id = 6) [LogShipping_JOBS]
         ON [Monitor_Details].backup_job_id = [LogShipping_JOBS].job_id 

Passing Multiple Value Parameter To Report Using Action

Once a in a while there is a need to hard code the action link with a multiple values.  This can be done as follows. 

1. On the action tab select the parameter to fill.
2. Hit the Fx button
3. =Split("ParameterValueX,ParameterValueY,ParameterValueZ",",")
   example:  =Split("SYSTEM DRIVE,CONTAINS DATA/LOG File(s),NO DATA/LOG File(s)", ",")

SSRS Keep All Data Together On One Page

If you want to keep a long report all on one page (no paging) do the following:

Properties >> Report >> InteractiveSize >> Height = 0

ANONYMOUS LOGINS TO SSRS

Here are some notes I have collected to open up SSRS for anonymous logins and the errors you may see

Stored Credentials and Anonymous Logons and Browsing in Reporting Services Does not work rsAccessDenied   rsErrorOpeningConnection

The permissions granted to user are insufficient for performing this operation. (rsAccessDenied)

rsErrorOpeningConnection Windows NT user does not have server access.

1.       Folder Security

a.        Open up report manager >> home .>> Folder Settings

b.       Add BUILTIN\Users as BROWSER ROLE

c.        Add BUILTIN\IIS_IUSRS as BROWSER ROLE

d.       Apply

2.       Store Credentials (do for each datasource)

a.        Open Report Manager >> Home >> Datasources

b.       Click on the datasource

c.        Add the credentials to the database under “Credentials stored securely in the report server”

d.       Do not check the other two boxes.  ** Unless using domain account ??

e.       Apply

3.       Reporting Services Service Account

a.        Open reporting services configuration manager

b.       Click Service Account

                                                               i.      Make note of the account used.

c.        Click on Execution Account

                                                               i.      Make note of the account used

4.       Grant allow log on locally

a.        Administrator tools >> Local Security Policy

b.       Security Settings >> Local Policies >> user Rights Assignment

c.        Log on Locally

                                                               i.      Add the two accounts found above as grant.

SSRS (Reporting Services) Separate Legend (as Tablix) From Chart or Graph with Drilldown Links

I am often frustrated with SSRS's legend layout.  My legend items are often wrapped or too small and my chart of graph is squeezed to make everything fit properly.  The other problem is the legend item does not allow the users to click on the items to drill through to the details.

An easy solution is to make a separate legend as a tablix outside the graph which can be hidden.  This gives you the added ability to create drill through links and allow the users to easily see the totals.

Below I give you two examples.  1) Using standard colors where you don't care what color the items are.  2)  The color needs to reflect the data (ie Red, Yellow, Green .... Bad, Warning, Good).

1.      Open the report

2.      Click REPORTS under the main menu >> REPORT PROPERTIES

3.      Click CODE

4.      INSIDE THE CODE BOX TYPE / COPY one of the following (standard or custom)

[STANDARD COLORS]

Private colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0

Private mapping As New System.Collections.Hashtable()

Public Function FillColor(ByVal groupingValue As String) As String

    If mapping.ContainsKey(groupingValue) Then

       Return mapping(groupingValue)

    End If

    Dim myColor As String = colorPalette(count Mod colorPalette.Length)

    count = count + 1

    mapping.Add(groupingValue, myColor)

    Return myColor

End Function

[CUSTOM COLORS]    Public Function FillSpecificColor(ByVal ProblemCode As Integer)

        Dim ReturnColor As String = "Violet"

        Select Case ProblemCode

            Case 300

                ReturnColor = "DarkGreen"

            Case 1

                ReturnColor = "Red"

            Case 2

                ReturnColor = "#C60000" ' Dark Red

            Case 3

                ReturnColor = "#FF3E3E" ' Light Red

            Case 4

                ReturnColor = "#930000" ' Darker Red

            Case 5

                ReturnColor = "#FF8484" ' Lighter Red

            Case 6

                ReturnColor = "#FFD5D5"  ' Pink                 

            Case 7

                ReturnColor = "#3E0000"  ' Brown

                '--------------------------------------- 

            Case 100

                ReturnColor = "Yellow"

            Case 101

                ReturnColor = "#D5D500" ' Dark Red

            Case 102

                ReturnColor = "#FFFF48" ' Light Red

            Case 103

                ReturnColor = "#C6C600" ' Darker Red

            Case 104

                ReturnColor = "#FFFF77" ' Lighter Red

            Case 105

                ReturnColor = "#A8A800"  ' Brown yellow               

            Case 106

                ReturnColor = "#FFFFB9"  ' Beige    

                '--------------------------------------- 

            Case 200

                ReturnColor = "#009100" ' Shade lighter than Dark Green (300)

            Case 201

                ReturnColor = "#00BB00" ' Shade lighter than above

            Case 202

                ReturnColor = "#00DF00" ' Shade lighter than above

            Case 203

                ReturnColor = "#17FF17" ' Shade lighter than above

            Case 204

                ReturnColor = "#48FF48"  ' Shade lighter than above          

            Case 205

                ReturnColor = "#AAFFAA"  ' Shade lighter than above   

            Case 206

                ReturnColor = "#D9FFD9"  ' Shade lighter than above               

        End Select

       

        Return ReturnColor

    End Function

5.  Now create a table on the page and add the field you used for the chart grouping as in the second column of the details row.

      6.  Now in the properties of the ROW GROUPINGS you just created ADD a grouping as that field

7.   Now in the first column second row (where your color will go) >> Text Box Properties >> FILL

8.  Fill Color Table >> Fx

a.      STANDARD

                                                              i.      Fill Color =Code.FillColor(Fields!Problem.Value)  where problem is your grouped field

b.      CUSTOM

                                                              i.      Fill Color =Code.FillSpecificColor(Fields!ORDER.Value)  where ORDER also serves as a color coder (ie 1 = RED 300 = Dark Green)

9.  Right + Click your chart >> SERIES PROPERTIES >> FILL COLOR

10. Fill Color Table

a.      STANDARD

                                                              i.      Again =Code.FillColor(Fields!Problem.Value)

b.      CUSTOM

                                                              i.      Again =Code.FillColor(Fields!ORDER.Value)

11.  Create a merged cell for the header and add your chart title

12.  Now delete your title and legend and drag your new legend as close to the chart as possible.

OPTIONAL:  Add a calculated column for the totals and percentages.

OPTIONAL:  Use the legend title as toggle button to hide / show the legend saving space.

OPTIONAL:  Split the table using two Tablix and a filter to put half the legend items on the left and half on the right.

SSRS (Reporting Services) Excel Friendly Check Mark

Indicators are nice, but what if the users what to do their own manipulation of the data through Excel?  You cannot sort or filter by an indicator so you have to use a ASCII friendly symbol. 

=IIF(Fields!Is_Orphaned.Value = 1,
=IIF(Fields!Is_Orphaned.Value = True,
=IIF(Fields!Is_Orphaned.Value = "1",chr(0254),Nothing)"√", Nothing)"√", Nothing)

SSRS (Reporting Services) Action Link Opened In New Window _Blank

Add to the action report expression

="javascript:void(window.open('"+ Fields!ReferURL.Value + "','_blank'))"
or to a url
="javascript:void(window.open('http://www.google.com','_blank'))"

Sort Outlook Tasks By Subject

Outlook 2007+ cannot sort by task subject field

Outlook 2003 can do this but for some reason it was changed in 2007 (2010?)
When you try this you get the error “You cannot sort by this field”

To resolve if you need to remove Task Subject from the view and replace it with Subject.

1.  Right click on the column heading
2. Select customise current view
3. Click on fields
4. Add subject, remove task subject
5. Now it you will be able to sort by the subject field

CLUSTER NAME, VIRTUAL RESOURCE NAME, PHYSICAL SERVER NAME(S)

IF (SELECT CONVERT(bit, SERVERPROPERTY('IsClustered'))) = 1

      BEGIN

            DECLARE @ClusterName VarChar(1000)       

            EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='Cluster', @value_name='ClusterName', @value=@ClusterName OUTPUT

           

            SELECT

            Upper(NodeName) as PhysicalServerName

            ,Upper(CONVERT(varchar(100), SERVERPROPERTY('MachineName'))) as [VirtualPhysicalName]

            ,Upper(@ClusterName) as ClusterName

            FROM sys.dm_os_cluster_nodes 

      END

      ELSE

      BEGIN

            SELECT

            Upper(CONVERT(varchar(100), SERVERPROPERTY('MachineName'))) as [PhysicalServerName]

            ,Upper(CONVERT(varchar(100), SERVERPROPERTY('MachineName'))) as [VirtualPhysicalName]

            ,NULL as ClusterName

      END

SQL | Detecting and Fixing Torn Pages

Detecting and Fixing Torn Pages

--Find the torn page. If no results returned then you are fine

SELECT db_name(database_id) DatabaseName, file_id, page_id, last_update_date

FROM msdb..suspect_pages

WHERE event_type = 3

--Backup your transaction log

USE master

BACKUP LOG DBName

TO DISK = 'C:\DBName.trn'

WITH NORECOVERY

--Restore Torn Page. 1 is the file_id, and 123 is the page_id from the first query

RESTORE DATABASE DBName PAGE='1:123'

FROM DISK='C:\DBName.bak'

WITH NORECOVERY

--Restore your log

RESTORE LOG DBName FROM

DISK='C:\DBName.trn'

WITH RECOVERY