Dynamically build T-SQL for trigger 2

[LonnieJohnson]

I am trying to build an SQL string for a trigger. I have to build a string because the table name changes monthly.

I get an error that makes no sense to me...


Server: Msg 170, Level 15, State 1, Procedure RS_AC_TE, Line 5
Line 5: Incorrect syntax near ','.

GO
--GET CURRENT MONTH
DECLARE @M CHAR(4)

SET @M = 
SUBSTRING(convert(char(8),getdate(),112),5,2) + 
SUBSTRING(convert(char(8),getdate(),112),3,2)

DECLARE @flag char(1)
SET @flag = 'A'

DECLARE @sql varchar(255)
SET @sql = 
'CREATE TRIGGER [RS_AC_TE] ON [dbo].[DA' + @M + 'C] 
FOR INSERT
AS
INSERT INTO [ENCT].[dbo].[RS_AUDIT_C]([LNAME], [FNAME], [DOR], [CSMANAGER], [PLCSUBCONT], [PLCTYPE], [PLCLOC], [CURPLCDATE], 
[NEWCURPDAT], [DISCHDATE], [EM_RESP_TY], [EM_LOC],  [FOS_ADOPNM], [STATUS], [NPLCTYPE], [NPLCLOC], [NFOS_ADOPN], [NFOSFNAME], 
[NPLCCO], [FLAG], [CSNBR], [INPUTDT],  [PR], [DISCHTO], [NSUBCONT], [COUNTY], [REGION], [SUPERVISOR], [KVCOFFICE], [GADLITEM], [CSO], [DOB],
 [AGE], [INITPMT], [INITDATE], [REINT], [REINTDATE], [FOSFNAME],  [FCSNBR], [NFCSNBR], [PLCCO], [TOTFOST], [REFSRSWRKR], [AREAOFFICE],
 [EMERGMOVE], [RELEASED], [CURTSUP], [FPDATE],  [INPUTID],  [PROJPERM], [JODATE], [NOOH], [NOOH1ST], [NOOH2], [REENTRY], [REENTRY1], 
[REENTRY2], [REENTRYNBR], [REENTRY1NB], [REENTRY2NB], [CALRENT], [NOOH3], [NOOH4], [REENTRY3NB], [REENTRY4NB], [REENTRY5NB], 
[NOOH5], [CALRENT1], [CALRENT2], [CALRENT3], [CALRENT4], [CALRENT5], [REENTRY3], [REENTRY4], [REENTRY5], [REENTRY6],  [COMMENT], 
[UNITS], [UPUNITS], [DOC], [ENDDOC], [PROCCODE], [PROCEXT], [PROVTYPE], [PROVZIP], [PROVAD], [PROVCITY], [PROVST], [PROVTAX], [H24K], 
[ORGNAME], [UNITCOST], [TOTCOST], [POSU], [CNTOSU], [TOTOSU], [CODECAT], [SEX], [RACE], [MI], [CREFNBR], [ID], [GEN], [FULLNAME], [AMTPAID], 
[PLCCAT], [AGECAT], [MONTHREF], [LOSCAT], [MONTHPERM], [SUPVRNAME], [CSMGRNAME],  [SSN], [CPAOFFICE], [FSCOORD], [FSCNAME], [MRDD], 
[HC], [CC], [IR], [KAECSESID], [FBA], [AONBR], [KVCDEPT], [TYPECHG], [NUNITCOST], [FPUNITCOST], [NFPUNITCOS], [PDRESPITE], [ADMITDATE], 
[PROJENDDAT], [RATECHGDAT], [PLCZIP], [COR], [UNISVC], [CHANGETYPE])

SELECT [LNAME], [FNAME], [DOR], [CSMANAGER], [PLCSUBCONT], [PLCTYPE], [PLCLOC], [CURPLCDATE], [NEWCURPDAT], [DISCHDATE],
 [EM_RESP_TY], [EM_LOC],  [FOS_ADOPNM], [STATUS], [NPLCTYPE], [NPLCLOC], [NFOS_ADOPN], [NFOSFNAME], [NPLCCO], [FLAG], [CSNBR], 
[INPUTDT],  [PR], [DISCHTO], [NSUBCONT], [COUNTY], [REGION], [SUPERVISOR], [KVCOFFICE], [GADLITEM], [CSO], [DOB], [AGE], [INITPMT], [INITDATE], 
[REINT], [REINTDATE], [FOSFNAME],  [FCSNBR], [NFCSNBR], [PLCCO], [TOTFOST], [REFSRSWRKR], [AREAOFFICE], [EMERGMOVE], [RELEASED], 
[CURTSUP], [FPDATE],  [INPUTID],  [PROJPERM], [JODATE], [NOOH], [NOOH1ST], [NOOH2], [REENTRY], [REENTRY1], [REENTRY2], [REENTRYNBR],
 [REENTRY1NB], [REENTRY2NB], [CALRENT], [NOOH3], [NOOH4], [REENTRY3NB], [REENTRY4NB], [REENTRY5NB], [NOOH5], [CALRENT1], [CALRENT2], 
[CALRENT3], [CALRENT4], [CALRENT5], [REENTRY3], [REENTRY4], [REENTRY5], [REENTRY6],  [COMMENT], [UNITS], [UPUNITS], [DOC], [ENDDOC], 
[PROCCODE], [PROCEXT], [PROVTYPE], [PROVZIP], [PROVAD], [PROVCITY], [PROVST], [PROVTAX], [H24K], [ORGNAME], [UNITCOST], [TOTCOST], 
[POSU], [CNTOSU], [TOTOSU], [CODECAT], [SEX], [RACE], [MI], [CREFNBR], [ID], [GEN], [FULLNAME], [AMTPAID], [PLCCAT], [AGECAT], [MONTHREF],
 [LOSCAT], [MONTHPERM], [SUPVRNAME], [CSMGRNAME],  [SSN], [CPAOFFICE], [FSCOORD], [FSCNAME], [MRDD], [HC], [CC], [IR], [KAECSESID], [FBA],
 [AONBR], [KVCDEPT], [TYPECHG], [NUNITCOST], [FPUNITCOST], [NFPUNITCOS], [PDRESPITE], [ADMITDATE], [PROJENDDAT], [RATECHGDAT], 
[PLCZIP], [COR], [UNISVC], ' + @FLAG +  ' FROM INSERTED'
exec(@sql)

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[zhavic]

SET @flag = '''A'''

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[LonnieJohnson]

I tried that and get the same thing.

Is that three tick marks on each side, or are there some quotation marks in there?

I tried it with three tick marks.

Thanks for the response.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[gmmastros]

DECLARE @sql varchar(255)

Uhhhh, 255 characters may not be enough. Why not be generous.

DECLARE @sql varchar(8000)



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LonnieJohnson]

Oh my. I am so ashamed. So so ashamed. I just copied that from another script I had and did not think twice about the size of the string.

Please, tell no one about this. Thanks gmmastros.

zhavic, thanks for that quote tip.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[gmmastros]

Lonnie,

I promise. I will not tell anyone about this. I'll keep it between you and me.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom