Is triggers multi-threaded?

Hi,
I need to call sp_OACreate inside an INSERT trigger to create COM component
that's developed in VB6 (STA), my question is if there are many records
inserted at very short period of time, is it multi-threaded (sp_OACreate -
MTA) from trigger point of view? If this is true, then I have to use the MTA
component in trigger.
Thanks!
John
John Lee wrote:
> Hi,
> I need to call sp_OACreate inside an INSERT trigger to create COM component
> that's developed in VB6 (STA), my question is if there are many records
> inserted at very short period of time, is it multi-threaded (sp_OACreate -
> MTA) from trigger point of view? If this is true, then I have to use the MTA
> component in trigger.
> Thanks!
> John
Creating a COM component in a trigger is almost certainly an extremely
bad idea. There is no obvious way that your external code can be made
part of a SQL Server transaction. How will you roll-back the effect of
your VB code if a nested transaction is rolled back? If the external
code is non-transactional in nature then there is little sense in
putting it in a trigger and doing so can only hurt performance and
reliability. Also, in my limited experience the sp_OA procs will not
scale and COM will often leak memory.
Invoke your code from outside SQL Server is my suggestion - from a job
or some other server-side process for example. If you must do it in the
database then use a stored procedure rather than a trigger. In SQL
Server 2005 you also have the option of using a CLR proc.
To answer your original question. Triggers are "multi-threaded" in the
sense that the same trigger can fire simultaneously for several
different connections. Each of those connections will attempt to create
another instance of your COM object.
If multiple rows are updated in a single statement then the trigger
will only fire ONCE. It will NOT fire once for each row. This is
another reason why not to use a trigger to invoke external code. In
order to call your COM component for each row you will probably have to
use a cursor to cycle through each row in the INSERTED virtual table
(INSERTED is available only in triggers and contains the rows that were
inserted or updated). Cursors are best avoided in most circumstances
and especially so in triggers.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1159123193.138245.268530@.b28g2000cwb.googlegr oups.com...
> John Lee wrote:
> Creating a COM component in a trigger is almost certainly an extremely
> bad idea. There is no obvious way that your external code can be made
> part of a SQL Server transaction. How will you roll-back the effect of
> your VB code if a nested transaction is rolled back? If the external
> code is non-transactional in nature then there is little sense in
> putting it in a trigger and doing so can only hurt performance and
> reliability. Also, in my limited experience the sp_OA procs will not
> scale and COM will often leak memory.
> Invoke your code from outside SQL Server is my suggestion - from a job
> or some other server-side process for example. If you must do it in the
> database then use a stored procedure rather than a trigger. In SQL
> Server 2005 you also have the option of using a CLR proc.
> To answer your original question. Triggers are "multi-threaded" in the
> sense that the same trigger can fire simultaneously for several
> different connections. Each of those connections will attempt to create
> another instance of your COM object.
> If multiple rows are updated in a single statement then the trigger
> will only fire ONCE. It will NOT fire once for each row. This is
> another reason why not to use a trigger to invoke external code. In
> order to call your COM component for each row you will probably have to
> use a cursor to cycle through each row in the INSERTED virtual table
> (INSERTED is available only in triggers and contains the rows that were
> inserted or updated). Cursors are best avoided in most circumstances
> and especially so in triggers.
>
Ditto all of that. Just adding in COM programming an STA component can be
use by a multi-threaded client. The restriction is that each object
instance can be accessed only by the thread that created it. In a trigger
or procedure the COM component is created (SP_OACreate), used (SP_OAMethod)
and destroyed (SP_OADestroy) all in the same thread. So a plain VB6 STA COM
Component should work fine.
In addition to David's reasons why this is a poor idea, you should generally
avoid doing anything expensive in a trigger. That includes any kind of
network communication or file IO, regardless of whether the code is VB6 COM
or TSQL or CLR code in 2005.
David
|||Hi David
STAs can certainly be launched from MTAs but the bigger problem with VB6 COM
components is that they use TLS which isn't designed to work with SQL
Server's UMS. Under load, this can lead to various possible undesirable
conditions such as inconsistent results, crashes & hung UMS schedulers (at
least in SQL2K).
If the original poster really HAS to create a component under a trigger, my
advice is to use sp_OACreate only if the trigger activity is low (as
developing in VB does have its advantages in terms of productivity). If the
trigger activity is high (meaning it has a high probability of being heavily
pre-empted & re-scheduled), consider either a different architecture
(asynch, MQ etc) or write an extended stored proc, perhaps with delphi or c.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1159123193.138245.268530@.b28g2000cwb.googlegr oups.com...
> Ditto all of that. Just adding in COM programming an STA component can be
> use by a multi-threaded client. The restriction is that each object
> instance can be accessed only by the thread that created it. In a trigger
> or procedure the COM component is created (SP_OACreate), used
> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
> plain VB6 STA COM Component should work fine.
> In addition to David's reasons why this is a poor idea, you should
> generally avoid doing anything expensive in a trigger. That includes any
> kind of network communication or file IO, regardless of whether the code
> is VB6 COM or TSQL or CLR code in 2005.
> David
|||Thanks very much for your reply - David P, David B and Greg!!!
I totally agree with you all regarding the bad things using triggers doing
heavy lifting job ... BUT here is the business problem I have to solve:
We need to sync some data from SQL server 2000 to CRM in near realtime (< 5
minutes)
so we added triggers to around 10 tables, inside those triggers, we only
write 4 pieces of info to ChangeLog table
1. option 1 - write a service to pull ChangeLog table every 2 minutes
2. option 2 - create a trigger on this table to write out a text file to
notify the service to do the sync work
I also developed an extended stored proc to write file out and this seems
twice as fast as the sp_OACreate.
sp_OACreate has two issues to me:
1. late binding - because we are using progID to create the object
2. possible MTA calling into STA issue - if triggers side are multiple
threaded, then calling into STA will make it as serialized operation.
Thanks!
John
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi David
> STAs can certainly be launched from MTAs but the bigger problem with VB6
> COM components is that they use TLS which isn't designed to work with SQL
> Server's UMS. Under load, this can lead to various possible undesirable
> conditions such as inconsistent results, crashes & hung UMS schedulers (at
> least in SQL2K).
> If the original poster really HAS to create a component under a trigger,
> my advice is to use sp_OACreate only if the trigger activity is low (as
> developing in VB does have its advantages in terms of productivity). If
> the trigger activity is high (meaning it has a high probability of being
> heavily pre-empted & re-scheduled), consider either a different
> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
> with delphi or c.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
|||A much safer and more efficient way to do this would be to have your trigger
write to staging tables and have an external process read those tables once
a minute or so. In SQL Server 2005 you could use the Service Broker to do
this in a scaleable and asynchronous way.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Lee" <pursca@.newsgroups.nospam> wrote in message
news:O$27%237M4GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Thanks very much for your reply - David P, David B and Greg!!!
> I totally agree with you all regarding the bad things using triggers doing
> heavy lifting job ... BUT here is the business problem I have to solve:
> We need to sync some data from SQL server 2000 to CRM in near realtime (<
> 5 minutes)
> so we added triggers to around 10 tables, inside those triggers, we only
> write 4 pieces of info to ChangeLog table
> 1. option 1 - write a service to pull ChangeLog table every 2 minutes
> 2. option 2 - create a trigger on this table to write out a text file to
> notify the service to do the sync work
> I also developed an extended stored proc to write file out and this seems
> twice as fast as the sp_OACreate.
> sp_OACreate has two issues to me:
> 1. late binding - because we are using progID to create the object
> 2. possible MTA calling into STA issue - if triggers side are multiple
> threaded, then calling into STA will make it as serialized operation.
> Thanks!
> John
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
>

Is triggers multi-threaded?

Hi,
I need to call sp_OACreate inside an INSERT trigger to create COM component
that's developed in VB6 (STA), my question is if there are many records
inserted at very short period of time, is it multi-threaded (sp_OACreate -
MTA) from trigger point of view? If this is true, then I have to use the MTA
component in trigger.
Thanks!
JohnJohn Lee wrote:
> Hi,
> I need to call sp_OACreate inside an INSERT trigger to create COM component
> that's developed in VB6 (STA), my question is if there are many records
> inserted at very short period of time, is it multi-threaded (sp_OACreate -
> MTA) from trigger point of view? If this is true, then I have to use the MTA
> component in trigger.
> Thanks!
> John
Creating a COM component in a trigger is almost certainly an extremely
bad idea. There is no obvious way that your external code can be made
part of a SQL Server transaction. How will you roll-back the effect of
your VB code if a nested transaction is rolled back? If the external
code is non-transactional in nature then there is little sense in
putting it in a trigger and doing so can only hurt performance and
reliability. Also, in my limited experience the sp_OA procs will not
scale and COM will often leak memory.
Invoke your code from outside SQL Server is my suggestion - from a job
or some other server-side process for example. If you must do it in the
database then use a stored procedure rather than a trigger. In SQL
Server 2005 you also have the option of using a CLR proc.
To answer your original question. Triggers are "multi-threaded" in the
sense that the same trigger can fire simultaneously for several
different connections. Each of those connections will attempt to create
another instance of your COM object.
If multiple rows are updated in a single statement then the trigger
will only fire ONCE. It will NOT fire once for each row. This is
another reason why not to use a trigger to invoke external code. In
order to call your COM component for each row you will probably have to
use a cursor to cycle through each row in the INSERTED virtual table
(INSERTED is available only in triggers and contains the rows that were
inserted or updated). Cursors are best avoided in most circumstances
and especially so in triggers.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use the
>> MTA
>> component in trigger.
>> Thanks!
>> John
> Creating a COM component in a trigger is almost certainly an extremely
> bad idea. There is no obvious way that your external code can be made
> part of a SQL Server transaction. How will you roll-back the effect of
> your VB code if a nested transaction is rolled back? If the external
> code is non-transactional in nature then there is little sense in
> putting it in a trigger and doing so can only hurt performance and
> reliability. Also, in my limited experience the sp_OA procs will not
> scale and COM will often leak memory.
> Invoke your code from outside SQL Server is my suggestion - from a job
> or some other server-side process for example. If you must do it in the
> database then use a stored procedure rather than a trigger. In SQL
> Server 2005 you also have the option of using a CLR proc.
> To answer your original question. Triggers are "multi-threaded" in the
> sense that the same trigger can fire simultaneously for several
> different connections. Each of those connections will attempt to create
> another instance of your COM object.
> If multiple rows are updated in a single statement then the trigger
> will only fire ONCE. It will NOT fire once for each row. This is
> another reason why not to use a trigger to invoke external code. In
> order to call your COM component for each row you will probably have to
> use a cursor to cycle through each row in the INSERTED virtual table
> (INSERTED is available only in triggers and contains the rows that were
> inserted or updated). Cursors are best avoided in most circumstances
> and especially so in triggers.
>
Ditto all of that. Just adding in COM programming an STA component can be
use by a multi-threaded client. The restriction is that each object
instance can be accessed only by the thread that created it. In a trigger
or procedure the COM component is created (SP_OACreate), used (SP_OAMethod)
and destroyed (SP_OADestroy) all in the same thread. So a plain VB6 STA COM
Component should work fine.
In addition to David's reasons why this is a poor idea, you should generally
avoid doing anything expensive in a trigger. That includes any kind of
network communication or file IO, regardless of whether the code is VB6 COM
or TSQL or CLR code in 2005.
David|||Hi David
STAs can certainly be launched from MTAs but the bigger problem with VB6 COM
components is that they use TLS which isn't designed to work with SQL
Server's UMS. Under load, this can lead to various possible undesirable
conditions such as inconsistent results, crashes & hung UMS schedulers (at
least in SQL2K).
If the original poster really HAS to create a component under a trigger, my
advice is to use sp_OACreate only if the trigger activity is low (as
developing in VB does have its advantages in terms of productivity). If the
trigger activity is high (meaning it has a high probability of being heavily
pre-empted & re-scheduled), consider either a different architecture
(asynch, MQ etc) or write an extended stored proc, perhaps with delphi or c.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use the
>> MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
> Ditto all of that. Just adding in COM programming an STA component can be
> use by a multi-threaded client. The restriction is that each object
> instance can be accessed only by the thread that created it. In a trigger
> or procedure the COM component is created (SP_OACreate), used
> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
> plain VB6 STA COM Component should work fine.
> In addition to David's reasons why this is a poor idea, you should
> generally avoid doing anything expensive in a trigger. That includes any
> kind of network communication or file IO, regardless of whether the code
> is VB6 COM or TSQL or CLR code in 2005.
> David|||Thanks very much for your reply - David P, David B and Greg!!!
I totally agree with you all regarding the bad things using triggers doing
heavy lifting job ... BUT here is the business problem I have to solve:
We need to sync some data from SQL server 2000 to CRM in near realtime (< 5
minutes)
so we added triggers to around 10 tables, inside those triggers, we only
write 4 pieces of info to ChangeLog table
1. option 1 - write a service to pull ChangeLog table every 2 minutes
2. option 2 - create a trigger on this table to write out a text file to
notify the service to do the sync work
I also developed an extended stored proc to write file out and this seems
twice as fast as the sp_OACreate.
sp_OACreate has two issues to me:
1. late binding - because we are using progID to create the object
2. possible MTA calling into STA issue - if triggers side are multiple
threaded, then calling into STA will make it as serialized operation.
Thanks!
John
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi David
> STAs can certainly be launched from MTAs but the bigger problem with VB6
> COM components is that they use TLS which isn't designed to work with SQL
> Server's UMS. Under load, this can lead to various possible undesirable
> conditions such as inconsistent results, crashes & hung UMS schedulers (at
> least in SQL2K).
> If the original poster really HAS to create a component under a trigger,
> my advice is to use sp_OACreate only if the trigger activity is low (as
> developing in VB does have its advantages in terms of productivity). If
> the trigger activity is high (meaning it has a high probability of being
> heavily pre-empted & re-scheduled), consider either a different
> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
> with delphi or c.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use
>> the MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
>>
>> Ditto all of that. Just adding in COM programming an STA component can
>> be use by a multi-threaded client. The restriction is that each object
>> instance can be accessed only by the thread that created it. In a
>> trigger or procedure the COM component is created (SP_OACreate), used
>> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
>> plain VB6 STA COM Component should work fine.
>> In addition to David's reasons why this is a poor idea, you should
>> generally avoid doing anything expensive in a trigger. That includes any
>> kind of network communication or file IO, regardless of whether the code
>> is VB6 COM or TSQL or CLR code in 2005.
>> David
>|||A much safer and more efficient way to do this would be to have your trigger
write to staging tables and have an external process read those tables once
a minute or so. In SQL Server 2005 you could use the Service Broker to do
this in a scaleable and asynchronous way.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Lee" <pursca@.newsgroups.nospam> wrote in message
news:O$27%237M4GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Thanks very much for your reply - David P, David B and Greg!!!
> I totally agree with you all regarding the bad things using triggers doing
> heavy lifting job ... BUT here is the business problem I have to solve:
> We need to sync some data from SQL server 2000 to CRM in near realtime (<
> 5 minutes)
> so we added triggers to around 10 tables, inside those triggers, we only
> write 4 pieces of info to ChangeLog table
> 1. option 1 - write a service to pull ChangeLog table every 2 minutes
> 2. option 2 - create a trigger on this table to write out a text file to
> notify the service to do the sync work
> I also developed an extended stored proc to write file out and this seems
> twice as fast as the sp_OACreate.
> sp_OACreate has two issues to me:
> 1. late binding - because we are using progID to create the object
> 2. possible MTA calling into STA issue - if triggers side are multiple
> threaded, then calling into STA will make it as serialized operation.
> Thanks!
> John
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Hi David
>> STAs can certainly be launched from MTAs but the bigger problem with VB6
>> COM components is that they use TLS which isn't designed to work with SQL
>> Server's UMS. Under load, this can lead to various possible undesirable
>> conditions such as inconsistent results, crashes & hung UMS schedulers
>> (at least in SQL2K).
>> If the original poster really HAS to create a component under a trigger,
>> my advice is to use sp_OACreate only if the trigger activity is low (as
>> developing in VB does have its advantages in terms of productivity). If
>> the trigger activity is high (meaning it has a high probability of being
>> heavily pre-empted & re-scheduled), consider either a different
>> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
>> with delphi or c.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>>
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
>> John Lee wrote:
>> Hi,
>> I need to call sp_OACreate inside an INSERT trigger to create COM
>> component
>> that's developed in VB6 (STA), my question is if there are many
>> records
>> inserted at very short period of time, is it multi-threaded
>> (sp_OACreate -
>> MTA) from trigger point of view? If this is true, then I have to use
>> the MTA
>> component in trigger.
>> Thanks!
>> John
>> Creating a COM component in a trigger is almost certainly an extremely
>> bad idea. There is no obvious way that your external code can be made
>> part of a SQL Server transaction. How will you roll-back the effect of
>> your VB code if a nested transaction is rolled back? If the external
>> code is non-transactional in nature then there is little sense in
>> putting it in a trigger and doing so can only hurt performance and
>> reliability. Also, in my limited experience the sp_OA procs will not
>> scale and COM will often leak memory.
>> Invoke your code from outside SQL Server is my suggestion - from a job
>> or some other server-side process for example. If you must do it in the
>> database then use a stored procedure rather than a trigger. In SQL
>> Server 2005 you also have the option of using a CLR proc.
>> To answer your original question. Triggers are "multi-threaded" in the
>> sense that the same trigger can fire simultaneously for several
>> different connections. Each of those connections will attempt to create
>> another instance of your COM object.
>> If multiple rows are updated in a single statement then the trigger
>> will only fire ONCE. It will NOT fire once for each row. This is
>> another reason why not to use a trigger to invoke external code. In
>> order to call your COM component for each row you will probably have to
>> use a cursor to cycle through each row in the INSERTED virtual table
>> (INSERTED is available only in triggers and contains the rows that were
>> inserted or updated). Cursors are best avoided in most circumstances
>> and especially so in triggers.
>>
>> Ditto all of that. Just adding in COM programming an STA component can
>> be use by a multi-threaded client. The restriction is that each object
>> instance can be accessed only by the thread that created it. In a
>> trigger or procedure the COM component is created (SP_OACreate), used
>> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
>> plain VB6 STA COM Component should work fine.
>> In addition to David's reasons why this is a poor idea, you should
>> generally avoid doing anything expensive in a trigger. That includes
>> any kind of network communication or file IO, regardless of whether the
>> code is VB6 COM or TSQL or CLR code in 2005.
>> David
>>
>sql

