Welcome to Julian Kuiters Friday, October 19 2018 @ 09:26 AM AEDT

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

SQL Server 2008

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 filtered indexes and/or query notifications and/or XML
data type methods and/or spatial index operations.

This error message means the settings for ANSI_NULLS and QUOTED_IDENTIFIER were different at the time the procedure was created compared to when the table was created. You will notice that whenever you script out an object from sql server management studio that it will add the SET options at the top of the script. In this case it appears the stored procedure was created without the correct SET options.

To fix the problem, you will need to recreate / alter the stored procedure with the correct SET options turned on before the CREATE PROCEDURE / ALTER PROCEDURE statement.

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

alter procedure dbo.myprocedure
....(stored procedure body)

 

 

Seeing as you've already had this problem once - it is worthwhile checking your entire database to see if there are any other procedures creates with QUOTED_IDENTIFIER set off. Luckily Andrei Volkov has already provided the answer on how to do this:

SELECT
SCHEMA_NAME(s.schema_id)  + '.' + s.name AS name,
s.create_date,
s.modify_date,
OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') AS IsQuotedIdentOn
FROM sys.objects s
WHERE
s.type IN ('P','TR','V','IF','FN','TF')
AND OBJECTPROPERTY(s.object_id,'ExecIsQuotedIdentOn') = 0
ORDER BY SCHEMA_NAME(s.schema_id)  + '.' + s.name DESC

You should note that the SQL CAT team have recently posted that the incorrect QUOTED_IDENTIFIER setting can cause problems with persisted computed columns. The SQLCAT ISV team have also posted about issues with filtered indexes

If you read the above articles you'll now see why all scripts generated by sql server management studio include SET QUOTED_IDENTIFIER ON

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

SQL Server 2008

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 of this is still not optimal.

Paul White has written a great article on using the INTERSECT command (a feature in SQL Server 2005 and SQL Server 2008) to compare two sets looking for differences and why this performs much better inside SQL Server.

Paul's article details the different query plans that are created by using ISNULL and COALESCE and INTERSECT to do the comparisons, including the internal predicates that SQL Server uses to fulfill the query.

In short an INTERSECT handles null comparisons and is certainly much simpler to write. Below is a quick sample of the INTERSECT example Paul gives (for my own reference), but I highly recommend reading the article for the full details.

SELECT *
FROM  table1 as T1
JOIN table2 as T2 ON  T1.pk = T2.pk
WHERE NOT EXISTS
          (SELECT T1.* INTERSECT T2.*)

 

 

Security – How To Assign Rights To SQL Server Database Objects

SQL Server 2008

Piotr Rodak has a great article on Security – How To Assign Rights To SQL Server Database Objects  and why you should not assign rights to the public role and why assigning rights to roles instead of users gives you much more flexibility.

He also covers why you should only grant permissions to your stored procedures and not to your table / view objects directly.

If you're new to SQL Server database security - or even if your an old hat - take the time to read Piotr's article.

 

Searching SQL Stored Procedure Source Code in Information_Schema.Routines

SQL Server 2008
-- 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%'

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

Monitor Calibration for Better Colors

Website Design

If you are a website designer or graphic artist, its important your monitor is calibrated to ensure the colors you are using will display and print the same for other people. Most monitors include an "auto-adjust" feature, but this isn't enough.

Most website designers and graphic designers will use special hardware that will calibrate a monitor automatically - making sure that colors and pictures match perfectly.

If you can't afford the hardward monitor calibrators, there are some great websites that offer monitor calibration tests that you can use to manually calibrate your monitor.

One of the best test pages I've found is the Lagom LCD monitor test pages. There are a variety of test images for gamma, contrast, saturation as well as other features. And they've optimised their test images for LCD monitors, which differ to CRT monitors.

So take the time to calibrate your monitor - you'll be surprised how much better photos and most websites appear when you calibration is correct.

Website Designers already on board for iPad compatable websites

Apple iPad

Now that the iPad has been released and web designers know what iPad limitations there are, a number of websites are already underway with their iPad website redesigns.

ABC has announced that they are going to create an iPad app / website design and Wired has a video preview of the site they are going to create.

Large websites and websites with a wide screen layout need to start planning now if they are to support the iPad.

So are you redesigning your website? Let us know in our poll or leave a comment below.

 

iPad (TM) a no no?

Apple iPad

With the release of the iPad, there's been a lot of chatter about the iPad name, so it got me wondering - Who had the iPad name first?

A search of the Internet Archive for ipad shows the earliest mention of "IPAD" was in an audio track and webpage from 2nd of April 1994 when eSoft talk about adding an internet connection called IPAD ("Internet Protocol Adapter") to their bulletin board system. So popular was the IPAD from 1994 there is the IPAD Owners Association started in 2001 (see their webpage from 2001).

There has also been some news about Fujitsu naming a product the "iPad"

Will your website work with the Apple iPad ?

Apple iPad

With all the hype that is surrounding the launch of the Apple iPad - the big question for website owners must be "Will my website work on the Apple iPad?".

If the iPad gets the same massive uptake by Apple fans and the public - there could be a lot people surfing the web using their iPads. If your site doesn't display properly on the iPad you could miss out on customers.

What is the screen resolution of the Apple iPad?

1024 x 768 is the current iPad screen resolution.

If you site was designed for widescreen monitors (6:9) you will need to check how your website looks in the old 1024x768 size (most old CRT monitors were this size). You can check by changing the your monitor display size and surf your own website to look for problems.

Does the Apple iPad support flash? 

No. Just like the iPhone and touch iPods flash does not run on the Apple iPad. Website designers will need to take this into consideration.

More things missing from the iPad

Business Catalyst - Bulk Opt-in of Contacts

Adobe Business Catalyst

Do you have an Adobe Business Catalyst website with a lot of customers you wish to Opt-In ?

You can request Business Catalyst to set the master opt-in on your site, so all existing customers are "opt-in" to your marketing emails. [Forum thread here]

Send a support ticket to Business Catalyst to have them turn on the master opt-in option for your website and ask them to opt-in your entire database at the same time.

 

What is double opt-in ?

When a customer signs up to a newletter list on your website, Business Catalyst automatically sends the auto-responder to the customer. The tag {tag_verificationurl} in the auto-responder email is replaced with a link the customer clicks to confirm they want to receive your newsletters. This is known as double opt-in.

 

What if the customer does not click the verification link?

If the customer does not click the link, they will not be receive your newsletters unless you force opt in for their account in the admin panel, or have used the bypass double opt-in feature in your webforms, or have the master opt-in turned on by BC support.

 

What if I've turned off the auto-responder for a form?

If you've disabled the auto-responder on your form, the verification link will not be sent to your customers. In this case you will need to either:

  • use the force opt-in feature on your webforms
  • or ask BC to opt-in all your customers
  • or opt-in your customers individually

 

Why does Business Catalyst use double opt-in?

Business Catalyst uses double opt-in to comply with anti-spam measures and legislation, but a lot of websites have a Newletter Signup form or similar where customers are expressly wanting to join in to your marketing list.

By default all newsletter forms are double opt-in and there are a few things you can do to skip this process.

How to: Force Opt-in on Newsletter Subscription Forms

To ensure your customers are going to receive your newsletters when they subscribe, you need to add the force opt-in parameter to your form.

  1. Edit your newsletter forms and add &OPTIN=true to the end of the form action ( <form action="..."  )

All new subscribers will then be opted-in as soon as they subscribe to a newsletter using this form.
 

 

Additional references

Wiki page on force opt-in