By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 1,388 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

Stored Procedure Problem

dcharnigo
P: 20
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)

Expand|Select|Wrap|Line Numbers
  1. BEGIN
  2. SELECT * INTO #Temp FROM zCIFRecord AS C 
  3. WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
  4.  
  5. SELECT
  6.     CIFPan,
  7.     CIFMemNum,
  8.     CIFLName,
  9.     CIFFName,
  10.     CIFInitial
  11. FROM
  12.     #Temp
  13. WHERE
  14.     ((CIFAcctNum1 IS NULL) OR (LEN(LTRIM(CIFAcctNum1)) = 0))
  15. AND
  16. ...
  17. AND
  18.     ((CIFAcctNum17 IS NULL) OR (LEN(LTRIM(CIFAcctNum17)) = 0))
  19.  
  20. DROP TABLE #Temp
  21. END
  22.  
Thanks for the help,
Dan
Apr 7 '08 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
Try not using a temporary table, just for testing purposes. Once you run your TSQL, check the content of your "temp" table.

-- CK
Apr 7 '08 #2

dcharnigo
P: 20
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.

Expand|Select|Wrap|Line Numbers
  1. BEGIN
  2. SELECT * INTO zCIFRecord1 FROM zCIFRecord AS C 
  3. WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
  4.  
  5. SELECT
  6.     CIFPan,
  7.     CIFMemNum,
  8.     CIFLName,
  9.     CIFFName,
  10.     CIFInitial
  11. FROM
  12.     zCIFRecord1
  13. WHERE
  14.     ((CIFAcctNum1 IS NULL) OR (LEN(LTRIM(CIFAcctNum1)) = 0))
  15. AND
  16. ...
  17. AND
  18.     ((CIFAcctNum17 IS NULL) OR (LEN(LTRIM(CIFAcctNum17)) = 0))
  19.  
  20. END
  21. GO
  22.  
Apr 8 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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
Apr 8 '08 #4

dcharnigo
P: 20
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:
Expand|Select|Wrap|Line Numbers
  1.         while ((retcode = SQLFetch(_hstmt)) != SQL_NO_DATA) {
  2. ....
  3.             ok = SQLGetData(_hstmt, 1, SQL_C_CHAR, &cif.zCIFPan[0],
  4.                 sizeof(cif.zCIFPan) + 1, &sqllen);
  5.  
We are going to post to Microsoft if we cannot solve this, I believe we have a MSDN developer support account.

Thanks,
Dan
Apr 11 '08 #5

ck9663
Expert 2.5K+
P: 2,878
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
Apr 11 '08 #6

dcharnigo
P: 20
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
Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5.  
  6. ALTER PROCEDURE [dbo].[spGetCIFOrphans1]
  7.   @max as int = 101
  8. AS
  9.  
  10. SET ROWCOUNT @max
  11.  
  12. BEGIN
  13. SELECT * INTO zCIFRecord1 FROM zCIFRecord AS C 
  14. WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
  15. GO
  16. SELECT
  17.     CIFPan,
  18.     CIFMemNum,
  19.     CIFLName,
  20.     CIFFName,
  21.     CIFInitial
  22. FROM
  23.     zCIFRecord1
  24. WHERE
  25.     ((CIFAcctNum1 IS NULL) OR (LEN(LTRIM(CIFAcctNum1)) = 0))
  26. AND
  27.     ((CIFAcctNum2 IS NULL) OR (LEN(LTRIM(CIFAcctNum2)) = 0))
  28. AND
  29.     ((CIFAcctNum3 IS NULL) OR (LEN(LTRIM(CIFAcctNum3)) = 0))
  30. AND
  31.     ((CIFAcctNum4 IS NULL) OR (LEN(LTRIM(CIFAcctNum4)) = 0))
  32. AND
  33.     ((CIFAcctNum5 IS NULL) OR (LEN(LTRIM(CIFAcctNum5)) = 0))
  34. AND
  35.     ((CIFAcctNum6 IS NULL) OR (LEN(LTRIM(CIFAcctNum6)) = 0))
  36. AND
  37.     ((CIFAcctNum7 IS NULL) OR (LEN(LTRIM(CIFAcctNum7)) = 0))
  38. AND
  39.     ((CIFAcctNum8 IS NULL) OR (LEN(LTRIM(CIFAcctNum8)) = 0))
  40. AND
  41.     ((CIFAcctNum9 IS NULL) OR (LEN(LTRIM(CIFAcctNum9)) = 0))
  42. AND
  43.     ((CIFAcctNum10 IS NULL) OR (LEN(LTRIM(CIFAcctNum10)) = 0))
  44. AND
  45.     ((CIFAcctNum11 IS NULL) OR (LEN(LTRIM(CIFAcctNum11)) = 0))
  46. AND
  47.     ((CIFAcctNum12 IS NULL) OR (LEN(LTRIM(CIFAcctNum12)) = 0))
  48. AND
  49.     ((CIFAcctNum13 IS NULL) OR (LEN(LTRIM(CIFAcctNum13)) = 0))
  50. AND
  51.     ((CIFAcctNum14 IS NULL) OR (LEN(LTRIM(CIFAcctNum14)) = 0))
  52. AND
  53.     ((CIFAcctNum15 IS NULL) OR (LEN(LTRIM(CIFAcctNum15)) = 0))
  54. AND
  55.     ((CIFAcctNum16 IS NULL) OR (LEN(LTRIM(CIFAcctNum16)) = 0))
  56. AND
  57.     ((CIFAcctNum17 IS NULL) OR (LEN(LTRIM(CIFAcctNum17)) = 0))
  58.  
  59. DROP TABLE #Temp
  60. END
  61.  
  62. SET ROWCOUNT 0
  63.  
  64. GO
  65.  
  66. SET ANSI_NULLS OFF
  67. GO
  68. SET QUOTED_IDENTIFIER OFF
  69. GO
  70.  
GOOD PROCEDURE
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE [dbo].[spGetCIFOrphans]
  2. AS
  3. SET NOCOUNT ON
  4.  
  5. SELECT * INTO ##Temp FROM zCIFRecord AS C
  6. WHERE InsertTime = (SELECT MAX(InsertTime) FROM zCIFRecord WHERE CIFPan = C.CIFPan)
  7.  
  8. SELECT
  9.     CIFPan,
  10.     CIFMemNum,
  11.     CIFLName,
  12.     CIFFName,
  13.     CIFInitial
  14. FROM
  15.     ##Temp
  16. WHERE
  17.     ((CIFAcctNum1 IS NULL) OR (LEN(LTRIM(CIFAcctNum1)) = 0))
  18. AND
  19.     ((CIFAcctNum2 IS NULL) OR (LEN(LTRIM(CIFAcctNum2)) = 0))
  20. AND
  21.     ((CIFAcctNum3 IS NULL) OR (LEN(LTRIM(CIFAcctNum3)) = 0))
  22. AND
  23.     ((CIFAcctNum4 IS NULL) OR (LEN(LTRIM(CIFAcctNum4)) = 0))
  24. AND
  25.     ((CIFAcctNum5 IS NULL) OR (LEN(LTRIM(CIFAcctNum5)) = 0))
  26. AND
  27.     ((CIFAcctNum6 IS NULL) OR (LEN(LTRIM(CIFAcctNum6)) = 0))
  28. AND
  29.     ((CIFAcctNum7 IS NULL) OR (LEN(LTRIM(CIFAcctNum7)) = 0))
  30. AND
  31.     ((CIFAcctNum8 IS NULL) OR (LEN(LTRIM(CIFAcctNum8)) = 0))
  32. AND
  33.     ((CIFAcctNum9 IS NULL) OR (LEN(LTRIM(CIFAcctNum9)) = 0))
  34. AND
  35.     ((CIFAcctNum10 IS NULL) OR (LEN(LTRIM(CIFAcctNum10)) = 0))
  36. AND
  37.     ((CIFAcctNum11 IS NULL) OR (LEN(LTRIM(CIFAcctNum11)) = 0))
  38. AND
  39.     ((CIFAcctNum12 IS NULL) OR (LEN(LTRIM(CIFAcctNum12)) = 0))
  40. AND
  41.     ((CIFAcctNum13 IS NULL) OR (LEN(LTRIM(CIFAcctNum13)) = 0))
  42. AND
  43.     ((CIFAcctNum14 IS NULL) OR (LEN(LTRIM(CIFAcctNum14)) = 0))
  44. AND
  45.     ((CIFAcctNum15 IS NULL) OR (LEN(LTRIM(CIFAcctNum15)) = 0))
  46. AND
  47.     ((CIFAcctNum16 IS NULL) OR (LEN(LTRIM(CIFAcctNum16)) = 0))
  48. AND
  49.     ((CIFAcctNum17 IS NULL) OR (LEN(LTRIM(CIFAcctNum17)) = 0))
  50.  
  51. DROP TABLE ##Temp
  52.  
Apr 12 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.