|
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?
|
|
|
|
|
Yes, but there's the same Problem with Framework 3.1 installed
|
|
|
|
|
Does my demo run ok from either machine, i.e. do you just get this when trying to recreate the project?
|
|
|
|
|
rebuild means.. i tryed to get your demo running. (PS: maybe Skype or icq faster? skype: antinero icq: 120965230 )
|
|
|
|
|
Sorry, I don't use ICQ or Skype but my msn is: shilessam@hotmail.com
You should be able to add the reference in via the 'Add Web Reference' option in vs 2008. I have just tried this my self and it works.
You will need to modify the host slightly by adding the following few lines of code before the ServiceHost.Open() method call:
<br />
ServiceMetadataBehavior smb = new ServiceMetadataBehavior(); <br />
<br />
smb.HttpGetEnabled = true; <br />
<br />
myHost.Description.Behaviors.Add(smb);<br />
Let me know how you get on with that.
|
|
|
|
|
....in the final result we think there is no posibility with Visual studio 2008. We tried to consume via adding a Webreference, but this didn't work.
The only workaround I see in time is using a webservice hosted in IIS --> adding this via webreference....(don' forget to add the *.xmlserializer file)
Not the finest way, but works.
|
|
|
|
|