Is triggers multi-threaded?

Hi,
I need to call sp_OACreate inside an INSERT trigger to create COM component
that's developed in VB6 (STA), my question is if there are many records
inserted at very short period of time, is it multi-threaded (sp_OACreate -
MTA) from trigger point of view? If this is true, then I have to use the MTA
component in trigger.
Thanks!
JohnJohn Lee wrote:
> Hi,
> I need to call sp_OACreate inside an INSERT trigger to create COM componen
t
> that's developed in VB6 (STA), my question is if there are many records
> inserted at very short period of time, is it multi-threaded (sp_OACreate -
> MTA) from trigger point of view? If this is true, then I have to use the M
TA
> component in trigger.
> Thanks!
> John
Creating a COM component in a trigger is almost certainly an extremely
bad idea. There is no obvious way that your external code can be made
part of a SQL Server transaction. How will you roll-back the effect of
your VB code if a nested transaction is rolled back? If the external
code is non-transactional in nature then there is little sense in
putting it in a trigger and doing so can only hurt performance and
reliability. Also, in my limited experience the sp_OA procs will not
scale and COM will often leak memory.
Invoke your code from outside SQL Server is my suggestion - from a job
or some other server-side process for example. If you must do it in the
database then use a stored procedure rather than a trigger. In SQL
Server 2005 you also have the option of using a CLR proc.
To answer your original question. Triggers are "multi-threaded" in the
sense that the same trigger can fire simultaneously for several
different connections. Each of those connections will attempt to create
another instance of your COM object.
If multiple rows are updated in a single statement then the trigger
will only fire ONCE. It will NOT fire once for each row. This is
another reason why not to use a trigger to invoke external code. In
order to call your COM component for each row you will probably have to
use a cursor to cycle through each row in the INSERTED virtual table
(INSERTED is available only in triggers and contains the rows that were
inserted or updated). Cursors are best avoided in most circumstances
and especially so in triggers.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> John Lee wrote:
> Creating a COM component in a trigger is almost certainly an extremely
> bad idea. There is no obvious way that your external code can be made
> part of a SQL Server transaction. How will you roll-back the effect of
> your VB code if a nested transaction is rolled back? If the external
> code is non-transactional in nature then there is little sense in
> putting it in a trigger and doing so can only hurt performance and
> reliability. Also, in my limited experience the sp_OA procs will not
> scale and COM will often leak memory.
> Invoke your code from outside SQL Server is my suggestion - from a job
> or some other server-side process for example. If you must do it in the
> database then use a stored procedure rather than a trigger. In SQL
> Server 2005 you also have the option of using a CLR proc.
> To answer your original question. Triggers are "multi-threaded" in the
> sense that the same trigger can fire simultaneously for several
> different connections. Each of those connections will attempt to create
> another instance of your COM object.
> If multiple rows are updated in a single statement then the trigger
> will only fire ONCE. It will NOT fire once for each row. This is
> another reason why not to use a trigger to invoke external code. In
> order to call your COM component for each row you will probably have to
> use a cursor to cycle through each row in the INSERTED virtual table
> (INSERTED is available only in triggers and contains the rows that were
> inserted or updated). Cursors are best avoided in most circumstances
> and especially so in triggers.
>
Ditto all of that. Just adding in COM programming an STA component can be
use by a multi-threaded client. The restriction is that each object
instance can be accessed only by the thread that created it. In a trigger
or procedure the COM component is created (SP_OACreate), used (SP_OAMethod)
and destroyed (SP_OADestroy) all in the same thread. So a plain VB6 STA COM
Component should work fine.
In addition to David's reasons why this is a poor idea, you should generally
avoid doing anything expensive in a trigger. That includes any kind of
network communication or file IO, regardless of whether the code is VB6 COM
or TSQL or CLR code in 2005.
David|||Hi David
STAs can certainly be launched from MTAs but the bigger problem with VB6 COM
components is that they use TLS which isn't designed to work with SQL
Server's UMS. Under load, this can lead to various possible undesirable
conditions such as inconsistent results, crashes & hung UMS schedulers (at
least in SQL2K).
If the original poster really HAS to create a component under a trigger, my
advice is to use sp_OACreate only if the trigger activity is low (as
developing in VB does have its advantages in terms of productivity). If the
trigger activity is high (meaning it has a high probability of being heavily
pre-empted & re-scheduled), consider either a different architecture
(asynch, MQ etc) or write an extended stored proc, perhaps with delphi or c.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1159123193.138245.268530@.b28g2000cwb.googlegroups.com...
> Ditto all of that. Just adding in COM programming an STA component can be
> use by a multi-threaded client. The restriction is that each object
> instance can be accessed only by the thread that created it. In a trigger
> or procedure the COM component is created (SP_OACreate), used
> (SP_OAMethod) and destroyed (SP_OADestroy) all in the same thread. So a
> plain VB6 STA COM Component should work fine.
> In addition to David's reasons why this is a poor idea, you should
> generally avoid doing anything expensive in a trigger. That includes any
> kind of network communication or file IO, regardless of whether the code
> is VB6 COM or TSQL or CLR code in 2005.
> David|||Thanks very much for your reply - David P, David B and Greg!!!
I totally agree with you all regarding the bad things using triggers doing
heavy lifting job ... BUT here is the business problem I have to solve:
We need to sync some data from SQL server 2000 to CRM in near realtime (< 5
minutes)
so we added triggers to around 10 tables, inside those triggers, we only
write 4 pieces of info to ChangeLog table
1. option 1 - write a service to pull ChangeLog table every 2 minutes
2. option 2 - create a trigger on this table to write out a text file to
notify the service to do the sync work
I also developed an extended stored proc to write file out and this seems
twice as fast as the sp_OACreate.
sp_OACreate has two issues to me:
1. late binding - because we are using progID to create the object
2. possible MTA calling into STA issue - if triggers side are multiple
threaded, then calling into STA will make it as serialized operation.
Thanks!
John
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi David
> STAs can certainly be launched from MTAs but the bigger problem with VB6
> COM components is that they use TLS which isn't designed to work with SQL
> Server's UMS. Under load, this can lead to various possible undesirable
> conditions such as inconsistent results, crashes & hung UMS schedulers (at
> least in SQL2K).
> If the original poster really HAS to create a component under a trigger,
> my advice is to use sp_OACreate only if the trigger activity is low (as
> developing in VB does have its advantages in terms of productivity). If
> the trigger activity is high (meaning it has a high probability of being
> heavily pre-empted & re-scheduled), consider either a different
> architecture (asynch, MQ etc) or write an extended stored proc, perhaps
> with delphi or c.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uww$mBB4GHA.3840@.TK2MSFTNGP06.phx.gbl...
>|||A much safer and more efficient way to do this would be to have your trigger
write to staging tables and have an external process read those tables once
a minute or so. In SQL Server 2005 you could use the Service Broker to do
this in a scaleable and asynchronous way.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Lee" <pursca@.newsgroups.nospam> wrote in message
news:O$27%237M4GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Thanks very much for your reply - David P, David B and Greg!!!
> I totally agree with you all regarding the bad things using triggers doing
> heavy lifting job ... BUT here is the business problem I have to solve:
> We need to sync some data from SQL server 2000 to CRM in near realtime (<
> 5 minutes)
> so we added triggers to around 10 tables, inside those triggers, we only
> write 4 pieces of info to ChangeLog table
> 1. option 1 - write a service to pull ChangeLog table every 2 minutes
> 2. option 2 - create a trigger on this table to write out a text file to
> notify the service to do the sync work
> I also developed an extended stored proc to write file out and this seems
> twice as fast as the sp_OACreate.
> sp_OACreate has two issues to me:
> 1. late binding - because we are using progID to create the object
> 2. possible MTA calling into STA issue - if triggers side are multiple
> threaded, then calling into STA will make it as serialized operation.
> Thanks!
> John
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:eAzr0RD4GHA.600@.TK2MSFTNGP05.phx.gbl...
>

Is This Possible? Defaulting of a Dimension Atrribute

I have a dimension with one visible attribute, let's call it "Status." This attribute is binded to one column from a forecast fact table that stores monthly snapshots. This column simply has a 'Yes' or 'No' value for every record in the fact table.

For a specific customer and product combination in this fact table, this "Status" column could have started off having a value of 'No' a few months ago, and now has 'Yes' for the current month. In other words, it can have both possible values.

For example, let's assume the following forecast data for the Customer A and Product B combination:

Month | Forecast $ | Status
-
November 2006 | $5 | 'No'
December 2006 | $10 | 'No'
January 2007 | $10 | 'Yes'

This "Status" attribute is meant to be used at the Page Field level in Excel, and users would like to be able to see customer forecast data based on the "Status" being filtered for either 'Yes' or 'No'.

Now, all of our measures in the cube have the following MDX ((TAIL(EXISTING [Date].[Month].MEMBERS).ITEM(0).ITEM(0), [Measure]). This defaults the measures to the current month in a report unless a time frame, whether year(s) and/or month(s), is explicityly specified or filtered at the Page Field level.

Assuming a timeframe is not explicitly specified at the Page Field level and a user is looking at the forecast data for Customer A for Product B, this customer will show up regardless of what the "Status" Page Field is filtered for. I believe this is the case because it historically has had a record associated with both 'No' and 'Yes'. What does differ hough, is whether or not Forecast $ will populate or not.

For example, Status = 'Yes', then Forecast $ will show $10. If Status = 'No', then Forecast $ will now be NULL. Ideally, if Status = 'No', this customer would not even show up for the current month.

So is this possible? Hopefully what I'm asking makes sense.

Thanks!From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.|||

Mosha Pasumansky wrote:

From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like. Would it be easier if you took a look at my solution file to see what I may have missed? I've tried this numerous times with no luck.

Once I get this behavior resolved, I'll follow up with you with the MDX.

Thanks!|||

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.

You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?

Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.

|||

Mosha Pasumansky wrote:

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.

You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?

Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.

My fault. My mind must have been elsewhere when I posted. I did as you suggested with no luck. I set it to false from the default of true.

I did not follow your suggestion on the LastChild semiadditve aggregation type yet, as I wanted to focus on the above since I'm not too familiar with this LastChild thing. Now that I think about it, I assume this wouldn't be available to me in Standard edition? We're running Standard Edition.|||In fact, LastChild is the only semiadditive aggregation type available in Standard Edition - so you got lucky :)|||Anyone have any other thoughts? Is what I'm looking for even possible?

The IsAggregatable property when set to false, simply removed the 'All' member and didn't do what I am seeking.

is this call inner join as well ?

hi, if i have query like following

select * from o_customer,o_address
where
o_customer.name = o_address.custname

is it same as

select * from o_customer
inner join o_address on o_customer.name = o_address.custname

if there are same, which one is prefer in term of performance ..

thank you for guidanceHi

Former is old school, latter is current ANSI compliant.

I would imagine the optimiser would be able to equate the two but if it didn't then number one is likely to be a dog compared to the ANSI join

HTH|||BTW - I would use neither:

SELECT Col1, Col2, Col3
FROM o_customer
INNER JOIN o_address ON o_customer.name = o_address.custname
;)

Is these .net methods supported to call from SQL Stored Procedures?

Is these .net methods supported to call from SQL Stored Procedures?

public static int MultiplyMany(params int[] intArray)
{
int result = 1;

foreach(int now in intArray)
{
result *= now;
}

return result;
}

public static void TestOut(string name, out string firstName, out string lastName)
{
int spaceIndex = name.IndexOf(' ');
firstName = name.Substring(0, spaceIndex);
lastName = name.Substring(++spaceIndex);
}

What do you mean by "call from SQL SP"? You cannot call CLR methods directly from Transact-SQL. You can invoke them from T-SQL only by calling SQLCLR stored procedure/function/UDT method etc.

How do you expect to obtain an int[] array in SQL?

MultiplyMany method: you cannot make it as SQLCLR stored procedure, because there is no SQL type corresponding to int[]. If you will call it from another SQLCLR method - then, of course, you will be able to call it as you are already inside CLR.
If you are trying to multiply values from the column of the table it might be useful for you to look through the new SQL CLR User-Defined Aggregate.

TestOut method - you will have to create an SQLCLR stored procedure that will invoke this method to be able to call it from T-SQL. You can either add [SqlProcedure] attribute to the method so that SQLCLR SP will be created automatically by Visual Studio, or manually run the statement (don't forget to replace AssemblyName, YourNamespace and ClassName with correct ones ):

CREATE PROCEDURE dbo.TestOut
@.name nvarchar(250),
@.firstName nvarchar(125) output,
@.lastName nvarchar(125) output
AS
EXTERNAL NAME [AssemblyName].[YourNamespace.ClassName].[TestOut]

|||

I agree with you for TestOut.

For the 1st method I didn't think that I would send array object offcourse, but you didnot notice the params keyword.
My question for this method is: is it supported to send undetermined number of parameters that will be put into array in the .net?

Thank you for your help.

|||No, it is not possible.
Don't forget that each SQLCLR stored procedure has two parts: implementation on the CLR side and registration of the stored procedure on the SQL side. If the syntax of CREATE PROCEDURE statement doesn't allow undetermined number of parameters, you cannot expect it from SQLCLR stored procedure.|||

Thank you.

I am convinced.

Is there any way better than XP_CMDSHELL?

Hi ..
I want to Write in files or read from files
for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt .
and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ??

Is there any way better than XP_CMDSHELL for writing in or reading from MyFile.txt ??

thanksYou could BCP the SQL file into a temporary table and then execute the code through dynamic SQL, but that is pretty round-a-bout.

Is there any chance to execute or call PL/SQL SP in SQl Server 2000 T-sql

HI Group,

Is there any chance to execute or call PL/SQL Sp in SQL Server 2000 T-Sql ?
If any chance, please kindly sent steps .
Regards
ravi Shankar.

You can call a PL/SQL SP indirectly using an Oracle package wrapper procedure that returns a PL/SQL table as output which can be called via OPENQUERY. I have posted lot of examples that demonstrate this technique 3-4 years back in the public newsgroups. Use link below:

http://groups.google.com/group/microsoft.public.sqlserver.programming/search?group=microsoft.public.sqlserver.programming&q=oracle+stored+procedure+call+umachandar&qt_g=1

You will find more examples if you search for my name and appropriate keywords (oracle, linked servers, stored procedure etc…).