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)

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

List Object in SQL Db - Specific Examples

The generic SQL found here can be tweaked to do almost anything


The following are more specific examples

-- This snippet searches for columns named like %product% 
SELECT
o.name AS TableName,c.*
FROM
sysobjects AS o
 INNER JOIN syscolumns AS c
  ON c.id = o.id
WHERE
(o.type = 'U' OR o.type = 'S')
AND c.name LIKE '%product%'

SQL- Strpping out time from DateTime - going back to weekdays

-- locate last sunday
DECLARE @Date As DATETIME
DECLARE @CurDay AS INTEGER

SET @CurDay = DATEPART(weekday,GETDATE())
SET @Date =
CASE
WHEN 1 = @CurDay THEN GETDATE()
WHEN 2 = @CurDay THEN DATEADD(DAY,-1,GETDATE())
WHEN 3 = @CurDay THEN DATEADD(DAY,-2,GETDATE())
WHEN 4 = @CurDay THEN DATEADD(DAY,-3,GETDATE())
WHEN 5 = @CurDay THEN DATEADD(DAY,-4,GETDATE())
WHEN 6 = @CurDay THEN DATEADD(DAY,-5,GETDATE())
WHEN 7 = @CurDay THEN DATEADD(DAY,-6,GETDATE())
ELSE GETDATE()
END
--Strip out the time part
SET @Date = CAST((STR( YEAR( GETDATE() ) ) + '/' + STR( MONTH( GETDATE() ) ) + '/' + STR( DAY( GETDATE() ) ) )AS DATETIME )
Print @Date

Avoid cursors in SQL Server with these methods to loop over records

This article copied from http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1339242,00.html?track=NL-414&ad=672066&asrc=EM_NLT_5051592&uid=6482800#

Matthew Schroeder
11.17.2008
Rating: --- (out of 5)

Digg This! StumbleUpon Del.icio.us

Many articles have beaten up on SQL Server cursors -- database objects that manipulate data in a set on a row-by-row basis -- and I want to add my name to the list of people who wish cursors had never been introduced. But, unfortunately, cursors are a fact of life. Problems with cursors include extending locks, their inability to cache execution plans and CPU/RAM overhead. Many T-SQL programmers and DBAs do not know how to successfully loop over records without the need for cursors. In this tip, I'll share some alternatives to cursors that provide looping functionality. Method 1: Temp table with identity column In the first approach, we will use a temp table with an identity column added to allow for row-by-row selection. If you're performing an INSERT/UPDATE/DELETE, be sure to use the explicit transactions. This vastly reduces the load on your log file by committing per loop, and it prevents huge rollbacks in the case of failure. set nocount on declare @i int --iterator declare @iRwCnt int --rowcount declare @sValue varchar(100) set @i = 1 --initialize create table #tbl(ID int identity(1,1), Value varchar(100)) insert into #tbl(Value) select name from master..sysdatabases (nolock) set @iRwCnt = @@ROWCOUNT --SCOPE_IDENTITY() would also work create clustered index idx_tmp on #tbl(ID) WITH FILLFACTOR = 100 /* Always do this after the insert, since it's faster to add the index in bulk than to update the index as you write into the temp table. Since you know the data in this column, you can set the fill factor to 100% to get the best read times. */ while @i <= @iRwCnt begin select @sValue = Value from #tbl where ID = @i --begin tran print 'My Value is ' + @sValue --replace with your operations on this value --commit tran set @i = @i + 1 end drop table #tbl Method 2: Temp table without ID In the second approach, we use a temp table without an identity column and simply grab the top row to process, then loop until we find no more rows to process. If you're performing an INSERT/UPDATE/DELETE, again, be sure to use the explicit transactions to vastly reduce the load on your log file by committing per loop, which prevents huge rollbacks in the case of failure. set nocount on declare @i int --iterator declare @iRwCnt int --rowcount declare @sValue varchar(100) set @i = 1 --initialize create table #tbl(Value varchar(100)) insert into #tbl(Value) select name from master..sysdatabases (nolock) set @iRwCnt = @@ROWCOUNT --SCOPE_IDENTITY() would also work create clustered index idx_tmp on #tbl(Value) WITH FILLFACTOR = 100 /* Always do this after the insert, since it's faster to add the index in bulk than to update the index as you write into the temp table. Since you know the data in this column, you can set the fill factor to 100% to get the best read times. */ while @iRwCnt > 0 begin select top 1 @sValue = Value from #tbl set @iRwCnt = @@ROWCOUNT --ensure that we still have data if @iRwCnt > 0 begin --begin tran print 'My Value is ' + @sValue --replace with your operations on this value --commit tran delete from #tbl where value = @sValue --remove processed record end end drop table #tbl Method 3: Selecting a comma-delimited list of items When most developers/DBAs are asked to come up with a list of comma-delimited values from a table, they typically use a cursor or temp table (as above) to loop through the records. However, if you do not need to use a GROUP BY or an ORDER BY, then you can use the method below that operates in batch to handle the task. This cannot be used with GROUP BY DISTINCT, or ORDER BY, because of how SQL Server handles those operations. Basically, this takes a given variable, and for every row in the table it adds the current value to the variable along with a comma. declare @vrs varchar(4000) declare @sTbl sysname set @sTbl = 'TableName' set @vrs = '' select @vrs = @vrs + ', ' + name from syscolumns where id = (select st.id from sysobjects as st where name = @sTbl) order by colorder set @vrs = right(@vrs, len(@vrs)-2) print @vrs This article gives you some good reasons why cursors in SQL Server should be avoided as well as some alternatives that give you looping functionality. Keep in mind that SQL Server is designed around batch processing, so the less you loop, the faster your system will run.

Importing Active Directory Details to SQL Server

The following script can be used to import Active Directory data into a Sql table.

Note - there is a big limitation with using this - only the first 1000 entries will be selected

You need to initially create the Active Directory database as a linked server.

The script uses subquerys to allow for manipulation in the SELECT Field1,Field2,Field3,Field4 FROM statement and you will have to put in your own AD specific details for the parameters and fields.

Apologies for the code formating problems I had with this - remove the underscores

-- to link AD Server
-- sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
BEGIN TRANSACTION
  TRUNCATE TABLE AdUserDetails
  INSERT INTO AdUserDetails (Field1, Field2, Field3, Field4)
        -- Select the required fields from Active Directory
        SELECT  Field1,Field2,Field3,Field4 FROM
            (SELECT TOP 100 PERCENT *
            FROM OPENQUERY(ADSI,
            '<LDAP:/ /dc=YourADParam1,dc=YourADParam2,dc=YourADParam3>;(&(objectCategory=person)(objectClass=user)(name=*));AdField1,AdField2,AdField3,AdField4;subtree') AS t
            WHERE AdField3 IS NOT NULL) AS ad
COMMIT TRANSACTION
select * from AdUserDetails

SLQ 2000 Script to Maintain Indexes

There seem to be a lot of problems with the maintenance of indexes in SQL 2000 using the inbuilt Database Maintenance Plans.

The following script creates 2 tables and a stored procedure that can be run as a periodic job. It will defrag any indexes > 30% fragmentation in its default state.

Just be wary if you are log shipping though. Index work of any kind can blow out your log file size.

To install replace "Database Name Here" with your database name - open query analyzer and run the script.

The handling of the indexes can be controlled via the maint_index table. Be aware that the stored procedure processes the indexes in this table then repopulates it with the current state of the database. It is designed this way for a set and forget operation that will also allow you to make modifications to the indexes or force a rebuild by changing values in the maint_index table. This means in practice that if you are running it once per week then the indexes will be rebuilt bases on their state the previous week when the stored procedure was last run.
Fragmenting indexes will bubble up to the top and get processed but the maximum they will be processed is every second run.

Script Variables that can be set

• @db_name - self explanitory - this needs to be set
  
• @maxfrag - changes the fragmentation level at which an index is
  
• @maxminutes - This is a safety setting to limit how long the defrag process will run. If the script is still defraging after this period of time it will finish the current index and exit the loop
  

Database Fields that can be changed
You can go to the maint_index table and change operation performed on individual indexes the next time the script is run.

• Force_Defrag_REINDEX By default the script runs a defrag on the index (0). If you set this to 1 then a full reindex will be performed (but be aware that the table might be offline during this operation)
  
• SetFillFactor This will change the fill factor of the index. the default is its existing value.
  

I have had great success with this script improving "Doggy" servers performance.

Enjoy

Gerard


CREATE TABLE [maint_index] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LogicalFrag] [decimal](18, 0) NULL ,
[Force_Defrag_REINDEX_1or2] [int] NULL CONSTRAINT [DF_maint_index_Force_Defrag_REINEX] DEFAULT (0),
[SetFillFactor] [int] NULL CONSTRAINT [DF_maint_index_FillFactor] DEFAULT (0),
[ObjectId] [int] NULL ,
[IndexId] [int] NULL ,
[Lvl] [int] NULL ,
[CountPages] [int] NULL ,
[CountRows] [int] NULL ,
[MinRecSize] [int] NULL ,
[MaxRecSize] [int] NULL ,
[AvgRecSize] [int] NULL ,
[ForRecCount] [int] NULL ,
[Extents] [int] NULL ,
[ExtentSwitches] [int] NULL ,
[AvgFreeBytes] [int] NULL ,
[AvgPageDensity] [int] NULL ,
[ScanDensity] [decimal](18, 0) NULL ,
[BestCount] [int] NULL ,
[ActualCount] [int] NULL ,
[ExtentFrag] [decimal](18, 0) NULL ,
[Defrag] [int] NULL CONSTRAINT [DF_maint_index_Defrag] DEFAULT (0),
[Fill_Factor] [int] NULL CONSTRAINT [DF_maint_index_Fill_Factor] DEFAULT (0)
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[maint_index_process] AS

SET NOCOUNT ON

--maint_index

SET NOCOUNT ON

DECLARE @db_name VARCHAR (128)

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @indexname VARCHAR (255)

DECLARE @objectid INT

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @reindex INT

DECLARE @fill INT

DECLARE @maxfrag DECIMAL

DECLARE @maxminutes INT

DECLARE @maxruntime DATETIME

DECLARE @TempId int

--specify for each database it is run on

SELECT @db_name = 'CMS'

--maximum fragmentation to allow

SELECT @maxfrag = 30.0

--maximum time for the process to run, it will exit the loop if this time is exceeded

--Note1 if reindex run over they will not be stopped

SELECT @maxminutes = 90--1.5 hours

select @maxruntime = DATEADD(minute,@maxminutes,GETDATE()) --2 hours max run

--process reindexes, marked defrags, defrags over @maxfrag

DECLARE indexes CURSOR FOR

SELECT ObjectName, ObjectId, IndexName, IndexId, LogicalFrag, Force_Defrag_REINDEX_1or2, SetFillFactor

FROM maint_index

WHERE indexid >0

AND IndexId <> 255 --this indicates image data and cannot be defragged

AND (LogicalFrag>=@maxfrag

OR Force_Defrag_REINDEX_1or2 > 0)

ORDER BY Force_Defrag_REINDEX_1or2 DESC --do the reindexing first, indexes specifed to be defragmented then general fragmented over @maxfrag

-- Open the cursor

OPEN indexes

-- loop through the indexes

FETCH NEXT

FROM indexes

INTO @tablename,@objectid, @indexname, @indexid, @frag, @reindex, @fill

WHILE (@@FETCH_STATUS = 0) AND (GETDATE()< @maxruntime)--while we still have records and we havn't exceeded out times

