Solved

ODBC and QA.

Posted on 2006-06-09
13
1,053 Views
Last Modified: 2012-06-21
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
Comment
Question by:RQuadling
  • 7
  • 5
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16869607
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16869610
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
 
LVL 40

Author Comment

by:RQuadling
ID: 16869635
In QA, they both return valid results.
In my ODBC both return NO results.


0
 
LVL 40

Author Comment

by:RQuadling
ID: 16869677
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16869707
never worked that way, so the only thing I can suggest is to use OLEDB Provider instead of ODBC...
0
 
LVL 40

Author Comment

by:RQuadling
ID: 16869739
I'm just trying a snapshot build of the system.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Accepted Solution

by:
RQuadling earned 0 total points
ID: 16870135
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
 
LVL 40

Author Comment

by:RQuadling
ID: 16870246
Not sure what to do with this question now.

The answer had nothing to do with the problem (as such).
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16870397
you should request the q to be paqued with points refunded
0
 
LVL 40

Author Comment

by:RQuadling
ID: 16870897
Ok.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16934814
Note that, as I was the only one contributing, I reduced the waiting time for closing to 0 also :-)
CHeers
0
 
LVL 40

Author Comment

by:RQuadling
ID: 16940221
Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now