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
