In yesterday’s posting the BDC was used to present data from both Project Server and the AdventureWorks database with the join being the Vendor ID from the Purchasing.Vendors table. To allow this to happen we need a custom field and lookup table in Project Server that has the values for the vendors so that we can make the association. One way to do this (and there are many) is to use a CLR trigger – and that is another part of Christophe and my presentation next Thursday at TechEd.
SQL Server 2005 introduced the capability to use Common Language Runtime procedures within database entities. CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. This example uses C# within a trigger to make PSI calls and insert values to a Project Server lookup table. As with any programming task, just because you can do something a particular way does not necessarily mean you should, and you certainly wouldn’t want to use the code here to make very frequent updates between systems – but for occasional updates – such as adding a new vendor in a small application it may fit the bill.
There are a number of hoops to jump through to get CLR integration running with SQL Server – and as it does open up some security concerns you will need to fully understand the consequences of these changes before making them. They are full described, and some alternative options presented, in the SQL Server Books Online starting with the Introduction linked above and specifically dealing with security considerations here. Please read these articles – then the following steps will make more sense.
The first step is to set the database instance to CLR enabled – and this uses sp_configure:-
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Next is to give the dbo UNSAFE ASSEMBLY permissions:-
use master
GRANT UNSAFE ASSEMBLY To "domain\user"
go
and then set the database property TRUSTWORTHY to true
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
an alternative approach here is to sign the assembly with an asymmetric key or cert that has a LOGIN with UNSAFE ASSEMBLY permission.
When using calls to Web Services, as we will be with the PSI, you also need to deploy the serialization assembly – so this involves setting the option to Generate serialization assembly and then configuring PostDeployScript.sql and PreDeployScript.sql to make the deployment.
PreDeployScript.sql
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'AWTrigger.XmlSerializers')
DROP ASSEMBLY [AWTrigger.XmlSerializers]
PostDeployScript.sql
CREATE ASSEMBLY [AWTrigger.XmlSerializers] from
'C:\TechEd\LOB\Brian\AWTrigger\bin\Debug\AWTrigger.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE
Finally piece of setup in this example is that I created a new table in AdventureWorks and then used just a normal SQL trigger to copy data from the Vendor table to my table. This was a workaround for what I later found was a bug with VS 2008 in that it cannot deploy automatically to entities in schema other than dbo – so the Purchasing.Vendor table would take the trigger directly. I could have manually deployed to work around this – but as I already had the workaround working I haven’t done this. For completeness this is the SQL to create my extra table called AWTrigger, and is followed by the SQL to add the SQL trigger to the Purchasing.Vendor table.
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[AWTrigger] Script Date: 05/20/2008 12:36:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AWTrigger](
[VendorID] [int] NULL,
[VendorName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
USE [AdventureWorks]
GO
/****** Object: Trigger [Purchasing].[copyToAWTrigger] Script Date: 05/20/2008 12:36:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [Purchasing].[copyToAWTrigger]
ON [Purchasing].[Vendor]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @vendorid int;
declare @vendorname varchar(50);
select @vendorid=i.VendorID from inserted i;
select @vendorname=i.Name from inserted i;
insert into AWTrigger (VendorID, VendorName) values(@vendorid, @vendorname)
END
Now to the real code! In VS 2008 you can create a project of type SQL-CLR
and you will set up a database connection. In this solution I also have web references added for the LoginWindows and LookupTable web services.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Net;
using System.Security.Principal;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
// Using another table rather than messing with the Vendor table to work around known bug with triggers in other schema
// Could have deployed trigger manually.
[Microsoft.SqlServer.Server.SqlTrigger(Name = "LUTTrigger", Target = "AWTrigger", Event = "FOR INSERT")]
public static void LUTTrigger()
{
#region Setup
const string PSI_URI = "http://W2K3/pwa/_vti_bin/psi/"; // <<--Change to match your project server and directory.
const string LOBI_NAME = "AW - LUTTrigger";
WindowsIdentity winID = SqlContext.WindowsIdentity;
// Set up the services.
AWTrigger.wsLoginWindows.LoginWindows loginWindows = new AWTrigger.wsLoginWindows.LoginWindows();
AWTrigger.wsLookupTable.LookupTable lookupTable = new AWTrigger.wsLookupTable.LookupTable();
loginWindows.Url = PSI_URI + "loginWindows.asmx";
lookupTable.Url = PSI_URI + "lookuptable.asmx";
loginWindows.UseDefaultCredentials = true;
lookupTable.UseDefaultCredentials = true;
CookieContainer cookies = new CookieContainer();
loginWindows.CookieContainer = cookies;
AWTrigger.wsLookupTable.LookupTableDataSet dsLookupTable;
// Guid for AW-Vendor Lookup Table set and then put into array
Guid lutGuid = new Guid("E6342609-6A5D-4D05-94D1-519D0901F566");
// You will need to change the UID used here – and good practice would be to get this from a .config file.
Guid[] lutList = new Guid[] { lutGuid };
bool autoCheckOut = false;
string msg = string.Empty;
#endregion
try
{
// Attempt to log in using Windows Credentials
if (!loginWindows.Login())
{
msg = string.Format("{0}: User {1} CANNOT login using Windows Credentials: {2}"
, LOBI_NAME, winID.Name, loginWindows.Url);
WriteEvent(msg, EventLogEntryType.FailureAudit, 8989);
return;
}
// Get the VendorID and Name from the inserted row
int vendorID;
string vendorName;
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
reader = command.ExecuteReader();
reader.Read();
vendorID = (int)reader[0];
vendorName = (string)reader[1];
reader.Close();
} // Get a dataset for our vendor lookup table and add out row – then update
dsLookupTable = lookupTable.ReadLookupTablesByUids(lutList, autoCheckOut, 1033);
AWTrigger.wsLookupTable.LookupTableDataSet.LookupTableTreesRow rowLookupTableTree =
dsLookupTable.LookupTableTrees.NewLookupTableTreesRow();
Guid lt_struct_uid = Guid.NewGuid();
rowLookupTableTree.LT_STRUCT_UID = lt_struct_uid;
rowLookupTableTree.LT_UID = lutGuid;
rowLookupTableTree.LT_VALUE_NUM = (decimal)vendorID;
rowLookupTableTree.LT_VALUE_DESC = vendorName;
dsLookupTable.LookupTableTrees.AddLookupTableTreesRow(rowLookupTableTree);
lookupTable.CheckOutLookupTables(lutList);
bool validateOnly = false;
lookupTable.UpdateLookupTables(dsLookupTable, validateOnly, autoCheckOut, 1033);
bool forceCheckIn = false;
lookupTable.CheckInLookupTables(lutList, forceCheckIn);
}
catch(Exception ex)
{
msg = string.Format("{0}: EXCEPTION {1}", LOBI_NAME, ex.Message);
WriteEvent(msg, EventLogEntryType.Error, 8989);
}
}
#region Helper Methods
private static void WriteEvent(string msg, EventLogEntryType logEntryType, int eventId)
{
EventLog myLog = new EventLog();
myLog.Source = "Adventure Works LOBI";
string message = msg;
myLog.WriteEntry(msg, logEntryType, eventId);
}
#endregion
}
The code isn’t totally robust, and will fail if the lookup table is checked out – and the code that Christophe posted earlier this week on controlling lookup table updates helps with that. It controls who can check out that particular lookup table. In triggers it is also really important to handle exceptions as you always want the trigger to “work” even when it fails. Try/Catch and writing errors out to the event log in this case ensures the trigger works and doesn’t undo any of the changes to the original vendor table.
The test.sql script is used for debug – by actually making an insert that will fire the trigger – thanks Christophe for adding this piece and also the better exception and error handling.
DECLARE @AcctNumber nvarchar(10)
SET @AcctNumber=ROUND(RAND()*1000,0)
INSERT INTO Purchasing.Vendor (AccountNumber, Name, CreditRating, PreferredVendorStatus, ActiveFlag, ModifiedDate)
VALUES (@AcctNumber, 'Vendor '+@AcctNumber, 1, 1, 1, GETDATE())
As I was working on this I made good use of Live Search – so thanks to Vineet on the SQL Server team for the very helpful blog posting as well as excellent content from the following KB and TechNet articles.
http://support.microsoft.com/kb/913668
CLR Triggers
http://technet.microsoft.com/en-us/library/ms131093.aspx
Building Database Objects with Common Language Runtime (CLR) Integration
http://technet.microsoft.com/en-us/library/ms131046.aspx
Getting Started with CLR Integration
http://technet.microsoft.com/en-us/library/ms131052.aspx
Enabling CLR Integration
http://technet.microsoft.com/en-us/library/ms131048.aspx
Part 3 will probably be posted early next week – and will include a server side event handler that uses the OnPublished event to query a project custom field for the type of Project Workspace you would like created for that project. This will be part of my chalk talk session with Boris Scholl on Wednesday morning on customizing the UI in Project Server 2007.