anonymous
Wed Jan 07 08:09:48 CST 2004
You're a star Miha!!
All it took was that I removed one of two indexes I had
on the table.
Kenneth
>-----Original Message-----
>Hi Kenneth,
>
>Check this article if it helps you:
>
> PRB: Error "INSERT Failed" When You Update Table
Referenced in an
>Indexed View
>
http://support.microsoft.com/default.aspx?
scid=kb;en-us;305333
>
> --
> Miha Markic - RightHand .NET consulting &
development
> miha at rthand com
> www.rthand.com
>
>
>"Kenneth Bohman" <kbohman@itinitiative.co.uk> wrote in
message
>news:089401c3d512$7394e010$a501280a@phx.gbl...
>> Hi Miha,
>>
>> Here they are.
>>
>> They show up correctly in SQL Profiler and execute
>> correctly under SQL Analyzer and the structure is
exactly
>> the same as in other SP:s. So I can see no reason why
>> they should be rolledback either.
>>
>> Regards,
>>
>> Kenneth
>>
>> 1) SP that renders ARITHABORT error
>> I have tried setting ARITHABORT to both ON and OFF in
the
>> SP, but to no avial. Any idea why that setting should
>> influence (in this case)?
>>
>> CREATE PROCEDURE [dbo].
[InsertReportPeriodExchangeRates]
>> (
>> @ReportPeriodID nvarchar(8),
>> @FromCurrencyID nchar(3),
>> @ToCurrencyID nchar(3),
>> @ExchangeRate decimal (10,6),
>> @ReportPeriodExchangeRateID int OUTPUT,
>> @TimeStamp timestamp OUTPUT
>> )
>> AS
>> SET NOCOUNT OFF;
>>
>> INSERT INTO [ReportPeriodExchangeRates]
>> ([ReportPeriodID], [FromCurrencyID], [ToCurrencyID],
>> [ExchangeRate])
>> VALUES (@ReportPeriodID, @FromCurrencyID,
@ToCurrencyID,
>> @ExchangeRate);
>>
>> SELECT @ReportPeriodExchangeRateID = SCOPE_IDENTITY()
>>
>> IF @@ROWCOUNT = 1
>> SELECT @TimeStamp = TimeStamp FROM
>> ReportPeriodExchangeRates
>> WHERE (ReportPeriodExchangeRateID =
>> @ReportPeriodExchangeRateID);
>> GO
>>
>>
>> 2) SP that does not execute (or is rolled back)
>> CREATE PROCEDURE [dbo].[DeleteReportPeriods]
>> (
>> @Original_ReportPeriodID nvarchar(8),
>> @TimeStamp timestamp
>> )
>> AS
>> SET NOCOUNT OFF;
>> DELETE FROM ReportPeriods WHERE (ReportPeriodID =
>> @Original_ReportPeriodID) AND (TimeStamp = @TimeStamp
OR
>> @TimeStamp IS NULL AND TimeStamp IS NULL);
>> GO
>>
>> >-----Original Message-----
>> >Hi Kenneth,
>> >
>> >What is the body of sp?
>> >
>> >--
>> >Miha Markic - RightHand .NET consulting & development
>> >miha at rthand com
>> >www.rthand.com
>> >
>> >"Kenneth Bohman" <developer@itinitiative.co.uk> wrote
in
>> message
>> >news:O9Lj5UP1DHA.2180@TK2MSFTNGP12.phx.gbl...
>> >> I often use ADO.NET and SP:s, but now I'm stuck
after
>> spending all
>> >yesterday
>> >> on two issues
>> >>
>> >> 1) ARITHABORT
>> >> A form calls SP to insert a record, but executing it
>> only results in the
>> >> following message :"Insert failed becase the
following
>> SET options have
>> >> incorrect settings 'ARITHABORT'". I've checked the
SQL
>> Server
>> >documentation
>> >> and can't see how it at all applies in this case.
>> >>
>> >> Tracing the command in SQL Profiler this is what it
>> looks like
>> >> exec insertReportPeriodExchangeRates
@FromCurrencyID =
>> N'GBP',
>> >@ToCurrencyID
>> >> = N'USD', @ExchangeRate = 1.600000, @ReportPeriodID
=
>> N'2004Q3',
>> >> @ReportPeriodExchangeRateID = @P1 output,
@TimeStamp =
>> @P2 output
>> >>
>> >> If I execute that command from SQL Analyzer it works
>> just fine. No error
>> >> message
>> >>
>> >> 2) .SP does not execute at all
>> >> Another form calls a SP to delete a record, but the
SP
>> is never executed!
>> >> How is that possible?
>> >>
>> >> Tracing the command in SQL Profiler this is what it
>> looks like
>> >> exec deleteReportPeriods @Original_ReportPeriodID =
>> N'2003Q5', @Timestamp
>> >=
>> >> 0x00000000000083C1
>> >> If I execute that command from SQL Analyzer it works
>> just fine.
>> >>
>> >> All in all about 200 SP:s are called in the
>> application, and these are the
>> >> only one that failing. Again, if I run them from SQL
>> Analyzer they execute
>> >> correctly, so there is something odd happing in
>> ADO:NET. All the commands
>> >> are executed from inherited forms. Any ideas where
to
>> look?
>> >>
>> >> Kind regards,
>> >>
>> >> Kenneth Bohman
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>