asynchronous select statements

We have an application deployed on two seperate machines both reading from
the same database. The applications poll the database every few seconds and
find the first record in a table that has a bit field set to 0x0. The
application then updates the field to a value of 0x1 and then do some
processing based on the contents of the record. The problem we have is that
it is currently possible for each application to get the same record because
application 2 might select the record just before application 2 updates the
bit flag. we currently use two sql calls (in stored procs) such as:
select top 1 * from table1 where processed = 0x0
update table1 set processed = 0x1 where recid = @.ID
How can we avoid both apps getting the same recordHave the app call a single SP.
This SP updates the record, and then returns it to the client application.
This is in one transaction, so the other application can not get to the same
record.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:#GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
that
> it is currently possible for each application to get the same record
because
> application 2 might select the record just before application 2 updates
the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Jeremy,
This is how you would do it; put the desired row into lock until the update
is done. For rowlock to be effective, you need a primary key on the table!
ReadPast hint will allow you to bypass the locked row and process the next
available one. Without it, your #2 connection will have to wait until #1 is
done. You can try both scenarios out to gain some deeper insight.
e.g.
/*
--sample tb
create table t1(i int primary key, b bit)
insert t1 values(1,0)
insert t1 values(2,0)
insert t1 values(3,0)
*/
-- drop table t1
go
--on connection #1
--this will lock i=1
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
--on connnection #2
--this will lock i=2
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
> and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
> that
> it is currently possible for each application to get the same record
> because
> application 2 might select the record just before application 2 updates
> the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Magnificent! Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||Actually, testing discovered that this might not work, because if the sql
gets run at the same time, the select statements could select the same
record, because nothing is locked at that point.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||You could add another hint to the select to exclusively hold the lock. As
soon as the row is read, it's locked until you invoke commit/rollback.
e.g.
select *
from tb with (rowlock,xlock,readpast)
where pkid=@.para
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:uLpwUeIFFHA.464@.TK2MSFTNGP09.phx.gbl...
> Actually, testing discovered that this might not work, because if the sql
> gets run at the same time, the select statements could select the same
> record, because nothing is locked at that point.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> update
> is
> from
>

Asynchronous Script Component

Hi--done some searching, but I am not finding exactly what I need. I am using an asynchronous script component as a lookup since my table I am looking up on requires an ODBC connection. Here is what my data looks like:

From an Excel connection:

Order Number

123

234

345

The table I want to do a lookup on has multiple rows for each order number, as well as a lot of rows that aren't in my first table:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

778 Another thing

889 Yet more stuff

etc. My desired result would be to pull all the items from table two that match on Order Number from table one. My actual results from the script I have is a single (random) row from table two for each item in table one.....So my current results look like:

Order Number Description

123 Freight

234 Freight

345 Null

And I want:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

345 Null

etc.... Here is my code, courtesy of half a dozen samples found here and elsewhere...

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc

Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager90
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.JDEConnection
odbcConn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)

End Sub

Public Overrides Sub PreExecute()

odbcCmd = New OdbcCommand("SELECT F4211.SDDSC1, F4211.SDDOCO FROM DB.F4211 F4211 Where F4201.SHDOCO = ?", odbcConn)

odbcParam = New OdbcParameter("1", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim reader As Odbc.OdbcDataReader
odbcCmd.Parameters("1").Value = Row.SO
odbcCmd.ExecuteNonQuery()
reader = odbcCmd.ExecuteReader()
If reader.Read() Then

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With

End If

reader.Close()

End Sub

Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(odbcConn)
End Sub

End Class

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

I have also worked out an alternate way to do this using merge join tasks...but then my datareader source goes off and fetches 300,000 rows from F4211 before my final result set of about 1200 rows. That just feels like a bad approach to me...or am I being over-cautious? I'm a newb (if you couldn't already tell)...so guidence is appreciated.

Thank you....

In a first data flow, you could load a staging table in SQL server with the contents of your ODBC source table. Then in a second data flow, you can use that staging table as the source for your lookup component. Might be a bit less work for you.|||So, to make sure I understand--add another data flow. Have it write the records from my F4211 table to a SQL table, then, in my original data flow, do a lookup on my newly created table in SQL...then, I suppose, add an Execute SQL task to blow all those records away? And, I suppose, just to be tidy about it....I could add a shrink database task to clean up afterwards.....?|||

In general and when possible it is a good idea to use staging tables to put all data pieces on the SQL Server side. Besides simplify the dataflow; it improves performance.

