Today I needed a query to see what the size and growth settings were for our databases. The query below will output name, size and growth information.

SELECT  SUBSTRING([name], 0, 40) as [Name],
                (([size]*8)/1024) as SizeMB,
                CASE [growth]
                        WHEN 0 THEN 'Fixed Size'
                        ELSE
                                CASE [is_percent_growth]
                                        WHEN 0 THEN 'Absolute growth: ' + CAST([growth] as varchar)
                                        WHEN 1 THEN 'Percentage growth: ' + CAST([growth] as varchar)
                                END
                END as [GrowthInfo]
FROM    sys.database_files

Output looks like this:

Name                                     SizeMB      GrowthInfo
---------------------------------------- ----------- -------------------------------------------------
AdventureWorks                           10          Fixed Size
AdventureWorks_log                       5           Fixed Size

Microsoft has release SQL Server 2005 Service Pack 3, download the service pack here.

Release notes can be found here and there is also a list of the bugs that are fixed in SQL Server 2005 Service Pack 3. Looks like around 45 bugs fixes!

In this blog the release schedule for SQL Server is explained and it becomes clear that SQL Server 2008 won't become available until 2008-Q3. Now aside from SQL Server 2008 not becoming available, this also means that the business intelligence suite which comes with SQL Server, the one you need to create SSIS packages and SQL Server reports, will not RTM until Q3. This in turn means that you won't be able to use these designers in Visual Studio 2008.

The Business Intelligence Suite uses the Visual Studio Shell and integrates in such an excellent manner that it is quite thightly bound to a specific version of Visual Studio. The SQL Server 2005 Suite is bound to VS2005 and the SQL Server 2008 Suite is bound to VS2008. So if you're planning on moving to VS2008 you'll have to run VS2005 as well, at least until 2008-Q3.

During our usergroup meeting last week the topic of 'materialized views' in Oracle came up. I wasn't familiar with the term, but John looked it up for me (thanks John!).
Materialized Views in Oracle seem to differ in implementation, but accomplish much the same as Index Views in SQL Server.

More on Materialized Views:
http://en.wikipedia.org/wiki/Materialized_view

More on Indexed Views:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

 

Niels confirmed it for me. Yes, it is possible to run a LINQ to SQL query in SQL Server 2008 using a .NET managed stored procedure.

As he puts it: "yes you can - but as soon there are any transactions invloved things will go pear-shaped".

Note that there will also be an optimized LINQ to SQL provider as part of SQL Server 2008:

"Language Integrated Query (LINQ) enables developers to issue queries against data by using a managed programming language such as C# or Visual Basic.NET, instead of SQL statements. LINQ enables seamless, strongly typed, set-oriented queries written in .NET Framework languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping Provider. SQL Server 2008 features a new LINQ to SQL Provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns."

I needed to export all rights granted to a specific role from a SQL Server 2005 database in order to check these roles into our TFS server.

/*
	Script	:	ExportRole.sql
	Version	:	1.0
	Date	:	11-09-2007
	Author	:	Mark Blomsma, Develop-One (www.develop-one.com)
	Desc.	:	Export all rights for a role from a SQL Server 2005 database.
			Including Service Broker messages, contracts and services.
*/

-- Role is the parameter for this procedure
DECLARE @Role VARCHAR(40)
SET @Role = 'Rolename goes here'

-- Retrieve the roleid for the role which we need to export
DECLARE @RoleId int
SELECT 
	@RoleId = dp.principal_id
FROM
	 sys.database_principals dp
WHERE
	dp.name = @Role

-- Declare a temp table for collecting results.
-- Each grant statement will be a line in the results table.
DECLARE @Result 
TABLE
	(
		Line VARCHAR(256)
	)

-- Use the current database
INSERT INTO
	@Result (Line)
VALUES
	('USE [' + DB_NAME() + ']')

-- Declare variable for retrieving data from cursor
DECLARE 
	@permission_type	char(4),
	@permission_name	sysname,
	@object_name		sysname,
	@object_type		char(2),
	@type_desc			nvarchar(60)

-- Declare object cursor for retrieving rights on database objects.
DECLARE objectCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT     
	p.type AS permission_type, 
	p.permission_name, 
	o.name, 
	o.type AS object_type, 
	o.type_desc
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.objects AS o ON p.major_id = o.object_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	o.type, o.name

OPEN 
	objectCursor;

-- Fetch first row with object permissions
FETCH NEXT FROM 
	objectCursor
INTO
	@permission_type,
	@permission_name,
	@object_name,
	@object_type,
	@type_desc	

-- Loop through object permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		objectCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name,
		@object_type,
		@type_desc	
END

-- Cleanup cursor
CLOSE 
	objectCursor
DEALLOCATE
	objectCursor

-- Declare object cursor for retrieving rights on message types.
DECLARE messagetypeCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	mt.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.service_message_types AS mt ON p.major_id = mt.message_type_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	mt.name

OPEN 
	messagetypeCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	messagetypeCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON MESSAGE TYPE :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		messagetypeCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	messagetypeCursor
DEALLOCATE
	messagetypeCursor


-- Declare object cursor for retrieving rights on contracts.
DECLARE contractCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	c.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.service_contracts AS c ON p.major_id = c.service_contract_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	c.name

OPEN 
	contractCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	contractCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON CONTRACT :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		contractCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	contractCursor
DEALLOCATE
	contractCursor



-- Declare object cursor for retrieving rights on services.
DECLARE serviceCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT    
	p.type AS permission_type, 
	p.permission_name, 
	s.name
FROM
	sys.database_permissions AS p 
INNER JOIN
	sys.services AS s ON p.major_id = s.service_id
WHERE
	(p.grantee_principal_id = @RoleId)
AND (
		p.state = 'G'
		OR
		p.state = 'W'
	)
ORDER BY
	s.name

OPEN 
	serviceCursor;

-- Fetch first row with message type permissions
FETCH NEXT FROM 
	serviceCursor
INTO
	@permission_type,
	@permission_name,
	@object_name	

-- Loop through message type permissions
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Insert permission into results
	INSERT INTO 
		@Result (Line)
	VALUES
		('GRANT ' + @permission_name + ' ON SERVICE :: [' + @object_name + '] TO [' + @Role + '];')

	-- Fetch next row
	FETCH NEXT FROM 
		serviceCursor
	INTO
		@permission_type,
		@permission_name,
		@object_name
END

-- Cleanup cursor
CLOSE 
	serviceCursor
DEALLOCATE
	serviceCursor



SELECT
	*
FROM
	@Result

Useful change to know about.

In SQL Server 2000 you needed quite some permissions (SS2000: TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.)

In SQL Server 2005 you're allowed to perform a truncate table as soon as you have 'ALTER' permissions on that table.

Microsoft Visual Studio 2005 Team Edition for Database Professionals Power Tools has been released and is available at: http://www.microsoft.com/downloads/details.aspx?FamilyID=da3f11ad-bd54-4eda-b08c-4df84df0d641&displaylang=en

The Microsoft Visual Studio 2005 Team Edition for Database Professionals Power Tools is a set of enhancements and tools that compliment and improve the user experience of Team Edition for Database Professionals.

This release includes 5 new refactoring types, a new dependency viewer, additional data generators and editors, 2 new MSBuild tasks for Schema and Data Compare as well as the introduction of the TSQL Static Code Analysis feature

TSQL Static Code Analysis
• Static Code Analysis - A precursor to the functionality that will be in future versions of VSTS that will allow you to perform Static Code Analysis on T-SQL code.

Refactoring
• “Move Schema” Refactoring - Allows a user to right click on an object and move it to a different but existing schema
• SP Rename Generation - Generate a new script that will contain sp_renames for all rename refactored objects that the user can then execute.
• Wildcard Expansion - Automatically expand the wildcard in a select to the appropriate columns.
• Fully-Qualified Name Support - Automatically inject fully-qualified names when absent in a script
• Refactoring extended to Dataset - Refactor into strongly typed dataset definitions

MSBuild Tasks
• Data / Schema Compare Build Tasks - MSBuild tasks that can generate scripts as if the user had run the Data / Schema compare UI

Schema View
• API Access to Schema View - Insert / Update / Delete to schema View and list schema objects and their associated files

Dependency Tool Window
• Dependency Tree - Show the dependencies ( incoming / outgoing ) for selected schema objects in a new tool window

Miscellaneous Tools
• Script Preprocessor - Expand SQLCMD variables and include files and command line version (sqlspp.exe) & an MSBuild version ( wraps the command line version )

Can't wait for the new DB Pro Power Tools to become available!

Microsoft® Visual Studio® 2005 Team Edition for Database Professionals Service Release 1 has been released. This is must have tool for people that develop using Visual Studio Team System!

Go to: http://www.microsoft.com/downloads/details.aspx?FamilyID=9810808c-9248-41a5-bdc1-d8210a06ed87&displaylang=en

Recently I've started using VSTS for DB Pro. I've installed CTP 2 of Service Release 1, and it's been great.

The VSTS DB Pro team plans to release power tools for VSTS DB Pro as well. These are tools that will be released as soon as possible, without having to wait for a major release.

The Power Tools are listed on the Future Release page of VSTS: http://msdn2.microsoft.com/en-us/teamsystem/bb407307.aspx

Team Edition for Database Professionals Power Tools

Feature	Description
“Move Schema” Refactoring	Allows a user to right click on an object and move it to a different but existing schema
SP Rename Generation	Generate a new script that will contain sp_renames for all rename refactored objects that the user can then execute.
Data / Schema Compare Build Tasks	MSBuild tasks that can generate scripts as if the user had run the Data / Schema compare UI
Script Preprocessor	Expand SQLCMD variables and include files and command line version (sqlspp.exe) & an MSBuild version ( wraps the command line version )
API Access to Schema View	Insert / Update / Delete to schema View and list schema objects and their associated files
Data / Schema Compare Build Tasks	MSBuild tasks that can generate scripts as if the user had run the Data / Schema compare UI
Dependency Tree	Show the dependencies ( incoming / outgoing ) for selected objects in a new tool window
Static Code Analysis	A precursor to the functionality that will be in future versions of VSTS that will allow you to perform Static Code Analysis on T-SQL code.
Wildcard Expansion	Automatically expand the wildcard in a select to the appropriate columns.
Fully-Qualified Name Support	Automatically inject fully-qualified names when absent in a script
Schema Reporting	DBSpecGen functionality for the project system allows you to easily document your database schema.

 

 

In earlier posts I already blogged about creating some simple queuing solutions with SQL Server Service Broker. Last week I spend some time actually implementing my research in a customer project and of course ran into two 'interesting' issues.

1. It is possible for the Service Broker to somehow die on you, whilst appearing to be up and running and with RETENTION=ON sent message appear to be in the queue.
   

   When using the SQL Profiler it becomes clear that messages don't get processed because the Service Broker is not enabled.
   
   You can use the following statement to enable Service Broker:
   
   alter database PFA_DATA set ENABLE_BROKER
   

   I had to do it on two machines and on of them gave me the following error:
   
   Msg 9772, Level 16, State 1, Line 2
   The Service Broker in database "PFA_DATA" cannot be enabled because there is already an enabled Service Broker with the same ID.
   Msg 5069, Level 16, State 1, Line 2
   ALTER DATABASE statement failed.
   

   So instead I used:
   
   alter database PFA_DATA set NEW_BROKER

    

   After this the Service Broker was up and running and message started getting delivered.
   
   If the query takes extremely long to complete restart SQL Server and try again.
   
   One potential cause for this is when you restore a database.
   

2. In my earlier post I just blatantly used WITH CLEANUP to avoid getting EndDialog messages.
   The documentation however states:
   
   WITH CLEANUP
   Remove all messages and catalog view entries for this side of the conversation without notifying the other side of the conversation. Microsoft SQL Server drops the conversation endpoint, all messages for the conversation in the transmission queue, and all messages for the conversation in the service queue. Use this option to remove conversations which cannot complete normally. For example, if the remote service has been permanently removed, you use WITH CLEANUP to remove conversations to that service.

   
   So use this sparingly.
   
   I've discovered a much cleaner way to handle it.
   When sending a message you can use "END CONVERSATION @dialog" to specify that as far as the sender is concerned the dialog is over. In other words: a fire and forget message.
   When the receiving side of the conversation receives the messages and performs its "END CONVERSATION" statement, Service Broker will detect that no acknowledgement (EndDialog) needs to be send.

My friends, Klaas and Hans, pointed out that there is a neat tool for viewing queues, contracts, types etc. in SQL Server 2005. It's called SSBAdmin and is written by Niels from DevelopMentor. Go to http://sqljunkies.com/WebLog/nielsb/archive/2005/12/27/17701.aspx to download the tool.

Here is the sample SQL script to create a simple queue in SQL Server 2005:

-- Needed to send messages!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';
GO

-- All messages need to be typed.
CREATE MESSAGE TYPE
[//DEVELOP-ONE.COM/schemas/Test/XmlMessage]
VALIDATION = VALID_XML;
GO

-- Create a contract
CREATE CONTRACT
[//DEVELOP-ONE.COM/schemas/Test/BasicXmlQueueContract]
( [//DEVELOP-ONE.COM/schemas/Test/XmlMessage]
SENT BY ANY
) ;
GO

-- Create a queue which is turned on and which retains
-- messages in the database.
CREATE QUEUE BasicXmlQueue
WITH STATUS=ON, RETENTION=ON;
GO

-- Create the service needed to send messages.
CREATE SERVICE [//DEVELOP-ONE.COM/Sql/Services/Test/BasicXmlService]
ON QUEUE [dbo].[BasicXmlQueue]
([//DEVELOP-ONE.COM/schemas/Test/BasicXmlQueueContract]) ;
GO

-- Create a stored procedure to send a message
CREATE PROCEDURE SendXMLMessage
@MessageContent XML
AS
BEGIN

DECLARE @dialog_handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [//DEVELOP-ONE.COM/Sql/Services/Test/BasicXmlService]
TO SERVICE '//DEVELOP-ONE.COM/Sql/Services/Test/BasicXmlService'
ON CONTRACT [//DEVELOP-ONE.COM/schemas/Test/BasicXmlQueueContract] ;


SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [//DEVELOP-ONE.COM/schemas/Test/XmlMessage]
(@MessageContent) ;

-- return dialog_handle
SELECT @dialog_handle ;

END ;
GO

CREATE PROCEDURE ReceiveXMLMessage
@Timeout int
AS
BEGIN
DECLARE @tempTable as TABLE(
conversation_handle UNIQUEIDENTIFIER,
message_body XML) ;

WAITFOR( RECEIVE TOP (1)
conversation_handle,
message_body
FROM BasicXmlQueue
INTO @tempTable ), TIMEOUT @Timeout;

DECLARE @conversation_handle as UNIQUEIDENTIFIER;

SELECT TOP (1) @conversation_handle = conversation_handle
FROM @tempTable ;

IF ( @conversation_handle IS NOT NULL )
BEGIN
-- remove message from queue without sending an End Of Conversation message.
END CONVERSATION @conversation_handle WITH CLEANUP ;
END

-- return the message body
select message_body from @tempTable;

END ;
GO

SQL Server 2005 introduces Service Broker, an extensive mechanism for supporting asynchronous messaging with SQL Server.

The very heart of Service Broker is the new SQL Server object: QUEUE. To utilize just the queue you still have to go through the service broker and do all the extensive stuff which you may or may not need.

The minimum steps you need to create a queue are:

1. Create a message type
2. Create a contract
3. Create a queue
4. Create a service

Make sure there is a master encryption key available in your database, otherwise the message won't get send.

I've created stored procedures for sending en receiving messages. Works quite well. I'll post some SQL code later.