Richard Quadling
asked on
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.
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.
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
SET NOCOUNT ON
SELECT 'CONTRACTS' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1
ASKER
In QA, they both return valid results.
In my ODBC both return NO results.
In my ODBC both return NO results.
ASKER
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.
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.
never worked that way, so the only thing I can suggest is to use OLEDB Provider instead of ODBC...
ASKER
I'm just trying a snapshot build of the system.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Not sure what to do with this question now.
The answer had nothing to do with the problem (as such).
The answer had nothing to do with the problem (as such).
you should request the q to be paqued with points refunded
ASKER
Ok.
Note that, as I was the only one contributing, I reduced the waiting time for closing to 0 also :-)
CHeers
CHeers
ASKER
Thanks.
SET NOCOUNT OFF
SELECT 'CONTRACTS' AS [Database], POH_ORDER_NUMBR, POH_CONTRACT, POH_ACCOUNT FROM CONTRACTS.DBO.POP_HEADER WHERE POH_MU_LOCK=1