Board index MS SQL Server

Can't retrieve temp table rows using SQLOLEDB provider

Can't retrieve temp table rows using SQLOLEDB provider

Post by Michael Keiste » Tue, 28 Sep 1999 04:00:00



    For quite a while now,  I've been using the trick of performing some
fairly complicated processing of records in a stored procedure and saving
the resultant records in a temp table created in the procedure.  At the end
of the procedure I do a select * from #temp and open a record set in the
client using this procedure.

    This has always worked fine for me up until recently,  when I switched
to using the native SQL Server provider (SQLOLEDB) instead of the default
ODBC provider.  Now I get an error saying that actions can not be performed
on the object (the recordset) if the object is not open.

    Has anyone else encountered this limitation?  Is there a magic
combination of property settings to get around it?  I'd really like to
continue to use the native provider (progress?!?) but if I can't get around
this limitation I'll have to switch back to ODBC.

    I know,  I shouldn't be using temp tables if there is a SQL statement
that will do what I want.  But in this case,  there is no SQL statement to
do the type of sequential and iterative processing that I need done and it's
definitely better performance-wise to process the data on the server instead
of lugging it all down to the client and processing it there.

Michael Keister

 
 
 
Top

Can't retrieve temp table rows using SQLOLEDB provider

Post by M Tjade » Wed, 29 Sep 1999 04:00:00


Michael,
are you using ADO? In case try to change CursorLocation = adUseServer
try to set the lock properties to adLockReadOnly too
--

/MT

>     For quite a while now,  I've been using the trick of performing some
> fairly complicated processing of records in a stored procedure and saving
> the resultant records in a temp table created in the procedure.  At the
end
> of the procedure I do a select * from #temp and open a record set in the
> client using this procedure.

>     This has always worked fine for me up until recently,  when I switched
> to using the native SQL Server provider (SQLOLEDB) instead of the default
> ODBC provider.  Now I get an error saying that actions can not be
performed
> on the object (the recordset) if the object is not open.

>     Has anyone else encountered this limitation?  Is there a magic
> combination of property settings to get around it?  I'd really like to
> continue to use the native provider (progress?!?) but if I can't get
around
> this limitation I'll have to switch back to ODBC.

>     I know,  I shouldn't be using temp tables if there is a SQL statement
> that will do what I want.  But in this case,  there is no SQL statement to
> do the type of sequential and iterative processing that I need done and
it's
> definitely better performance-wise to process the data on the server
instead
> of lugging it all down to the client and processing it there.

> Michael Keister



 
 
 
Top

1. OpenQuery using OLE DB provider 'SQLOLEDB'

Hi,

I'm trying to populate data in one SQL Server db (TARGET)
using data from another SQL Server (SOURCE).  In the
TARGET server I set up SOURCE server as a linked server, I
have DTC service running.

Here is the code I'm trying to execute (it worked once,
then does work the rest of the time, I'm sure there is no
syntax error):

-- specify database name in TARGET db
Use Guardian

-- declare and set value for variables








-- begin populating reference tables
BEGIN TRANSACTION
print 'working with ApplicationClass table'
-- insert new reference data from BOI1160
INSERT INTO dbo.ApplicationClass
(ApplicationClassID, ApplicationClassName,
ApplicationClassDescription, ActiveFlag)
SELECT ApplicationClassID, ApplicationClassName,

FROM OPENQUERY(BOI1160, 'select * from
Guardian.dbo.ApplicationClass')
-- commit transaction
COMMIT

Here is the error I kept getting:
Server: Msg 7391, Level 16, State 1, Line 33
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].

I tried shutting down the DTC service, TARGET server,
deleting then adding the linked server, etc, with no
luck.  This worked once yesterday, then refused to work
afterwards.

Please help....

2. connection.open using DSN

3. Can't connect using OleDB provider SQLOLEDB

4. CFP: Conference -teaching OODB

5. SQLOLEDB Provider Row Updates

6. drop down from an Arrary

7. Problem with retrieving an ADO Recordset from SQLServer using Temp Tables

8. Relinking SQL*Net libraries

9. provider=SQLOLEDB doesn't seem to work with ADO

10. ASP Script using SQLOLEDB provider Question....

11. streaming result sets to disk using the SQLOLEDB provider


All times are UTC
Board index