Progress® DataDirect® OpenAccess SDK FAQ for OLE DB

Q1: How to configure linked server of SQL Server 7.0?

Q2: How to give connect parameters in ADO?

Q3: I am getting following error when try to UPDATE using linked server.

error : Server: Msg 7352, Level 16, State 1, Line 1 OLE DB provider 'OpenRDA' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.

Q4: Need information on Writing Custom OLE DB Provider Using ATL (Article from MSDN Magazine)

Q5: When OLE DB provider being used by Seagate Crystal Reports. When Crystal adds references to tables to a report it creates a select statement in the form of: "SELECT * FROM table WHERE 0=1".

Q6: How do I setup the Oracle Generic Connectivity using OpenAccess OLE DB Provider?

Q7: when trying to run a select statement using Oracle Generic connectivity and OpenAccess OLE DB Provider I get a General error in nvITrans_BeginT - rc = -1.

Q8: How to populate rowset on demand?

Q1: How to configure linked server of SQL Server 7.0?

• Run the Enterprise Manager from the SQL Server.
• Select security -> Link server.
• Right click on link server to create a new Linked server. Enter linked server name as "TEST_OLE".
• Select the Provider name as 'Automation Technology OLE DB Provider'
• specify the DataSource name eg: test_local
• Under the Provider Options
  
  Enable : Dynamic Parameters, Allow InProcess
  Disable: Nested queries, Level zero only, Non-transacted updates, Index as access path
  
  
• Under Server Options.
  
  Enable: Collation Compatible, Data Access, Use Remote Collation.
  Disable: RPC, RPC Out
  Leave Collation Name empty
  Leave Connection Timeout and Query Timeout at 0
  
  
• Under the Security tab, select "Be made using this security context" and specify the Remote Login and Password for the Provider.
• Once the linked server is created, you should be able to query your database tables.

select * from TEST_OLE."SCHEMA".OAUSER.EMP;

select * from OPENQUERY(Test_OLE, 'select * from emp');

select * FROM OPENROWSET('MSDASQL','DSN=test_local', 'SELECT * FROM emp')

Q2: How to give connect parameters in ADO?

You can run an OLE DB or ADO compliant application and connect using {EXAMPLE} as the database name. Following is an excerpt from a sample VB Script:

<!-- ADO recordset object -->
<object id=rs classid="clsid:00000535-0000-0010-8000-00AA006D2EA4"></object>
<script language="VBScript">

'******************************************************** ' 

Open the recordset / execute the sql query

connect = "provider=DataDirect OpenAccess SDK 6.0 Provider;data source={example};

user id=abc;password=xyz;"

query = "select * from emp"

adOpenKeyset = 1

rs.Open query, connect, adOpenKeyset 

</script>

Q3: I am getting following error when try to UPDATE using linked server.

error : Server: Msg 7352, Level 16, State 1, Line 1 OLE DB provider 'DataDirect OpenAccess SDK 6.0 Provider' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.

Install MDAC 2.5 SP1 from Microsoft site on the SQL Server machine and use OPENQUERY syntax to UPDATE the database.

Q5: When OLE DB provider being used by Seagate Crystal Reports.

When Crystal adds table references to a report it creates a select statement in the form of: "SELECT * FROM table WHERE 0=1".

When Crystal adds the clause "Where 0 = 1".
DAM doesn't detect that where clause has a search condition that is always false and return empty results.
However we have a workaround that the IP can use. In ip_execute() before starting the query processing, call dam_getInfo(DAM_INFO_ORIGINAL_QUERY_STRING) to get the query string and search for the pattern "WHERE 0=1". If pattern found, return DAM_SUCCESS to indicate empty results and not do any result processing.

Q6: How do I setup the Oracle Generic Connectivity using OpenAccess OLE DB Provider?

Please download (Generic Connectivity Using OLE DB.pdf) document for step-by-step guide on setting up Oracle generic connectivity using OpenAccess OLE DB Provider.

Q7: when trying to run a select statement using Oracle Generic connectivity and OpenAccess OLE DB Provider I get the following error.

[Generic Connectivity Using OLEDB_FS][S1000] [9013]General error in 
nvITrans_BeginT - rc = -1. Please refer to the log file for details.
ORA-02063: preceding 2 lines from EH

Please install our latest 4.7 version of OpenAccess OLEDB Provider as we do not support nested transaction in our previous version.

Q8: How to populate rowset on demand?

ADO application has to set DBPROP_ROWSET_ASYNCH property of OLE DB provider as DBPROPVAL_ASYNCH_POPULATEONDEMAND. Then ADO client side cursor won't fetch all records at a time. In ADO application please do as follows before fetching the records:

rs.Properties("Asynchronous Rowset Processing")= 32 (i.e. DBPROPVAL_ASYNCH_POPULATEONDEMAND 0x00000020L).