Showing posts with label operations. Show all posts
Showing posts with label operations. Show all posts

Wednesday, March 7, 2012

buffered trigger

Hallo i need to read/write data using triggers between two remote MSSQL2K servers, over an unstable connection.
Can trigger operations be buffered, so they are completed when connection is on?
Or should i make the triggers to write into local tables, then replicated in merge? (i would like to avoid this)

No. Trigger is part of the active transaction so if you have an unstable remote connection, I would suggest you queue your data into a worker queue table and process it later. I suggest you take a look at Service Broker in book online. It's designed for message queue/processing.

|||

This asyncronous read/write data can be resolved using SQL Service Broker a feature of SQL Server Express 2005 (that is free).

My ideea is :

1.The trigger of SQL2K1 server write the data in a Table1 of SQL2K1

2.SSSBroker get the data of Table1 in a Table2 from SSEXPRESServer in a Database1

3.From Table2 SSExpress copy data to SQL2K2

HTH

Sunday, February 12, 2012

BPA issues

BPA (SQL Best practices analyzer) issues
First off, great idea!
-Dead Logons should scavange better. The app locked up on a long operations and when I logged back in it told me I was already logged in. I went into the repository via EM and axed the row out of active_conns and everything was good again. Point being,
the average user might not know to do this and the app won't do anything if you can't logon.
-I get the following 2 lines in the Cursor usage detail when using FAST_FORWARD for my cursor in a sproc. According to BOL FAST_FORWARD = FORWARD_ONLY + READ_ONLY (which is NOT updatable)
-This cursor is declared as updatable, however it doesn't seem to be updated.
-This cursor does not specify explicit updatability information (FOR UPDATE clause or READ_ONLY clause).
-6 exceptions for resutls when running all the rules
Hi Sean
Thanks for your feedback. Some comments inlined.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean B" <anonymous@.discussions.microsoft.com> wrote in message
news:C250B186-9486-4BA6-877F-F7C928CCC6DC@.microsoft.com...
> BPA (SQL Best practices analyzer) issues
> First off, great idea!
> -Dead Logons should scavange better. The app locked up on a long
operations and when I logged back in it told me I was already logged in. I
went into the repository via EM and axed the row out of active_conns and
everything was good again. Point being, the average user might not know to
do this and the app won't do anything if you can't logon.
Yes, this was annoying. It has been fixed in later builds.

> -I get the following 2 lines in the Cursor usage detail when using
FAST_FORWARD for my cursor in a sproc. According to BOL FAST_FORWARD =
FORWARD_ONLY + READ_ONLY (which is NOT updatable)
> -This cursor is declared as updatable, however it doesn't seem to be
updated.
> -This cursor does not specify explicit updatability information (FOR
UPDATE clause or READ_ONLY clause).
Fixed this one too, making both, fast_forward and static be treated as read
only cursors.

> -6 exceptions for resutls when running all the rules
>
Do you have more information here?
Thanks for your feedback.
- Christian