Tuesday, March 8, 2011

Create a Data Dictionary on the fly for any SQL Server 2005 or 2008 Database

I have included a quick script that creates a stored procedure that will generate a data dictionary on the fly for any SQL Server 2005 or 2008 database:

Monday, March 7, 2011

Import XML Files into SQL Server 2005 or 2008

Presented is a script that allows one to import all XML files in a specific directory into SQL Server 2005 or 2008 and store it as XML. Most of what is presented should work for importing the contents of any type of file, not just XML. The example uses OLE Automation Procedures to extract extra data that one may find of use storing along with the XML such as file creation time, size, etc.

The first step is to enable OLE Automation Procuedures in SQL Server:

USE [master];
GO

EXECUTE SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXECUTE SP_CONFIGURE 'Ole Automation Procedures', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXECUTE SP_CONFIGURE 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
GO

The next step is to create a table to hold the XML documents. This table will also allow one to version the XML documents if a document is imported with the same name as one that already exists:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[meta].[XMLSchema]') AND type in ('U'))
 DROP TABLE [meta].[XMLSchema];
GO

CREATE TABLE [meta].[XMLSchema](
  [XMLSchemaID] [int] IDENTITY (1, 1) NOT NULL
 ,[FileName] [VARCHAR](500) NOT NULL
 ,[FileVersion] AS CAST(Major AS varchar) + '.' + CAST(Minor AS varchar) + '.' + CAST(Maint AS varchar)
 ,[Major] int NOT NULL
 ,[Minor] int NOT NULL
 ,[Maint] int NOT NULL
 ,[FileType] [VARCHAR] (100) NOT NULL
 ,[FileSize] int NOT NULL
 ,[TimeCreated] [DATETIME] NOT NULL
 ,[TimeLastModified][DATETIME] NOT NULL 
 ,[XMLData] [xml] NOT NULL
 ,[EffDate] [datetime] NOT NULL
 ,[InEffDate] [datetime] NULL
 ,[CurrVersionFlag] [tinyint] NOT NULL
) ON [PRIMARY]
GO

-- Primary key
ALTER TABLE [meta].[XMLSchema]
 ADD CONSTRAINT [PK_XMLSchema] PRIMARY KEY CLUSTERED(
   [FileName] ASC
  ,[Major] ASC
  ,[Minor] ASC
  ,[Maint] ASC
 ) ON [PRIMARY];
GO

-- Defaults
ALTER TABLE [meta].[XMLSchema]
 ADD CONSTRAINT DF_XMLSchema_CurrVersionFlag
  DEFAULT 0 FOR CurrVersionFlag;
GO

Sunday, March 6, 2011

Blogger and SyntaxHighlighter Test

Just a quick test to see if Alex Gorbatchev's SyntaxHighlighter will work with my blog on Google. After futzing around with Alex's scripts I got it to work:
SELECT Col1, SUM(Col2)
FROM TEST
GROUP BY Col1; 

Tuesday, March 1, 2011

New Blog

It has been some time since I posted anything to this blog.  Since moving from Billings to Salt Lake City, I haven't had the time to get my old Wordpress server up and running.  In that light I decided to try Blogger on Google.

I have quite a few of my older posts that need to be converted and eventually they will.  I know many of you found them helpful.  It will be interesting to see how the conversion process from Wordpress to Blogger turns out.  So be patient and check back often.

Thank you for your support!