The disadvantage of using the QuickOPC-COM over Interop is that you do not get all the benefits of .NET type system, and you end up doing lots of typecasting and other "adaptations" to make it work; but currently we do not have any other way to do it from inside SQL Server.
You basically need to refer to QuickOPC-COM reference documentation instead, and read what the types of returned objects are, and then typecast them accordingly. The situation is made worse that in order to support all possible usage scenarious from various COM controllers, we had to use a plain VARIANT as the type almost everywhere (becomes Object in .NET), and just document what's inside it. In the particular case of ReadMultipleItems, the result is an array (SAFEARRAY in VARIANT) of IDAVtqResult, so you need to cast it to .NET Array type first. There is an example under QuickOPC-COM (named ReadMultipleSqlCom, installed with the product) with similar stuff for SQL stored procedure, and looks like this - I have highligted the interesting parts:
// ReadCurrentDataCom: Reads data of multiple OPC items and returns them in a recordset.
// Instructions:
// 1. Create the QuickOPCExamplesdatabase using \Examples\MSSQL\QuickOPCExamples.sql.
// 2. Create the assymetric key and login by running \Examples\MSSQL\CreateExamplesKey.sql.
// 3. Create the interop assembly in the database by \Examples\MSSQL\CreateInteropAssembly.sql.
// 4. Build and deploy this project from Visual Studio.
// 5. Start the project (runs Test Scripts\Test.sql)
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Diagnostics;
using Microsoft.SqlServer.Server;
using OpcLabs.Interop.EasyOpcLib;
// ReSharper disable CheckNamespace
public class StoredProcedures
// ReSharper restore CheckNamespace
{
[SqlProcedure]
public static void ReadCurrentDataCom()
{
Trace.Assert(SqlContext.Pipe != null);
// Create EasyOPC-DA component.
// If you get "Access is denied" error:
// Start DCOMCNFG, and for applications "EasyOPC-COM 5.2" and "OPCKitServer", configure their security for the SQL
// Server process identity. Typically, this means adding Network Service or Local Service (depending on the account
// the SQL Server uses) to "Launch and Activation Permissions -> Local Launch, Local Activation", and to "Access
// Permissions -> Local Access".
IEasyDAClient easyDAClient = new EasyDAClient();
// Read the OPC items.
var itemIDs = new[] { "Simulation.Random", "Trends.Ramp (1 min)", "Trends.Sine (1 min)", "Simulation.Register_I4" };
var results = (Array)easyDAClient.ReadMultipleItems("", "OPCLabs.KitServer.2", itemIDs, null, 0, "");
// Create a record object that represents an individual row, including it's metadata.
var record = new SqlDataRecord(
new SqlMetaData("ItemID", SqlDbType.NVarChar, 50),
new SqlMetaData("Value", SqlDbType.Variant),
new SqlMetaData("Timestamp", SqlDbType.DateTime),
new SqlMetaData("Quality", SqlDbType.Int));
// Mark the beginning of a result set.
SqlContext.Pipe.SendResultsStart(record);
for (int i = 0; i < results.Length; i++)
{
var vtqResult = (IDAVtqResult)results.GetValue(i);
// In this example, we only return valid data. Production code would also handle errors.
var vtq = (IDAVtq)vtqResult.Vtq;
if (vtq != null)
{
// Populate the record.
record.SetString(0, itemIDs);
if (vtq.Value == null)
record.SetDBNull(1);
else
record.SetValue(1, vtq.Value);
SqlDateTime sqlTimestamp = (vtq.Timestamp < (DateTime) SqlDateTime.MinValue)
? SqlDateTime.MinValue
: vtq.Timestamp;
record.SetSqlDateTime(2, sqlTimestamp);
record.SetInt32(3, vtq.Quality);
// Send the record to the client.
SqlContext.Pipe.SendResultsRow(record);
}
}
// Mark the end of a result set.
SqlContext.Pipe.SendResultsEnd();
}
};