Stored Procedure Problem

[dcharnigo]

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

[ck9663]

Try not using a temporary table, just for testing purposes. Once you run your TSQL, check the content of your "temp" table.

-- CK

[dcharnigo]

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

[ck9663]

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

[dcharnigo]

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

[ck9663]

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

[dcharnigo]

I ended up solving the problem myself, I guess if I would have posted the entire procedure it might have given it away. Apparently you cannot use a ROWCOUNT:

BAD PROCEDURE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spGetCIFOrphans1]
  @max as int = 101
AS

SET ROWCOUNT @max

BEGIN
SELECT * INTO zCIFRecord1 FROM zCIFRecord AS C 
WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
GO
SELECT
    CIFPan,
    CIFMemNum,
    CIFLName,
    CIFFName,
    CIFInitial
FROM
    zCIFRecord1
WHERE
    ((CIFAcctNum1 IS NULL) OR (LEN(LTRIM(CIFAcctNum1)) = 0))
AND
    ((CIFAcctNum2 IS NULL) OR (LEN(LTRIM(CIFAcctNum2)) = 0))
AND
    ((CIFAcctNum3 IS NULL) OR (LEN(LTRIM(CIFAcctNum3)) = 0))
AND
    ((CIFAcctNum4 IS NULL) OR (LEN(LTRIM(CIFAcctNum4)) = 0))
AND
    ((CIFAcctNum5 IS NULL) OR (LEN(LTRIM(CIFAcctNum5)) = 0))
AND
    ((CIFAcctNum6 IS NULL) OR (LEN(LTRIM(CIFAcctNum6)) = 0))
AND
    ((CIFAcctNum7 IS NULL) OR (LEN(LTRIM(CIFAcctNum7)) = 0))
AND
    ((CIFAcctNum8 IS NULL) OR (LEN(LTRIM(CIFAcctNum8)) = 0))
AND
    ((CIFAcctNum9 IS NULL) OR (LEN(LTRIM(CIFAcctNum9)) = 0))
AND
    ((CIFAcctNum10 IS NULL) OR (LEN(LTRIM(CIFAcctNum10)) = 0))
AND
    ((CIFAcctNum11 IS NULL) OR (LEN(LTRIM(CIFAcctNum11)) = 0))
AND
    ((CIFAcctNum12 IS NULL) OR (LEN(LTRIM(CIFAcctNum12)) = 0))
AND
    ((CIFAcctNum13 IS NULL) OR (LEN(LTRIM(CIFAcctNum13)) = 0))
AND
    ((CIFAcctNum14 IS NULL) OR (LEN(LTRIM(CIFAcctNum14)) = 0))
AND
    ((CIFAcctNum15 IS NULL) OR (LEN(LTRIM(CIFAcctNum15)) = 0))
AND
    ((CIFAcctNum16 IS NULL) OR (LEN(LTRIM(CIFAcctNum16)) = 0))
AND
    ((CIFAcctNum17 IS NULL) OR (LEN(LTRIM(CIFAcctNum17)) = 0))

DROP TABLE #Temp
END

SET ROWCOUNT 0

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

GOOD PROCEDURE

CREATE PROCEDURE [dbo].[spGetCIFOrphans]
AS
SET NOCOUNT ON

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
    ((CIFAcctNum2 IS NULL) OR (LEN(LTRIM(CIFAcctNum2)) = 0))
AND
    ((CIFAcctNum3 IS NULL) OR (LEN(LTRIM(CIFAcctNum3)) = 0))
AND
    ((CIFAcctNum4 IS NULL) OR (LEN(LTRIM(CIFAcctNum4)) = 0))
AND
    ((CIFAcctNum5 IS NULL) OR (LEN(LTRIM(CIFAcctNum5)) = 0))
AND
    ((CIFAcctNum6 IS NULL) OR (LEN(LTRIM(CIFAcctNum6)) = 0))
AND
    ((CIFAcctNum7 IS NULL) OR (LEN(LTRIM(CIFAcctNum7)) = 0))
AND
    ((CIFAcctNum8 IS NULL) OR (LEN(LTRIM(CIFAcctNum8)) = 0))
AND
    ((CIFAcctNum9 IS NULL) OR (LEN(LTRIM(CIFAcctNum9)) = 0))
AND
    ((CIFAcctNum10 IS NULL) OR (LEN(LTRIM(CIFAcctNum10)) = 0))
AND
    ((CIFAcctNum11 IS NULL) OR (LEN(LTRIM(CIFAcctNum11)) = 0))
AND
    ((CIFAcctNum12 IS NULL) OR (LEN(LTRIM(CIFAcctNum12)) = 0))
AND
    ((CIFAcctNum13 IS NULL) OR (LEN(LTRIM(CIFAcctNum13)) = 0))
AND
    ((CIFAcctNum14 IS NULL) OR (LEN(LTRIM(CIFAcctNum14)) = 0))
AND
    ((CIFAcctNum15 IS NULL) OR (LEN(LTRIM(CIFAcctNum15)) = 0))
AND
    ((CIFAcctNum16 IS NULL) OR (LEN(LTRIM(CIFAcctNum16)) = 0))
AND
    ((CIFAcctNum17 IS NULL) OR (LEN(LTRIM(CIFAcctNum17)) = 0))

DROP TABLE ##Temp