July 13, 2007 at 2:08 pm
I'm running the following code in a VB6 program. It works with no problem against a SQL2K database with ~200,000 qualifying rows, but on a SQL 2005 database with the identical table, it returns a limit of around 69750 to 69775 rows.
The query itself returns all qualifying rows from the SQL 2005 database when run from SQL Server Management Studio.
Any ideas why this is happening?
SelectString = "SELECT * FROM TABLEA WITH (NOLOCK) " & _
" WHERE (LEFT (M1,3) = 'ZZZ' OR LEFT (M1,3) = 'XXX' OR LEFT (M1,3) =
'YYY')" & _
" AND (SUBSTRING(M2,2,3) <> 'LGL' AND SUBSTRING(M2,2,3) <> 'LEZ' AND "
& _
" SUBSTRING(M2,2,3) <> 'PR9' AND SUBSTRING(M2,2,3) <> '338') AND " & _
" CONVERT(VARCHAR(8),LOAD_DATE,1) = '" & sMMDDYY & "' ORDER BY
M1,M2,M3"
Set ADORS1 = New ADODB.Recordset
ADORS1.Open SelectString, ADOCN, adOpenForwardOnly, adLockReadOnly
The SQL Connection Statement is:
Set ADOCN = New ADODB.Connection
ADOCN.ConnectionTimeout = 0
ADOCN.CommandTimeout = 0
ADOCN.CursorLocation = adUseClient
ADOCN.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=" & Server1\Instance1; & _
"Initial Catalog=Database1;" & _
"Integrated Security = SSPI;" & _
"Persist Security Info = False;"
ADOCN.Open
July 14, 2007 at 4:16 pm
I am more interested in why the query returns a different number of records between each execution!
I can't see the point on returning 200k rows to the client.
N 56°04'39.16"
E 12°55'05.25"
July 16, 2007 at 9:34 am
Sounds like the tables on the different servers have different data.
July 16, 2007 at 10:04 am
The source tables have identical data.
I agree that 200K records is a bit much - the application was built solely to extract records and write them to a flat file. SSIS is certainly the better way to go, but I'd sure like to know why ADO chokes where it does and not reliably on the same record count.
July 16, 2007 at 10:22 am
Carl, I dont doubt you, but maybe worth running the query directly via SSMS or QA to verify that you get the right rowcount there. That would confirm that the data is the same and also tell you if the problem is possibly external to ADO. Are you running both tests from the same machine? Otherwise differences in client side ADO could somehow change things, maybe.
July 16, 2007 at 10:30 am
Sorry I didn't note from the start that the query works just fine in QA against the SQL2005 instance.
July 16, 2007 at 1:24 pm
How about trying a simpler query, just to prove its ADO? Start with Select top 65000 from table (no where clause), bump up until you get it to break. Potentially use a different table too. Either SQL not sending your records back, or ADO seeing some kind of bad char or delimiter that makes it stop processing, my guesses anyway, and the first doesnt seem likely.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply