Stuart McLachlan
stuart at lexacorp.com.pg
Wed Mar 4 04:50:17 CST 2009
I've run into very similar problems trying to use conditional execution and multiple selects in a SP called through ODBC (in my case using the SQLTools ODBC library with PowerBasic). It appears to be a limitation of the ODBC/ADODB drivers. In the end I had to split the one SP which worked fine in SQL Server into several separate calls to simple SPs and handle the conditional stuff in my PB code between calls. Sorry to be the bearer of bad tidings, but I suspect you may have to end up doing the same. -- Stuart On 4 Mar 2009 at 10:04, Paul Hartland wrote: > 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.... > > > > > > > -- > Paul Hartland > paul.hartland at googlemail.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com