I think you are undeestanding Phil's sugestion pretty well; but I am not shure is I would bother with the shrink database step; if you are going to execute this process in a regular basis then you would need that space anyway.

|||Yep, you got it. Now have fun!|||

hilaryjade wrote:

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

So, to go back to the original question.... You want all the rows from the recordset, right? Don't you just need to change that If to a While loop?

Code Snippet

While reader.Read()

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With

End While

|||Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.|||

hilaryjade wrote:

Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.

Did you test it? Can you share the timing results and row counts?

|||I haven't finished adding my full data set I need to pull to my script component yet, but after I do, I can run and time them. I did try adding and using a staging table last night--but ran into a bit of a data type mismatch roadblock on the lookup--I tried a handful of conversions to see if I could get my DT_R8 from Excel to play nicely with my Numeric from the staging table in SQL, but got a bit frustrated and went off to work on my third alternative...using merge joins (which works nicely and runs in 2.2 minutes, but 2 minutes starts feeling a bit long, you know?). At any rate, just creating the staging table took longer than the script takes (but, again, that was without my full data set)....After I get the script component complete and pulling all my data, I'll run, time, and post results. Again, thanks for all the help!|||

Well, I'm not really comparing apples to apples with this, since my script component is part of a data flow that starts with a connection to an excel file, does a lookup on a table with an odbc connection via the script component and then writes to a recordset and the data flow for the staging table concept uses a data reader to collect my records from a table with an odbc connection and then writes them to a SQL db table...

The dataflow with the script component ran in 00:06.844 and wrote 953 rows to my recordset (just writing the rows I needed, selected via script component)

The dataflow to create a staging table ran in 01:16.313 and wrote 219,155 rows to a table in a SQL db, where I could then do a lookup to grab the records I need (953 rows)

I think for this instance, where I need so few records from such a large table, it makes sense to use the asynchronous script component rather than create a staging table.

Again, thanks to all for the help and suggestions. I really appreciate it.

|||Did you use the Fast Load option in the OLE DB Destination when using the staging table approach?|||Yes, on the Connection Manager page for the destination editor, Data access mode is set to Table or View - fast load.

Asynchronous Script Component

Hi--done some searching, but I am not finding exactly what I need. I am using an asynchronous script component as a lookup since my table I am looking up on requires an ODBC connection. Here is what my data looks like:

From an Excel connection:

Order Number

123

234

345

The table I want to do a lookup on has multiple rows for each order number, as well as a lot of rows that aren't in my first table:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

778 Another thing

889 Yet more stuff

etc. My desired result would be to pull all the items from table two that match on Order Number from table one. My actual results from the script I have is a single (random) row from table two for each item in table one.....So my current results look like:

Order Number Description

123 Freight

234 Freight

345 Null

And I want:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

345 Null

etc.... Here is my code, courtesy of half a dozen samples found here and elsewhere...

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc

Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager90
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.JDEConnection
odbcConn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)

End Sub

Public Overrides Sub PreExecute()

odbcCmd = New OdbcCommand("SELECT F4211.SDDSC1, F4211.SDDOCO FROM DB.F4211 F4211 Where F4201.SHDOCO = ?", odbcConn)

odbcParam = New OdbcParameter("1", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim reader As Odbc.OdbcDataReader
odbcCmd.Parameters("1").Value = Row.SO
odbcCmd.ExecuteNonQuery()
reader = odbcCmd.ExecuteReader()
If reader.Read() Then

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With

End If

reader.Close()

End Sub

Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(odbcConn)
End Sub

End Class

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

I have also worked out an alternate way to do this using merge join tasks...but then my datareader source goes off and fetches 300,000 rows from F4211 before my final result set of about 1200 rows. That just feels like a bad approach to me...or am I being over-cautious? I'm a newb (if you couldn't already tell)...so guidence is appreciated.

Thank you....

In a first data flow, you could load a staging table in SQL server with the contents of your ODBC source table. Then in a second data flow, you can use that staging table as the source for your lookup component. Might be a bit less work for you.|||So, to make sure I understand--add another data flow. Have it write the records from my F4211 table to a SQL table, then, in my original data flow, do a lookup on my newly created table in SQL...then, I suppose, add an Execute SQL task to blow all those records away? And, I suppose, just to be tidy about it....I could add a shrink database task to clean up afterwards.....?|||

In general and when possible it is a good idea to use staging tables to put all data pieces on the SQL Server side. Besides simplify the dataflow; it improves performance.

I think you are undeestanding Phil's sugestion pretty well; but I am not shure is I would bother with the shrink database step; if you are going to execute this process in a regular basis then you would need that space anyway.

|||Yep, you got it. Now have fun!|||

hilaryjade wrote:

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

So, to go back to the original question.... You want all the rows from the recordset, right? Don't you just need to change that If to a While loop?

Code Snippet

While reader.Read()

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With

End While

|||Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.|||

hilaryjade wrote:

Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.

Did you test it? Can you share the timing results and row counts?

|||I haven't finished adding my full data set I need to pull to my script component yet, but after I do, I can run and time them. I did try adding and using a staging table last night--but ran into a bit of a data type mismatch roadblock on the lookup--I tried a handful of conversions to see if I could get my DT_R8 from Excel to play nicely with my Numeric from the staging table in SQL, but got a bit frustrated and went off to work on my third alternative...using merge joins (which works nicely and runs in 2.2 minutes, but 2 minutes starts feeling a bit long, you know?). At any rate, just creating the staging table took longer than the script takes (but, again, that was without my full data set)....After I get the script component complete and pulling all my data, I'll run, time, and post results. Again, thanks for all the help!|||

Well, I'm not really comparing apples to apples with this, since my script component is part of a data flow that starts with a connection to an excel file, does a lookup on a table with an odbc connection via the script component and then writes to a recordset and the data flow for the staging table concept uses a data reader to collect my records from a table with an odbc connection and then writes them to a SQL db table...

The dataflow with the script component ran in 00:06.844 and wrote 953 rows to my recordset (just writing the rows I needed, selected via script component)

The dataflow to create a staging table ran in 01:16.313 and wrote 219,155 rows to a table in a SQL db, where I could then do a lookup to grab the records I need (953 rows)

I think for this instance, where I need so few records from such a large table, it makes sense to use the asynchronous script component rather than create a staging table.

Again, thanks to all for the help and suggestions. I really appreciate it.

|||Did you use the Fast Load option in the OLE DB Destination when using the staging table approach?|||Yes, on the Connection Manager page for the destination editor, Data access mode is set to Table or View - fast load.sql

Asynchronous Outputs on Script Component Best practice

If you have an output that is not synchronous with the input what is the best way of processing the data.

I am currently using a generic queue, and a custom class. I am creating an instance of the class in the ProcessINputRow and then adding it to the Queue.

The CreateNewOutputRows Dequeues the class instances and creates buffer rows.

Is there a better solution?

ArrayList? I've seen asynch components that cache data in an ArrayList.

-Jamie

|||The problem is having two threads, one putting data into a container and one taking it off. I think the queue is the best solution.

Asynchronous operation

What I am looking to do is have a stored procedure begin a dialog with my request service.
With that dialog established my stored procedure sends 50 request messages, one for each of the 50 of the United States. I want these to be processed asynchronously by a procedure that is called on activation for the request queue. In that activation procedure the request is processed against the respective state and a response message is sent to the response service (to the response queue). I want to be able to tie these request messages and response messages together with some type of shared identifier. These requests don't need to be processed in any specific order and don't need any fancy locking mechanism via conversation group since these requests require to be processed asynchronously. What is the best approach? Do I need to create 50 seperate queues and open dialogs with each? If this is the route to take, would this be a performance hit?

My goal is to have all 50 states process all at once, each finishing with a response message sent to the response queue. The initiating procedure, after sending these 50 requests, would then spin and wait for all 50 to complete, be it a response, error, or timeout. When all 50 have returned, the procedure would then merge the results and return. So as you can see in my scenario, I dont care when a state is complete as they do not affect the outcome, nor do they access any of the same resources when being processed.Interesting. Are you running into a problem that the 50 messages are processing synchronously, rather than asynchronously? If so, I believe it is because they are part of the same conv. group, and an activation proc. will only fire more activation instances for different conversation groups. Does that make sense?
Tim|||Yes, that is exactly what is happening. All of the messages are part of the same conversation group, therefore, they are processing synchronously as you've mentioned. With that in mind, do I then open new conversations for each of the 50 states? Will opening so many conversations have an adverse effect on performance? I still need to group these messages together somehow. In a way, the only possible solution I see is creating 50 individual request queues and sending messages to each within the same conversation group... Does this sound reasonable?|||Would it be possible for you to create two conversation groups, and send 25 through each group?

Asynchronous Mirroring and Server Failure

Hi

Can anyone please tell me what happens if I have Asynchronous mirroring setup and my Primary server physically dies and not available then what happens?. Does

1. Automatic failover occur to Secondary server?

2. What does the Database state show as. Primary, disconnected?.

3. what happens to my transactions. Are they lost?

4. Does any data loss occur?

If I rebuild a new server how do I sync back my current primary to the new one? In that case is it going to be just a fail back?

Any information is appreciated,

Thank you

AK

In Asynchronous mirroring the following occurs,
1. failover has to be forced only as it will be in High performance mode and witness server will not be present
2. safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal)
3. yes the if the principal server is down it will be shown as disconnected
4. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service.
5. you need to failover using the option force_service allow data loss.......you need to run the below command in mirror server,

Code Snippet

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable.

refer the below links,
http://sql-articles.com/articles/dbmrr.htm
sql-articles.com

Thanxx
Deepak

|||

Hi Deepak

Thanks for your answers. So my question is after failing forceover and the mirror database has been recovered what do we need to do to apply those transaction log blocks from principal that have not been recieved?. does restoring the transaction logs backups from the principal (if they are available) and applying them on recoverd mirror database is something that guarantees no data loss?

Thank you

AK

|||Hi Ankith,
Definitely data loss is bound to be present in High performance mode (Asynchronous mirroring). You cannot apply the transaction logs from principal because mirroring works on the concept of transactions and not transaction logs........transaction logs are present only in log shipping ! in mirroring all the transactions occur internally and cannot perform any restorations

Thanxx
Deepak
|||

Hi Deepak,

Great Explanation. In such a case, how can we

1.Find out how much data loss has occured. Is there any way to know this? (I am thinking not).

2.what is the best way to verify to know if data loss has occured?

3. Is there any way to mitigate or reduce the data loss if we cant prevent it?

Thanks

AK

|||Hi Ankith,
1. I don't think its possible to identify the data loss
2. Definitely data loss will occur but i am not sure
3. The only thing that comes to my mind is High availability mode which has automatic failover as witness server is present ! since you need to perform forced failover i.e you need to run the query so that failover occurs instantly ! if you want to mitigate it in asynchronous mode i think you need to configure alerts which will let you know the status of mirroring so that you can perform failover

Asynchronous Mirroring and Server Failure

Hi

Can anyone please tell me what happens if I have Asynchronous mirroring setup and my Primary server physically dies and not available then what happens?. Does

1. Automatic failover occur to Secondary server?

2. What does the Database state show as. Primary, disconnected?.

3. what happens to my transactions. Are they lost?

4. Does any data loss occur?

If I rebuild a new server how do I sync back my current primary to the new one? In that case is it going to be just a fail back?

Any information is appreciated,

Thank you

AK

In Asynchronous mirroring the following occurs,
1. failover has to be forced only as it will be in High performance mode and witness server will not be present
2. safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal)
3. yes the if the principal server is down it will be shown as disconnected
4. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service.
5. you need to failover using the option force_service allow data loss.......you need to run the below command in mirror server,

Code Snippet

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror. The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable.

refer the below links,
http://sql-articles.com/articles/dbmrr.htm
sql-articles.com

Thanxx
Deepak

|||

Hi Deepak

Thanks for your answers. So my question is after failing forceover and the mirror database has been recovered what do we need to do to apply those transaction log blocks from principal that have not been recieved?. does restoring the transaction logs backups from the principal (if they are available) and applying them on recoverd mirror database is something that guarantees no data loss?

Thank you

AK

|||Hi Ankith,
Definitely data loss is bound to be present in High performance mode (Asynchronous mirroring). You cannot apply the transaction logs from principal because mirroring works on the concept of transactions and not transaction logs........transaction logs are present only in log shipping ! in mirroring all the transactions occur internally and cannot perform any restorations

Thanxx
Deepak
|||

Hi Deepak,

Great Explanation. In such a case, how can we

1.Find out how much data loss has occured. Is there any way to know this? (I am thinking not).

2.what is the best way to verify to know if data loss has occured?

3. Is there any way to mitigate or reduce the data loss if we cant prevent it?

Thanks

AK

|||Hi Ankith,
1. I don't think its possible to identify the data loss
2. Definitely data loss will occur but i am not sure
3. The only thing that comes to my mind is High availability mode which has automatic failover as witness server is present ! since you need to perform forced failover i.e you need to run the query so that failover occurs instantly ! if you want to mitigate it in asynchronous mode i think you need to configure alerts which will let you know the status of mirroring so that you can perform failover

Asynchronous events to database clients via DAL?

Greetings, I have a requirement for a SQL Server 2005 database to

notify 3rd Party applications of a table change. As is stands now, 3rd

Party applications access the database via a Data Access Layer (DAL)

dll (C#). I'd like to somehow implement an asychronous event

notification scheme via the same DAL to these clients.

Can someone offer a clever way to implement such events?

Broker Services? I am under the impression the SSBS is typically deployed when databases are to communicate with one another.

Triggers to call some CLR code?

Other?

Thanks in advance,
Loopsludge

In SQL 2005 there is a buil-in solution for 'table change' notifications, namely Query Notifications and the technologies based on it (SqlNotifications, SqlDependency, SqlCacheDependency). See this post for a discussion on them: http://blogs.msdn.com/remusrusanu/archive/2006/06/17/635608.aspx

If your goal is a generic notification platform, then some questions arrise:
What kind of events would you notify (data changes, dirty cache notifications, arbitrary events)?
Where are the subscribers located (same appdomain, same machine, different machines)?
What scale do you need (tens, hundreds or millions of subscribers)?
What kind of reliability is desired for the notifications (what is the cost of loosing one notification, what happens if the subscriber or publisher are recycled)?
Are the subscriptions and/or notifications persistent or transient?

Service Broker offers persisted, reliable communication channel between services hosted on SQL 2005 databases.

HTH,
~ Remus

|||Greetings Remus,

Sorry it has taken me so long to respond. I have been exploring Query Notifications. I'm still not certain this is the correct way to go.

In answer to your questions:

What kind of events would you notify (data changes, dirty cache notifications, arbitrary events)?
I am interested in notifying clients of data changes, specifically new records.

Where are the subscribers located (same appdomain, same machine, different machines)?
The DAL will be consumed by 3rd Party applications located on a different machine.

What scale do you need (tens, hundreds or millions of subscribers)?
The number of subscribers will be very small. In most cases only 1 but potentially there could be more but not many, five max.

What

kind of reliability is desired for the notifications (what is the cost

of loosing one notification, what happens if the subscriber or

publisher are recycled)?
Now this is a very interesting question. I was reading up on the SqlDependency and noticed that one has to re-submit for each notification from the database. What happens if an event occurs inbetween the time the last notification is being processed and the event subscription? Is it lost? If so then that's a bummer. How might I get around this? Service Broker seems like serious overkill.

Are the subscriptions and/or notifications persistent or transient?
I'm not quite sure I understand completely. Could you please explain? (Sorry)

Best Regards,
Loopsludgesql

Asynchronous data flow tasks how to run more than 4 at a time

Hi guys,

i have a for each loop and it has about 20 data flow tasks (simple data extractions). i notice when i run the package it only runs up to 4 data flow tasks at a time. others have to wait till one of the first 4 flows finishes.

i was wondering if there's a way to change the limit of how many data flow tasks can run at a time. is there a property some where ?

i know this will be stressfull to the server, but the server is well equiped with CPU power and memory, so performance will not be an issue.

any thoughts?

Package.MaxConcurrentExecutables Property

Valid values are one and higher, or -1. Other values are invalid. A value of -1 allows the maximum number of concurrently running executables to equal the number of processors plus two. Setting this property to zero or any other negative value fails with an error code that indicates an invalid argument.

This property is used when parallelism exists in the workflow. If the workflow is a series of sequential precedence constraints, then this property has no effect.

I don't know if you can get more than CPU Count + 2 by forcing the value. If this is a 32-bit server then I would be concerned about memory, as despite having 10 GB in there, a process (read SSIS Package) can only use 2GB or 3GB with the/3GB boot.ini switch, so you may want to break out into multiple packages, or just call the same package multiple times. The Execute Package Task can be used to get multiple processes with the out of processes property, but this has a higher overhead for loading and starting the packages.

Asynchronous Batch Processing

Currently we have a process that does synchronous batch processing that can
take a long time and the user cannot do anything while this is happening. W
e
want to encapsulate this process into an asynchronous operation. I imagine
that the processing itself will now reside in a DTS package (SSIS package
actually, since we will be using SQL Server 2005).
Scheduling a package to run asynchronously is no problem, but it would be
nice if it notified the calling app (which is UNIFACE btw - I don't know a
lot about it, so don't ask me) that the batch process is complete. We could
have the UNIFACE app poll to check the status, but I was wondering if Servic
e
Broker could help in this capacity or would that be overkill?
Ultimately, we will be replacing the UNIFACE app with our own suite, and
want the process to be as modular as possible, to facilitate a painless
conversion.
Thanks,
BrandonBrandon,
There are a number of reasons which I believe make Service Broker specially
qualified for this kind of jobs (i.e. asynchronous execution). Being
entirely contained in the database and is running inside the SQL Server
process allows Service Broker based apps to benefit from backup/restore (the
state of your jobs is backed up as part of the database), from
failover/clustering and from database mirroring (the job schedule just fails
over along with the database). Service Broker also gives you a mean to
communicate back from this jobs to the calling app (dialogs are always
bidirectional, the job can reply back on the same dialog that started the
job). Also you'll benefit from the poll free model of the Service Broker:
WAITFOR (RECEIVE ...) does not poll, it blocks until a message becomes
available.
Another nice feature of Service Broker is that it can give you persisted
timers (BEGIN CONVERSATION TIMER ...), stored in the database (again,
benefiting from all the backup/restore and availability benefits of
databases)
What are you afraid of when you say that Service Broker would be overkill?
HTH,
~ Remus
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:0F6D5B45-7A24-4539-BE76-E5B8675A3E83@.microsoft.com...
> Currently we have a process that does synchronous batch processing that
> can
> take a long time and the user cannot do anything while this is happening.
> We
> want to encapsulate this process into an asynchronous operation. I
> imagine
> that the processing itself will now reside in a DTS package (SSIS package
> actually, since we will be using SQL Server 2005).
> Scheduling a package to run asynchronously is no problem, but it would be
> nice if it notified the calling app (which is UNIFACE btw - I don't know a
> lot about it, so don't ask me) that the batch process is complete. We
> could
> have the UNIFACE app poll to check the status, but I was wondering if
> Service
> Broker could help in this capacity or would that be overkill?
> Ultimately, we will be replacing the UNIFACE app with our own suite, and
> want the process to be as modular as possible, to facilitate a painless
> conversion.
> Thanks,
> Brandon|||Mainly I am hesitant for two reasons... I am not that familiar with the
capabilities of UNIFACE (from what I understand it would have to poll instea
d
of using a blocking call like WAITFOR to determine whether job
completed/status of job) and also that I have only had minimal experience
with Service Broker (in the form of the several very simply demos out there)
.
Since the UNIFACE interface will eventually be replaced by a Delphi.NET app,
I can more easily see how that would work better in the long term.
Have you seen any Service Broker examples that communicate with a SSIS
package?
Thanks,
Brandon|||I'm gonna have to do some research about SSIS to see how it integrates with
SSB
What kind of asynchronous batch is gonna be processed? Are talking about
launching an external process, calling a stored proc, running a t-sql batch,
calling an CLR stored procedure?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon|||Two Connect has a sample of Service Broker custom tasks for SSIS:
[url]http://www.twoconnect.com/pages/product_solutions/sqlserver.enhancements.ASPX[/url
]
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon

Asynchronous ADO procedure invocation?

Is there any way to send several ADO Command.Execute asynchronously at once?
Please reply. Thanks in advance.
Regards,
Hyun-jik BaeIn the following example, an event handler has been implemented to print to
the Debug window when the command has completed:
Dim WithEvents conn As ADODB.Connection
Sub Form_Load()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql70server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open
Set cmd.ActiveConnection = conn
cmd.Execute "select * from authors", , adAsyncExecute
Debug.Print "Command Execution Started."
End Sub
Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal _
pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal _
pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)
Debug.Print "Completed Executing the Command."
End Sub
Hope you mean ADO not ADO.NET ;-) For further information look in MSDN
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
news:eHtTcykRFHA.3988@.tk2msftngp13.phx.gbl...
> Is there any way to send several ADO Command.Execute asynchronously at
> once?
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>|||Thanks for your answer.
However, I found that the secondary asynchronous Execute fails while the
first asynchronous Execute is not completed yet. Is this phenomenon
ordinary? Or is there any way to enable more overlapped Execute be allowed?
Thanks.
Regards,
Hyun-jik Bae
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfiXV1kRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> In the following example, an event handler has been implemented to print
> to
> the Debug window when the command has completed:
> Dim WithEvents conn As ADODB.Connection
> Sub Form_Load()
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
> Set conn = New ADODB.Connection
> conn.ConnectionString = _
> "Provider=SQLOLEDB;Data Source=sql70server;" _
> & "User ID=sa;Password='';Initial Catalog=pubs"
> conn.Open
> Set cmd.ActiveConnection = conn
> cmd.Execute "select * from authors", , adAsyncExecute
> Debug.Print "Command Execution Started."
> End Sub
> Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal _
> pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal _
> pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
> ByVal pConnection As ADODB.Connection)
> Debug.Print "Completed Executing the Command."
> End Sub
> Hope you mean ADO not ADO.NET ;-) For further information look in MSDN
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
> news:eHtTcykRFHA.3988@.tk2msftngp13.phx.gbl...
>|||There should be the possibility to do this unless you will not use the same
connection for it.
Jens Suessmeyer.
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
news:uCgnSElRFHA.2736@.TK2MSFTNGP09.phx.gbl...
> Thanks for your answer.
> However, I found that the secondary asynchronous Execute fails while the
> first asynchronous Execute is not completed yet. Is this phenomenon
> ordinary? Or is there any way to enable more overlapped Execute be
> allowed?
> Thanks.
> Regards,
> Hyun-jik Bae
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfiXV1kRFHA.3156@.TK2MSFTNGP15.phx.gbl...
>

