Introduction
This article will demonstrate how we can integrate T-SQL with an external application.
Background
When we need to store or retrieve data in SQL server database, we will go for T-SQL. But the thing is how can we integrate T-SQL with an external application? For example, we want to integrate SQL server with Microsoft Word, Excel or Microsoft exchange server as well. So how can we achieve this?
Using the Code
There are seven extended stored procedures to call external applications like DLLs. The name and description of those stored procedures are given below:
Stored Procedure | Description |
sp_OACreate | Creates an instance of the OLE object on an instance of Microsoft SQL Server |
sp_OADestroy | Destroys a created OLE object |
sp_OAGetErrorInfo | Obtains OLE Automation error information |
sp_OAGetProperty | Gets a property value of an OLE object |
sp_OASetProperty | Sets a property of an OLE object to a new value |
sp_OAMethod | Calls a method of an OLE object |
sp_OAStop | Stops the server-wide OLE Automation stored procedure execution environment |
Reference: SQL Server Books Online.
Let’s take a simple example to implement this. Our requirement will be accessing a third party DLL from a SQL server stored procedure.
The following are the two functions which are developed in C# .NET.
Function (a) getOSPlatform
will return the System platform and the function (b) writeToFile
will write text into a text file called CLRTempE.txt with the value given by the parameter.
Sample COM+ Code (C#. NET)
public string getOSPlatform()
{
return Convert.ToString(System.Environment.OSVersion.Platform);
}
public int writeToFile(
string strPath
, string strText)
{
int intResult = -1;
try
{
if (strPath != "")
{
if (System.IO.File.Exists(strPath))
{
FileStream stream = new FileStream(
strPath
, FileMode.Open
, FileAccess.Write);
StreamWriter writer = new StreamWriter(stream);
writer.BaseStream.Seek(0, SeekOrigin.End);
writer.Write("TimeStamp: [ "
+ Convert.ToString(System.DateTime.Now)
+ " ] "
+ strText
+ " || (:-> || Info ! [Successfully inserted.]");
writer.Write(Environment.NewLine);
writer.Flush();
writer.Close();
}
else
{
FileStream stream = new FileStream(
strPath
, FileMode.CreateNew
, FileAccess.Write);
StreamWriter writer = new StreamWriter(stream);
writer.BaseStream.Seek(0, SeekOrigin.End);
writer.Write("TimeStamp: [ "
+ Convert.ToString(System.DateTime.Now)
+ " ] "
+ strText
+ " || (:-> || Info ! [Successfully inserted.]");
writer.Write(Environment.NewLine);
writer.Flush();
writer.Close();
}
intResult = 0;
}
}
catch (Exception ex)
{ throw ex; }
return intResult;
}
Sample SQL Stored Procedure
CREATE PROCEDURE dbo.spCLRExample
AS
BEGIN
DECLARE @intResult INT
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @inputText VARCHAR(500)
DECLARE @OSPlatform VARCHAR(500)
DECLARE @isSuccess INT
set @inputText = 'This is a simple text.'
EXEC @intResult = sp_OACreate 'CLRExample.Example', @comHandle OUTPUT, 4
IF (@intResult <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, _
@errorDescription OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
EXEC @intResult = sp_OAMethod @comHandle, 'getOSPlatform',@OSPlatform OUTPUT
Print @OSPlatform
IF (@intResult <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, _
@errorDescription OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
EXEC @intResult = sp_OAMethod @comHandle, _
'writeToFile',@isSuccess OUTPUT, 'C:\CLRTempE.txt', @inputText
IF (@intResult <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @comHandle, _
@errorSource OUTPUT, @errorDescription OUTPUT
SELECT [Error Source] = @errorSource, [Description] = @errorDescription
RETURN
END
EXEC sp_OADestroy @comHandle
END
GO
Now we are going to discuss how the above code works. sp_OACreate
will create an instance of the DLL to SQL Server. If it is a successfully created output, that will be 0. If it is non-zero, then send it to sp_OAGetErrorInfo
and get the error messages. sp_OAMethod
is to call the method of the DLL. In the first case, we are calling the method getOSPlatform
which only has a returned value. The last parameter of sp_OACreate
must be either of 1, 4 or 5. It specifies the execution context in which the newly created OLE object runs. If specified, this value must be one of the following:
- 1 = In-process (.dll) OLE server only
- 4 = Local (.exe) OLE server only
- 5 = Both in-process and local OLE server allowed
If not specified, the default value is 5.
If an in-process OLE server is allowed (by using a context value of 1 or 5 or by not specifying a context value), it has access to memory and other resources owned by SQL Server. An in-process OLE server may damage SQL Server memory or resources and cause unpredictable results, such as a SQL Server access violation.
When we specify a context value of 4, a local OLE server does not have access to any SQL Server resources, and it cannot damage SQL Server memory or resources. Finally sp_OADestroy
will destroy a created OLE object in the SQL Server.
Reference SQL Server Books Online.
Points of Interest
When registering the COM+, we must use the following command by using Visual Studio command prompt.
Commands:
C:\Program Files\Microsoft Visual Studio 9.0\VC> RegAsm CLRExample.dll \ tlb: CLRExample.tlb \ codebase
Note: Do not use regsvr32 for registering the COM+.
Conclusion
I hope that this article will be helpful to you. Enjoy!
History
- 15th June, 2009: Initial post