Solved

ODBC and QA.

Posted on 2006-06-09
13
1,056 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

930 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

9 Experts available now in Live!

Get 1:1 Help Now