Archive for January, 2010

Updated: Attach Database Scripts

I updated my previous version of this script, which was written for SQL 2005, to handle filestream data. And I added a header for Ignas.

Please post in the comments if you run in to any issues.

~Ron

/******************************************************************************
    Name: Create Each Database for Attach
    Author: Ron Carpenter
    Date: 08/18/2009
    Revision: 1.0
    Purpose: This script will generate scripts to create each database on a
             sever for attach. It handles multiple files, multiple files,
             multiple files on a filegroup and multiple log files. There is a
             parameter to attach the logs or create the databases for attach
             with rebuild log.

    Author: Ron Carpenter
    Date: 01/20/2010
    Revision: 1.1
    Purpose: Updated the script to handle filestream data.
******************************************************************************/
USE [master] ;
GO
CREATE TABLE #_dba_migrate_db
    (
     FileID int IDENTITY(1,1)
                NOT NULL,
     DBName varchar(255) NOT NULL,
     DBGroupName varchar(255) NOT NULL,
     DBType tinyint NOT NULL,
     DBFileLogicalName varchar(255) NOT NULL,
     DBFileName varchar(1000) NOT NULL,
     DBSize varchar(15) NOT NULL,
     DBMaxSize varchar(15) NOT NULL,
     DBFileGrowth varchar(15) NOT NULL,
     CONSTRAINT PK_#_dba_migrate_db PRIMARY KEY CLUSTERED (DBType ASC,FileID ASC)
    ) ;
GO
sp_msforeachdb
'
USE [?] ;

IF DB_NAME() NOT IN (''master'',''model'',''msdb'',''tempdb'')
    INSERT INTO
        #_dba_migrate_db
        (
         DBName,
         DBGroupName,
         DBType,
         DBFileLogicalName,
         DBFileName,
         DBSize,
         DBMaxSize,
         DBFileGrowth
        )
        SELECT
            DB_NAME(),
            COALESCE(fg.name,''LOG''),
            f.type,
            LTRIM(RTRIM(f.name)),
            LTRIM(RTRIM(f.physical_name)),
            CAST(CEILING(((CONVERT(decimal(25,2),f.size) / 1024) * 8)) AS varchar(15)) + ''MB'',
            CASE f.max_size
              WHEN-1 THEN ''UNLIMITED''
              ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.max_size) / 1024) * 8)) AS varchar(15)) + ''MB''
            END,
            CASE f.is_percent_growth
              WHEN 1 THEN CAST(f.growth AS varchar(15)) + ''%''
              ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.growth) / 1024) * 8)) AS varchar(15)) + ''MB''
            END
        FROM
            sys.database_files f
        LEFT OUTER JOIN sys.filegroups fg
            ON f.data_space_id = fg.data_space_id ;
'
GO

SET NOCOUNT ON ;

SELECT
    'USE [master] ;' + CHAR(10) + 'GO' + CHAR(10) ;

DECLARE
    @DBName varchar(255),
    @DBGroupName varchar(255),
    @DBType int,
    @FileID int,
    @MaxData int,
    @MaxLog int,
    @MaxFileStrem int,
    @IncludeLog bit ;

SET @IncludeLog = 1 ;

DECLARE db_cursor CURSOR
    FOR SELECT DISTINCT
            DBName
        FROM
            #_dba_migrate_db

OPEN db_cursor ;

