SSMS 'Copy Database' fails (error about ANSI_PADDING)

[stijnherreman]

I'm not able to use 'Copy Database' on databases that have the Exceptions table created by SQLExceptionTable.sql

CREATE NONCLUSTERED INDEX [IX_Exceptions_Applica..." failed with the following error: "CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_PADDING'. 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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Is this something that can be resolved by the creation script? Or could this be caused by server configuration? (I'm not the DBA here, just a developer) The db runs on an SQL Server 2008 R2 instance.

[NickCraver]

Is your default SET ANSI PADDING OFF? That's non-default and would cause this issue...I can explicitly fix it in the script but would like to confirm this is the case first.

[stijnherreman]

According to

select
    db.name
    ,db.is_ansi_padding_on
from
    sys.databases db

it's OFF (0) for all our databases. Database Properties via SSMS also says it's OFF (False).

So I believe it's safe to say that it's off indeed (although I have no idea why).

---

Edit: running dbcc useroptions prints ansi_padding SET. I'm confused now...

[stijnherreman]

I've asked a question on dba.se to clear up the confusion.

[stijnherreman]

I've taken another look at this. Turns out this is only a problem when using the SQL Management Object method, it works fine when using the detach and attach method. It's probably also fixable by changing our database defaults, something I intend to do soon.