SAS Provider for OLE DB (SAS.IOMProvider) doesn't work with ObjectPool

Question

I'm using the SAS Integration Technologies COM components to connect to SAS Server from a C# .NET project. I want to submit statements to a SAS Workspace then load the output dataset from SAS using the OLE DB provider (SAS.IOMProvider). I am able to do this successfully using code like this:

static int Main(string[] args)
{
    var keeper = new ObjectKeeper();
    var factory = new ObjectFactoryMulti2();
    var server = new ServerDef()
    {
        MachineDNSName = "sas.server.com",
        Protocol = Protocols.ProtocolBridge,
        Port = 8591,
        BridgeSecurityPackage = "Negotiate",
    };
    var workspace = (IWorkspace)factory.CreateObjectByServer("Workspace1", true, server, null, null);

    keeper.AddObject(1, workspace.UniqueIdentifier, workspace);

    try
    {
        using (var conn = new OleDbConnection("Provider=SAS.IOMProvider.1; Data Source=iom-id://" + workspace.UniqueIdentifier))
        {
            // success
            conn.Open();
        }
    }
    catch (Exception ex)
    {
        System.Console.Error.WriteLine(ex.ToString());
        return 1;
    }
    finally
    {
        keeper.RemoveObject(workspace);
        workspace.Close();
    }

    return 0;
}

However, when I try using the ObjectPool feature of ObjectFactoryMulti2, the OLE DB connection doesn't work. It always throws "The object could not be found; make sure it was previously added to the object keeper." Here is the code that does not work:

static int Main(string[] args)
{
    var keeper = new ObjectKeeper();
    var factory = new ObjectFactoryMulti2();
    var server = new ServerDef()
    {
        MachineDNSName = "sas.server.com`",
        Protocol = Protocols.ProtocolBridge,
        Port = 8591,
        BridgeSecurityPackage = "Negotiate",
        MaxPerObjectPool = Environment.ProcessorCount,
        RunForever = true,
        RecycleActivationLimit = 100,
    };
    var login = new LoginDef();

    var pool = factory.ObjectPools.CreatePoolByServer("Pool1", server, login);
    var lease = pool.GetPooledObject(null, null, 5000);
    var workspace = (IWorkspace)lease.SASObject;

    keeper.AddObject(1, workspace.UniqueIdentifier, workspace);

    try
    {
        using (var conn = new OleDbConnection("Provider=SAS.IOMProvider.1; Data Source=iom-id://" + workspace.UniqueIdentifier))
        {
            // throws System.Data.OleDb.OleDbException: 'The object 1EFCE532-99BA-4A27-AF37-574EAE1CD04C could not be found; make sure it was previously added to the object keeper.'
            conn.Open();
        }
    }
    catch (Exception ex)
    {
        System.Console.Error.WriteLine(ex.ToString());
        return 1;
    }
    finally
    {
        keeper.RemoveObject(workspace);
        lease.ReturnToPool();
        pool.Shutdown();
    }

    return 0;
}

Is there a way to use SAS connection pooling with the SAS OLE DB provider?

Answer 1

Got a good answer to this question from SAS Support. When using a connection pool, you have to cast the workspace to IServerStatus and connect using its ServerStatusUniqueID property instead of IWorkspace.UniqueIdentifier.

var pool = factory.ObjectPools.CreatePoolByServer("Pool1", server, login);
var lease = pool.GetPooledObject(null, null, 5000);
var workspace = (IWorkspace)lease.SASObject;
var status = (IServerStatus)lease.SASObject;

keeper.AddObject(1, workspace.UniqueIdentifier, workspace);

using (var conn = new OleDbConnection("Provider=SAS.IOMProvider.1; Data Source=iom-id://" + status.ServerStatusUniqueID))
{
    // success
    conn.Open();
}

keeper.RemoveObject(workspace);
lease.ReturnToPool();