|
Any hint, how this can be done using mssql Server 2012.
Seems like using of wcf is prohibited there.
Even after i added the .Net assemblies for version4.0, i get a error regarding a bad PE Header for a visualbasic component.
SQL72014: .Net SqlClient Data Provider: Meldung 6544, Ebene 16, Status 1, Zeile 18 CREATE ASSEMBLY for assembly 'System.ServiceModel' failed because assembly 'microsoft.visualbasic.activities.compiler' is malformed or not a pure .NET assembly.
Nicht überprüfbarer PE-Header / systemeigener Stub.
modified 13-Feb-13 7:59am.
|
|
|
|
|
This is still a problem, even so many years later.
I always knew VB was evil and from the devil. This just proves it again.
My plan is to live forever ... so far so good
|
|
|
|
|
This problem is not fixable. Starting in SQL Server 2012, SQLCLR is linked to CLR version 4.0, and in that version a new library was added: Microsoft.VisualBasic.Activities.Compiler . This new library contains unmanaged code, and unmanaged code (including mixed-mode assemblies that contain both managed and unmanaged code) cannot be loaded into SQL Server. Unfortunately, another change in CLR version 4.0 was that System.ServiceModel was updated and now includes a reference to Microsoft.VisualBasic.Activities.Compiler , not directly (as far as I can tell), but via another library, most likely Microsoft.Transactions.Bridge (another new library as of CLR v 4.0). The only real and stable fix is to use HttpWebRequest and HttpWebResponse . That does mean more manual work (dealing with the XML), but those classes exist in supported .NET Framework libraries, so they are guaranteed to continue working across upgrades of .NET and SQL Server.
|
|
|
|
|
first of all thx for such a great example
but im getting a error while inserting a record in table
A .NET Framework error occurred during execution of user-defined routine or aggregate "WCFTrigger":
System.ServiceModel.CommunicationObjectFaultedException: The communication object, System.ServiceModel.ChannelFactory`1[ServiceClient.localhost.IServiceContract], cannot be used for communication because it is in the Faulted state.
my service is running without any error
|
|
|
|
|
You're welcome.
This is a generic error which basically means something went wrong with the communication channel. You should have an inner exception which should be vastly more enlightening! Have a look and if it's still not clear to you whats going wrong, post back with the inner exception details. There may even be many nested inner exceptions so keep digging until you get to the root cause. 
|
|
|
|
|
Thx a lot for replying
I have solve the error by changing binding to basichttpbinding 
|
|
|
|
|
How can I log anything from the SendData, if I call it async?
|
|
|
|
|
You can't.
Well, only if sqlservr.exe is run with commandline parameter -c.
Then it is inside a Console and Console.Writeline should work
|
|
|
|
|
|
Hi!
Have you a working example with MSVS 2008 (.NET 3.5) and with SQL 2008? Because I get the following exception:
No row was updated.
The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: A .NET Framework error occured during execution of a user-defined routine or aggregate "WCFTrigger": System.Security.HostProtectionException: Attempt to perform an operation that was forbidden by the CLR host.
The protected resource (only available with full trust) where: All
The demanded resources were: Synchronization, ExternalThreading
System.Security.HostProtectionException:
at System.ServiceModel.Description.TypeLoader.LoadContractDescriptionHelper(Type ContactType, Type ServiceType, Object serviceImplementation)
at System.ServiceModel.ChannelFactory '1.CreateDescription()
at System.ServiceModel.ChannelFactory.InitializeEndpoint(Binding binding,
EndpointAddress address)
at System.ServiceModel.ChannelFactory '1..ctor(Binding binding, EndpointAddress address)
at System.ServiceModel.ClientBase '1..ctor(Binding binding, EndpointAddress address)
at ServiceClient.WCFServiceReference.ServiceContractClient..ctor(Binding binding,
EndpointAddress address)
at...
And I've seen this
|
|
|
|
|
Hi,
I don't have a working sample on 3.5 or 2008 but it sounds to me like you've forgotten to execute one of the steps in the "Preparing the database" the database section of the article.
If you have executed all these steps, then I would suggest the later version of either .net or sql server have further dependent assemblies that will need that permissions set to unsafe (as per the ones mentioned in the article.).
Cheers
Sam
|
|
|
|
|
Hi there,
Great article - I found it very useful.
In my case I am trying to publish an inserted record to a message broker.
I am struggling with using the fast method - if I use delegate.Invoke(), it works quite happily and all is well.
If I use delegate.BeginInvoke(), my method never gets called, debugging stops and the message doesn't get pushed onto the broker.
I'm using VS2010 and SQL Server 2008 on Windows 7 x86 if that helps.
Is there something I'm doing wrong?
Regards,
Dan
|
|
|
|
|
|
|
Of course you are correct. There is a perceived increased in the speed of the query execution however.
|
|
|
|
|
i tested your app on 32-bit VS2008, it worked perfectly. But then i formatted my PC to 64-bit Windows 7, problems occured. All CREATE ASSEMBLY commands were issued correctly (i changed some to use C:\Windows\Microsoft.NET\Framework64 folder instead of C:\Windows\Microsoft.NET\Framework), but when i insert a row to database, error occured:
Msg 6522, Level 16, State 1, Procedure GSMAudit, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GSMAudit":
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) See Microsoft Knowledge Base article 949080 for more information.
System.IO.FileLoadException:
at GSMAuditClass.GSMAudit()
Is there any solution to this?
|
|
|
|
|
|
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
|
|
|
|
|