Stored Procedure Not returning values!!!

Results 1 to 3 of 3

Thread: Stored Procedure Not returning values!!!

  1. #1
    Join Date
    Dec 1969

    Default Stored Procedure Not returning values!!!

    Hi folks,<BR><BR>I have a stored procedure that works fine on our dev server, but which doesn&#039;t return anything to the ASP page on the live server. The SP is definately returning the values, as if you run it in the Query Analyzer on the live server, it works. On the live sertver however, it doesn&#039;t even open the recordset in the ASP!<BR><BR>Here&#039;s the code:<BR><BR>CREATE PROCEDURE [AddNewRegistration]<BR>@Email varchar(75),@Password varchar(25)<BR><BR>AS<BR>insert into Users<BR>(US_Email, US_Password) values (@Email, <BR>@Password<BR>)<BR><BR>SELECT US_ID,US_FRIENDLY FROM USERS WHERE US_FRIENDLY= @@IDENTITY<BR><BR>To call the procedure I am using the following code on the page:<BR>Set rsUser = Conn.Execute("AddNewRegistration &#039;;,&#039;password&# 039;")<BR><BR>Anyone got any ideas? I&#039;m stumped! It works if you do a separate stored procedure afterwards, but I would like to do it all in one go if possible.<BR><BR>Thanks in advance,<BR><BR>Tim.<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Stored Procedure Not returning values!!!

    I&#039;d go to SQL Server and check the permissions on the Stored Procedure. Make sure that whatever user is specified in the DSN connection has exec rights for the procedure. Or just make sure it has the same permissions as other procedures that work.

  3. #3
    Andres C. Guest

    Default RE: Stored Procedure Not returning values!!!

    Try adding this line before you try to read the recordset:<BR><BR>Set rsUser = rsUser.NextRecordSet<BR><BR>I suspect the problem is that you get a result back from the INSERT statement (which is an empty return), and another recordset is returned after that with the data you expect. NextRecordSet will give you an error if there is no other recordset. If not, then try reading the data and see if it&#039;s there.<BR><BR>You might have different versions of ADO (MDAC) on these servers, which might be why it works on one but not the other. <BR><BR>Hope this help.<BR>Andres

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts