[dba-SQLServer] Can't get resuts from sproc

You might try the following kludge:

At the beginning of the sproc, before all other code, have a dummy select statement that mirrors the final select that you want to execute:
pseudo code:  SELECT 'Success' as Result,
        ,'Some text' as nvcPayroll
        ,[Column 1]
        ,[Column 2]
        ...
        FROM MyTable2
        WHERE 1=0
Obviously this will not return any rows, but it will let the front end know what kind of result set to expect.  Then have the rest of the sproc as below.  I'm not certain this will solve your problem, but I have used in in other instances successfully.

Regards,  d lewis



To all,

Sorry about cross posting this but really having a nightmare, I have the
following store procedure in SQL Server 2005 :

usp_MyStoredProcedure
 (
  @nvcPayroll  [nvarchar](50),
  @dtiJobDate  [datetime],
  @nvcCode  [nvarchar](1)
 )
AS
BEGIN
 DECLARE @intUpdateFailed INT
 UPDATE dbo.MyTable
 SET AvailabilityCode=@nvcCode
 WHERE PayrollNo =  @nvcPayroll AND
  JobDate  =  @dtiJobDate AND
   (
  @nvcCode =  'A'  OR
  @nvcCode =  'N'
   )                AND
   (
  AvailabilityCode=  'A'  OR
  AvailabilityCode=  'N'
   )

 SET @intUpdateFailed=@@ROWCOUNT
 DELETE FROM dbo.MyTable2
 WHERE PayrollNo=@nvcPayroll
 INSERT INTO MyTable2 (WebResult, PayrollNo)
 SELECT CASE WHEN @intUpdateFailed>0 THEN 'Success' ELSE 'Failed' END,
@nvcPayroll
 SELECT *
 FROM MyTable2
 WHERE PayrollNo=@nvcPayroll
END

If I run this in SQL Server seems to work fine, and the SELECT * FROM
MyTable2 WHERE PayrollNo=@nvcPayroll returns all the fields.  However I have
a simple piece of VB code as below:

Dim rs As ADODB.Recordset
If DE.Conn.Start = adStateClosed Then
    DE.Conn.Open
Endif
Set rs = DE.Conn.Execute(usp_MyStoredProcedure '999999', '" &
format(Date,"mm/dd/yy") & "','N'")
msgbox rs.fields(0)

DE.Conn.Close

' DE.Conn is a DataEnvironment connection which links to the SQL Server

The vb code returns Item cannot be found in the collection corresponding to
the requested name or ordinal.

However if I run the SP, then comment out everything except the SELECT
statement it returns the resultset.

Can anyone tell me what is happening or what I am doing wrong please.

Thanks in advance for any help on this....





The contents of this e-mail message and its attachments are covered by the Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended solely for the addressee(s) hereof. If you are not the named recipient, or the employee or agent responsible for delivering the message to the intended recipient, or if this message has been addressed to you in error, you are directed not to read, disclose, reproduce, distribute, disseminate or otherwise use this transmission.  If you have received this communication in error, please notify us immediately by return e-mail or by telephone, 530-893-3520, and delete and/or destroy all copies of the message immediately.