|
|
Is it possible to catch this exception in the trigger? I tried with a simple try-catch, but does not work. And I tried to subscribe to the AppDomain.CurrentDomain.UnhandledException event, but no change
|
|
|
|
|
I wanted to pass a parameter to the service and have it printed out in a console window.
These are the changes, I can't figure out why the parameter is not being printed out.
1. Changed the operation contract to:
[ServiceContract]
interface IServiceContract
{
[OperationContract]
void UpdateOccured();
[OperationContract]
void InsertOccured(string fileName);
}
2. Changed the implementation to:
public void InsertOccured(string fileName)
{
Console.WriteLine("Insert Occured. File name returned: " + fileName);
}
3. Changed SendData to accept fileName as parameter. Please not that since I'm only responding to INSERT then the meaning of the original string paramter also changed. I basically, only call myclient.InsertOccured(fileName")
public static void SendData(string fileName)
4. In the trigger itself I query the INSERT table for the file name
SqlCommand command = new SqlCommand("SELECT fileName FROM INSERTED");
[...DB access code, which executes ok and then call SendData()...].SendData(fileName);
Everything is fine except that the WCF serive does not output the file name - NULL is passed instead of fileName. I can quarantee that myClient.InsertOccured(fileName) has been called with correct fileName.
|
|
|
|
|
Hi,
From what you've shown me it looks like it should work...you've not changed a great deal so there is no reason why it shouldn't be working.
I don't really know what to suggest without looking at your code but from what you've said, if I was you, I'd start eliminating points of failure. For example, don't query that file name from a table, stick a simple string in. Stick some pipe.sends in to trace out the path of execution for trigger and get it to spit out useful variables etc. Consume the service from a different client to rule out sql server being an arse etc etc
The real world implementation of this did pass parameters from the db to the service so I know it is feasible. I think I might still have that code of a cd somewhere if you can't get any further with the above suggestions/
It sounds like you've probably already done most of this but without looking at your code (and because its been so long since I wrote this article) I can't be any more helpful.
Cheers
|
|
|
|
|
Sam,
In addition to what I have already included in my previous post here is what I have in Trigg1.cs right now. You can see some Pipe.Send() debug statements which work as expected. Everything is predictable and works fine exept the output in a concole window reads "Insert Occured. File name returned: " instead of "Insert Occured. File name returned: Test.xml". I tried calling myClient.InsertOccured(fileName) from Trigger1, then did the same via SendData(), then even hardcoded the value. Result is still the same - no fileName passed. It's getting lost somewhere.
In my Sql management window after the first insert I can see this (which is correct):
no guid returned
(1 row(s) affected)
After I do the second insert (including reference quid) I also see what I expected:
49627BCA-2243-4A7D-8844-12B89E031234
Is guid, ok
Reader is not null, ok
File name: Test.xml
(1 row(s) affected)
Here is the code:
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.ServiceModel;
public class Triggers
{
public static EndpointAddress endpoint = new EndpointAddress(new Uri("http://localhost:8000/services/myservice"));
public static WSHttpBinding httpBinding = new WSHttpBinding();
public static ServiceClient.localhost.ServiceContractClient myClient = new ServiceClient.localhost.ServiceContractClient(httpBinding, endpoint);
public delegate void MyDelagate(string fileName);
[SqlProcedure]
public static void SendData(string fileName)
{
myClient.InsertOccured(fileName);
SqlContext.Pipe.Send("in send Data SP. File name: " + fileName);
}
[SqlTrigger(Name = "WCFTrigger", Target = "tblMessageInfo", Event = "FOR INSERT")]
public static void Trigger1()
{
SqlTriggerContext myContext = SqlContext.TriggerContext;
MyDelagate d;
if (myContext.TriggerAction != TriggerAction.Insert) return;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
SqlCommand command = new SqlCommand("SELECT RequestedGuid FROM INSERTED");
connection.Open();
command.Connection = connection;
string guid = null;
using (IDataReader reader = command.ExecuteReader())
{
if (reader == null) return;
while (reader.Read())
{
guid = reader[0].ToString();
}
}
if (string.IsNullOrEmpty(guid)) SqlContext.Pipe.Send("no guid returned");
else SqlContext.Pipe.Send(guid);
if (string.IsNullOrEmpty(guid) || guid.Length != 36) return;
SqlContext.Pipe.Send("Is guid, ok");
command.CommandText =
"SELECT mia.OriginalIdentifier " +
"FROM dbo.tblMessageInfo mi " +
"JOIN dbo.tblMessageInfo mia ON mi.RequestedGuid = mia.TransactionGuid WHERE mi.RequestedGuid = " +
"'" + guid + "'";
string fileName = null;
using (IDataReader reader = command.ExecuteReader())
{
if (reader == null) return;
SqlContext.Pipe.Send("Reader is not null, ok");
while (reader.Read())
{
fileName = reader[0].ToString();
SqlContext.Pipe.Send("File name: " + fileName);
}
}
SendData(fileName);
}
}
}
|
|
|
|
|
Hi Mate,
I've dug up the old production code that I used to do something very similar to what you're trying to achieve. Two things strike me as different. First is that I'm invoking the SendData procedure via an async delegate call. Try this, I can see you're just calling SendData directly. Secondly, I can't think of any good reason I'm doing this unless is was required, is that I'm passing the parameter as an object array; try changing your contract to do this. Let me know how you get on.
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.ServiceModel.Description;
using System.ServiceModel;
using System.Collections;
using System.Diagnostics;
using System.Threading;
public partial class Triggers
{
public static int i;
public static EndpointAddress endpoint = new EndpointAddress(new Uri("http://localhost:8005/services/myservice"));
public static BasicHttpBinding httpBinding = new BasicHttpBinding();
public static ServiceClient.localhost.ServiceContractClient myClient = new ServiceClient.localhost.ServiceContractClient(httpBinding, endpoint);
public delegate void MyDelagate(string tableName, String crudType, String ColName, String RecordID, String Value);
public static SqlPipe pipe;
[SqlProcedure()]
public static void SendData(string tableName, String crudType, String ColName, String RecordID, String Value)
{
object[] c = new object[1];
object[] r = new object[1];
object[] v = new object[1];
c[0] = ColName; ;
r[0] = RecordID;
v[0] = Value;
switch (crudType)
{
case "Update":
myClient.UpdateOccured(tableName, c, r, v);
break;
case "Insert":
myClient.InsertOccured(tableName, c,r,v);
break;
}
}
[Microsoft.SqlServer.Server.SqlTrigger(Name = "CallTrigger", Target = "tbcall", Event = "FOR UPDATE, INSERT")]
public static void CallTrigger()
{
const string tableName = "tbCall";
SqlTriggerContext myContext = SqlContext.TriggerContext;
pipe = SqlContext.Pipe;
SqlConnection connection = new SqlConnection(@"context connection=true");
connection.Open();
SqlCommand command = new SqlCommand();
SqlDataReader reader;
command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
reader = command.ExecuteReader();
DataTable InsertedTable = new DataTable();
InsertedTable.Load(reader);
reader.Close();
command = new SqlCommand(@"SELECT * FROM DELETED;", connection);
reader = command.ExecuteReader();
DataTable DeletedTable = new DataTable();
DeletedTable.Load(reader);
reader.Close();
switch (myContext.TriggerAction)
{
case TriggerAction.Update:
pipe.Send("in the switch");
for (int RowNumber = 0; RowNumber < InsertedTable.Rows.Count; RowNumber++)
{
for (int ColumnNumber = 0; ColumnNumber < InsertedTable.Columns.Count; ColumnNumber++)
{
pipe.Send(InsertedTable.Columns[ColumnNumber].ColumnName + " = " + Convert.ToString(myContext.IsUpdatedColumn(ColumnNumber)));
if (myContext.IsUpdatedColumn(ColumnNumber) == true)
{
if (DeletedTable.Rows[RowNumber][ColumnNumber].Equals(InsertedTable.Rows[RowNumber][ColumnNumber]) == false)
{
pipe.Send(InsertedTable.Columns[ColumnNumber].ColumnName);
MyDelagate d = new MyDelagate(SendData);
pipe.Send("TableName: " + InsertedTable.TableName);
d.BeginInvoke(tableName, "Update", Convert.ToString(InsertedTable.Columns[ColumnNumber].ColumnName), Convert.ToString(InsertedTable.Rows[RowNumber][0]), Convert.ToString(InsertedTable.Rows[RowNumber][ColumnNumber]), null, null);
}
}
}
}
break;
case TriggerAction.Insert:
for (int RowNumber = 0; RowNumber < InsertedTable.Rows.Count; RowNumber++)
{
for (int ColumnNumber = 0; ColumnNumber < InsertedTable.Columns.Count; ColumnNumber++)
{
pipe.Send("inside insert");
pipe.Send("Insert " + Convert.ToString(InsertedTable.Columns[ColumnNumber].ColumnName) + Convert.ToString(InsertedTable.Rows[RowNumber][0]) + Convert.ToString(InsertedTable.Rows[RowNumber][ColumnNumber]));
MyDelagate d = new MyDelagate(SendData);
pipe.Send("TableName: " + InsertedTable.TableName);
d.BeginInvoke(tableName,"Insert", Convert.ToString(InsertedTable.Columns[ColumnNumber].ColumnName), Convert.ToString(InsertedTable.Rows[RowNumber][0]), Convert.ToString(InsertedTable.Rows[RowNumber][ColumnNumber]), null, null);
}
}
break;
}
}
}
|
|
|
|
|
Sam,
I found the reason why this was happening. In the ServiceClient project, under Service References, localhost.map->localhost.cs there is this:
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.ServiceContractAttribute(ConfigurationName="ServiceClient.localhost.IServiceContract")]
public interface IServiceContract
{
[System.ServiceModel.OperationContractAttribute(Action="http://tempuri.org/IServiceContract/UpdateOccured", ReplyAction="http://tempuri.org/IServiceContract/UpdateOccuredResponse")]
void UpdateOccured();
[System.ServiceModel.OperationContractAttribute(Action="http://tempuri.org/IServiceContract/InsertOccured", ReplyAction="http://tempuri.org/IServiceContract/InsertOccuredResponse")]
void InsertOccured();
}
and for some reason it compiled fine. I then started looking at your code, playing with different types, hard-coding and at some point it wouldn't compile complaining that there was a type mismatch or type wasn't implementing proper interface. That's how I got to that code and then changed it to
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
[System.ServiceModel.ServiceContractAttribute(ConfigurationName="ServiceClient.localhost.IServiceContract")]
public interface IServiceContract
{
[System.ServiceModel.OperationContractAttribute(Action="http://tempuri.org/IServiceContract/UpdateOccured", ReplyAction="http://tempuri.org/IServiceContract/UpdateOccuredResponse")]
void UpdateOccured();
[System.ServiceModel.OperationContractAttribute(Action="http://tempuri.org/IServiceContract/InsertOccured", ReplyAction="http://tempuri.org/IServiceContract/InsertOccuredResponse")]
void InsertOccured(string fileName);
}
On the top of this I also had to change ServiceContractClient accordingly :
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]
public class ServiceContractClient : System.ServiceModel.ClientBase<IServiceContract>, IServiceContract
{
[... some other code here ...]
public void UpdateOccured()
{
Channel.UpdateOccured();
}
public void InsertOccured(string fileName)
{
Channel.InsertOccured(fileName);
}
}
... and now I have my fileName back from the trigger. Thanks for your help!!!
|
|
|
|
|
No problem, glad you got it working. Now you've told me I can vaguley remember doing that but it was years ago!
|
|
|
|
|
This is what I see in a debugger right after the myClient.InsertOccured(fileName) call
No fileName
|
|
|
|
|
CREATE ASSEMBLY
SMDiagnostics from
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll'
with permission_set = UNSAFE
I got following Error message:
Msg 15404, Level 16, State 19, Line 1
Could not obtain information about Windows NT group/user 'domain\user', error code 0x5.
|
|
|
|
|
Due to our environment, I can't create/deploy a SQL project, so I have to do a lot of steps manually.
I'm getting an error when I run it:
"System.InvalidOperationException: Could not find default endpoint element that references contract 'MyServer.IMyServerInternal' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element."
I assume it can't find the config file (that is at the same as the dll).
Do I have to use machine.config? Try to stick the config file with the SqlServer process? (yuck)
|
|
|
|
|
Hi,
You should be able to do it all manually though it will be a little more work, still more than possible though. I don't think the particular problem you are facing is because of your lack of SQL Server project though. I suspect that you are trying to configure your WFC service through an app.config file, this will not work as once the assembly is loaded into sql server the app.config won't be accessible.
You may be able to specify in the machine.config but I'm not 100% sure and it really isn't the correct place to do it anyway. You need to specify all WCF service settings IN CODE which means adding all end points, bindings, behaviors and contracts IN CODE and not via a config file...
I believe I ran into this same issue when I first tried to invoke a wcf service from SQL CLR but it was a long time ago now so I can't be sure. Basically ignore the fact that theres an app config, do everything via c#.
Let me know if you're still having issues.
|
|
|
|
|
You are right. I could not get machine.config nor sqlserver.exec.config to work. In the end, I did it in code, and after a small amount of trial and error got it to work.
It is a surprisingly small about of code, something akin to:
System.ServiceModel.Channels.Binding binding = new NetTcpBinding( SecurityMode.None);
binding.Name = "DoStuff";
System.ServiceModel.EndpointAddress remoteAddress = new System.ServiceModel.EndpointAddress(ServerUri);
ServiceInterface si = new ServiceInterface( binding, remoteAddress);
Frankly, this seems to me like a big gaping hole in the SQL Server CLR hosting. Config files are such a large part of the CLR, they almost seem like a necessity.
Again, thank you for the excellent article
|
|
|
|
|
Hi,
I am trying to create a assmbly for System.Web.dll as per the command above:
CREATE ASSEMBLY
[System.Web] from
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
with permission_set = UNSAFE
GO
But it results in a error:
Msg 10300, Level 16, State 2, Line 1
Assembly 'System.Web' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', 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: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.
Can you please guide me as to how can I go ahead.
Thanks.
|
|
|
|
|
I had the same problem. I'm running on a x64 Vista with x64 SQL Server 2005.
I tried updating to .NET 3.5 SP1 and SQL2005 SP3 but that didn't fix the error.
What worked for me was importing the Web assembly from Framework64 directory.
CREATE ASSEMBLY [System.Web] from
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
with permission_set = UNSAFE
GO
I also tried importing all of the above assemblies from their x64 directory but that caused the error others complained about:
System.TypeInitializationException: The type initializer for 'CLRTrigger.Triggers' threw an exception. ---> System.IO.FileLoadException: Could not load file or assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)
So, I only imported Web with its x64 version. That worked for me.
|
|
|
|
|
|
Thanks for the article, it helped greatly by confirming the (large number of) assemblies that must be added to SQL Server to use WCF.
I hope this tidbit might help someone. I ran into this boggling exception, which threw on client construction:
The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior' could not be loaded.
After a significant amount of digging I found removing the following from the machine.config file fixed the problem, which seems to affect Vista machines.
<commonBehaviors>
<endpointBehaviors>
<Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior/>
</endpointBehaviors>
<serviceBehaviors>
<Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior/>
</serviceBehaviors>
</commonBehaviors>
|
|
|
|
|
|
Hi,
I get the error below when I try to change a field in the table to
activate the trigger.
Have googled around for this problem and cant find a solution or fix.
My System consist of following:
Vista Ultimate x64, Visual Studio 2008 Pro RTM SP1.
cheers,
Raianeh
---------------------------
Microsoft SQL Server Management Studio
---------------------------
No row was updated.
The data in row 17 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: A .NET Framework error occurred during execution of user-defined routine or aggregate "WCFTrigger":
System.TypeInitializationException: The type initializer for 'CLRTrigger.Triggers' threw an exception. ---> System.IO.FileLoadException: Could not load file or assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)
System.IO.FileLoadException:
at CLRTrigger.Triggers..cctor()
System.TypeInitializationException:
at CLRTrigger.Triggers.CLRTrigger()
.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
---------------------------
OK Help
---------------------------
|
|
|
|
|
Hi,
I think I had this error once during the development of the article. I believe, although it was a while a go that I wrote it, that the problem is occuring because the assembly you reference in the CREATE ASSEMBLY statement (the one for the System.ServiceModel) is a different version from the one that you have installed in the GAC (C:\windows\assmbly).
You should be able to fix the issue by:
1, Check the version of System.ServiceModel in SQL Management studio (yourDB\programmability\Assemblies).
2, Check the version of System.ServiceModel in the GAC (C:\windows\assembly)
3a, If the version the GAC is older, drop the newer version into the GAC so that it matches the one loaded into your db.
4a, If the version in the GAC is newer:
Drop the assembly from your db and then CREATE ASSEMBLY with the newer dll.
Hope this helps.
Sam
|
|
|
|
|
firts i would like to say that is was really a good artical..
now i have a problem, i followed ur example, every thing worked hust fine till i deployed the project - i called it "PlugInServerProject"- I get the following error :
"CREATE ASSEMBLY failed because type 'Triggers' in safe assembly 'PlugInServerProject' has a static field 'endpoint'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language"..
any ideas how can i solve that?..
thank you
|
|
|
|
|
I had the same issue. On your PlugInServerProject, go to properties, and go to the Database tab. Now change the permission level from 'safe' to 'unsafe', save, and you should be able to deploy at that point.
|
|
|
|
|
i had the same problem with you, am using Windows 7 and VS2008. When i opent he prokject, its permission level was 0, when i change to unsafe or any other values, the popup just keeps poping saying its not a valid value, then i cant save or even exit VS2008, have to reboot the system always...
|
|
|
|
|
Hi, on my developer machine I've got installed VS2005 and VS 2008.
When creating an Sql Serverproject in Visual Studio 2008 there is no possibility to add a Service,
reference only webreferences are possible. I tryed to rebuild the Sample in Visual Studio 2005
..problem was the installation of the WCF add on.
I had to create an registry entry to emulate the existence of Frame work 3.0
(Installed was 1.1;2.0;3.1;3.5) to be able to install
all in all I could rebuild the project, but when init the static myClient
<br />
myClient= new ServiceClient.localhost.ServiceContractClient(httpBinding, endpoint);)<br />
in the CLR-Trigger I get error Message:
The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, <br />
PublicKeyToken=b03f5f7f11d50a3a' registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior' could not be loaded. (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 188)
..After small Google search I found a "solution" to remove that line from machine.config
<br />
<commonBehaviors><br />
<endpointBehaviors><br />
<Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior></Microsoft.VisualStudio.Diagnostics.<br />
ServiceModelSink.Behavior><br />
</endpointBehaviors><br />
<serviceBehaviors><br />
<Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior/><br />
</serviceBehaviors><br />
</commonBehaviors><br />
I removed the two lines with ServiceModelSink and the client was able to init...but now, when calling a method the following error occurs:
Content Type text/xml; charset=utf-8 was not supported by service http:
The client and service bindings may be mismatched.
Anyone an idea how to get the trigger work?
|
|
|
|
|
Have you got access to a 'clean' machine to try it on, i.e. one with just VS2005?
|
|
|
|
|