Ghazi Sarhan's Blog - SQL Server

Add an unsupported assembly to Sql Server

Ghazi Sarhan — Sat, 22 Dec 2007 04:58:45 GMT

I needed to create a CLR function to generate a report and uses ZedGraph to make a graph and save it on the disk.

I used VS 2005 to make the CLR function, but when try to add a reference to the project, all I see is few of the .Net assemblies, and there is no `Browse` button to add assemblies from the disk.

I did some google as usual and found this on Sql Server's Book Online:

Unsupported Libraries

Unsupported libraries can still be called from your managed stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. The unsupported library must first be registered in the SQL Server database, using the CREATE ASSEMBLY statement, before it can be used in your code. Any unsupported library that is registered and run on the server should be reviewed and tested for security and reliability.

And that solved the problem...

First, I'm not sure what Permission_Set ZedGrpah needs, so I'm going to mark the database as TrustWorthy so I can give the assembly an unsafe Permission_Set.

Use Master>

Alter Database Test Set TrustWorthy On>

Use Test>

Then when I added the ZedGraph I got an error message:

Create Assembly [ZedGraph]>

From 'C:\ZedGraph\ZedGraph.dll'>

With Permission_Set=Unsafe>

Msg 10301, Level 16, State 1, Line 1

Assembly 'ZedGraph' references assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

So I did as it says, I added the System.Windows.Forms first

Create Assembly [System.Windows.Forms]>

From 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'>

With Permission_Set=Unsafe>

I noticed on the object explorer that the System.Windows.Forms was added with its refereces

Now add the ZedGraph

Create Assembly [ZedGraph]>

From 'C:\ZedGraph\ZedGraph.dll'>

With Permission_Set=Unsafe>

Now back to VS 2005

And everything goes well.

Important: Always add the unsupported assemblies from a query window, using CREATE ASSEMBLY command, do not add them using the Object Explorer, I tried it and it always gives error.

Created DateTime & Modified DateTime Columns

Ghazi Sarhan — Sun, 16 Dec 2007 19:22:07 GMT

Sometimes you need to add a CreatedDateTime and ModifiedDateTime columns to your table, here is an easy way to do it...

First create a test table:

Create Table Table_1 >

(

ID Int Primary Key Identity(1, 1),>

Col1 Varchar(50),>

CreatedDateTime DateTime Not Null Default Current_Timestamp,>

ModifiedDateTime DateTime Null Default Null>

)

Note that the default value for the column CreatedDateTime is Current_Timestamp.

To update the ModifiedDateTime column we need this trigger:

Create Trigger Table_1_Update On Table_1 After Update>

Update Table_1 Set ModifiedDateTime = GetDate()>

From Table_1 Inner Join Inserted>

On Table_1.ID = Inserted.ID>

Now Test:

Insert Into Table_1 (Col1) Values ('Hello');>

Select * From Table_1;>

Update Table_1 Set Col1 = 'Hi' Where ID = 1;>

Select * From Table_1;>

ID Col1 CreatedDateTime ModifiedDateTime

----------- ------------------- ----------------------- -----------------------

1 Hello 2007-12-16 22:18:39.013 NULL

ID Col1 CreatedDateTime ModifiedDateTime

----------- ------------------- ----------------------- -----------------------

1 Hi 2007-12-16 22:18:39.013 2007-12-16 22:18:39.013> >

Disable/Re-enable Trigger

Ghazi Sarhan — Sun, 16 Dec 2007 18:43:18 GMT

Sometimes you need to temporary disable a trigger, without dropping it, the following commands do this...

To disable a trigger:

Disable Trigger Table_1_Trigger On Table_1>

Alter Table Table_1 Disable Trigger Table_1_Trigger>

To Re-enable it:

Enable Trigger Table_1_Trigger On Table_1>

Alter Table Table_1 Enable Trigger Table_1_Trigger>

Asynchronous Trigger

Ghazi Sarhan — Sun, 16 Dec 2007 17:01:14 GMT

I developed a small application for my office, in this application I used a trigger to read data from a web service, then update the table after the record is inserted.

Everything was working fine, except... the web service sometimes takes time to come with the result, what keeps the user waits for a long time before he gets a confirmation that the record was saved.

I thought about an asynchronous trigger, I did some google, and found that asynchronous triggers can be done with Sql Server's Service Broker, little bit from here and there, I built the following as a template for my asynchronous triggers:

USE master >

-- Check if database exists and drop it

IF EXISTS(SELECT * FROM sys.databases where name = 'AsyncTrigger') >

DROP DATABASE AsyncTrigger

-- Create the database

