If you are interested in calling the IDispatch-based COM component in SQL server, you can query the usage of storage procedures such as sp_OACreate, sp_OAMethod, and sp_OADestroy in SQL help. The following is some SQL code in a small project of text message alarm. the alarm information is inserted into SQL-server through various software, then the component is called through the trigger, and the short SQL Server is sent.
SQL server can call the COM component based on IDispatch
If you are interested, you can check the usage of storage procedures such as sp_OACreate, sp_OAMethod, and sp_OADestroy in SQL help.
The following is some SQL code in a small project of text message alarm. the alarm information is inserted into SQL-server through various software, and then the component is called through the trigger, and sends a text message to the specified mobile phone to enable automatic alarm.
// Test the 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 to send a text message */
Begin
EXEC @ hr = sp_OACreate '{26850d424862c-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 okay, but it can also be said to be a big problem.
The key lies in the SendMsg method of the component. why? I can introduce several issues that I have encountered.
There are two main points
1st: whether the COM component is an in-process component, and whether the internal code of the component is strong enough
2nd: create and destroy components and component methods as quickly as possible
Let me explain the above two points.
If the COM component is an in-process component, it means that the component is loaded by SQL-server. if the code is not robust enough, it will be suspended and crashed due to the component itself, it will directly affect the entire SQL-server, so the situation is very serious. this kind of error is enough to happen once, if it happens to blow up on the customer's face ......
The solution is as follows: first, make sure that your component code is strong enough to be no longer strong. In addition, try to prevent components from being in the process. if the component is already a DLL, it should be handed over to COM Catalog for management. This solves the SQL server issue caused by component crashes and hangs.
Second, you need to make the creation, method call, and destruction of your components so fast that they can be completed almost instantly. Otherwise, if a massive amount of data comes in and your trigger cannot be reflected, your data may be rolled back, and the data is very important, then you will be waiting for the Fire to L 5555 ~~~~~
If you have to use this method, you need to complete the above 2 points, and it is best to enable the COM object pool. If the method takes a long time to return and cannot be optimized, remove the methods and execute these actions in another program so that the COM method can return immediately.
All in all, in a word, in the system integration, if a few developers did not discuss the interface, and are using a SQL-SERVER database, then the operation of such data, it seems that using this method is the best remedy.
I personally feel a little arrogant, although very sharp, but it will inevitably hurt myself
Gentle poison 2005-12-30