I remember that some time ago I had the need to interact with a library written in .NET from MS SQL 2000. Creating a COM+ object using managed code was the first thing that came to my mind (I had done it before using C++). I googled it for a little bit but couldn't find the right way to get this done and since I was really in a hurry ended up creating a simple console application that was called using xp_cmdshell.

Today I had the time to revisit this issue and this time in less than 1 minute the right article came on top of my search results: Microsoft Enterprise Services allows you to create Microsoft COM+ applications using the .NET Framework.

This is the article that explains this process in details:http://support.microsoft.com/kb/306296

How to invoke COM applications from SQL?http://www.sqlservercentral.com/columnists/dasanka/callingcomfromtsql.asp

Imagine the following scenario: The table defined below is populated with "key values" available to a given application. These values are generated by other application and inserted in batches from time to time into this "key pool" table.

/* Create table */CREATE TABLE [KeyPool]([KeyPoolId] [int] IDENTITY(1,1) NOT NULL,[Key] [varchar](30) NOT NULL,[IsUsed] [bit] NOT NULLCONSTRAINT [PK_KeyPool] PRIMARY KEY CLUSTERED ([KeyPoolId] ASC))

/* Insert test values */INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00001',0)INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00002',0)INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00002',0)INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00003',0)INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00004',0)INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00005',0)INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00006',0)INSERT INTO [KeyPool] ([Key], [IsUsed]) VALUES ('KEY00007',0)

How you select a unique key from this table and also marking it as "used" so is not returned again. Given the current conditions, around 5 different processes may be requesting a value at the same time.

...