CREATE DATABASE AsyncTrigger >

-- Enable Service Broker

ALTER DATABASE AsyncTrigger >

SET ENABLE_BROKER >

USE AsyncTrigger >

-- Create our test table

CREATE TABLE Table_1( >

[id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,

[Col1] [int] NOT NULL,

[Col2] [int] NOT NULL,

[Sum] [int] NULL)

-- This procedure will run asynchronously and execute our task

CREATE PROCEDURE usp_Processor >

AS >

BEGIN >

SET NOCOUNT ON; >

DECLARE @Handle UNIQUEIDENTIFIER; >

DECLARE @MessageType SYSNAME; >

DECLARE @Message XML; >

RECEIVE TOP (1) >

@Handle = conversation_handle,

@MessageType = message_type_name,

@Message = message_body

FROM [MyQ]; >

IF (@Handle IS NOT NULL AND @Message IS NOT NULL) >

BEGIN >

DECLARE @tmpT Table(id int, Col1 int, Col2 int, [Sum] int); >

insert into @tmpT SELECT >

Table1.Column1.value('@ID', 'INT') as id,

Table1.Column1.value('@Col1', 'INT')as col1,

Table1.Column1.value('@Col2', 'INT') as col2,

Table1.Column1.value('@Sum', 'INT') as [sum]

FROM @Message.nodes('/row') AS Table1(Column1) >

Update Table_1 set [Sum] = tmpT.Col1 + tmpT.Col2 >

from @tmpT as tmpT Inner Join Table_1 as T >

on tmpT.id = T.id; >

END -- End If >

END -- End Procedure usp_Processor >

-- Create Message Type

CREATE MESSAGE TYPE [MyMessage] VALIDATION = WELL_FORMED_XML; >

-- Create Contract

CREATE CONTRACT [MyContract] ([MyMessage] SENT BY ANY); >

-- Create Queue

CREATE QUEUE [MyQ] WITH ACTIVATION >

( STATUS = ON, MAX_QUEUE_READERS = 1, >

PROCEDURE_NAME = usp_Processor,

EXECUTE AS OWNER); >

-- Create Service

CREATE SERVICE [MyService] ON QUEUE [MyQ] ([MyContract]); >

-- This trigger will send our message to the queue

CREATE TRIGGER Table_1_Insert ON Table_1 AFTER INSERT >

AS >

BEGIN >

SET NOCOUNT ON; >

DECLARE @MessageBody XML; >

set @MessageBody = (SELECT ID, Col1, Col2, [Sum] FROM Inserted FOR XML RAW, TYPE); >

DECLARE @Handle UNIQUEIDENTIFIER; >

BEGIN DIALOG CONVERSATION @Handle >

FROM SERVICE [MyService] >

TO SERVICE 'MyService' >

ON CONTRACT [MyContract] >

WITH ENCRYPTION = OFF; >

SEND ON CONVERSATION @Handle >

MESSAGE TYPE [MyMessage](@MessageBody); >

END >

Now execute the following:

Insert Into Table_1 (Col1, Col2) Values (34, 78) >

Select * From Table_1 >

Waitfor Delay '00:00:03' >

Select * From Table_1 >

The result will be:

id Col1 Col2 Sum
----------- ----------- ----------- -----------
1 34 78 NULL

id Col1 Col2 Sum
----------- ----------- ----------- -----------
1 34 78 112

Select... with optional parameters

Ghazi Sarhan — Sun, 16 Dec 2007 16:37:37 GMT

I was working on a simple Address Book for my office using Sql Server and ASP.NET.

I created the table that has the fields: FirstName, LastName, Code, StaffNo and Phone.

I prepaired the web page with five TextBoxes and a GridView.

Now... writing the code...

I want when the user enters the FirstName, he gets all matching records for this FirstName... when he enters FirstName and LastName he gets all records with the matching FirstName and LastName...

This means that if a field was not supplied, it shouldn't be included in the Where clause...

This means that I will need to customize the search on the web page to check the supplied fields, build the SQL Command dynamically then run the query... !@#$%^&*

That didn't sound profissional...

I did some Google until I found this...

Select FirstName, LastName, Code, StaffNo, Phone From AddressBook Where

((@FirstName is null) or (@FirstName = FirstName)) and

((@LastName is null) or (@LastName = LastName)) and

((@Code is null) or (@Code = Code)) and

((@StaffNo is null) or (@StaffNo = StaffNo)) and

((@Phone is null) or (@Phone = Phone))

The where clause checks each parameter to see if it is NULL which means we don't want to filter our results by that parameter. If the parameter is not null it then checks the fields value with the parameter.