Correct SP:s not executing

  • Thread starter Thread starter Kenneth Bohman
  • Start date Start date
K

Kenneth Bohman

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
 
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
 
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 said:
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

spending all
yesterday Server N'GBP', N'2003Q5', @Timestamp
=


.
Click to expand...
Click to expand...
 
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


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" <[email protected]> wrote
Click to expand...
in
message
I often use ADO.NET and SP:s, but now I'm stuck
Click to expand...
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
Click to expand...
following
SET options have
incorrect settings 'ARITHABORT'". I've checked the
Click to expand...
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
Click to expand...
@FromCurrencyID =
N'GBP',
@ToCurrencyID
= N'USD', @ExchangeRate = 1.600000, @ReportPeriodID
Click to expand...
=
N'2004Q3',
@ReportPeriodExchangeRateID = @P1 output,
Click to expand...
@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
Click to expand...
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
Click to expand...
to
look?
Kind regards,

Kenneth Bohman




.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top