BEGIN

IF @reindex = 2

BEGIN

IF @fill<1

BEGIN

SELECT @execstr = '])'--go with the default fill factor

SELECT @fill = -1 --indicate in history table that existing fill was used

END

ELSE

BEGIN

SELECT @execstr = '],' + CAST(@fill AS VARCHAR) + ')'

END

--create the command and insert initial history record

SELECT @execstr = 'DBCC DBREINDEX (''[' + @db_name + '].dbo.[' + RTRIM(@tablename) + ']'',[' + RTRIM(@indexname) + @execstr

INSERT INTO maint_index_history (maint_Database,maint_Table,maint_Index,StartTime,RequestedAction,FillUsed,CommandString)

VALUES (@db_name,RTRIM(@tablename),RTRIM(@indexname),GETDATE(),@reindex,@fill,@execstr)

SELECT @TempId=@@identity

--Do a reindex

EXEC (@execstr)

--update the history record

UPDATE maint_index_history SET EndTime= GETDATE() WHERE Id = @TempId

SELECT @TempId=0

END

ELSE

BEGIN

--create the command and insert initial history record

SELECT @execstr = 'DBCC INDEXDEFRAG ([' + @db_name + '], [' + RTRIM(@tablename) + '],[' + RTRIM(@indexname) + '])'

INSERT INTO maint_index_history (maint_Database,maint_Table,maint_Index,StartTime,RequestedAction,CommandString)

VALUES (@db_name,RTRIM(@tablename),RTRIM(@indexname),GETDATE(),@reindex,@execstr)

SELECT @TempId=@@identity

--Do a Defrag

EXEC (@execstr)

--update the history record

UPDATE maint_index_history SET EndTime= GETDATE() WHERE Id = @TempId

SELECT @TempId=0

END

FETCH NEXT FROM indexes

INTO @tablename,@objectid, @indexname, @indexid, @frag, @reindex, @fill

END

-- Close and deallocate the cursor

CLOSE indexes

DEALLOCATE indexes

--create temporary table

-- Create the table

CREATE TABLE #fraglist (

ObjectName CHAR (255),

ObjectId INT,

IndexName CHAR (255),

IndexId INT,

Lvl INT,

CountPages INT,

CountRows INT,

MinRecSize INT,

MaxRecSize INT,

AvgRecSize INT,

ForRecCount INT,

Extents INT,

ExtentSwitches INT,

AvgFreeBytes INT,

AvgPageDensity INT,

ScanDensity DECIMAL,

BestCount INT,

ActualCount INT,

LogicalFrag DECIMAL,

ExtentFrag DECIMAL)

-- Declare cursor

DECLARE tables CURSOR FOR

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

-- Open the cursor

OPEN tables

-- Loop through all the tables in the database

FETCH NEXT

FROM tables

INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

-- Do the showcontig of all indexes of the table

INSERT INTO #fraglist

EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')

WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

FETCH NEXT

FROM tables

INTO @tablename

END

-- Close and deallocate the cursor

CLOSE tables

DEALLOCATE tables

--insert the temporary table into the maint_index then drop it

TRUNCATE TABLE maint_index--clear out existing data

INSERT INTO maint_index

(ObjectName,IndexName,LogicalFrag,ObjectId,IndexId,Lvl,CountPages,CountRows,MinRecSize,MaxRecSize,AvgRecSize,ForRecCount,Extents,ExtentSwitches,AvgFreeBytes,AvgPageDensity,ScanDensity,BestCount,ActualCount,ExtentFrag )

select

ObjectName,IndexName,LogicalFrag,ObjectId,IndexId,Lvl,CountPages,CountRows,MinRecSize,MaxRecSize,AvgRecSize,ForRecCount,Extents,ExtentSwitches,AvgFreeBytes,AvgPageDensity,ScanDensity,BestCount,ActualCount,ExtentFrag

from #fraglist

where indexid >0

DROP TABLE #fraglist