CmdExec (running a bat or executable file via SSIS)

I was having an issue of being able to run a package that prints in BIDS but not as a SQL Server Agent job.
Even though I sent everything as run as 32 bit.

Finally tried this.

used this application to print via command line in SSIS Script Task http://www.coolutils.com/TotalPDFPrinterX

Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.ComponentModelImports System.DiagnosticsImports System.IO
Process.Start(
System.Threading.Thread.Sleep(5000)
"C:\Program Files (x86)\Total PDF PrinterX\PDFPrinterX.exe", Chr(34) + complaint_folder + "\*.pdf" + Chr(34) + "-p" + Dts.Variables("varPrinterName").Value.ToString)
System.Threading.Thread.Sleep(5000)
Dts.TaskResult = ScriptResults.SuccessJob Tye (SQL Server Agent)
Operating System (CmdExec)
----------------------------------
"D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"  /DECRYPT  "password" /FILE "D:\PRODUCTION_FOLDER\DEPLOYED_PACKAGES\PROD\RegulatoryPrint3.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

VS2008 Integration Services Project error: Failed to save package file .... with error 0x8002801D "Library not registered."

 run
regsvr32 msxml3.dll
and
regsvr32 msxml6.dll

Get message saying dll regestered.
SSIS works fine

Automate Windows Server defragmentation

E drive is the drive you wish to defragment

Put this in a bat file and run a scheuled task pointing the bat file.
defrag.exe -v e: >c:\temp\logfile.txt

To check the status you can look how long, etc in the log file under the Advanced /  View Log of the scheduled task  menu window.

Spaced Used by a table in a specific database.

Shows you how to get the SQL Server database table size
EXEC SP_SPACEUSED 'TABLE NAME'

Shows you how to get the size of a database
EXEC SP_SPACEUSED

Shows you how to get the all tables size from database
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

–--create temp table to store the result
CREATE TABLE #temptable
  (
     tablename    VARCHAR(100),
     numberofrows VARCHAR(100),
     reservedsize VARCHAR(50),
     datasize     VARCHAR(50),
     indexsize    VARCHAR(50),
     unusedsize   VARCHAR(50)
  )

---Inserting into temp table
INSERT INTO #temptable
            (tablename,
             numberofrows,
             reservedsize,
             datasize,
             indexsize,
             unusedsize)

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SELECT *
FROM   #temptable
---drop the temporary table
DROP TABLE #temptable

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

Cancel A Specific SSRS Job

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:      CancelSpecificJob.rss
'
'  Summary:  Demonstrates a script that can be used with RS.exe to
'      cancel a specific job that is currently running on a server.
'
'
'---------------------------------------------------------------------
'
' 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.
'==============================================================================
'
' 1.0 Documentation
'
' Read the following in order to familiarize yourself with the sample script.
'
' 1.1 Overview
'
' The script lists all jobs that are currently running on the report server and
' then enables the user to cancel a spefic job ID.
'
' 1.2 Script Variables
'
' None.
'
' 1.3 Sample Command Lines
'
'
' 1.3.1 To view and cancel running jobs
'
'       rs -i CancelSpecificJob.rss -s http://myserver/reportserver
'
Public Sub Main()
   Dim jobs As Job() = rs.ListJobs()
   If ListRunningJobs(jobs) Then
     
      Console.Write("Which Job ID to kill? ")
     
      Dim  userJobID As String = Console.ReadLine()
      'If [Char].ToLower(Microsoft.VisualBasic.ChrW(userJobID)) = "XX" Then
       'CancelRunningJobs(jobs)
       'Else
       rs.CancelJob(userJobID)
      'End If
   End If
End Sub
  
  
Public Function ListRunningJobs(jobs() As Job) As Boolean
   Dim runningJobCount As Integer = 0
   Dim message As String
   Console.WriteLine("Current Jobs")
   Console.WriteLine(("================================" + Environment.NewLine))
   Dim job As Job
   For Each job In  jobs
      If job.Status = JobStatusEnum.Running Or job.Status = JobStatusEnum.[New] Then
      ' New goes away soon
         Console.WriteLine("--------------------------------")
         Console.WriteLine("JobID: {0}", job.JobID)
         Console.WriteLine("--------------------------------")
         Console.WriteLine("Action: {0}", job.Action)
         Console.WriteLine("Description: {0}", job.Description)
         Console.WriteLine("Machine: {0}", job.Machine)
         Console.WriteLine("Name: {0}", job.Name)
         Console.WriteLine("Path: {0}", job.Path)
         Console.WriteLine("StartDateTime: {0}", job.StartDateTime)
         Console.WriteLine("Status: {0}", job.Status)
         Console.WriteLine("Type: {0}", job.Type)
         Console.WriteLine("User: {0}" + Environment.NewLine, job.User)
         runningJobCount += 1
      End If
   Next job
   If runningJobCount = 1 Then
      message = "There is 1 running job. "
   Else
      message = String.Format("There are {0} running jobs. ", runningJobCount)
   End If
   Console.Write(message)
   If runningJobCount > 0 Then
      Return True
   Else
      Return False
   End If
End Function
   
Public Sub CancelRunningJobs(jobs() As Job)
   Dim job As Job
   For Each job In  jobs
      If job.Status = JobStatusEnum.Running Or job.Status = JobStatusEnum.[New] Then
         ' New goes away soon
         rs.CancelJob(job.JobID)
      End If
   Next job
   Console.WriteLine("All jobs successfully canceled.")
End Sub