Computed Column Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Computed Column Help

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-12-17 : 07:19:58

I'm trying to run a SP that takes data from Table A and backs it up in Table B. Table B has a computed column in it so I've set my code up to read like:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[mp_whsStockWatch_viewsWTableMTS2BKUP]

AS
SET NOCOUNT ON

BEGIN
SET ARITHABORT ON

INSERT INTO TableB

When i try to execute this Sp via a scheduled task this message is outputted into the error log file:
"17/12/2010 12:17:02,modMain,SubMain,5,INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods."

Sachin.Nand

2937 Posts

Posted - 2010-12-18 : 05:11:02

Post the whole code
Also what happens when you remove all the SET option from the SP ?

PBUH

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-12-20 : 05:11:27

[code]SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[mp_whsStockWatch_viewsWTableMTS2BKUP]

AS
SET NOCOUNT ON

BEGIN

SET ARITHABORT ON --PMC

INSERT INTO mp_whsStockWatchMTS2BKUP
SELECT MAX(Category1) AS Category1,
Region,
Site,
Area,
MAX(Category2) AS Category2,
MAX(COALESCE(Category3, 'NULL')) AS Category3,
--MAX(Category3) AS Category3,
MAX(COALESCE(Category7, 'NULL')) AS Category7,
--MAX(Category7) AS Category7,
Resource,
MAX(COALESCE(Description, 'NULL')) AS Description,
--MAX(Description) AS Description,
SUM(Quantity) AS Quantity,
MAX(UM) AS UM,
SUM(EURValue) AS EURValue,
SUM(GBPValue) AS GBPValue,
MAX(Price) AS Price,
MAX(Currency) AS Currency,
QAStatus,
Days,
CAST(CONVERT(DATETIME,GETDATE(),105) AS VARCHAR(35)) AS CurrDate,
Age
FROM mp_whsStockWatchVw1
GROUP BY Region,
Site,
Area,
Resource,
UM,
QAStatus,
Days,
Age
ORDER BY Category1

END

SET NOCOUNT OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO[/code]
I tried removing all the SET options but i got the following error:
20/12/2010 10:09:11,modMain,SubMain,5,INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources