I haven't gotten a response yet, so I moved this from another group. I
have been working on this for 2 days so if anyone has any ideas, I
would be grateful.

I have a 3rd party program that creates and populates tables in my
SQL
Server 2005 database.

The program fails on the inserts on "tblB" because the field it
creates is too small for the data that it is trying to put in it
(stupid).

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

(yes, it truncates the error message)

My trigger is basically:
USE [IGPLINK]
GO
/****** Object: DdlTrigger [NO_SOUP_FOR_YOU] Script Date:
03/24/2008 16:04:42 ******/
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)
SET @xmlEventData = eventdata()
SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/
EVENT_INSTANCE/ObjectName)'))
IF @tableName ='tblB'
BEGIN
ALTER TABLE dbo.tblB ALTER COLUMN STULINK Numeric(16,0)
END

However, when I have enterprise manager script my trigger, it looks
altered. I think these ON/OFF settings at the end are screwing things
up. Any suggestions?

USE [IGPLINK]
GO
/****** Object: DdlTrigger [NO_SOUP_FOR_YOU] Script Date:
03/25/2008 11:10:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)
SET @xmlEventData = eventdata()
SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/
EVENT_INSTANCE/ObjectName)'))
IF @tableName ='tblB'
BEGIN
ALTER TABLE dbo.tblB ALTER COLUMN STULINK Numeric(16,0)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE

(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

> 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, esq...@sommarskog.se
>
Thanks Erland, but that does not seem to work. If I add those in my
trigger script, right before the CREATE TRIGGER statement, then I get
the error on creating the first table, but the trigger effect is in
place. If I put your statements underneath, as in:
....
CREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
Go
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @xmlEventData XML,
@tableName VARCHAR(50).......

then I don't get the error, but the trigger mechanism is not in place
(id doesn't work and when I right click on the trigger and script to
query window, my alter statements are missing.)

With my last version that I tried yesterday, my 3rd party program
failed as usuall, but when I ran it's queries that I saw in the
profilef in a query window, they worked fine.
By the way, here is the exact statement it is running. Earlier I
referred to this table as tblA for simplicity.

CREATE TABLE ICMF7071(STATUS CHAR (1) NOT NULL DEFAULT ' ', SCHOOLNUM
VARCHAR (3) NOT NULL DEFAULT ' ', CLASSKEY NUMERIC (7, 0) NOT NULL
DEFAULT 0, SECTION VARCHAR (18) NOT NULL
DEFAULT ' ', COURSE VARCHAR (14) NOT NULL DEFAULT ' ', PERIOD VARCHAR
(12) NOT NULL DEFAULT ' ', TCHNUM NUMERIC (3, 0), TCHCODE VARCHAR (6)
NOT NULL DEFAULT ' ', TCHNAME VARCHAR
(40) NOT NULL DEFAULT ' ', NAME VARCHAR (50) NOT NULL DEFAULT ' ',
NOTEKEY NUMERIC (7, 0), CONSTRAINT ICMF7071_PRIMARY PRIMARY KEY
(CLASSKEY))

I have experiemented with many combinations of the ANSI settings and
others in the Database Properties options, but so far, no dice.

PS, I see you are from Sweden. Have you heard of the Ghost Rider?

> Were you able to get yourtriggerworking, by the way?
>
> --
> Erland Sommarskog,SQLServerMVP, esq...@sommarskog.se
>
Erland,

Yes, thank you very much for you . (I just got back from
vacation). Your last recommendation about the GO was the key.

Actually, I now want to enhance my process. If you recall from my
original postings, I have this third party utility that puts their
data from their program into SQL Server. It does this for a bunch of
tables, however, I only need 4 of them, so I would like to skip the
inserts on the other tables to save a whole lot of time. I am
wondering if I can use a DDL trigger to detect the table being
created, then have it create a DML trigger which basically has it
ignore the insert. However, what I came up with does not parse
successfully. It's almost like you are not allowed to do a trigger
within a trigger.

CREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET QUOTED_IDENTIFIER ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)
SET @xmlEventData = eventdata()
SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/
EVENT_INSTANCE/ObjectName)'))
IF @tableName ='ISTD7291'
BEGIN
CREATE TRIGGER NOINSERT_ISTD7291
ON ISTD7291
INSTEAD OF INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Do nothing
END
END

Thanks, figured it out

(RogBaker@gmail.com) writes:
> However, what I came up with does not parse
> successfully. It's almost like you are not allowed to do a trigger
> within a trigger.
>....
> BEGIN
> CREATE TRIGGER NOINSERT_ISTD7291
> ON ISTD7291
> INSTEAD OF INSERT
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Do nothing
> END
> END

You need to do that part with dynamic SQL, as you appears to have found
out.



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