Asynchronous ADO Command execute

I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,
ThanksIt also happens inconsistently. Sometimes, it just runs fine and sometimes, it just gives a Program Error and everything blows out.|||Originally posted by archnam
I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,

Thanks

Have you looked in the SQL Server error log in enterprise manager?|||Where exactly are error logs in the SQL Server under Enterprise Manager?
Also, if I put a breakpoint in my program, it seems to run fine and when I just run it, it bombs, do you think may be I should put some delay in it. The Command.Execute might be taking some time to run.

Asynchronous ActiveX Replication Problems

Hello,
I have a VB.NET service that has a timer. On certain timer ticks, a
merge replication is called.
I have a wrapper class that asynchronously starts the merge replication
process (using the .BeginInvoke() method). I did this so that my
service could continue to do other things while the merge replication
took place.
Through logging, I know that a separate thread is indeed handling the
replication process.
The problem is that the merge replication appears to block the calling
thread as soon as replication begins. I can see this happening because
I have logging statements inside the merge status event. I see the
calling thread continue to do work until the merge thread begins to
initialize. As soon as the merge is complete, the calling thread once
again continues.
At first I thought that maybe the entire process was blocked by the
merge replication ActiveX object, but the timer event continues to fire.
So it appears that only the calling thread is blocked.
Any ideas?
Thanks,
Jeff
are you using an MSDE database? MSDE is throttled at 8 simultaneous
workloads.
Perhaps this is the problem you are running into.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:Nu02d.286$DY.238@.chiapp18.algx.net...
> Hello,
> I have a VB.NET service that has a timer. On certain timer ticks, a
> merge replication is called.
> I have a wrapper class that asynchronously starts the merge replication
> process (using the .BeginInvoke() method). I did this so that my
> service could continue to do other things while the merge replication
> took place.
> Through logging, I know that a separate thread is indeed handling the
> replication process.
> The problem is that the merge replication appears to block the calling
> thread as soon as replication begins. I can see this happening because
> I have logging statements inside the merge status event. I see the
> calling thread continue to do work until the merge thread begins to
> initialize. As soon as the merge is complete, the calling thread once
> again continues.
> At first I thought that maybe the entire process was blocked by the
> merge replication ActiveX object, but the timer event continues to fire.
> So it appears that only the calling thread is blocked.
> Any ideas?
> Thanks,
> Jeff
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.replication:56175
Hilary Cotter wrote:
> are you using an MSDE database? MSDE is throttled at 8 simultaneous
> workloads.
No, SQL Server 2000 on both publisher/distributor and subscriber.
But besides that, even if it were being throttled - it shouldn't block
the other thread from continue to process non-SQL Server instructions.
Thanks,
Jeff
|||Can you find out what the merge replication process is doing when it blocks
the other process?
If it is applying a snapshot you can expect blocking. For the type of
singleton transactions that merge replication uses you should not
experieince this level of locking, unless perhaps you are updating columns
which have indexes on them.
You might want to run sp_lock on the client to get an idea of what sort of
locks the replication process is applying.
You might also want to see if perhaps the locking is occuring due to your
code. For instance, I do not experience any locking when using databases
which are part of a merge publication or subscription when the merge
replication agents are running and when I am running the replication process
via tsql or em. This will rule out replication and give you a better window
into what is occuring.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:7b12d.297$DY.106@.chiapp18.algx.net...
> Hilary Cotter wrote:
> No, SQL Server 2000 on both publisher/distributor and subscriber.
> But besides that, even if it were being throttled - it shouldn't block
> the other thread from continue to process non-SQL Server instructions.
> Thanks,
> Jeff
|||Hilary Cotter wrote:
> Can you find out what the merge replication process is doing when it blocks
> the other process?
Yes: It blocks the calling thread as soon as I get the "Initializing"
status until I get the "Complete" status.

> If it is applying a snapshot you can expect blocking. For the type of
> singleton transactions that merge replication uses you should not
> experieince this level of locking, unless perhaps you are updating columns
> which have indexes on them.
> You might want to run sp_lock on the client to get an idea of what sort of
> locks the replication process is applying.
It sounds like you are describing a possible lock on the database. This
is not what is happening. What I am describing is my main thread in my
application is getting blocked until the merge thread (separate from the
main thread) is complete. The main thread is not doing any database
work at all when it gets blocked by the merge thread.

> You might also want to see if perhaps the locking is occuring due to your
> code. For instance, I do not experience any locking when using databases
> which are part of a merge publication or subscription when the merge
> replication agents are running and when I am running the replication process
> via tsql or em. This will rule out replication and give you a better window
> into what is occuring.
I am sure that if I were to run the replication from tsql or em it would
not block - because of my above paragraph. It's the actual ActiveX COM
replication object that is blocking my main thread for some reason. And
like I said in my original post, I am positive that the main thread is
properly spawning a new thread for the merge.
Thanks for the ideas!
Jeff
|||If you want to send the code to me, offline I'll try to repro it.
Otherwise I suggest you open a support incident with PSS.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeff Hedlund" <jeff.hedlund_NOSPAM_@._NOSPAM_elsym.com> wrote in message
news:K%g2d.520$DY.90@.chiapp18.algx.net...[vbcol=seagreen]
> Hilary Cotter wrote:
blocks[vbcol=seagreen]
> Yes: It blocks the calling thread as soon as I get the "Initializing"
> status until I get the "Complete" status.
columns[vbcol=seagreen]
of[vbcol=seagreen]
> It sounds like you are describing a possible lock on the database. This
> is not what is happening. What I am describing is my main thread in my
> application is getting blocked until the merge thread (separate from the
> main thread) is complete. The main thread is not doing any database
> work at all when it gets blocked by the merge thread.
your[vbcol=seagreen]
process[vbcol=seagreen]
window
> I am sure that if I were to run the replication from tsql or em it would
> not block - because of my above paragraph. It's the actual ActiveX COM
> replication object that is blocking my main thread for some reason. And
> like I said in my original post, I am positive that the main thread is
> properly spawning a new thread for the merge.
> Thanks for the ideas!
> Jeff
|||Jeff Hedlund wrote:
> The problem is that the merge replication appears to block the calling
> thread as soon as replication begins.
FYI - I have found the source of my problem. When using BeginInvoke()
to create a separate thread for the replication object, VB.NET uses a
thread from the thread pool.
The thread pool operates with the MTA apartment state. The SQL
replication ActiveX object apparently uses STA, so it was blocking the
MTA apartment while it ran. Make sure I used an STA thread to run the
SQL replication object on solved the blocking problem.
Thanks,
Jeff
sql

Asynchronous / non-blocking trigger

Is it possible to create an asynchronous / non-blocking trigger in SQL
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
Jon
Triggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>
|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway.
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>
|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CreatingTSQLAsynchronousTriggers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>

Asynchronous / non-blocking trigger

Is it possible to create an asynchronous / non-blocking trigger in SQL
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
JonTriggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway. :)
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>> Is it possible to create an asynchronous / non-blocking trigger in SQL
>> Server 2005 that will allow operations to continue asynchronously while
>> the trigger can still read the in-memory "inserted" and "deleted" virtual
>> tables?
>> Thanks,
>> Jon
>|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CreatingTSQLAsynchronousTriggers.aspx
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway. :)
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>> Triggers always execute synchronously in the context of a transaction.
>> If you need to invoke an asynchronous process from within a trigger,
>> consider using Service Broker. Asynchronous triggers are exactly the
>> functionality that Service Broker provides. See the Books Online for
>> details.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
>> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>> Is it possible to create an asynchronous / non-blocking trigger in SQL
>> Server 2005 that will allow operations to continue asynchronously while
>> the trigger can still read the in-memory "inserted" and "deleted"
>> virtual tables?
>> Thanks,
>> Jon
>>
>

Asynchronous / non-blocking trigger

Is it possible to create an asynchronous / non-blocking trigger in SQL
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
JonTriggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway.
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/s...br />
ers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>