I created a stored procedure... and when I execute it in the sql managment studio I get the data I was expecting. But when I execute it through TSQL in code I get back an empty row for each row in the table, 17,000 records and not the results I see in management studio. I think this is because I have multiple selects in my SP, how do I get around this? (I was watching in the profiler and saw it returned 17,000 whatever rows both when executed in management studio and code) -
BEGIN
-
SELECT * INTO #Temp FROM zCIFRecord AS C
-
WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
-
-
SELECT
-
CIFPan,
-
CIFMemNum,
-
CIFLName,
-
CIFFName,
-
CIFInitial
-
FROM
-
#Temp
-
WHERE
-
((CIFAcctNum1 IS NULL) OR (LEN(LTRIM(CIFAcctNum1)) = 0))
-
AND
-
...
-
AND
-
((CIFAcctNum17 IS NULL) OR (LEN(LTRIM(CIFAcctNum17)) = 0))
-
-
DROP TABLE #Temp
-
END
-
Thanks for the help,
Dan
6 981
Try not using a temporary table, just for testing purposes. Once you run your TSQL, check the content of your "temp" table.
-- CK
Try not using a temporary table, just for testing purposes. Once you run your TSQL, check the content of your "temp" table.
-- CK
I might not have understood exactly what you meant, but instead of selecting into a temp table I let the server create a actual table, the data looked good, but again the same thing happened, in the Management Studio I get the three rows I am expecting, in my web applications I get about 17,000 empty records. I need to be specific, the "temp" table should have about 17,000 records in it, which is slightly less than the original table, the data is as expected, there are three rows I am looking for in the "temp" table, these are returned in the Management Studio, but not when my application executes the stored procedure. I am thinking maybe the application is trying to read the rows it is copying to the new table and not the end results. -
BEGIN
-
SELECT * INTO zCIFRecord1 FROM zCIFRecord AS C
-
WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
-
-
SELECT
-
CIFPan,
-
CIFMemNum,
-
CIFLName,
-
CIFFName,
-
CIFInitial
-
FROM
-
zCIFRecord1
-
WHERE
-
((CIFAcctNum1 IS NULL) OR (LEN(LTRIM(CIFAcctNum1)) = 0))
-
AND
-
...
-
AND
-
((CIFAcctNum17 IS NULL) OR (LEN(LTRIM(CIFAcctNum17)) = 0))
-
-
END
-
GO
-
You understood me perfectly. That's what I was asking for. Apology for my communication skills ;)
This is how I would resolve it.
1. Instead of returning resultset, I would do a SELECT INTO on my apps. That would create a table inside your db. If the table is created and the rows are there, your query is not your problem. It's the way being returned from sql server to your apps.
2. Before executing the query, try grabbing that query and display it in a messagebox or a text on your screen. Copy that entire text and paste in your query analyzer. Run it.
3. Create view instead and simply access that view.
If all test runs OK, that specific query is not your problem. You have other problem somewhere in your apps.
-- CK
CK-
Been working on this some more, running our application in the debugger, The application uses a DSN to connect to the sql server, I am running the profiler as well, do you know if there is a way to view the data coming across the DSN?
I think there is some sort of problem in these commands: -
while ((retcode = SQLFetch(_hstmt)) != SQL_NO_DATA) {
-
....
-
ok = SQLGetData(_hstmt, 1, SQL_C_CHAR, &cif.zCIFPan[0],
-
sizeof(cif.zCIFPan) + 1, &sqllen);
-
We are going to post to Microsoft if we cannot solve this, I believe we have a MSDN developer support account.
Thanks,
Dan
If everything is working on the db end, that would mean the problem is in the front-end. Microsoft might be able to help you more. You're paying for the support, anyway.
Kindly update us for their response and how it was resolved so we'll have an idea if it hits us.
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Michael Trosen |
last post by:
Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the...
|
by: Rhino |
last post by:
I've spent the last couple of hours trying to figure out how to debug a Java
stored procedure and am just going in circles. The last straw came when I
got "Cannot open input stream for default"...
|
by: Jeff Wang |
last post by:
Hi all,
Can someone help me out? I've been struggling with this for almost a
week and still have no clue what's wrong.
Basically I want to write a DB2 stored procedure for OS/390 in REXX.
In...
|
by: Thomasb |
last post by:
With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.
I have...
|
by: Kent Lewandowski |
last post by:
hi all,
Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered...
|
by: Wojciech Wendrychowicz |
last post by:
Hello to All,
I'm trying to retrieve records from AS/400 in an VBA application.
So, I've made an RPG program, then a stored procedure wchich calls that RPG
program, and finally some VBA code to...
|
by: Eli |
last post by:
Hi all
We currently have a strange problem with calling a Stored Procedure
(SQL Database) in our C# Project. The only error I get is "System
error" which says a lot :)
Background:
We have...
|
by: Dabbler |
last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuery):
System.Data.SqlClient.SqlException: Procedure or Function...
|
by: nishi57 |
last post by:
I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine...
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from that
table....
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |