Archive for the 'SQL Server' Category



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

Advertisements

Using SSIS to Export a Fixed Width Flat File

To begin, open Business Intelligence Development Studio (or Visual Studio, if you have that installed) and under Business Intelligence projects, create a new Integration Services Package. You will also need a directory at c:\export to contain the output flat file.
Start by adding a new database connection on the Connection Manager tab (this is usually at the bottom of the SSIS Package Designed). For the purposes of this tutorial I will be using the AdventureWorks database my local machine. This connection will be the source of the data for the flat file.
Database Connection

Database Connection

After you create the database connection, go ahead and create the flat file destination connection. I used the c:\export directory and named my export file Export.txt, but anything will work. On the format drop down, select “Fixed width.”

Data Destination

Data Destination

Please ignore the “Column” sub-menu on the right for now. Select “Advanced” from the sub-menu on the right. Now we will create all of our columns. For this tutorial I created a mail merge type export using First Name, Last Name, Address, City, State and Zip. Add one column to handle the line feed/carriage return. Without this column the package output will concatenated into on one row.

Data Destination Columns

Data Destination Columns

On the Control Flow tab of the SSIS Package, add a data flow task. After adding the task, select the Data Flow tab. Add an OLE DB Source. On the OLE DB Connection Manager, the database connection we created earlier should already be selected. If it is not, select the connection we created at the beginning of the tutorial. The T-SQL used to select the data is below. Please note that there is no data formatting in the T-SQL, only the inclusion the column to handle the line feed/carriage return.

ANSI decimal character 10 is line feed, CHAR(10) in T-SQL, and ANSI decimal character 13 is a carriage return, CHAR(13) in T-SQL. Sometimes these characters are used in conjunction, depending on the format, but for our purposes we must use only one or the other.

SELECT
    pc.LastName,
    pc.FirstName,
    pa.AddressLine1,
    pa.City,
    psp.StateProvinceCode,
    pa.PostalCode,
    CHAR(10) CRLF
FROM
    Person.Contact pc
INNER JOIN HumanResources.Employee hre
    ON pc.ContactID = hre.ContactID
INNER JOIN HumanResources.EmployeeAddress hrea
    ON hre.EmployeeID = hrea.EmployeeID
INNER JOIN Person.Address pa
    ON hrea.AddressID = pa.AddressID
INNER JOIN Person.StateProvince psp
    ON pa.StateProvinceID = psp.StateProvinceID
WHERE
    psp.CountryRegionCode = 'US' ;
Data Source

Data Source

One of the issues that you will face with any fixed width export are columns whose width is greater than the fixed width destination. We are going to handle this with a Data Conversion task. Be sure to give the converted column a friendly name, other than “Copy of ” and the column and set the length of the converted field to the same length of the destination fixed width column.

Data Conversion

Data Conversion

To make sure that the package succeeds if the width of the origin column is greater than the destination column, we will set the conversion to ignore all truncation errors. For these purposes it is important not to confuse width and length. A column with a data type of nchar(4) has a width of 4 but may have a length of 0 to 4. The data length may be less than or equal to destination, but if the width is greater it may prove problematic.

Error Handling

Error Handling

After adding the data conversions and error handling, let’s map the converted columns to our flat file destination columns.

Flat File Mapping

Flat File Mapping

That’s it. Run the package and you will see the fruits of your labor.
~Ron

UPDATE: The zipped solution can be downloaded from here. WordPress does not allow files of this type, so it is available via Dropbox.

Script Column Default Values

Another database developer had copied several database from our model office back to our development environment. When we started to wire up out applications I noticed that there were no indexes, defaults, keys, or constraints of any kind. Just heaps.

Adding these constraints is fairly straigtforward, and there are many tools out there to do that. But I could not find a tool that would script just the default constraints without also dropping and/or creating the table. It took a couple of days to get the databases moved over in the first place, but there were over a thousand default constraints, which could also take several days if done manually.

I was sure that there was a way to script this, but after over an hour of searching I decided that I burned enough time and that I would write one myself. I don’t know how often anyone would come across this issue, but here it is anyways.

SELECT
    'ALTER TABLE dbo.' + OBJECT_NAME(s.id) + ' ADD CONSTRAINT DF_' + OBJECT_NAME(s.id) + '_' + sc.name + ' DEFAULT ' + c.text + ' FOR ' + sc.name
FROM
    dbo.sysconstraints s
INNER JOIN syscolumns sc
	ON s.id = sc.id AND
	   s.colid = sc.colid
INNER JOIN dbo.syscomments c
    ON s.constid = c.id
WHERE
    s.status & 5 = 5

Disable Auto-Recover in SSMS

I recently upgraded to SQL Server Management Studio 2008. (I LOVE the Script as … Drop and Create functionality.) Everything was working great at first, but after a while I noticed that management studio was freezing up on for a couple of seconds every minute. This got progressively worse throughout the day.

Then I noticed that this freeze coincided with the Auto Recover save time. I had never messed with this setting before, so I looked around to see where to change it.

Apparently there is no way to change this in the GUI, but you can change it in the registry.

SQL 2005

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover]
"AutoRecover Always Enabled"=dword:00000000
"AutoRecover Enabled"=dword:00000000

SQL 2008

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover]
"AutoRecover Always Enabled"=dword:00000000
"AutoRecover Enabled"=dword:00000000

~Ron

Installing (and Running) SSRS 2008 in Windows Vista

It’s been a while since I’ve played around with SQL Server Reporting Services, and I found a couple of interesting articles on using a report model with SSRS, so I decided to give it a go.

The installation went smoothly, as I have performed this task many times. However, when I visited the reporting services site I had no access. I am an administrator on the machine, and as such should have had access to the site.

I searched around for a good half hour and still hadn’t found anything that worked. Somehow I got the idea to run my internet browser as Administrator. Eureka! I added my local account to the SSRS instance and everything works very nicely.

~Ron

Debug Stored Procedures in SQL 2000 with Visual Studio

Before I forget this again … if you are not a member of the sysadmin role you need to run this to debug SQL 2000 stored procedures in Visual Studio.

USE [master];
GO

GRANT EXECUTE ON sp_sdidebug TO [User\Group\Role];

Attach Database Scripts

I was tasked with migrating a development database server to another development database server. Time was of the essence, so the decision was made to detach the files from the original database server and attach them to the new server.

There were twenty-one databases in all. The caveat in all this was each database had multiple file groups and multiple files within each file group. There are plenty of attach database scripts out there, but none of them worked for me with the conditions listed above.

Since time was of the essence and this would be a massive undertaking if done manually, I wrote my own script to add to the masses. This script worked perfectly for me, and I hope you find it useful.

NOTE: I have added the option about half way down to create the scripts and have the logs rebuilt in case you only want to move the data files.

NOTE2: You should run the results to text.

~Ron

CREATE TABLE #_dba_migrate_db
(
	FileID int IDENTITY(1,1) NOT NULL,
	DBName varchar(255) NOT NULL,
	DBGroupName varchar(255) 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
	(
		FileID
	)
);
GO

sp_msforeachdb
'
	USE [?];

	IF DB_NAME() NOT IN (''master'',''model'',''msdb'',''tempdb'')
		INSERT INTO #_dba_migrate_db
		(
			DBName,
			DBGroupName,
			DBFileLogicalName,
			DBFileName,
			DBSize,
			DBMaxSize,
			DBFileGrowth
		)
		SELECT
			DB_NAME(),
			ISNULL(g.groupname,''LOG'') GroupName,
			LTRIM(RTRIM(f.name)) LogicalName,
			LTRIM(RTRIM(f.filename)) FileName,
			CAST(CEILING(((CONVERT(decimal(25,2),f.size)/1024)*8)) AS varchar(15)) + ''MB'' [Size],
			CASE f.maxsize WHEN -1 THEN ''UNLIMITED'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.maxsize)/1024)*8)) AS varchar(15)) + ''MB'' END [MaxSize],
			CASE WHEN f.status & 0x100000 > 0 THEN CAST(f.growth AS varchar(15)) + ''%'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.growth)/1024)*8)) AS varchar(15)) + ''MB'' END [FileGrowth]
		FROM
			sysfiles f
		INNER JOIN
			sysfilegroups g ON
				f.groupid = g.groupid
		UNION ALL

		SELECT
			DB_NAME(),
			''LOG'' GroupName,
			LTRIM(RTRIM(f.name)) LogicalName,
			LTRIM(RTRIM(f.filename)) FileName,
			CAST(CEILING(((CONVERT(decimal(25,2),f.size)/1024)*8)) AS varchar(15)) + ''MB'' [Size],
			CASE f.maxsize WHEN -1 THEN ''UNLIMITED'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.maxsize)/1024)*8)) AS varchar(15)) + ''MB'' END [MaxSize],
			CASE WHEN f.status & 0x100000 > 0 THEN CAST(f.growth AS varchar(15)) + ''%'' ELSE CAST(CEILING(((CONVERT(decimal(25,2),f.growth)/1024)*8)) AS varchar(15)) + ''MB'' END [FileGrowth]
		FROM
			sysfiles f
		WHERE
			groupid = 0
'
GO

SET NOCOUNT ON;

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

SET @IncludeLog = 0;

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) FileID,
			DBGroupName
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			(DBGroupName <> 'LOG' OR @IncludeLog = 1)
		GROUP BY
			DBGroupName
		ORDER BY
			MIN(FileID);

	OPEN group_cursor;

	FETCH NEXT FROM group_cursor INTO @FileID,@DBGroupName;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT
			@MaxData = MAX(FileID)
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			DBGroupName <> 'LOG';

		SELECT
			@MaxLog = MAX(FileID)
		FROM
			#_dba_migrate_db
		WHERE
			DBName = @DBName AND
			DBGroupName = 'LOG';
			

		SELECT CASE WHEN @DBGroupName NOT IN ('PRIMARY','LOG') THEN 'FILEGROUP ' + @DBGroupName
			WHEN @DBGroupName = 'LOG' THEN @DBGroupName + ' ON' ELSE @DBGroupName END;

		SELECT
			+ '('
			+ CHAR(10)
			+ '	NAME = ' + DBFileLogicalName + ', '
			+ CHAR(10)
			+ '	FILENAME = ''' + DBFileName + ''','
			+ CHAR(10)
			+ '	SIZE = ' + DBSize + ','
			+ CHAR(10)
			+ '	MAXSIZE = ' + DBMaxSize + ','
			+ CHAR(10)
			+ '	FILEGROWTH = ' + DBFileGrowth
			+ 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,@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