Can't get RecordCount of a Query Output from Oracle DB using VBA

[agarwaldvk]

Hi Everyone


Could someone please help me on this one!

I am trying to run a query on an Oracle database (I have been similar thing from an SQL Server database and have had no problems about this). I am using Excel VBA to connect to the database and send a query to get an extract from there - no problems there, works ok.

The problem is that when I try and get a record count of this record set, it shows a '-1'. I know that I have to do a '.MoveLast' to get the correct record count from the record set, but when I do that, it still shows a '-1' for record count.

As a get around, I have a loop to go through the entire record set and then work witht the record set to load the data in to an array for further processing. However, given that the record set is very large containing about 3.5 million records, I am not able to load the same in an array, it comes up with an 'Out of Memory' error.

I tried to load it from a text file (obviously first created a text from the record set from Oracle database) - takes too long!

I am now trying to read the data from this record set (from Oracle database) and directly load in to a table in SQL Server 2008 R2 database. Both of these databases reside on different servers. I do not have 'Bulk Insert' permission on SQL Server - hence cannot use that functionality. Is there a faster way to do. I need to do that because I cannot create a table on the Orcle server - can do so only on the SQL Server server.

Haven't yet tried to load directly from Oracle record set to SQL Server record set - not sure how it will go but have got a feeling that it won't be much better.

This is what I have been trying to do :-
[vba]
stringSQL = "Select meter_id, TRUNC(contract_start_date) as contract_start_date, TRUNC(contract_end_date) as contract_end_date, utility_type From net_meter"
rs.Open stringSQL, con, adOpenKeyset, adLockOptimistic, adCmdText
rs.MoveLast
recordCount = rs.recordCount
rs.MoveFirst

'Had to do this as a get around
Dim counter As Long
counter = 0
Do While Not rs.EOF = True
counter = counter + 1
rs.MoveNext
Loop
[/vba]




Would using SSIS package or SAS help to fasten the process?



Best regards



Deepak

[mohanvijay]

to get recordcount try this

rs.Open stringSQL, con,adOpenStatic,adLockReadOnly

[agarwaldvk]

Hi Mohan Vijay


That didn't work - same problem persists.



Deepak

[stanl]

with Oracle I think you need to set the cursorlocation to client. And if using the Microsoft Oracle Driver, that might even work, in which case SELECT COUNT(*) may have to be issued in advance of your actual query.