Ok I just saw this behaviour whilst running a MSSQL Profiler session.

Running PB 11.5 with the connection dbparm provider ='sqloledb'.


If the DW's datasource is a select statement as simple as

Select company_id from company where company_id
= :al_company_id

And you retrieve the DW with a known id, Sqlserver will run the select
statement , but also create a cursror, open the cursor, fetch the
cursor and close the cursor.

if you change the DW's datasource to be a stored procedure that does
the same select statement, you'll get what is expected in the profiler
session, a single select statement.


If I change the connection dbparm to provider='sqlncli', the behaviour
as expected in MSSQL profiler, a single select statement.


If I change the connection to use
DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.ServerName = "<servername>"
AutoCommit = True
DBParm = "Database='asibig6'"

That too works as expected.

We haven't noticed it before because most of the DWs are based on
storedprocedures, it wasn't until we started migrating our other older
9.02 apps, who are not using stored procedures for retrieve , to 11.5
using sqloledb that we noticed this behaviour.

The wierd thing is that in the MSSQL profiler the select statement run
first then the cursror stuff.

here is an example from MSSQL profiler

EventClass Texdata objectName Duration StartTime EndTime
SP:StmtCompleted select * from ... null 0 17:30:57.027 17:30:57.027

RPC:Completed declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=8
declare @p4 int
set @p4=1
declare @p5 int
set @p5=1
exec sp_cursoropen
@p1 output,N'SELECT ... sp_cursoropen 11 17:30:57.010
17:30:57.027

SP:StmtCompleted FETCH API_CURSOR00000000000000F1
null 0 17:30:57.057 17:30:57.057

RPC:Completed exec sp_cursorfetch 180150003,16,1,2
sp_cursorfetch 0 17:30:57.057 17:30:57.057

SP:StmtCompeted FETCH API_CURSOR00000000000000F1
null 0 17:30:57.103 17:30:57.103

RPC:Completed exec sp_cursorfetch 180150003,16,2,2
sp_cursorfetch 0 17:30:57.103 17:30:57.103

RPC:Completed exec sp_cursorclose 180150003
sp_cursorclose 0 17:30:57.137 17:30:57.137


I think there is some DBPARM parameter that I'm missing that will
prevent this simple select statement generating a cursor in MSSQL...

here are my variations of the INI file we use.

--this is are current connection string and which exhibits the cursor
issue.
// Profile MYDATABASE_oledb_sqloledb
DBMS = "OLE DB"
LogPass = <**********>
LogId = "ansclf"
AutoCommit = True
DBParm =
"PROVIDER='SQLOLEDB',DATASOURCE='<myserver>',PROVIDERSTRING='database=<mydatabase>'"


here are options 1 and 2, but would require more regression
testing...since we regression tested using the sqloledb not these.

// Profile MYDATABASE_oledb_sqlncliDBMS = "OLE DB"
LogPass = <**********>
LogId = "ansclf"
SQLCA.AutoCommit = True
DBParm =
"PROVIDER='SQLNCLI',DATASOURCE='<myserver>',PROVIDERSTRING='database=<mydatabase>'"


// Profile MYDATABASE_sqlncli
SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"
SQLCA.LogPass = <**********>
SQLCA.ServerName = "<MYSERVER>"
SQLCA.LogId = "ansclf"
SQLCA.AutoCommit = True
SQLCA.DBParm = "Database='<MYDATABASE>'"

Any clue would be helpful.

There is a definite performance hit on the simplest of select
statements, let alone one that brings back thousands of rows.