colin.counsell (5/23/2012)
Hi
I added code to the sp last night set ARITHABORT and QUOTED_IDENTIFIER.
It has just finished after 3 hours 10 mins!
It ran in 50 mins on the old server and ran in 40 mins yesterday on the new server ecxecuting it from SSMS.
We reorganised all the indexes yesterday before anyone throws that idea in.
Anbody any other ideas?
Thanks
I'm sorry for necro'ing an old thread, but...
You should be aware that ARITHABORT is a run time option and QUOTED_IDENTIFIER is a parse time option. I draw your attention to the SET (Transact-SQL) page:
Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored.
This means:
1. Your SQL Agent job step's SQL should have SET ARITHABORT ON before EXEC dbo.yourProcName
2. Your stored procedure should have SET QUOTED_IDENTIFIER ON before CREATE PROC dbo.yourProcName...
Hope this helps,
Ant.
See also:
SET ARITHABORT (Transact-SQL)
SET QUOTED_IDENTIFIER (Transact-SQL)