I built a datawindow (PB v.10.2.1.9637) based on a stored procedure (MSSQL Server 2000). If I run the stored procedure in TSQL, I get 300 rows returned to me (a number that I believe to be correct). If I use exactly the same parameters with the datawindow (both via PBscript and manual user input), I only get 281 rows (compiled version and development environment). I receive no error messages, either from the SQL server or the PB application. I have no idea how to even begin to troubleshoot this problem. Any suggestions?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Tabular datawindow returns fewer rows than underlying stored procedure
- Thread starter rthunter
- Start date
- Thread starter
- #3
Thanks for the suggestion. I actually already did that, and the results are really strange. Here's what's taking place:
From datawindow:
(1) Stored procedure creates a temp table and populates it with 62050 rows (unique account records);
(2) Stored procedure calculates a total paid for each account (row) based on transactions in a linked table;
(3) Stored procedure deletes all records from temp table with a total paid amount < $100,000 (exactly 281 remain when I use a particular set of date values and account types);
From Query Analyzer (copy and paste, verbatim, the statement from Profiler):
(1) Stored procedure creates a temp table and populates it with 64,786 rows (exactly 2,736 more);
(2) Stored procedure calculates a total paid for each account based on transactions in a linked table;
(3) Stored procedure deletes all records from temp table with a total paid amount < $100,000 (exactly 300 remain).
If I reduce the number of initial records (e.g., 30,000) by changing some of my selection argument values, the rows returned by the datawindow are consistent with the rows returned by Query Analyzer. Specifically, the original selection represented account dates 1/1/1979 - 9/30/2006 (the date the account was opened). If I break it up into two groups, (1/1/1979 - 12/31/1992 and 1/1/1993 - 9/30/2006), the collective number of rows returned by the datawindow is 300.
The only possible explanation I can come up with is that there must be something afoul with the OLEDB connection, but what, exactly, I have no idea. I would think that the statement would be passed to the server, then the server would do exactly the same work that it would do if the statement came from any other source, and the small number of rows would be returned correctly. This has to be one of the strangest "quirks" I've ever seen with PowerBuilder (or SQL Server, whichever is responsible), and I have no idea how to fix it.
From datawindow:
(1) Stored procedure creates a temp table and populates it with 62050 rows (unique account records);
(2) Stored procedure calculates a total paid for each account (row) based on transactions in a linked table;
(3) Stored procedure deletes all records from temp table with a total paid amount < $100,000 (exactly 281 remain when I use a particular set of date values and account types);
From Query Analyzer (copy and paste, verbatim, the statement from Profiler):
(1) Stored procedure creates a temp table and populates it with 64,786 rows (exactly 2,736 more);
(2) Stored procedure calculates a total paid for each account based on transactions in a linked table;
(3) Stored procedure deletes all records from temp table with a total paid amount < $100,000 (exactly 300 remain).
If I reduce the number of initial records (e.g., 30,000) by changing some of my selection argument values, the rows returned by the datawindow are consistent with the rows returned by Query Analyzer. Specifically, the original selection represented account dates 1/1/1979 - 9/30/2006 (the date the account was opened). If I break it up into two groups, (1/1/1979 - 12/31/1992 and 1/1/1993 - 9/30/2006), the collective number of rows returned by the datawindow is 300.
The only possible explanation I can come up with is that there must be something afoul with the OLEDB connection, but what, exactly, I have no idea. I would think that the statement would be passed to the server, then the server would do exactly the same work that it would do if the statement came from any other source, and the small number of rows would be returned correctly. This has to be one of the strangest "quirks" I've ever seen with PowerBuilder (or SQL Server, whichever is responsible), and I have no idea how to fix it.
Ok, try this instead of create a temp table, create a table with the same structure in the database then modify the store procedure to fill this table and run it from query analizer.
Then create another table modify the store procedure to fill this second table using the datawindows.
Now you have 2 results 1 from QA and the other from DW,
Select all rows thar are in the table that has the QA result and not in the DW result that must be 2,736 rows. Then try to search in this rows if ther is a pattern example same date or missing data.
Other thing that you must do is to see that there is no rows that are in the DW result table and not in the QA result table.
I hope you understand my instructions.
Then create another table modify the store procedure to fill this second table using the datawindows.
Now you have 2 results 1 from QA and the other from DW,
Select all rows thar are in the table that has the QA result and not in the DW result that must be 2,736 rows. Then try to search in this rows if ther is a pattern example same date or missing data.
Other thing that you must do is to see that there is no rows that are in the DW result table and not in the QA result table.
I hope you understand my instructions.
- Thread starter
- #5
I've compared the output, and the results are the same each time I run the QA and DW versions. The DW never contains rows that are not included in the QA output. I can't find any recurring theme in the rows that are omitted by the datawindow, but I really don't think it has anything to do with the datawindow itself. I believe the issue lies within the SQLOLEDB connection, but I don't know how to test it with anything except a datawindow. I also don't know how to connect the datawindow to the SQL server other than using SQLOLEDB. In prior versions, I had the "MSSQL Server 6" connector available (and it worked with SQL2K), but that isn't available in PB10.
From your description it sounds like a datatype mismatch of some sort. A Powerbuilder integer has a value range of -32768 to +32767.
Can you manipulate your calls to set the initial record set to 32770 (just above the PB limit)? and then again to 32760?
Matt
"Nature forges everything on the anvil of time
Can you manipulate your calls to set the initial record set to 32770 (just above the PB limit)? and then again to 32760?
Matt
"Nature forges everything on the anvil of time
- Status