Dynamically creating table from variable not working 1

[hedgracer]

Here is the code:

DECLARE @SQLSTRING AS VARCHAR(5000);
DECLARE @YEARMO VARCHAR(6);

--IF @YEARMO IS NULL
IF DATEPART(MM, GETDATE()) = '01'
SET @YEARMO = ((DATEPART(YYYY, GETDATE()) - 1)* 100) + 12;
ELSE
SET @YEARMO = (DATEPART(YYYY, GETDATE())* 100) + (DATEPART(MM, GETDATE()) -1);
-- PRINT @YEARMO

--Define Table
SET @SQLSTRING = 'CREATE TABLE DBO.Month_Data_File_' + @YEARMO + ' ('+
' [Record_ID] [varchar](1) ,' +
' [Firm] [varchar](1) ,' +
' [Office] [varchar](3) ,' +
' [Account] [varchar](5) ,' +
' [Acct_Type] [varchar](2) ,' +
' [SubType] [varchar](2) ,' +
' [Expiration_Date] [varchar](8) ,' +
' [Trade_Date] [varchar](8) ,' +
' [Buy_Sell] [varchar](1) ,' +
' [Broker] [varchar](5) ,' +
' [Spread_Code] [varchar](1) ,' +
' [Tracer_Number] [varchar](20) ,' +
' [Class] [varchar](1) ,' +
' [SubClass] [varchar](1) ,' +
' [Sales_Series] [varchar](5) ,' +
' [Quantity] [int] ,' +
' [Description] [varchar](50) ,' +
' [Exch] [varchar](2) ,' +
' [Future_Code] [varchar](2) ,' +
' [Type] [varchar](2) ,' +
' [Curr_Code] [varchar](2) ,' +
' [Cmt_One] [varchar](3) ,' +
' [Cmt_Two] [varchar](3) ,' +
' [Cmt_Three] [varchar](3) ,' +
' [Exec_Brkr] [varchar](5) ,' +
' [Oppo_Brkr] [varchar](5) ,' +
' [Grs_Comm] [money] NULL,' +
' [Clrng_Fee] [money] NULL,' +
' [Exch_Fee] [money] NULL,' +
' [NFA_Fee] [money] NULL,' +
' [Brkg_Fee] [money] NULL,' +
' [Other_Fee] [money] NULL,' +
' [AT_GrsComm] [varchar](3) ,' +
' [AT_ClrngFee] [varchar](3) ,' +
' [AT_ExchFee] [varchar](3) ,' +
' [AT_NFAFee] [varchar](3) ,' +
' [AT_BrkgFee] [varchar](3) ,' +
' [AT_OtherFee] [varchar](3) ,' +
' [Ovn_Qty] [int] ,' +
' [Day_Qty] [int] ,' +
' [Scratch_Qty] [int] ,' +
' [Spread_Qty] [int] ,' +
' [Ovn_GrsComm] [money] NULL,' +
' [Day_GrsComm] [money] NULL,' +
' [Scratch_GrsComm] [money] NULL,' +
' [Spread_GrsComm] [money] NULL,' +
' [Buy_Qty] [int] ,' +
' [Sell_Qty] [int] ,' +
' [Trade_Price] [varchar](20) ,' +
' [FU_ClrngFee] [varchar](3) ,' +
' [FU_ExchFee] [varchar](3) ,' +
' [FU_NFAFee] [varchar](3) ,' +
' [FU_BrkgFee] [varchar](3) ,' +
' [FU_OtherFee] [varchar](3) ,' +
' [Data_Table] [varchar](3) ,' +
' [Date_Upload] [varchar](8) ,' +
' [Reg] [varchar](50) ,' +
' [Dept] [varchar](50) ,' +
' [Future_RH] [varchar](50) ,' +
' [Option_RH] [varchar](50) ,' +
' [Memberships] [varchar](50) ,' +
' [CTI] [varchar](50) ,' +
' [GIO_CD] [varchar](50) ,' +
' [GIO_F] [varchar](50) ,' +
' [TCALC] [varchar](50) ,' +
' [PSYMBL] [varchar] (50) ,' +
' [PLEVEL] [varchar] (50), ' +
'[Country] [varchar] [50] ,'+
'[TCARD] [varchar] [50] ,'+
'[TTYPE] [varchar] [50] ,'+
'[TRDEX] [varchar] [50] '+
' ) ON [PRIMARY]';

EXEC(@SQLSTRING);

Here is the error I keep getting:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '50'.

When I do a print @SQLSTRING everything looks okay. Can anyone point out where the problem is? I have been beating this for the last day and a half. Thanks.

Dave

 

[SgtJarrow]

Your last four columns have the numeric identifier for the varchar column length wrong:

    '[Country] [varchar] [50] ,'+
    '[TCARD] [varchar] [50] ,'+
    '[TTYPE] [varchar] [50] ,'+
    '[TRDEX] [varchar] [50] '+

Should be:

    '[Country] [varchar] (50) ,'+
    '[TCARD] [varchar] (50) ,'+
    '[TTYPE] [varchar] (50) ,'+
    '[TRDEX] [varchar] (50) '+

Note the square brackets are now parentheses.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[hedgracer]

Thanks!!!!! I have stared at this for a long time and didn't notice that simple problem. I appreciate it.