(RogBaker@gmail.com) writes:
> I wrote a DDL trigger that attempts to alter the table as soon as it
> created, allowing all the data to be loaded.
> However, something about this trigger causes a prior table "tblA" to
> fail.
>
> Here is the error message that I get on inserting into tblA with the
> trigger for tblB in place:
>
> Execution of this SQL statement failed: Create table tblA(STATUS
> CHAR(1) NOT NULL DEFAULT'', SCHOOLNUM
> [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed because
> the following SET options have incorrect settings:
> 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that
> SET options are correct for use with indexed views and/or indexes o

You get this error message, because your trigger uses XQuery, when
you use XQuery, these settings must be on: ANSI_NULLS, QUOTED_IDENTIFIER,
CONCAT_NULL_YIELDS_NULL, ANSI_WARNING and ANSI_PADDING. And
NUMERIC_ROUNDABORT must be off.

The first two settings are saved with the SQL module, so if you created
your trigger with ANSI_NULLS and QUOTED_IDENTIFIER, you are safe on
those two.

When you create a table ANSI_PADDING is saved with the table column,
but I don't think this is an issue. At least I hope, because in such
case you are in trouble.

That leads to that the three settings you are having problem with are
set by at run-time by the application, which apparently is an old
one. You should be able to get things to work by putting:

> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON

inside your trigger.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx