About calling COM components in Sql-server

Server

IDispatch COM components can be invoked in the Sql-server

Interested in the use of SQL Help to check the sp_OACreate, sp_OAMethod, sp_OADestroy and other stored procedures.

Below is I in a short message alarm in a small project of some SQL code, alarm information through various types of software inserted into the Sql-server, and then through the trigger call components, and send SMS to the designated mobile phone up to achieve automatic alarm function.

To test a database trigger

ALTER TRIGGER Message_trigger1

On Dbo.message

For INSERT/*, UPDATE, DELETE/*

As

/* IF UPDATE (column_name) ... */

Begin

declare @PhoneNum nvarchar (50)

declare @Content nvarchar (140)

declare @MessageId nvarchar (70)

DECLARE @index int

DECLARE @hr int

DECLARE @object int

Select @PhoneNum = phone_num, @Content = Content, @MessageId = message_id from inserted

Select @index = 1

/* Call COM Send SMS * *

Begin

EXEC @hr = sp_OACreate ' {26850dda-862c-44ff-9232-282937f2ca4b} ', @object out

If @hr = 0

Begin

EXEC @hr =sp_oamethod @object, ' sendmsg ', NULL, @Content, @PhoneNum, @index, @MessageId

exec sp_OADestroy @object

End

End

End

The code here can be said to be no problem, but it can be said that there is a big problem.

The key is the Sendmsg method of the component, why? I can cite a few of the problems I actually encountered to make specific instructions.

The main thing is 2.

1th: Whether this COM component is an in-process component, and whether the component's internal code is strong enough

2nd: Create components and destroy components and component methods to the maximum possible speed

I'll make a note of the above two points

If the COM component is an in-process component, which means that the component is sql-server loaded, and if the code is not robust enough, the suspension caused by the component itself, the crash, will directly affect the entire sql-server, then the situation is very serious, this error, Once is enough to kill you, if it happens to explode on the client's face ...

The only way to solve this problem is to make sure that your component code is strong enough to be strong until you're stronger. Also, try to make the component not in the process, if it is already a DLL, then give COM + catalog to manage. This solves the problem of sql-server caused by a component crash suspension.

Second, the creation, method invocation, and destruction of your components can be done quickly and quickly, almost instantaneously. Otherwise, if there is a huge amount of data coming in, and your trigger is too late to reflect, then your data can be rolled back, and the data is very important, then you wait to be fire L 5555~~~~~

If this is the way it is, it is necessary to complete the 2 points I mentioned above, and it is best to enable COM + object pooling. If the method takes a long time to return and cannot be optimized, then move the methods out, do them in another program, and let the COM method return immediately.

All in all, in short, in the system integration, if several developers do not negotiate the interface, and all the use of Sql-server database, then the operation of such data, it seems that this method is the best remedy.

Personally feel a little sword walk slant feeling, although very sharp, but inevitably hurt himself