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 2000 Forums

SQL Server Development (2000)

Computed Column help

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-26 : 18:19:44

Environment SQL 2K

I have a CC that I am calling trigger date.
[wfstarttype] is an int

the field looks like this:

[triggerdate] AS (case [wfstarttype] when 1 then (dateadd(day,[wfdaydiff],[orderdate])) when 2 then (dateadd(day,[wfdaydiff],[closingdate])) when 3 then [userdate] when 0 then [userdate] end)

When I try to update the table that holds this field, I get the below error. I have tried setting ARITHABORT, ARITHIGNORE, QUOTED IDENTS ON AND OFF.

Has anyone come across this problem before?

ERROR:
UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'.

PS It seems I can get it to work when I call it from with SQL but the ASP call fails. I even took off all the parameters in ASP and hardcoded the SPROC. Looks like this.

ASP CALL:
with objcmd
.activeconnection=strconn
.commandtext = "ps_order_workflow_chg_antcl"
.commandtype = adcmdstoredproc
.execute()
end with

SPROC [ modified 1000 times so far ]

ALTER PROC ps_order_workflow_chg_antcl

AS

-- --FIRST UPDATE ORDERS
-- UPDATE dbo.tblorders SET
-- antclosingdate = @antcldate
-- WHERE orderid = @orderid

--NOW UPDATE WORK FLOW
IF EXISTS (SELECT 1 FROM dbo.tblorders_workflow WHERE orderid = @orderid)
BEGIN
UPDATE dbo.tblorders_workflow SET
[closingdate] = '12-2-2002'
WHERE orderid = 1
END

slow down to move faster...

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-26 : 18:41:32

Ok,

I changed the default connections to "ARITHMETIC ABORT ON" and it works. Why can't I programatically set this in the SPROC and not have to make a global server setting change?

slow down to move faster...

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-26 : 18:43:17

Also if you are not returning anything from your stored procedure you can use the following in your asp to improve performance.

.Execute ,,adExecuteNoRecords

instead of
.Execute()

I would also do the following in the sp's ADO has been know to have problems when you don't have your sp's looking like this:

SET NOCOUNT ON

''rest of sp here

SET NOCOUNT OFF

Edited by - ValterBorges on 12/26/2002 18:43:44

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-26 : 18:48:39

Is this what you tried in your sp.

SET ARITHABORT ON

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-26 : 20:50:38

Thanks

I actually do set

COUNT ON AND OFF

AND

I have tried

setting

SET ARITHABORT ON
SET ARITHABORT OFF

SET ARITHIGNORE ON
SET ARITHIGNORE OFF

I am not sure why these don't work but setting global settings do. It looks like someone else had this issue. Could it be a bug in SQL.

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=11771

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources