0

I am using ASP to execute a stored procedure. When I run the stored procedure in SQL Server Management Studio, I get the columns returned, however nothing is returned in ASP.

The stored procedure is a bit different in that it has a select statement after an update statement. If I remove the update statements in the stored procedure, the data is being returned to ASP.

Here is the ASP

<% 'On error resume next %>
<% Session.LCID=2057 %>

<% 

MM_etdocs_STRING = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=MSSQL03\Websys;Initial Catalog=Projects;User ID=*****;Password=****;" 

Set rsnew = Server.CreateObject("ADODB.Recordset")
rsnew.ActiveConnection = MM_etdocs_STRING
rsnew.Source="EXEC eacActionTender 7003,'ZlBhH0APGg','EAC\USER',1,'d'"
rsnew.CursorType = 0
rsnew.CursorLocation = 2
rsnew.LockType = 1
rsnew.Open()

response.write rsnew("Status")
'response.write rsnew("auth_decision")

flds = rsnew.Fields.Count %>

<div><br><%response.write(flds)%><br>

And here is the stored procedure

/*    ==Scripting Parameters==

    Source Server Version : SQL Server 2012 (11.0.6607)
    Source Database Engine Edition : Microsoft SQL Server Enterprise Edition
    Source Database Engine Type : Standalone SQL Server

    Target Server Version : SQL Server 2017
    Target Database Engine Edition : Microsoft SQL Server Standard Edition
    Target Database Engine Type : Standalone SQL Server
*/

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[eacActionTender] 
    @tid INT, 
    @key CHAR(10), 
    @login NVARCHAR(255), 
    @approve BIT, 
    @message NVARCHAR(2000) 
AS
    /*IF EXISTS(SELECT * FROM t_ActionTender WHERE tid=@tid AND keygen=@key AND auth_decision IS NULL)*/
BEGIN
    IF(@approve = 1)
    BEGIN
        UPDATE t_MiniTender 
        SET Sent = 1 
        WHERE [ID] = @tid
    END

    UPDATE t_ActionTender 
    SET auth_login = @login,
        auth_decision = @approve,
        auth_text = @message,
        auth_date = GETDATE() 
    WHERE tid = @tid 
      AND keygen = @key  
      AND auth_decision = NULL

    SELECT 
        Status = 'OK', 
        viewNewMiniTender.*, 
        t_ActionTender.SentTo AS ActionSentTo, 
        t_ActionTender.auth_decision, 
        t_ActionTender.auth_text, 
        t_ActionTender.auth_date 
    FROM 
        t_ActionTender 
    INNER JOIN 
        viewNewMiniTender ON t_ActionTender.tid = viewNewMiniTender.[ID] 
    WHERE 
        t_ActionTender.tid = @tid 
        AND keygen = @key
END
/*ELSE
  BEGIN
    SELECT Status='Error',Val=@approve
  END*/

I have gave the user accessing the database and view as high as permissions as I can also had colleagues working on the issue.

Any help would be appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
kil lie
  • 89
  • 1
  • 8
  • Couldn't find a resolution, had to splt up the stored procedure into two separate ones. – kil lie Jan 25 '18 at 14:21
  • 2
    It’s the classic [add `SET NOCOUNT ON`](https://stackoverflow.com/a/35869388/692942) to the start of your stored procedure or ADODB will treat the `UPDATE` statement count as a seperate closed `ADODB.Recordset` object. The other way around this is instead of the one `ADODB.Recordset` you’re expecting you have two *(because of the reason I just stated)* so you can use `Set rsnew = rsnew.NextRecordset` to pull the next recordset. – user692942 Jan 25 '18 at 21:57
  • On a side note, [using `ADODB.Command`](https://stackoverflow.com/a/21944948/692942) is better and safer when it comes to calling stored procedures, especially parameterised ones. – user692942 Jan 25 '18 at 22:40

0 Answers0