May 30, 2006 at 8:35 am
Hello, I have successfully compiled the following stored procedure:
CREATE PROCEDURE ETL_TABLE_CHANGE_CAPTURE_TRIG @tblname VARCHAR(25)
AS
DECLARE
@tmpMessage VARCHAR(4000)
BEGIN
SET @tmpMessage = 'CREATE TRIGGER TR_AUI_TCC_' + @tblname + ' ON ' +
@tblname + ' FOR UPDATE, DELETE
AS
BEGIN
-- Insert table change capture data into the ETL_TABLE_CHANGED table.
INSERT INTO ETL_TABLE_CHANGED
(table_name, change_date)
VALUES
(''' + @tblname + ''', ''' + CONVERT(VARCHAR(18), GETDATE(), 101) + ''')
END'
EXECUTE @tmpMessage
--PRINT @tmpMessage
END
GO
However, when I try and execute it I get the following error:
ETL_TABLE_CHANGE_CAPTURE_TRIG 'AGE_RANGE'
Msg 203, Level 16, State 2, Procedure ETL_TABLE_CHANGE_CAPTURE_TRIG, Line 19
The name 'CREATE TRIGGER TR_AUI_TCC_AGE_RANGE ON AGE_RANGE FOR UPDATE, DELETE
AS
BEGIN
-- Insert table change capture data into the ETL_TABLE_CHANGED table.
INSERT INTO ETL_TABLE_CHANGED
(table_name, change_date)
VALUES
('AGE_RANGE', '05/30/2006')
END' is not a valid identifier.
If I manually execute the above "CREATE TRIGGER" command it works fine. Any suggestions?
Thank you,
David
Best Regards,
~David
May 30, 2006 at 8:41 am
hi,
change EXECUTE @tmpMessage to EXECUTE (@tmpMessage)
and your variable @tmpMessage should be nvarchar(4000)
HTH
Paul
May 30, 2006 at 8:46 am
Thank you Paul. Have a great day!
David
Best Regards,
~David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply