Solved

ODBC and QA.

Posted on 2006-06-09
13
1,059 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:Richard Quadling
  • 7
  • 5
13 Comments
 
LVL 143

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 143

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:Richard Quadling
ID: 16869635
In QA, they both return valid results.
In my ODBC both return NO results.


0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 40

Author Comment

by:Richard Quadling
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 143

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:Richard Quadling
ID: 16869739
I'm just trying a snapshot build of the system.
0
 
LVL 40

Accepted Solution

by:
Richard Quadling 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:Richard Quadling
ID: 16870246
Not sure what to do with this question now.

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

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:Richard Quadling
ID: 16870897
Ok.
0
 
LVL 143

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:Richard Quadling
ID: 16940221
Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

749 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