CLR Trigger to write file.......

Hello, theres,

I have a request to write to a file whenever new record added to the table.

When records insert row by row, it goes well. but when more than 2 session insert at the same time, sometimes it duplicate some record in the file. I try to add synchonize code ( like lock , Monitor) but it doesn't work. any idea ?

Regards,

Agi

I assume you are using this to generate an Audit log or something similar. Here is the route I took.

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

|||

Jonathan,

Thanks for your reply, I just want to write everything to the flat file whenever new record inserted to my table. I write a trigger using c#. I just wonder if more than 2 threads (sessions) insert new record at the same time, what will be write to the flat file ?

Regards,

Agi

|||Hi,
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all
may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti

CLR Trigger -> Send Insert/Update/Delete Message to Windows Service

Hi,

I have an c# windows service, which is running on the same machine where my mssql server is installed. This service must be informed for each insert/update/delete event on one specific table.

My idea was to create an CLR Trigger for this table which can communicate with this service via .NET remoting. But the problem is, that the system.runtime.remoting assembly is not integrated within the mssql clr enviroment => i can't use remoting.

Are there any other idea's how can i solve this?

Best regards,
Thomas

Hi,

what about using Query Notification ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I had the same problem as you and I have used two different tecniques: both of them uses CLR stored procs.

1. An OS event set by SqlServer: useful when to have to notify that an event occurred without any param. Each different event must be attached to a different event name.

[SqlProcedure]
public static void SetOsEvent(SqlString eventName) {
try {
EventWaitHandle.OpenExisting((string)eventName).Set();
}
catch (WaitHandleCannotBeOpenedException) {
}
}

2. An UDP Message. Useful for non-reliable and fast messaging system, such as broadcasting the internal net that an event has occurred.

[SqlProcedure]
public static void UdpSend(SqlString address, SqlInt32 port, SqlString message) {
try {
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
byte[] datagram = message.GetUnicodeBytes();
client.Send(datagram, datagram.Length, (string)address, (int)port);
}
catch{
}
}

[SqlProcedure]
public static void UdpBroadcast(SqlInt32 port, SqlString message) {
try {
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
byte[] datagram = message.GetUnicodeBytes();
System.Net.IPEndPoint ep = new System.Net.IPEndPoint(System.Net.IPAddress.Broadcast, (int)port);
client.Send(datagram, datagram.Length, ep);
}
catch {
}
}

Hope that helps.

CLR Table Value Function Insert Into Table Variable

I have a simple clr tvf that splits a string and returns a two column table. When I try and insert the results of this tvf into another table variable I get the error below. Can anyone help me on this one? What a huge letdown if clr tvf cannot be insert into table variables. Inserting into a temp table works fine.

-- BTW I am on the September CTP with VS 2005 RC

Thanks,
Adam

Error Message

Msg 8624, Level 16, State 1, Line 2

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

Below is the SQL I am using to test

declare @.t table(a int, b nvarchar(128))
insert into @.t
select * from dbo.Split('Hello,GoodBye', ',', 1)

Function Definition

CREATE FUNCTION [dbo].[Split](@.value [nvarchar](4000), @.seperator [nvarchar](32) = N',', @.removeEmptyEntries [bit] = 1)
RETURNS TABLE ([Position] [int] NULL,[Value] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Sit.Sql.Cdw].[Sit.Sql.Cdw.Split].[InitMethod]
GO

Source Code

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;

namespace Sit.Sql.Cdw
{
public class Split
{
public struct Splitter
{
public int pos;
public String value;
}

[SqlFunction(FillRowMethodName= "FillRow", Name="Split", TableDefinition="Position int, Value nvarchar(1000)")]
public static IEnumerable InitMethod(String value, String seperator, bool removeEmptyEntries)
{
Splitter[] sVals;
String[] vals = value.Split( new string[1] { seperator }, (removeEmptyEntries) ?
StringSplitOptions.RemoveEmptyEntries : StringSplitOptions.None);
sVals = new Splitter[vals.Length];
for(int i = 0; i < vals.Length; i++)
{
sVals.pos = i + 1;
sVals.value = vals;
}
return sVals;
}

public static void FillRow(Object obj, out SqlInt32 Position, out SqlChars Value)
{
Splitter s = (Splitter)obj;
Position = new SqlInt32(s.pos);
Value = new SqlChars(s.value);
}
}
}

I just realized my mistake after posting...
I originally called the function with default parameters dbo.Split('Hello,Goodbye', default, default) which produces the error. Substituting the default parameter with an actual value runs fine.

Close cursor in another procedure

Hi,
I declared a cursor in an Insert trigger. In this trigger I called a sp. Can
I close this cursor in the sp?
----
CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
...
DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTED
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyVariable
WHILE @.@.FETCH_STATUS = 0
BEGIN
...
IF @.@.ERROR <> 0
BEGIN
EXEC MY_ERROR_SP
RETURN
END
...
END
...
----
CREATE MY_ERROR_SP AS
...
IF @.@.TRANCOUNT > 0
ROLLBACK
CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
...
----Why have you put a cursor in a trigger? Cursors are rarely a good idea
and triggers are absolutely the last place you should use them. Updates
are set-based so triggers should be too.
The answer to your question is no, because your cursor is local. The
better answer is rewrite your trigger.
David Portas
SQL Server MVP
--|||Cursors by default hv global scope. Therefore, it is possible to create them
in 1 SP1 & close it in SP2 called from SP1.
Rakesh
"Tod" wrote:

> Hi,
> I declared a cursor in an Insert trigger. In this trigger I called a sp. C
an
> I close this cursor in the sp?
> ----
> CREATE TRIGGER MY_TRIGGER ON dbo.MY_TABLE FOR INSERT
> ...
> DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR SELECT MyField FROM INSERTE
D
> OPEN MyCursor
> FETCH NEXT FROM MyCursor INTO @.MyVariable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> ...
> IF @.@.ERROR <> 0
> BEGIN
> EXEC MY_ERROR_SP
> RETURN
> END
> ...
> END
> ...
> ----
> CREATE MY_ERROR_SP AS
> ...
> IF @.@.TRANCOUNT > 0
> ROLLBACK
> CLOSE MyCursor --CAN I CLOSE cURSOR HERE?
> DEALLOCATE MyCursor --CAN I DEALLOCATE cURSOR HERE?
> ...
> ----
>
>|||David is right.. since ur cursor is a local one, u will not be able to close
.
"Rakesh" wrote:
> Cursors by default hv global scope. Therefore, it is possible to create th
em
> in 1 SP1 & close it in SP2 called from SP1.
> Rakesh
> "Tod" wrote:
>

Clogged Database?

Hi,
I am having a really strange situation in my database, where a table
seems "clogged" until I manually insert a row to that table using
Enterprise Manager.
I have MSMQ set up so that it receives transactions pending for
insertion. When someone complains that is not seeing data in the apps, i
see that the MSMQ is loaded with data. As part of the troubleshooting, i
start testing connnections and test queries to diagnose the problem.
However, as soon as I insert a row to the problematic table, the MSMQ
empties agaig
Can you please give me some light regarding this problem'
Thanks, and Happy Thanksgiving.
--eval
Until then The database starts timing outHave you looked to see if someone is blocking the table? It's possable you
began a transaction with EM some how and didn't commit it. I have seen
similiar issues when people use EM to view the rows and if you touch any of
the data it can put a lock that you are not aware of.
Andrew J. Kelly SQL MVP
"eval" <eval@.eval.com> wrote in message
news:%23wL3JUj0EHA.3840@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am having a really strange situation in my database, where a table seems
> "clogged" until I manually insert a row to that table using Enterprise
> Manager.
> I have MSMQ set up so that it receives transactions pending for insertion.
> When someone complains that is not seeing data in the apps, i see that the
> MSMQ is loaded with data. As part of the troubleshooting, i start testing
> connnections and test queries to diagnose the problem. However, as soon as
> I insert a row to the problematic table, the MSMQ empties agaig
> Can you please give me some light regarding this problem'
> Thanks, and Happy Thanksgiving.
> --eval
> Until then The database starts timing out|||Actually it has happened sporadically in the past several weeks, and
happens when no one is connected to the database (usually very early in
the morning).
Andrew J. Kelly wrote:
> Have you looked to see if someone is blocking the table? It's possable yo
u
> began a transaction with EM some how and didn't commit it. I have seen
> similiar issues when people use EM to view the rows and if you touch any o
f
> the data it can put a lock that you are not aware of.
>|||"eval" <eval@.eval.com> wrote in message
news:elZKNlj0EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Actually it has happened sporadically in the past several weeks, and
> happens when no one is connected to the database (usually very early in
> the morning).
>
Next time it happens, before you do your "fix" check for blocking.
At the very least a sp_who2 may give you some useful info.
[vbcol=seagreen]
> Andrew J. Kelly wrote:
you[vbcol=seagreen]
of[vbcol=seagreen]

Clogged Database?

Hi,
I am having a really strange situation in my database, where a table
seems "clogged" until I manually insert a row to that table using
Enterprise Manager.
I have MSMQ set up so that it receives transactions pending for
insertion. When someone complains that is not seeing data in the apps, i
see that the MSMQ is loaded with data. As part of the troubleshooting, i
start testing connnections and test queries to diagnose the problem.
However, as soon as I insert a row to the problematic table, the MSMQ
empties agaig
Can you please give me some light regarding this problem?
Thanks, and Happy Thanksgiving.
--eval
Until then The database starts timing out
Have you looked to see if someone is blocking the table? It's possable you
began a transaction with EM some how and didn't commit it. I have seen
similiar issues when people use EM to view the rows and if you touch any of
the data it can put a lock that you are not aware of.
Andrew J. Kelly SQL MVP
"eval" <eval@.eval.com> wrote in message
news:%23wL3JUj0EHA.3840@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am having a really strange situation in my database, where a table seems
> "clogged" until I manually insert a row to that table using Enterprise
> Manager.
> I have MSMQ set up so that it receives transactions pending for insertion.
> When someone complains that is not seeing data in the apps, i see that the
> MSMQ is loaded with data. As part of the troubleshooting, i start testing
> connnections and test queries to diagnose the problem. However, as soon as
> I insert a row to the problematic table, the MSMQ empties agaig
> Can you please give me some light regarding this problem?
> Thanks, and Happy Thanksgiving.
> --eval
> Until then The database starts timing out
|||Actually it has happened sporadically in the past several weeks, and
happens when no one is connected to the database (usually very early in
the morning).
Andrew J. Kelly wrote:
> Have you looked to see if someone is blocking the table? It's possable you
> began a transaction with EM some how and didn't commit it. I have seen
> similiar issues when people use EM to view the rows and if you touch any of
> the data it can put a lock that you are not aware of.
>
|||"eval" <eval@.eval.com> wrote in message
news:elZKNlj0EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Actually it has happened sporadically in the past several weeks, and
> happens when no one is connected to the database (usually very early in
> the morning).
>
Next time it happens, before you do your "fix" check for blocking.
At the very least a sp_who2 may give you some useful info.
[vbcol=seagreen]
> Andrew J. Kelly wrote:
you[vbcol=seagreen]
of[vbcol=seagreen]

Clogged Database?

Hi,
I am having a really strange situation in my database, where a table
seems "clogged" until I manually insert a row to that table using
Enterprise Manager.
I have MSMQ set up so that it receives transactions pending for
insertion. When someone complains that is not seeing data in the apps, i
see that the MSMQ is loaded with data. As part of the troubleshooting, i
start testing connnections and test queries to diagnose the problem.
However, as soon as I insert a row to the problematic table, the MSMQ
empties agaig
Can you please give me some light regarding this problem'
Thanks, and Happy Thanksgiving.
--eval
Until then The database starts timing outHave you looked to see if someone is blocking the table? It's possable you
began a transaction with EM some how and didn't commit it. I have seen
similiar issues when people use EM to view the rows and if you touch any of
the data it can put a lock that you are not aware of.
--
Andrew J. Kelly SQL MVP
"eval" <eval@.eval.com> wrote in message
news:%23wL3JUj0EHA.3840@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am having a really strange situation in my database, where a table seems
> "clogged" until I manually insert a row to that table using Enterprise
> Manager.
> I have MSMQ set up so that it receives transactions pending for insertion.
> When someone complains that is not seeing data in the apps, i see that the
> MSMQ is loaded with data. As part of the troubleshooting, i start testing
> connnections and test queries to diagnose the problem. However, as soon as
> I insert a row to the problematic table, the MSMQ empties agaig
> Can you please give me some light regarding this problem'
> Thanks, and Happy Thanksgiving.
> --eval
> Until then The database starts timing out|||Actually it has happened sporadically in the past several weeks, and
happens when no one is connected to the database (usually very early in
the morning).
Andrew J. Kelly wrote:
> Have you looked to see if someone is blocking the table? It's possable you
> began a transaction with EM some how and didn't commit it. I have seen
> similiar issues when people use EM to view the rows and if you touch any of
> the data it can put a lock that you are not aware of.
>|||"eval" <eval@.eval.com> wrote in message
news:elZKNlj0EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Actually it has happened sporadically in the past several weeks, and
> happens when no one is connected to the database (usually very early in
> the morning).
>
Next time it happens, before you do your "fix" check for blocking.
At the very least a sp_who2 may give you some useful info.
> Andrew J. Kelly wrote:
> > Have you looked to see if someone is blocking the table? It's possable
you
> > began a transaction with EM some how and didn't commit it. I have seen
> > similiar issues when people use EM to view the rows and if you touch any
of
> > the data it can put a lock that you are not aware of.
> >

Client timeout expired, settings on 10hrs on server, how change cl

A client has to insert many files with bulk insert. I start it using windows
scripting host with VB Script and ole db provider for SQL Server. works good
and fast. Only really big files will take several minutes to terminate.
How can I set the client timeout?
I'm using windows server2003 SP1, SQL Server2000 SP4, MDAC 2.8 (SP1 cannot
be installed on windows 2003 server) all on same server. Because scripts are
interacting with sql-server (I'm looking foreward to sql server 2005!!!)
I have seen, some have the same problem, but I have not seen solutions.
Do I have to use ODBC instead of OLE DE?
Thanks for any help!
See if this helps:
http://www.aspfaq.com/show.asp?id=2066
Andrew J. Kelly SQL MVP
"Urban" <urban@.nospamplease> wrote in message
news:AA015201-3F8A-465D-9A45-317870105454@.microsoft.com...
>A client has to insert many files with bulk insert. I start it using
>windows
> scripting host with VB Script and ole db provider for SQL Server. works
> good
> and fast. Only really big files will take several minutes to terminate.
> How can I set the client timeout?
> I'm using windows server2003 SP1, SQL Server2000 SP4, MDAC 2.8 (SP1 cannot
> be installed on windows 2003 server) all on same server. Because scripts
> are
> interacting with sql-server (I'm looking foreward to sql server 2005!!!)
> I have seen, some have the same problem, but I have not seen solutions.
> Do I have to use ODBC instead of OLE DE?
> Thanks for any help!