Job Failed

Question

Job Failed

zi

SSCrazy

Points: 2387
More actions
January 5, 2015 at 3:12 am

#299958

Dear Experts
I have a job running everyday, a sql update statement, it was working very well till today
yesterday i worked on the tuningadvisor and I applied the recommendations
the error is
Msg 1934, Sev 16, State 1, Line 1 : UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000]
The same update statement is working well on management studio !!
can any one help me to solve this problem ?
Thanks lot

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Drop the indexed view which you created with the tuning adviser and next time test the tuning adviser's recommendations, don't apply them blindly

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zi SSCrazy Points: 2387 More actions · Answer 2

Thanks for replying

can you tell me the syntax of dropping indexed view

Thanks lot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

DROP VIEW <Name of the View>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zi SSCrazy Points: 2387 More actions · Answer 4

sorry for annoying , Kindly find the attached file , it contains a pic for the tuning advisor,

the first 2 tables EPISODE is the edited table

it created an index

what should i write to drop it, is it a view or a clustered index

Thanks lot

zi SSCrazy Points: 2387 More actions · Answer 5

any help please

what should i write exactly

Thanks lot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

zi (1/5/2015)
any help please

Patience. I'm posting during lunch break, my job takes priority.

To be honest, I'd recommend dropping every single thing that DTA created. You should NEVER blindly implement recommendations, you should have tested every one of them before implementing in production.

Drop the whole lot, then test them one by one in a test environment and only implement the ones that improve performance and don't break other things.

p.s. And stop using the sa account. Terrible security practice, and on a medical app too.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zi SSCrazy Points: 2387 More actions · Answer 7

zi

SSCrazy

Points: 2387

January 5, 2015 at 4:17 am

#1767704

so how to drop a statistics

Thanks lot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Is google broken today?

https://www.google.com/?q=drop+statistics+sql+server#q=drop+statistics+sql+server

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 9

Here's the entry in the Books Online for DROP STATISTICS.

I strongly recommend just keeping a link to the Books Online. That's where you'll need to go for most of your syntax questions. It'll be much, much faster than relying on us to get you answers.

I'm with Gail. Just blindly applying every single suggestion by the Tuning Advisor is a poor strategy. You need to take those things as suggestions, understand why it's suggesting them, test them, validate that they do anything at all before you apply them to your production machine. And then, I recommend you use real names, not the default _dta _ etc. names. Frankly, as a consultant, one of the things I look for are objects with _dta as a means of quickly identifying unneeded objects that can be hurting performance on the system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 10

And to follow-up on Grant and Gail, I've seen plenty of times when DBTA doesn't cleanup after itself very well. So whenever you use it always search for hypothetical indexes that may have been left.

You can find here:

select * from sys.indexes where is_hypothetical = 1

When I bother to use DBTA I only use its results as a launching point. It doesn't take into consideration whether the indexes it's suggesting already exist or whether it's the same as a current index with just another col or an include added. So while it can be kinda useful at times, just use it as a guide. Look at what it's giving you and inspect the indexes to see what actually needs to be done.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

zi SSCrazy Points: 2387 More actions · Answer 11

Thank you all for replying

someone advised me to run "SET QUOTED_IDENTIFIER ON" within the job, and now it is successfully running

any advice

Thanks lot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Yes, that will get the job running (although whether that's the only piece of code withe problem is another matter), you still should go back and take all the stuff that DTA created out, test each one individually and then implement only the suggestions which improve performance and don't break other code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andrew.lord 7802 Newbie Points: 3 More actions · Answer 13

I had the same problem late last year. The reason why it works in SSMS and not as a job is the two have different default values for the SET options. QUOTED_IDENTIFIER will most likely be set to ON in SSMS but OFF for the job.

If you add a line at the beginning of your job to set the options the same as your SSMS session then it should work. The following link shows how to output the SET options for the current sessions.

zi SSCrazy Points: 2387 More actions · Answer 14

zi

SSCrazy

Points: 2387

January 9, 2015 at 4:17 am

#1769017

Thank you all for your support