FETCH NEXT FROM db_cursor INTO @DBName ;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT
            'CREATE DATABASE ' + @DBName + ' ON'

        DECLARE group_cursor CURSOR
            FOR SELECT
                    MIN(FileID),
                    CASE DBType
                      WHEN 2 THEN 0
                      ELSE DBType
                    END,
                    DBGroupName
                FROM
                    #_dba_migrate_db
                WHERE
                    DBName = @DBName AND
                    (DBType <> 1 OR @IncludeLog = 1)
                GROUP BY
                    DBGroupName,
                    DBType
                ORDER BY
                    CASE DBType
                      WHEN 2 THEN 0
                      ELSE DBType
                    END,
                    MIN(FileID) ;

        OPEN group_cursor ;

        FETCH NEXT FROM group_cursor INTO @FileID,@DBType,@DBGroupName ;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT
                    @MaxData = MAX(FileID)
                FROM
                    #_dba_migrate_db
                WHERE
                    DBName = @DBName AND
                    DBType IN (0,2) ;

                SELECT
                    @MaxLog = MAX(FileID)
                FROM
                    #_dba_migrate_db
                WHERE
                    DBName = @DBName AND
                    DBType = 1 ;

                SELECT
                    CASE WHEN DBType = 0 AND DBGroupName = 'PRIMARY' THEN DBGroupName
                         WHEN DBType = 1 THEN DBGroupName + ' ON'
                         WHEN DBType = 2 THEN 'FILEGROUP ' + DBGroupName + ' CONTAINS FILESTREAM'
                         ELSE 'FILEGROUP ' + DBGroupName
                    END
                FROM
                    #_dba_migrate_db
                WHERE
                    FileID = @FileID ;

                SELECT
                    '(' + CHAR(10) + '	NAME = ' + DBFileLogicalName + ', ' + CHAR(10) + '	FILENAME = ''' + DBFileName + CASE WHEN DBType <> 2 THEN ''',' + CHAR(10) + '	SIZE = ' + DBSize + ',' + CHAR(10) + '	MAXSIZE = ' + DBMaxSize + ',' + CHAR(10) + '	FILEGROWTH = ' + DBFileGrowth
                                                                                                                             ELSE ''''
                                                                                                                        END + CHAR(10) + ')' + CASE WHEN FileID IN (@MaxData,@MaxLog) THEN ''
                                                                                                                                                    ELSE ','
                                                                                                                                               END
                FROM
                    #_dba_migrate_db
                WHERE
                    DBName = @DBName AND
                    DBGroupName = @DBGroupName ;

                FETCH NEXT FROM group_cursor INTO @FileID,@DBType,@DBGroupName ;
            END

        CLOSE group_cursor ;

        DEALLOCATE group_cursor ;

        SELECT
            CASE WHEN @IncludeLog = 1 THEN 'FOR ATTACH;'
                 ELSE 'FOR ATTACH_REBUILD_LOG;'
            END + CHAR(10) + 'GO' + CHAR(10) ;

        FETCH NEXT FROM db_cursor INTO @DBName ;
    END

CLOSE db_cursor ;

DEALLOCATE db_cursor ;

DROP TABLE #_dba_migrate_db ;
Advertisements

Search SQL Server Reporting Services Report Definition

A little over a year ago, Raja and I were having a discussion about reporting services. The discussing revolved around writing the T-SQL in the report definition or creating stored procedures for each dataset and executing the stored procedure in the report definition.

However, one of the drawbacks of keeping the T-SQL in the report definition is that you cannot just simply search in syscomments to find objects that may be affected by a schema change. But searching the report definition is still possible! A report definition file is simply XML stored in the ReportServer database in the Catalog table. (It is stored using the image data type, even in SQL 2008. What is up with this, Microsoft? I thought the image data type was deprecated in favor of varbinary(max).)

So the first thing that we need to do is convert the image data to XML. You cannot explicitly convert image to XML, but you can explicitly convert image to varbinary and varbinary to XML, so that is what we will do. Next we will shred the XML using the nodes() method. This is the most effective method since a report definition can contain multiple data sets, and we want to capture all of them.

The SQL below will create a view with the report path, report name, data set name and the T-SQL for easy searching.

NOTE: The XML namespace may be different, depending on the version you are using. If this does not work for you at first, run the T-SQL from the derived table and click on the XML. The top line of the XML will contain the namespace corresponding to the version you are using.


USE [ReportServer]
GO
IF EXISTS (SELECT
 *
 FROM
 sys.views
 WHERE
 object_id = OBJECT_ID(N'[dbo].[ReportDefinition]'))
 DROP VIEW [dbo].[ReportDefinition]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ReportDefinition]
AS
WITH XMLNAMESPACES
(
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS RES
)
SELECT
 c.Path,
 c.Name,
 DataSet.value('@Name','varchar(MAX)') DataSourceName,
 DataSet.value('RES:Query[1]/RES:CommandText[1]','varchar(MAX)') CommandText
FROM
 (SELECT
 ItemID,
 CAST(CAST(Content AS varbinary(max)) AS xml) ReportXML
 FROM
 dbo.Catalog
 WHERE
 Type = 2) ReportXML
CROSS APPLY ReportXML.nodes('RES:Report/RES:DataSets/RES:DataSet') DataSetXML (DataSet)
INNER JOIN dbo.Catalog c
 ON ReportXML.ItemID = c.ItemID
GO

Happy coding!

~Ron

Reports Usage in SSRS

We are using SSRS for all of our reports and we have a dedicated SQL Server just for this purpose. We wanted to see how many reports are being used on a consistent manner so that we can fine tune those reports for performance. Sometime back Ron wrote a simple query that would do this trick. Just found it in my archives and thought of sharing it with you.


SELECT
 c.[name],
 COUNT(*)
FROM
 dbo.ExecutionLog el inner join dbo.Catalog c on
 el.reportid = c.itemid
WHERE
 el.timestart between '20090901' and '20091231'
GROUP BY
 c.[name]

Hope this helps.

Happy Programming!!!

Cheers,

Raja