Recordset ok with one stored proc but not another. 1

[grnzbra]

I have an asp page that opens a recordset based on a stored procedure. It works fine. I have a second stored procedure that takes the exact same input parameters as the first and creates the exact same output.
When, in SQL Server Query Analyzer the input parameters have their values set and then and execute commands are run, one right after the other, both SPs run fine and give the same output.

When, in the .asp page I change the name of the stored procedure that is the source for the recordset, I get the following error message:

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/testpage4.asp, line 138

Lines 137 and 138 are:

Set rsAppTest = spAppTest.Execute
While (NOT rsAppTest.EOF)

The permissions on the SP have been set to allow it to run.

Any ideas why this might not work?

 

[gmmastros]

Check your stored procedure. Make sure that there is a line at the top that is:

[!]SET NOCOUNT ON[/!]

This line should be the first one immediately after your parameters.





-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[grnzbra]

Thanks, but that did not help. The proc that works also does not have that line.

 

[gmmastros]

Can you show the stored procedure?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[grnzbra]

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE sel_ApplicantManifest(

--STORED PROCEDURE PARAMETERS
@sitecode varchar(3),
@apptmonth varchar(2),
@apptday varchar(2),
@apptyear varchar(4),
@appthour varchar(2) = Null,
@scheduler char(1) = Null,
@appid varchar(25) = Null
)
AS

DECLARE @srcfle varchar(7)

--LOCAL VARIABLE DECLARATION
DECLARE @MainSQL as varchar(5000)
DECLARE @NI90SQL as varchar(5000)
DECLARE @I90SQL as varchar(5000)
DECLARE @C4SQL as varchar(5000)

DECLARE @DataSort as varchar(5000)

DECLARE @SiteCriteria as varchar(5000)

SET @appthour = ISNULL(@appthour, '%')
SET @appid = ISNULL(@appid, '%')
SELECT @srcfle =
CASE @scheduler
WHEN '1' THEN 'SNAPn'
WHEN '2' THEN 'SNAP'
WHEN '3' THEN 'CLAIMS4'
ELSE '%'
END
PRINT '@srcfle = ' + @srcfle
-- ****************************************************
-- NON I90 MANIFEST DATA
-- ****************************************************

SELECT ASC_CODE AS SITECODE,
CONVERT(varchar(10), Appointment_Dt, 101) AS APPT_DT,
CONVERT(varchar(10), Appointment_tm, 108) AS APPT_TIME,
CAST(Application_ID AS varchar) AS APP_ID,
Alien_No AS ANUMBER,
LAST_NAME, FIRST_NAME, MIDDLE_NAME,
Form_No AS FORM_TYPE, FP_Bio_Code AS BIOCODE,
STREET_ADDRESS_1, STREET_ADDRESS_2,
CITY, State_Code AS STATE, ZIP_CODE AS ZIPCODE
FROM Appointments
WHERE ASC_CODE = @sitecode
AND Source_File LIKE @srcfle
AND DATEPART(m, Appointment_DT) = @apptmonth
AND DATEPART(d, Appointment_Dt) = @apptday
AND DATEPART(yyyy, Appointment_DT) = @apptyear
AND CONVERT(CHAR(2), Appointment_Tm, 114) LIKE @appthour
AND Application_Id LIKE @appid
ORDER BY APPT_TIME, LAST_NAME, FIRST_NAME
RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[gmmastros]

try this:

ALTER     PROCEDURE sel_ApplicantManifest(

    --STORED PROCEDURE PARAMETERS
    @sitecode varchar(3),
    @apptmonth varchar(2),
    @apptday  varchar(2),
    @apptyear varchar(4), 
    @appthour varchar(2) = Null, 
    @scheduler char(1) = Null, 
    @appid varchar(25) = Null
)
AS 
[!]SET NOCOUNT ON[/!]

DECLARE @srcfle varchar(7)

--LOCAL VARIABLE DECLARATION
DECLARE @MainSQL as varchar(5000)
DECLARE @NI90SQL as varchar(5000)
DECLARE @I90SQL as varchar(5000)
DECLARE @C4SQL as varchar(5000)

DECLARE @DataSort as varchar(5000)

DECLARE @SiteCriteria as varchar(5000)

SET @appthour = ISNULL(@appthour, '%')
SET @appid = ISNULL(@appid, '%')
SELECT @srcfle =
    CASE @scheduler
      WHEN '1' THEN 'SNAPn'
      WHEN '2' THEN 'SNAP'
      WHEN '3' THEN 'CLAIMS4'
      ELSE '%'
    END
[!]--PRINT '@srcfle = ' + @srcfle[/!]
-- ****************************************************
-- NON I90 MANIFEST DATA
-- ****************************************************

    SELECT ASC_CODE AS SITECODE,
        CONVERT(varchar(10), Appointment_Dt, 101)  AS APPT_DT, 
        CONVERT(varchar(10), Appointment_tm, 108)  AS APPT_TIME, 
        CAST(Application_ID AS varchar) AS APP_ID, 
        Alien_No AS ANUMBER,
        LAST_NAME, FIRST_NAME, MIDDLE_NAME,
        Form_No AS FORM_TYPE, FP_Bio_Code AS BIOCODE, 
        STREET_ADDRESS_1, STREET_ADDRESS_2, 
        CITY, State_Code AS STATE, ZIP_CODE AS ZIPCODE
    FROM Appointments
    WHERE ASC_CODE = @sitecode
    AND Source_File LIKE @srcfle
    AND DATEPART(m, Appointment_DT) = @apptmonth
    AND DATEPART(d, Appointment_Dt) = @apptday
    AND DATEPART(yyyy, Appointment_DT) = @apptyear
    AND CONVERT(CHAR(2), Appointment_Tm, 114) LIKE @appthour
    AND Application_Id LIKE @appid
ORDER BY APPT_TIME, LAST_NAME, FIRST_NAME
RETURN

When you have a stored procedure that has multiple statements, ADO will return multiple recordsets, which messes things up. If there is a single query, you do not have this problems.

For example, when you run a stored procedure in a sql query window, there is a message tab. In it, you will see the number of rows affected by the query. If there is just one message, you will not have a problem. If there are multiple messages, the message data is sent back to ASP (through the ADO object), messing up your code in the process.

I could make other comments about the actual procedure itself (best practice type of stuff). But I will only do that if you want me to.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[grnzbra]

I just noticed the extraneous declared variables (left over from the original sp) and took them out. It still doesn't run.

 

[DotNetGnat]

can you show your code on testpage4.asp page...and what is the error that you get when you say "it still doesnt run"

-DNG

 

[grnzbra]

Thank you. That got it. I put the SET NOCOUNT ON

statement in the wrong place before.

Any other comments would be greatly appreciated.

 

[gmmastros]

Well.... there's actually a bunch of things, so please don't get depressed.

1. there's no reason to have separate columns for date and time. I would merge the columns Appointment_Dt and Appointment_Tm.

2. Anytime you use varchar, you should specify the size. Make this a habit.

CAST(Application_ID AS varchar[!](SomeNumberHere)[/!]) AS APP_ID,

3. You may be able to get better performance out of this by making it more sargable. Basically, if you had an index on ASC_Code and Appointment_dt, that index would not be used based on the way you built your where clause. By changing things slightly, you can cause your query to do an index seek instead of an index scan, which will improve performance. Now, please understand that the improvement will ONLY be seen if you have an index on ASC_Code and Appointment_Dt AND if you have many rows for the same ASC_Code but with different appointment_dt's. Anyway, what I would do is...

-- near the top, before the final query.
Declare @AppDate DateTime
Set @AppDate = Convert(DateTime, @apptyear + Right('00' + @apptmonth, 2) + Right('00' + @aptday, 2)

then, change:

AND DATEPART(m, Appointment_DT) = @apptmonth AND DATEPART(d, Appointment_Dt) = @apptday AND DATEPART(yyyy, Appointment_DT) = @apptyear

To

AND Appointment_DT = @AppDate

Basically, if you have a where clause (or join clause) condition with a function acting on a column, you are preventing SQL Server from using any index that may exist on the column. This causes a scan instead of a seek.

4. a similar concept applies to the time part of the where clause.

Instead of:
AND CONVERT(CHAR(2), Appointment_Tm, 114) LIKE @appthour

You could do:

And Appointment_Tm Between DateAdd(Hour, @appthour, 0) And DateAdd(Hour, 1 + @appthour, 0)

You'll get the same data, but notice the function is not on the left, it's on the right and only affects scalar values (@appthour) which will be evaluated once. Effectively speeding up your query.

5. Lastly, the way you deal with optional parameters is problematic depending on your data. If you want to ignore a certain parameter, you appear to be passing NULL in to the parameter, but, the way you deal with it will cause rows with null values to be filtered out even when you pass in a null.

For example, (when you pass NULL in for @AppId)....

You set it to a percent and then do a like comparison. You're probably thinking that this will return all the data, and in some circumstances, it will. But, if your Application_ID column allows NULL, the row will be filtered out because NULL does not compare (even with a like query) to anything (even a %).

Instead of:

AND Application_Id LIKE @appid

You should do....

And (@AppId Is NULL or Application_ID = @AppId)

Of course, if you do this, you should remove the line earlier that sets @AppId to %.

I know this is a lot of information to throw at you. And... you should understand that following all of this advice may not make any difference to this particular query. But I hope this gives you a couple things to think about. Things that may make your queries better.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[grnzbra]

Thank you for the suggestions. However, I must disagree with #1.
With the time in the same field as the date,

WHERE Appointment_Dt BETWEEN @startdate AND @enddate

must become

WHERE Appointment_Dt >= @startdate AND < DATEADD("d", 1, @enddate)

The original designer of the system forgot or wasn't aware of this and we discovered that all of our reports had dropped the end date appointments.

Furthermore, dates should be stored in text fields as yyyymmdd because in the value actually stored in a DateTime field is relative to some base date. In our SQL Server system that base date is 1/1/1900. However, there is one table we are aware of that stores dates based on 12/30/1899 and all the dates in that table are two days off. Again, something the original designer wasn't aware of and something we only discovered by accident. This, of course would require a major rewrite of the system, so we must do a DATEADD every time we use that table.

Also,

FROM tblDates LEFT JOIN tblAppointments
ON date = Appointment_Dt

wouldn't work except for appointments at midnight.

If I'm missing something here, I would appreciate an elaboration.

 

[gmmastros]

In my opinion, dates should be stored as dates. But I also think it's not worth a major re-write, either.

When handled correctly, dates are easier to deal with than storing them in a varchar column. The common problem with storing dates in a varchar column is 'bad data' I cannot begin to tell you how many questions I've seen in the forums regarding bad date data in a column. I understand that if you are 'careful' enough, you may never have a problem, but it really does require diligience to make sure your data is properly protected. When you use a DateTime data type, you don't need to worry about the data. It's either NULL or a valid date/time.

That being said, I really only wanted to point out some helpful "best practice" tips regarding your query.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom