Re: Really tough ADO Stored Procedure Question. Please Help!!!

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 10:07:01 -0400

Is SET NOCOUNT ON the first line in the sproc?

--Mary

On Thu, 19 Aug 2004 13:49:54 +0100, "Ian" <ian@NoWhere.com> wrote:

>Hi
>
> I was hopping some one can give me an explanation for this.
>
> Stored Procedure: (All this is in one stored procedure but this is the
>basis
> of what it does)
>
> Step 1: I have a stored procedure that selects some records from
>tblTableOne
> into #Temp1.
> Step 2: It then goes to and excel workbook and gets some data and holds
>that
> in a #Temp2.
> Step 3: Then I run an update statement on #Temp1 using the data in #Temp2.
> Step 4: Once this is done I then Insert the data from #Temp1 back into
> tblTableOne as new records.
>
>
> Scenario:
>
> When running in debug mode in Query Analyser the stored procedure works
> perfectly.
> When I then check the data it has all been accurately updated and inserted
> back into tblTableOne.
>
> When I run the Stored procedure from using the "exec sp_Name" it works
> perfectly also.
>
> BUT.
>
> When I run the Stored procedure from my VB 6 application at first glance it
> seems to work fine in the fact that there is no errors and when the data is
> checked it is there.
> However the data has not been changed to the Data in #Temp2. So something
>is
> going wrong with Step 2 or 3.
>
> So I then added an output parameter to the SP to check that there was data
> being imported form Excel and there were the correct no of records. and
> there are.
>
> So that has now narrowed it down to Step 3. Since it works fine from Query
> Analyser and no errors are being thorn by the stored procedure. I have also
> added Error handling just after the UPDATE statement and there is no error
> in @@ERROR.
>
> I have been battling with this for about 12 hours now.
>
> Please any thing that I could try would be welcome.
>
> I have been trying different database option but no luck. Maybe I am
>setting
> them incorrectly or in the wrong place.
>
> My guess is that I think that just that one UPDATE statement is rolling
> back.
> It doesn't sound possible I know, but I am watching it.
>
> I have tried creating a transaction inside the SP and I have also tried
> creating a transaction for the database connection using ADO. and still no
> joy.
>
> I know it is a tough one.
>
> I am willing to email the SP and sample data it your up for a challenge.
>
> Thanks
>
> Ian
>
>

Relevant Pages

  • Re: How can a recordset returned by a sproc be updated?
    ... > While I run the test on my box I haven't seen update statement as OP said. ... >> SQL Server MVP ... >>> ADO. ... >>> internal text of the stored procedure? ...
    (microsoft.public.sqlserver.programming)
  • Re: Cannot update identity column PointOfSaleID.
    ... The sp_MSupd_PointOfSales stored procedure checks to see if the bit flag ... set and if so it executes an update statement which includes the identity ... for the identity column, it executes fine and chooses the else path. ...
    (microsoft.public.sqlserver.replication)
  • A few T-SQL questions...
    ... I have a few T-SQL questions that i'm hoping someone can answer for me. ... trips (i'm not using a stored procedure for this for several reasons). ... What's the best way to write an update statement so that only fields ... I have a table that has a trigger that does ...
    (microsoft.public.sqlserver)
  • Re: Updating data
    ... Did you look at the error message? ... I assume that you ran an UPDATE statement similar to the one Sue ... Since there is no stored procedure call in that statement, ... WHERE JOB_STATUS = 'For Sale/Lease' ...
    (microsoft.public.sqlserver.odbc)
  • Re: Sql Stored Procedure Updates
    ... > I have fairly typical Stored Procedure that should be updating a table, ... an update statement affects 0 rows. ... So when you code an update statement ... and raise an error yourself after the update. ...
    (microsoft.public.dotnet.framework.adonet)