Create table problem

[hedgracer]

I have a problem with a create table inside of a stored procedure. The create table part bombs out with the following errors:

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'numeri'.
Msg 173, Level 15, State 1, Line 1
The definition for column 'Regulatory_Collateral_Value' must include a data type.


Here is the relevant data:

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

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

--create Moneyline_One @YEARMO table
SET @SQLSTRING = 'CREATE TABLE DBO.Moneyline_One_' + @YEARMO + ' ('+
' [Record_Id] [varchar](max) NULL,' +
' [Firm] [varchar](50) NULL,' +
' [Office] [varchar](50) NULL,' +
' [Account] [varchar](50) NULL,' +
' [Account_Type] [varchar](50) NULL,' +
' [Sales_Series] [varchar](50) NULL,' +
' [Account_Class_Code] [varchar](50) NULL,' +
' [Account_SubClass_Code] [varchar](50) NULL,' +
' [Account_Currency_Code] [varchar](50) NULL,' +
' [Product_Currency_Code] [varchar](50) NULL,' +
' [Account_Base_Currency_Code] [varchar](50) NULL,' +
' [Firm_Base_Currency_Code] [varchar](50) NULL,' +
' [Currency_Processing_Code] [varchar](50) NULL,' +
' [Number_Call_Records_Account] [numeric](18, 2) NULL,' +
' [Oldest_Call_Account] [numeric](18, 2) NULL,' +
' [Filler_Byte_Available] [varchar](50) NULL,' +
' [Avg_Equity_Calc_Days] [numeric](18, 2) NULL,' +
' [Discretionary_Code] [varchar](50) NULL,' +
' [Omnibus_Code] [varchar](50) NULL,' +
' [Country_Province_Code] [varchar](50) NULL,' +
' [Mgd_Account_Prgm_ID] [varchar](50) NULL,' +
' [Short_Name] [varchar](50) NULL,' +
' [Cust_Name_Address_One] [varchar](50) NULL,' +
' [Last_Activity_Date] [varchar](50) NULL,' +
' [Last_Activity_Date_Work] [varchar](50) NULL,' +
' [Related_Firm_One] [varchar](50) NULL,' +
' [Related_Office_One] [varchar](50) NULL,' +
' [Related_Account_One] [varchar](50) NULL,' +
' [Related_Type_One] [varchar](50) NULL,' +
' [Related_Firm_Two] [varchar](50) NULL,' +
' [Related_Office_Two] [varchar](50) NULL,' +
' [Related_Account_Two] [varchar](50) NULL,' +
' [Related_Type_Two] [varchar](50) NULL,' +
' [Related_Firm_Three] [varchar](50) NULL,' +
' [Related_Office_Three] [varchar](50) NULL,' +
' [Related_Account_Three] [varchar](50) NULL,' +
' [Related_Type_Three] [varchar](50) NULL,' +
' [Group_Firm_Number] [varchar](50) NULL,' +
' [Group_Office_Number] [varchar](50) NULL,' +
' [Group_Sales_Series] [varchar](50) NULL,' +
' [Group_Account_Number] [varchar](50) NULL,' +
' [Account_Balance] [numeric](18, 2) NULL,' +
' [Securities_Long_Mkt_Value] [numeric](18, 2) NULL,' +
' [Securities_Short_Mkt_Value] [numeric](18, 2) NULL,' +
' [Long_Option_Value] [numeric](18, 2) NULL,' +
' [Short_Option_Value] [numeric](18, 2) NULL,' +
' [Total_Outstanding_Margin_Calls] [numeric](18, 2) NULL,' +
' [Withdrawable_Funds] [numeric](18, 2) NULL,' +
' [Futures_Initial_Margin_Reqt] [numeric](18, 2) NULL,' +
' [Futures_Maint_Margin_Reqt] [numeric](18, 2) NULL,' +
' [Futures_Exch_Min_Initial_Reqt] [numeric](18, 2) NULL,' +
' [Margin_Excess_Deficit] [numeric](18, 2) NULL,' +
' [Futures_Open_Trade_Equity] [numeric](18, 2) NULL,' +
' [Special_Misc_Account_Amt] [numeric](18, 2) NULL,' +
' [Long_Options_Open_Trade_Equity] [numeric](18, 2) NULL,' +
' [Short_Option_Open_Trade_Equity] [numeric](18, 2) NULL,' +
' [Avg_Equity] [numeric](18, 2) NULL,' +
' [Month_To_Date_Commission] [numeric](18, 2) NULL,' +
' [Unsettled_Security_Long_Mkt_Value] [numeric](18, 2) NULL,' +
' [Trade_Date_Mkt_Balance_Securities] [numeric](18, 2) NULL,' +
' [Unsettled_Short_Mkt_Value] [numeric](18, 2) NULL,' +
' [Mkt_Maker_Haircut_Amt] [numeric](18, 2) NULL,' +
' [Jpn_Unr_Cash_Deposits] [numeric](18, 2) NULL,' +
' [Mth_To_Date_P_and_L] [numeric](18, 2) NULL,' +
' [Seg_Req_Long_Mkt_Value] [numeric](18, 2) NULL,' +
' [Seg_Req_Short_Mkt_Value] [numeric](18, 2) NULL,' +
' [Equities_Initial_Marging_Req] [numeric](18, 2) NULL,' +
' [Equities_Maint_Margin_Req] [numeric](18, 2) NULL,' +
' [Conv_Rate_Firm_Base_Curr] [numeric](18, 2) NULL,' +
' [Monthly_Dividend_Balance] [numeric](18, 2) NULL,' +
' [Jpn_Unsettled_Unrealized_P_and_L] [numeric](18, 2) NULL,' +
' [Futures_Style_Opt_OTE] [numeric](18, 2) NULL,' +
' [Loan_Value] [numeric](18, 2) NULL,' +
' [Buying_Power] [numeric](18, 2) NULL,' +
' [Pending_Dividend_Amt] [numeric](18, 2) NULL,' +
' [Pending_Interest_Amt] [numeric](18, 2) NULL,' +
' [Liquidating_Value] [numeric](18, 2) NULL,' +
' [Margin_Collateral_Value] [numeric](18, 2) NULL,' +
' [Futures_OTE_Credit] [numeric](18, 2) NULL,' +
' [Futures_OTE_Debit] [numeric](18, 2) NULL,' +
' [Opt_Premium_Value] [numeric](18, 2) NULL,' +
' [Futures_Style_Opt_OTE_Credit] [numeric](18, 2) NULL,' +
' [Avg_Sec_On_Deposit] [numeric](18, 2) NULL,' +
' [Long_Options_Premium] [numeric](18, 2) NULL,' +
' [Unsettled_Unrealized_P_and_L] [numeric](18, 2) NULL,' +
' [Avg_Margin_Excess] [numeric](18, 2) NULL,' +
' [Jpn_IR] [numeric](18, 2) NULL,' +
' [Jpn_IR_Cash] [numeric](18, 2) NULL,' +
' [Jpn_MR] [numeric](18, 2) NULL,' +
' [Jpn_Initial_To_Release] [numeric](18, 2) NULL,' +
' [Total_Account_Requirements] [numeric](18, 2) NULL,' +
' [Equity_Products_OTE] [numeric](18, 2) NULL,' +
' [LOV_US_Treasury] [numeric](18, 2) NULL,' +
' [Total_Equity] [numeric](18, 2) NULL,' +
' [Avg_Balance] [numeric](18, 2) NULL,' +
' [Avg_Opt_Value] [numeric](18, 2) NULL,' +
' [Conv_Rate_Account_Base_Currency] [numeric](18, 2) NULL,' +
' [Commissions_Accrued_Recv] [numeric](18, 2) NULL,' +
' [Commissions_Accrued_Unsecured] [numeric](18, 2) NULL,' +
' [Span_Option_Value] [numeric](18, 2) NULL,' +
' [Equities_Exch_Min_Margin_Req] [numeric](18, 2) NULL,' +
' [Equities_Initial_Req_Trades] [numeric](18, 2) NULL,' +
' [Equities_Option_Initial_Reqts] [numeric](18, 2) NULL,' +
' [Equities_Option_Maint_Reqts] [numeric](18, 2) NULL,' +
' [Futures_Exch_Min_Maint_Reqt] [numeric](18, 2) NULL,' +
' [Group_Excess_Used_Deficit_Covered] [numeric](18, 2) NULL,' +
' [Non_Cash_OTE] [numeric](18, 2) NULL,' +
' [Cash_Collateral_Daily_Net_Value] [numeric](18, 2) NULL,' +
' [Equity_Percent] [numeric](18, 2) NULL,' +
' [Regulatory_Collateral_Value] [numeric](18, 2) NULL,' +
' [Conv_Rate_Account_Base_Cur_SMFD] [numeric](18, 2) NULL,' +
' [Trade_Date_Mkt_Value_Securities] [numeric](18, 2) NULL,' +
' [Total_Current_Calls] [numeric](18, 2) NULL,' +
' [Margin_Limit_Firm_Base_Curr] [numeric](18, 2) NULL,' +
' [Pend_Other_Amts] [numeric](18, 2) NULL,' +
' [Initial_Reqts_Ineligible_Collat] [numeric](18, 2) NULL,' +
' [Initial_Reqts_Eligible_Collat] [numeric](18, 2) NULL,' +
' [Deficit_Offset_Treasuries] [numeric](18, 2) NULL,' +
' [Secured_Amt] [numeric](18, 2) NULL,' +
' [Credit_Lines_Cover_Margin_Reqts] [numeric](18, 2) NULL,' +
' [Credit_Lines_Cover_Debit_OTE] [numeric](18, 2) NULL,' +
' [Span_Adjusted_Equity] [numeric](18, 2) NULL,' +
' [Mkt_Value_Cover_Initial_Reqts] [numeric](18, 2) NULL,' +
' [Accreted_Interest] [numeric](18, 2) NULL,' +
' [TIFFE_Paid_Out_OTE] [numeric](18, 2) NULL,' +
' [Jpn_UNR_Deps] [numeric](18, 2) NULL,' +
' [Prev_Total_Calls] [numeric](18, 2) NULL,' +
' [Prev_SMA] [numeric](18, 2) NULL,' +
' [Liq_Value_T_Minus_Four_Days] [numeric](18, 2) NULL,' +
' [Liq_Value_T_Minus_Three_Days] [numeric](18, 2) NULL,' +
' [Liq_Value_T_Minus_Two_Days] [numeric](18, 2) NULL,' +
' [Prev_Equity_Opt_Initial_Reqt] [numeric](18, 2) NULL,' +
' [Prev_Equity_Initial_Reqt] [numeric](18, 2) NULL,' +
' [Prev_Futures_Exch_Maint_Reqt] [numeric](18, 2) NULL,' +
' [Prev_Futures_Exch_Initial_Reqt] [numeric](18, 2) NULL,' +
' [Prev_Futures_Initial_Reqt] [numeric](18, 2) NULL,' +
' [Prev_OTE] [numeric](18, 2) NULL,' +
' [Prev_Total_Equity] [numeric](18, 2) NULL,' +
' [Prev_Liq_Value] [numeric](18, 2) NULL,' +
' [Prev_Reg_Collat_Value] [numeric](18, 2) NULL,' +
' [Prev_Account_Balance] [numeric](18, 2) NULL,' +
' [Flag_Binary_Bit_One] [varchar](50) NULL,' +
' [Flag_Binary_Bit_Two] [varchar](50) NULL,' +
' [Flag_Binary_Bit_Three] [varchar](50) NULL,' +
' [Flag_Binary_Bit_Four] [varchar](50) NULL,' +
' [Flag_Binary_Bit_Five] [varchar](50) NULL,' +
' [Misc_Byte_Code_One] [varchar](50) NOT NULL,' +
' [Misc_Byte_Code_Two] [varchar](50) NULL,' +
' [Misc_Byte_Code_Three] [varchar](50) NULL,' +
' [Misc_Byte_Code_Four] [varchar](50) NULL,' +
' [Misc_Byte_Code_Five] [varchar](50) NULL,' +
' [Misc_Byte_Code_Six] [varchar](50) NULL,' +
' [Misc_Byte_Code_Seven] [varchar](50) NULL,' +
' [Misc_Byte_Code_Eight] [varchar](50) NULL,' +
' [Misc_Byte_Code_Nine] [varchar](50) NULL,' +
' [Misc_Byte_Code_Ten] [varchar](50) NULL,' +
' [Cond_Flag_Byte_One] [varchar](50) NULL,' +
' [Cond_Flag_Byte_Two] [varchar](50) NULL,' +
' [Cond_Flag_Byte_Three] [varchar](50) NULL,' +
' [Cond_Flag_Byte_Four] [varchar](50) NULL,' +
' [Cond_Flag_Byte_Five] [varchar](50) NULL,' +
' [Cond_Flag_Byte_Six] [varchar](50) NULL,' +
' [Cond_Flag_Byte_Seven] [varchar](50) NULL,' +
' [Cond_Flag_Byte_Eight] [varchar](50) NULL,' +
' [Cond_Flag_Byte_Nine] [varchar](50) NULL,' +
' [Cond_Flag_Byte_Ten] [varchar](50) NULL,' +
' [Reg_Sec_Unreg_Code] [varchar](50) NULL,' +
' [Seg_Sec_Rpt_Line] [varchar](50) NULL,' +
' [Net_Liq_Class] [varchar](50) NULL,' +
' [Net_Equity_Class] [varchar](50) NULL,' +
' [Cross_Margin_Indicator] [varchar](50) NULL,' +
' [Group_Net_Equity_Code] [varchar](50) NULL,' +
' [Unused_One] [varchar](50) NULL,' +
' [Unused_Two] [varchar](50) NULL,' +
' [Unused_Three] [varchar](50) NULL,' +
' [Unused_Four] [varchar](50) NULL,' +
' [UK_Flg_One] [varchar](50) NULL,' +
' [UK_Flg_Two] [varchar](50) NULL,' +
' [UK_Flg_Three] [varchar](50) NULL,' +
' [UK_Flg_Four] [varchar](50) NULL,' +
' [FR_Flg_One] [varchar](50) NULL,' +
' [FR_Flg_Two] [varchar](50) NULL,' +
' [FR_Flg_Three] [varchar](50) NULL,' +
' [JP_Flg_One] [varchar](50) NULL,' +
' [JP_Flg_Two] [varchar](50) NULL,' +
' [JP_Flg_Three] [varchar](50) NULL,' +
' [HK_Flg_One] [varchar](50) NULL,' +
' [GE_Flg_One] [varchar](50) NULL,' +
' [FR_Flg_Four] [varchar](50) NULL,' +
' [Reg] [varchar](50) NULL,' +
' [Dept] [varchar](50) NULL,' +
' [Date_Upload] [varchar](50) NULL ' +
' ) ON [PRIMARY]';
EXEC(@SQLSTRING);

As you can see the create table part is a bit long. I have struggled with this all day. Any guidance or help is appreciated.

Dave

 

[gmmastros]

DECLARE @SQLSTRING AS VARCHAR([!]5000[/!]);

Your string is longer than 5000 characters so it is getting truncated.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom