• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1071
  • Last Modified:

ODBC and QA.

Hi.

I have the following SQL Statement ...

SELECT 'CONTRACTS' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1;

In Query Analyser this returns the correct results.

If I use this query via ODBC, the SQL Profiler reveals the following ...

declare @P1 int
declare @P3 int
declare @P4 int
declare @P5 int
set @P1=0
set @P3=2
set @P4=8193
set @P5=0
exec sp_cursoropen @P1 output, N'SELECT ''CONTRACTS'' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1;', @P3 output, @P4 output, @P5 output
select @P1, @P3, @P4, @P5

All looks OK.

But the application gets no results.

So.

If I place the above in QA, I get the following results ...

1,330,634,8      2.00      1.00      -1.00

Now the important one is the -1.00

This is the number of rows in the response. -1 means what?

I tried this in QA

declare @P1 int
declare @P3 int
declare @P4 int
declare @P5 int
set @P1=0
set @P3=2
set @P4=8193
set @P5=0
set nocount off
exec sp_cursoropen @P1 output, N'SELECT POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1;', @P3 output, @P4 output, @P5 output
select @P1, @P3, @P4, @P5

exec sp_cursorfetch @P1, 2, 1, 1

and I get a valid row back.

What am I missing that is returning -1 for the number of rows?

Regards,

Richard Quadling.
0
Richard Quadling
Asked:
Richard Quadling
  • 7
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:

SET NOCOUNT OFF
SELECT 'CONTRACTS' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
resp:

SET NOCOUNT ON
SELECT 'CONTRACTS' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
In QA, they both return valid results.
In my ODBC both return NO results.


0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Richard QuadlingSenior Software DeveloperAuthor Commented:
What makes this really bizarre is ...

declare @P1 int
declare @P3 int
declare @P4 int
declare @P5 int
set @P1=0
set @P3=2
set @P4=8193
set @P5=0
set nocount off
exec sp_cursoropen @P1 output, N'SELECT DISTINCT SUSORT, COUNT(*) AS ServiceProviders FROM Contracts.dbo.PL_ACCOUNTS WHERE SU_ON_STOP <> 1 AND SUSORT > '''' GROUP BY SUSORT ORDER BY SUSORT', @P3 output, @P4 output, @P5 output
select @P1, @P3, @P4, @P5

exec sp_cursorfetch @P1, 2, 1, 1

returns ...

1,289,928,7      8.00      1.00      1.00


and a valid row.

Hmmm.

I wonder if my interpretation of that last column is correct.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
never worked that way, so the only thing I can suggest is to use OLEDB Provider instead of ODBC...
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
I'm just trying a snapshot build of the system.
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
ARGH!!!!

It seems that the ODBC mechanism won't return the number of rows unless there is an order by in the SQL statement.

I was testing 1 statement from a UNION and the UNION has its own ORDER BY clause.

Added the ORDER BY to my 1 SQL line and hey f*cking presto!

ARGH!!!

Thanks for, erm, well, um, listening?
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Not sure what to do with this question now.

The answer had nothing to do with the problem (as such).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should request the q to be paqued with points refunded
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Ok.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Note that, as I was the only one contributing, I reduced the waiting time for closing to 0 also :-)
CHeers
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Thanks.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now