How to get RDL information from Reporting Services Report.

--FROM http://blogs.netconnex.com/2011/05/extracting-ssrs-report-rdl-xml-from.html
--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,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

Disk System

What kind of database?
------------------------
OLAP - Sequential I/O,  Read intensive
OLTP - Random I/O, Read /Write intensive

Calculating the number of disks required
----------------------------------------
1. the required disk I/O per second
2. I/O per second capacity (IOPS) of the individual disks included

Required # Disks = (Reads/sec + (Writes/sec * RAID adjuster)) / Disk IOPS

Remember to separate the transaction log and data files.

RAID = Redundant Array of Independent Disks

RAID0 - zero redundancy RAID (not recommended)
RAID1 -  disk mirroring,  50% disk utilization level, write medium, read high
RAID5 - disk utilization  equals 1 - (1/N), write low, read high, minimum three drives

SAN - Storage Area Network
------------------------------------
*Multiple servers can connect to a SAN via special host bus adapter (HBA) cards
installed in each connecter server.
*Disks within the SAN are grouped together into logical unit numbers (LUNs)
and presented as required to connected servers. The server sees the LUN as a locally attached disk.
*Major benefits of a SAN is sharing disks among many servers to maximize usage.
*To get best performance dedicate the entire SAN to a single, mission critical database.

If your organization decides on a SAN storage system have a good working relationship with your SAN administrator.

Each physical disk in a SAN can be carved up into parts and used in the creation of separate LUNs.
As a result, LUNs from many servers can all be using different parts of the same physical disks.

Remotely Log Off Remote Desktop Users

How to query for users on a machine

qwinsta /server:<serverName>  OR  quser /server:<serverName>

How to log a user off of a machine

logoff <sessionId> /server:<serverName>

Dependency information on my table

To find out what depends on the table you are about to alter or drop.

SELECT referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities(' schema_name.referenced_entity_name ' , ' <referenced_class> ' )

additional information<
{referenced_class> ::=OBJECT| TYPE
| XML_SCHEMA_COLLECTION| PARTITION_FUNCTION}

STOP A SQL SERVER DATABASE THAT IS IN RECOVERY MODE

Sometimes you do not care if you loose data and/or do not have time to wait for recovery to finish.


USE MASTER
GO
RESTORE DATABASE [database_name] WITH NORECOVERY
GO

If it is important that SSRS reports exported to Excel worksheets

If it is important that reports exported to Excel worksheets then the following can help you reduce the number of merged cells in your Excel worksheets.

•  Not aligning items left and right is the most common cause of merged cells. Make sure the left and right edges of all report items line up with one another. Making items align and the same width will solve the problem in the majority of cases.

• Although you align all items precisely, you might find in some rare cases that some columns continue to be merged. This could be caused by internal unit conversion and rounding when the Excel worksheet is rendered. In the report definition language (RDL), you can specify position and size in different measurement units such as inches, pixels, centimeters, and points. Internally the Excel uses points. To minimize conversion and the potential inaccuracy of rounding when converting inches and centimeters to points, consider specifying all measurements in points for the most direct results. One inch is 72 points. http://technet.microsoft.com/en-us/library/dd255234.aspx

• Hide the header and footer. Cells will merge with those fields.  A fellow developer suggested in creating a report parameter. Each item that you want hidden will have an expression in the hidden property.    End user get to choose.

Add User To SSRS Reports In A Folder

RS.exe is located at \Program Files\Microsoft SQL Server\100\Tools\Binn. You can run the utility from any folder on your file system.

'=====================================================================
'  File:     AddUserToReportsInAFolder.rss
'
'  Summary:  Demonstrates a script that can be used with RS.exe to
'                set security on an item in Reporting Services.
'
'---------------------------------------------------------------------
' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
' PARTICULAR PURPOSE.
'=====================================================================*/
'
' Variables that are passed on the command line with the -v switch:
' userName - the name of the user for which to add a policy
' roleName - the name of the role to apply for the user (i.e. Browser, Content Manager)
' itemPath - the path of the item for which you want to add the policy (i.e. /SampleReports)
' keepCurrentPolicy - whether to keep the current policy and add the new one
'
' Sample command line:
' rs -i AddItemSecurity.rss -s http://localhost/reportserver -v userName="MyTestUser"
'    -v roleName="Browser" -v itemPath="/SampleReports" -v keepCurrentPolicy="True"

Public Sub Main()
   Dim isRoot As Boolean = False
   Dim inheritParent As Boolean
  
   Dim policies() As Policy
   Dim newPolicies() As Policy
   Dim policy As New Policy()
   Dim roles(0) As Role
   roles(0) = New Role()
   roles(0).Name = roleName
   policy.Roles = roles
   policy.GroupUserName = userName
  
   While Not isRoot
      ' Once the root of the catalog is reached,
      ' stop applying policies
      If itemPath = "/" Then
         isRoot = True
      End If
     
      policies = rs.GetPolicies(itemPath, inheritParent)
        
      ' If the user selects not to keep inherited or current policy,
      ' empty the policy
      If Not keepCurrentPolicy = "True" Then
         policies = Nothing
      End If
      newPolicies = AddNewPolicy(policy, policies)
      rs.SetPolicies(itemPath, newPolicies)
      itemPath = GetParentPath(itemPath)
   End While
   Console.WriteLine("Policy successfully set.")
End Sub 'Main
  

' Method to parse the path of an item and retrieve
' the parent path of an item
Private Function GetParentPath(currentPath As String) As String
   Dim delimiter As String = "/"
   Dim rx As New System.Text.RegularExpressions.Regex(delimiter)
   Dim childPath As String() = rx.Split(currentPath)
  
   Dim parentLength As Integer = childPath.Length - 1
   Dim parentPath(parentLength) As String
  
   Dim i As Integer
   For i = 0 To parentLength - 1
      parentPath(i) = childPath(i)
   Next i
   If parentPath.Length = 1 Then
      Return "/"
   Else
      Return String.Join("/", parentPath)
   End If
End Function 'GetParentPath
  
' Takes the policy to add and applies it to the current set
' of policies if applicable
Private Function AddNewPolicy(policyToAdd As Policy, policies() As Policy) As Policy()
   If Not (policies Is Nothing) Then
      Dim policy As Policy
      For Each policy In  policies
         If policy.GroupUserName = policyToAdd.GroupUserName Then
            Throw New Exception("The supplied User policy already exists for the item.")
         End If
      Next policy
      Dim list As New System.Collections.ArrayList(policies)
      list.Add(policyToAdd)
      Return CType(list.ToArray(GetType(Policy)), Policy())
   Else
      policies = New Policy(0) {}
      policies(0) = policyToAdd
      Return policies
   End If
End Function 'AddNewPolicy