Skip to main content

ANSWER: UPDATE failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’

I came across an interesting problem last night – a stored procedure that had been created years ago was suddenly coming up with this error message: UPDATE failed because the following SET options have incorrect settings: ‘ANSI_NULLS, QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or […]

Read More

Finding changed rows in SQL Server using INTERSECT, ISNULL or COALESCE and performance

If you are trying to determine which rows are different between two sets, the usual method is to create a query comparing table1.Column1 != table2.Column1 but this doesn’t check for rows where one of these columns is null. isnull(table1.Column1,-1) != isnull(table2.Column1,-1)    might seem like a good way to compare nullable columns – but the performance […]

Read More

Searching SQL Stored Procedure Source Code in Information_Schema.Routines

— Here’s a little line of code I use a lot to find out what SQL stored  procedures / functions use a particular table SELECT * FROM   information_schema.routines WHERE  routine_definition LIKE ‘%GetThemeColours%’ –&nnbsp;INFORMATION_SCHEMA.ROUTINES is similar to sys.routines but  has all the text names instead of objectid’s  with the added bonus the full  stored procedure text is searchable (its in ROUTINE_DEFINITION column) – its like  a super powered sp_helptext — Also check out these helpful views: — View all the columns in tables + views with full table/view name SELECT * FROM   information_schema.columns — stored procedure / function parameters SELECT * FROM   information_schema.parameters — output columns for table valued functions (NOT stored procedures) SELECT * FROM   information_schema.routine_columns — list of tables SELECT * FROM   information_schema.tables — list of views SELECT * FROM   information_schema.tables    — 195cac785d844883be7a61f2b54189c6

Read More