Jim McLeod

There are a few examples of DDL triggers available on the web, which use XML to retrieve the EventData() of the trigger.  EventData() retrieves an XML object containing properties about the event.  For example, Books Online describes ALTER TABLE event as:

<EVENT_INSTANCE>
    <EventType>type</EventType>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name</ServerName>
    <LoginName>name</LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>
    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>
    <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

By using XML query methods, we can extract data out of this XML structure.  For example:

DECLARE @eventdata xml
SELECT @eventdata = EventData()
SELECT @eventdata.value('(/EVENT_INSTANCE/EventType)[1]'
    , 'nvarchar(100)')

This works well in most cases, but there is one little gotcha that occasionally springs up.  To use the XML methods, you need to have a specific set of SET options applied to your session.  If you have a DDL trigger that uses XML methods to parse out the EventData, and you have incompatible SET options set in your session, your trigger will generate an error, and rollback the entire transaction.

Now, although you can change set options inside a trigger, and they will automatically roll back to their previous values once the trigger ends, you may not be aware that you need to do this, and once you deploy the trigger into production, you start getting errors from some clients.  The easiest way around this is to not use the XML methods in your trigger, and just retrieve EventData() and store it in an XML column.  You can then use a separate process which has the correct session options set to process the XML in greater detail.

The alternative option is to ensure you set the set options correctly in your trigger.  The correct settings to use are:

SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET NUMERIC_ROUNDABORT OFF
SET ANSI_WARNINGS ON

If you change any of these and attempt to run an XML method, you’ll get an error message telling you which option needs changing:

DECLARE @xml xml
SET @xml = '<Root><Product ID="1" Name="Widget, 25cm">
</Product></Root>'
SELECT @xml.query('/Root/Product')

Msg 1934, Level 16, State 1, Line 4
SELECT failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or
query notifications and/or xml data type methods.

Applying a DDL trigger into production without first checking that the session values are compatible could be disastrous!

Special thanks to Jon Kehayias for technical reviewing of this post.

A question that seems to come up often is how to attach a data file (.MDF) without a transaction log?  If a database has been detached from SQL Server cleanly, then there should be no information inside the transaction log of any use.  If the database was not detached cleanly (for example, if the machine crashed, and SQL Server has not been restarted, or if a copy of the file is made when SQL Server is still running) then the database may be in an inconsistent state, requiring the information currently contained inside the transaction log file.

Assuming that the database was shut down cleanly, the transaction log file can be recreated.  There are two methods: sp_attach_single_file_db and CREATE DATABASE.

The sp_attach_single_file_db is a shorthand way of calling CREATE DATABASE FOR ATTACH.  You can see exactly what sp_attach_single_file_db does by running “sp_helptext sp_attach_single_file_db” in Management Studio.  The main restriction for FOR ATTACH when used in this fashion is that only one transaction log can be created.  The new transaction log will be created in a default location, or if the database is read only, in the original location specified in the .MDF file.  The basic syntax is:

EXEC sp_attach_single_file_db @dbname='MyDatabase', 
    @physname='E:\Database\MyDatabase.mdf'
 

If you need two transaction log files recreated, you need to use CREATE DATABASE FOR ATTACH_REBUILD_LOG.

CREATE DATABASE MyDatabase ON 
    (FILENAME = 'E:\Database\MyDatabase.mdf') 
FOR ATTACH_REBUILD_LOG

If possible, I recommend taking backups and then restoring the backup, rather than try to attach a .MDF file.  It’s a safer and more reliable method of moving a database from server to server.

I was posed a question the other day – “Can query plan guides help when an index is explicitly chosen via a hint in the query?”.  The short answer is yes, but let’s have a look at the process of creating a plan guide.

First, we need to create a situation where there’s two indexes that could be used.  I’ve chosen to use the Production.Product table in the AdventureWorks database, and created an index  on ProductID and Name:

CREATE NONCLUSTERED INDEX ncix_ProductID_Name ON Production.Product (ProductID, Name)

 

If we then execute the following query, the new index will be used:

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID BETWEEN 300 AND 320

 

However, if we use an index hint, we can force the query to use the clustered index:

SELECT ProductID, Name
FROM Production.Product WITH (INDEX = PK_Product_ProductID)
WHERE ProductID BETWEEN 300 AND 320

 

Now, let’s create a query plan that will always use the non-clustered index, regardless of whether the index hint says to use the clustered index or not.  First, we need to create a Profiler trace to capture the XML Show Plan event, and we run the query, forcing the non-clustered index.  This results in an XML plan of, which we then create a Plan Guide with:

EXEC sp_create_plan_guide
    @name = N'TestPlanGuide1',
    @stmt = N'SELECT ProductID, Name
FROM Production.Product WITH (INDEX = PK_Product_ProductID)
WHERE ProductID BETWEEN 300 AND 320',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION(USE PLAN N''<ShowPlanXML ...</ShowPlanXML>'')'

 

This doesn’t quite fit on the screen nicely (and temporarily removed as it doesn’t play nice with IE6), but you get the idea.  Now, we can run the query above, providing a WITH (INDEX = PK_Product_ProductID) hint, and we can see that the hint has been ignored in favour of the plan guide.

SELECT ProductID, Name
FROM Production.Product WITH (INDEX = PK_Product_ProductID)
WHERE ProductID BETWEEN 300 AND 320

 

Finally, drop the plan guide using this command:

sp_control_plan_guide N'DROP', N'TestPlanGuide1'

 

So, even if you have queries that you can’t modify that have index hints, you can still add in a plan guide to force the plan you require.

I need to import Excel spreadsheets into SQL Server 2005 every few months, and I always end up banging my head on the same problem.  At least I know what to look for now!

Using the Import Data Wizard in Management Studio, is my preferred method of getting data from spreadsheet format into the database, and from there I can massage the data to load it into the necessary tables. The issue is that when you import a column that contains data that could be read as a number or text, for example a classification column containing values like 150, 160, K20, 146, 12G, Excel’s ISAM driver will take a guess as to what type of data will be going into this column, usually based on the first 8 rows.  If it determines that it’s text, you’ll get an nvarchar(255), and if it’s a number you’ll get a float.  However, if it decides that a nvarchar is needed, then all numbers will be imported as null.  Of course, it’s the same if a float is used.  If all 8 rows have a value of NULL, then the entire column will always be assumed to be NULL.

There’s a few ways around this.  The first is to separate your data so that all your “float” values are in one spreadsheet, and all your “nvarchars” are in another.  This isn’t a very attractive idea as it essentially doubles the work.

Another option is to modify the source document to ensure that text data is used for every cell, but just formatting the cells as text isn’t sufficient.  You have to reapply the data for each cell individually.  Needless to say, I didn’t go down this route, but it’s detailed in a link below.

The solution was to put IMEX=1 into the connection string.  This puts the Excel ISAM driver into “Import” mode, and all values come in correctly.  The main drawback to this method is that you can’t modify the connection string used in the Management Studio Import Data Wizard, and so you have to save it as an SSIS package and then make the change. “IMEX=1″ comes just after HDR, and in the same quotes:  “Excel 8.0;HDR=NO;IMEX=1;”. It’s important to get this right or you won’t be able to connect.  Note that even with IMEX=1, you still want to have data that isn’t just numeric in your first 8 rows. More information is available at http://support.microsoft.com/kb/194124/EN-US/.

A good overview on importing data from Excel to SQL Server with a variety of techniques is available at http://support.microsoft.com/kb/321686.