Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts

Sunday, March 11, 2012

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.

Wednesday, March 7, 2012

Close existing connection before deleting/restore database

I want to be able to force a restore or a delete on a database even if there still have some active connections. In SQL Server management studio there is a checkbox when you're trying to delete a database to "Close existing connection" i want to do the same thing but using SMO. How can i do this ?

ThankHi,

see my blog entry here:

http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Friday, February 10, 2012

clearing values from a column

hey all,
how would i clear values for a column for all rows but not delete the row?
thanks,
rodcharHi,
Use Update command and set the column value with null
UPDATE TABLE
SET COL1 = NULL
Thanks
Hari
SQL Server MVP
"rodchar" wrote:

> hey all,
> how would i clear values for a column for all rows but not delete the row?
> thanks,
> rodchar|||"Clear" meaning what' You have to decide, for each datatype (for each
Column) what value will mean "Cleared" For a chacacter type column that's
not hard, Just an Empty String, I guess makes the most sense, but for
numeric or date columns, it's bit harder, The value you choose may be a
meaningful value in rows that are not "Cleared"
If the Columns allow Nulls, you could use null, but that's not really what
null iod supposed to be for, (it means UNKNOWN) so I wouldn;t recommend that
,
(UNless you MEAN Unknown when you say "CLeared")
"rodchar" wrote:

> hey all,
> how would i clear values for a column for all rows but not delete the row?
> thanks,
> rodchar|||You can do:
--if column is defined as nullable
update tb
set col=null
-oj
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:D8369B7B-3634-4A8B-88FC-23D948D18D53@.microsoft.com...
> hey all,
> how would i clear values for a column for all rows but not delete the row?
> thanks,
> rodchar