Extending SQL Server functionality with COM Auto Service

SQL Server's T-SQL programming language is powerful in data storage and recovery, but is less functional in terms of interacting with systems outside of the SQL Server database. However, we can overcome this limitation with SQL Server's built-in COM automation environment, which enables users to automate COM objects in stored procedures.

With 7 extended stored procedures in SQL Server 7.0 and SQL Server 6.5, you can extend SQL Server functionality through off-the-shelf COM objects, such as your own or office. SQL Server also provides an error-handling mechanism that can write error messages to the SQL Agent log. With COM automation, you can also set SQL Server 6.5 with Microsoft's Exchange Server, Index Server, and other software that can be controlled by COM automation operations to introduce an object automation environment. It was originally called OLE. The name of the object operation changed over time, but the automatic operating environment in SQL Server 7.0 was unchanged compared to SQL Server 6.5, so Microsoft's documentation still referred to this feature as an OLE operation rather than a COM operation, in the lookup SQL This is especially true when the server online brochure (BOL) is in use. Let's talk about how to use SQL Server's COM automated operation stored procedures and how COM automated operations can help us solve real-world programming problems.

Details of COM operations

Table 1 lists the 7 extended stored procedures for COM operations in SQL Server. When automating a COM object, you need to first establish an instance of a COM object by calling sp_OACreate, and then complete the tasks that need to be done through a series of sp_OAGetProperty, sp_OASetProperty, and sp_OAMethod calls , you also need to call sp_OADestroy to release the object after you complete the operation on the COM object. When studying each stored procedure in detail, note two important questions.

First, you must provide all the parameters of the call, because the automatic action feature does not support well-known parameters, and if you cannot use a detailed parameter, you need to pass a null as a placeholder, and second, each call returns an HRESULT of the integer type, which is 0 if the call succeeds. Later, we'll discuss how to handle the return value as non.