Issue executing SQL command

PowerShell Help
1

I’m writing a script that goes through a data file and parameterizes the data and sends it to an SQL table, below is a snippet of the code i’m having a problem with:

$sql = "INSERT INTO [PershingData] ([TRANSACTION_CODE], [RECORD_INDICATOR], [RECORD_ID_SEQUENCE_NUMBER], [ACCOUNT_NUMBER], [INTRODUCING_BROKER_DEALER_NUMBER], [INVESTMENT_PROFESSIONAL_IP_NUMBER], [ACCOUNT_SHORT_NAME], [TAX_ID_TYPE], [TAX_ID_NUMBER], [DATE_TAX_ID_APPLIED_FOR], [W-8-W-9_INDICATOR], [W8-W-9_DATE], [W-8-W-9_EFFECTIVE], [W-8-W-9_DOCUMENT_TYPE], [TAX_STATUS], [B_NOTICE_REASON_CODE], [FIRST_B_NOTICE_STATUS], [DATE_FIRST_B_NOTICE_STATUS_ISSUED], [DATE_FIRST_B_NOTICE_STATUS_SATISFIED], [SECOND_B_NOTICE_STATUS], [DATE_SECOND_B_NOTICE_STATUS_ISSUED], [DATE_SECOND_B_NOTICE_STATUS_SATISFIED], [C_NOTICE_STATUS], [DATE_C_NOTICE_STATUS_ISSUED], [DATE_C_NOTICE_STATUS_SATISFIED], [OLD_ACCOUNT_NUMBER], [ORIGINAL_ACCOUNT_OPEN_DATE], [UNIDENTIFIED_LARGE_TRADER_ID], [LARGE_TRADER_TYPE_CODE], [LARGE_TRADER_TYPE_LAST_CHANGE_DATE], [INITIAL_SOURCE_OF_FUNDS], [FINANCE_AWAY], [ACCOUNT_FUNDING_DATE], [STATEMENT_CURRENCY_CODE], [FUTURE_STATEMENT_CURRENCY_CODE], [FUTURE_STATEMENT_CURRENCY_CODE_DATE], [ACCOUNT-LEVEL_ROUTING_CODE_1], [ACCOUNT-LEVEL_ROUTING_CODE_2], [ACCOUNT-LEVEL_ROUTING_CODE_3], [ACCOUNT-LEVEL_ROUTING_CODE_4], [SELF-DIRECTED_INDICATOR], [DIGITAL_ADVICE_INDICATOR], [PROHIBITED_TRANSACTON_EXEMPTION_PTE_86-128_ACCOUNT_INDICATOR], [LEGACY_FIRST_INVESTMENT_PROFESSIONAL_IP_NUMBER], [LEGACY_SECOND_INVESTMENT_PROFESSIONAL_IP_NUMBER], [LEGACY_THIRD_INVESTMENT_PROFESSIONAL_IP_NUMBER], [LEGACY_FOURTH_INVESTMENT_PROFESSIONAL_IP_NUMBER], [LEGACY_FIFTH_INVESTMENT_PROFESSIONAL_IP_NUMBER], [LEGACY_SIXTH_INVESTMENT_PROFESSIONAL_IP_NUMBER], [LEGACY_SEVENTH_INVESTMENT_PROFESSIONAL_IP_NUMBER], [LEGACY_EIGHTH_INVESTMENT_PROFESSIONAL_IP_NUMBER], [LEGACY_NINTH_INVESTMENT_PROFESSIONAL_IP_NUMBER], [LEGACY_TENTH_INVESTMENT_PROFESSIONAL_IP_NUMBER], [ALERT_IM_ACRONYM], [ALERT_IM_ACCESS_CODE], [BROKER_ACRONYM], [CROSS-REFERENCED_INDICATOR], [BNY_TRUST_INDICATOR], [SOURCE_OF_ASSETS_AT_ACCOUNT_OPENING], [COMMISSION_DISCOUNT_CODE], [EXTERNAL_ACCOUNT_NUMBER], [CONFIRMATION_SUPPRESSION_INDICATOR], [DATE_LAST_BOOKS_AND_RECORDS_MAILING_SENT_TO], [DATE_LAST_BOOKS_AND_RECORDS_MAILING_SENT_TO_CUSTOMER_OUTSIDE_PERSHING_SYSTEM], [FULLY_PAID_LENDING_AGREEMENT_INDICATOR], [FULLY_PAID_LENDING_AGREEMENT_DATE], [CUSTODIAN_ACCOUNT_TYPE], [MARKETS_IN_FINANCIAL_INSTRUMENTS_DIRECTIVE_MIFID_CUSTOMER_CATEGORIZATION], [CASH_MANAGEMENT_TRANSACTION_CODE], [SWEEP_STATUS_INDICATOR], [DATE_SWEEP_ACTIVATED], [DATE_SWEEP_DETAILS_CHANGED], [COVER_MARGIN_DEBIT_INDICATOR], [FIRST_FUND_SWEEP_ACCOUNT_ID], [FIRST_FUND_SWEEP_ACCOUNT_PERCENT], [FIRST_FUND_SWEEP_ACCOUNT_REDEMPTION_PRIORITY], [SECOND_FUND_SWEEP_ACCOUNT_ID], [SECOND_FUND_SWEEP_ACCOUNT_PERCENT], [SECOND_FUND_SWEEP_ACCOUNT_REDEMPTION_PRIORITY], [TYPE_OF_BANK_ACCOUNT], [BANKLINK_ABA_NUMBER], [BANKLINK_DDA_NUMBER], [FUND_BANK_INDICATOR], [W-9_CORPORATION_TAX_CLASSIFICATION_CODE], [COMBINED_MARGIN_ACCOUNT_INDICATOR], [PLEDGE_COLLATERAL_ACCOUNT_INDICATOR], [FINRA_INSTITUTIONAL_ACCOUNT_CODE], [PROPOSED_ACCOUNT_REFERENCE_ID], [ADVISOR_MODEL_ID], [FIRM_MODEL_STYLE_ID], [DVP_RESTRICTION_CODE], [DVP_RESTRICTION_EXPIRATION_DATE], [ESCHEATMENT_WITHHOLDING_INDICATOR], [SOURCE_OF_ORIGINATION], [SOURCE_OF_PERSONA], [CLIENT_ON-BOARDING_METHOD], [TAX_FILING_CODE], [NON-PURPOSE_COLLATERAL_ACCOUNT_INDICATOR]) VALUES (@TRANSACTION_CODE, @RECORD_INDICATOR, @RECORD_ID_SEQUENCE_NUMBER, @ACCOUNT_NUMBER, @INTRODUCING_BROKER_DEALER_NUMBER, @INVESTMENT_PROFESSIONAL_IP_NUMBER, @ACCOUNT_SHORT_NAME, @TAX_ID_TYPE, @TAX_ID_NUMBER, @DATE_TAX_ID_APPLIED_FOR, @W-8-W-9_INDICATOR, @W8-W-9_DATE, @W-8-W-9_EFFECTIVE, @W-8-W-9_DOCUMENT_TYPE, @TAX_STATUS, @B_NOTICE_REASON_CODE, @FIRST_B_NOTICE_STATUS, @DATE_FIRST_B_NOTICE_STATUS_ISSUED, @DATE_FIRST_B_NOTICE_STATUS_SATISFIED, @SECOND_B_NOTICE_STATUS, @DATE_SECOND_B_NOTICE_STATUS_ISSUED, @DATE_SECOND_B_NOTICE_STATUS_SATISFIED, @C_NOTICE_STATUS, @DATE_C_NOTICE_STATUS_ISSUED, @DATE_C_NOTICE_STATUS_SATISFIED, @OLD_ACCOUNT_NUMBER, @ORIGINAL_ACCOUNT_OPEN_DATE, @UNIDENTIFIED_LARGE_TRADER_ID, @LARGE_TRADER_TYPE_CODE, @LARGE_TRADER_TYPE_LAST_CHANGE_DATE, @INITIAL_SOURCE_OF_FUNDS, @FINANCE_AWAY, @ACCOUNT_FUNDING_DATE, @STATEMENT_CURRENCY_CODE, @FUTURE_STATEMENT_CURRENCY_CODE, @FUTURE_STATEMENT_CURRENCY_CODE_DATE, @ACCOUNT-LEVEL_ROUTING_CODE_1, @ACCOUNT-LEVEL_ROUTING_CODE_2, @ACCOUNT-LEVEL_ROUTING_CODE_3, @ACCOUNT-LEVEL_ROUTING_CODE_4, @SELF-DIRECTED_INDICATOR, @DIGITAL_ADVICE_INDICATOR, @PROHIBITED_TRANSACTON_EXEMPTION_PTE_86-128_ACCOUNT_INDICATOR, @LEGACY_FIRST_INVESTMENT_PROFESSIONAL_IP_NUMBER, @LEGACY_SECOND_INVESTMENT_PROFESSIONAL_IP_NUMBER, @LEGACY_THIRD_INVESTMENT_PROFESSIONAL_IP_NUMBER, @LEGACY_FOURTH_INVESTMENT_PROFESSIONAL_IP_NUMBER, @LEGACY_FIFTH_INVESTMENT_PROFESSIONAL_IP_NUMBER, @LEGACY_SIXTH_INVESTMENT_PROFESSIONAL_IP_NUMBER, @LEGACY_SEVENTH_INVESTMENT_PROFESSIONAL_IP_NUMBER, @LEGACY_EIGHTH_INVESTMENT_PROFESSIONAL_IP_NUMBER, @LEGACY_NINTH_INVESTMENT_PROFESSIONAL_IP_NUMBER, @LEGACY_TENTH_INVESTMENT_PROFESSIONAL_IP_NUMBER, @ALERT_IM_ACRONYM, @ALERT_IM_ACCESS_CODE, @BROKER_ACRONYM, @CROSS-REFERENCED_INDICATOR, @BNY_TRUST_INDICATOR, @SOURCE_OF_ASSETS_AT_ACCOUNT_OPENING, @COMMISSION_DISCOUNT_CODE, @EXTERNAL_ACCOUNT_NUMBER, @CONFIRMATION_SUPPRESSION_INDICATOR, @DATE_LAST_BOOKS_AND_RECORDS_MAILING_SENT_TO, @DATE_LAST_BOOKS_AND_RECORDS_MAILING_SENT_TO_CUSTOMER_OUTSIDE_PERSHING_SYSTEM, @FULLY_PAID_LENDING_AGREEMENT_INDICATOR, @FULLY_PAID_LENDING_AGREEMENT_DATE, @CUSTODIAN_ACCOUNT_TYPE, @MARKETS_IN_FINANCIAL_INSTRUMENTS_DIRECTIVE_MIFID_CUSTOMER_CATEGORIZATION, @CASH_MANAGEMENT_TRANSACTION_CODE, @SWEEP_STATUS_INDICATOR, @DATE_SWEEP_ACTIVATED, @DATE_SWEEP_DETAILS_CHANGED, @COVER_MARGIN_DEBIT_INDICATOR, @FIRST_FUND_SWEEP_ACCOUNT_ID, @FIRST_FUND_SWEEP_ACCOUNT_PERCENT, @FIRST_FUND_SWEEP_ACCOUNT_REDEMPTION_PRIORITY, @SECOND_FUND_SWEEP_ACCOUNT_ID, @SECOND_FUND_SWEEP_ACCOUNT_PERCENT, @SECOND_FUND_SWEEP_ACCOUNT_REDEMPTION_PRIORITY, @TYPE_OF_BANK_ACCOUNT, @BANKLINK_ABA_NUMBER, @BANKLINK_DDA_NUMBER, @FUND_BANK_INDICATOR, @W-9_CORPORATION_TAX_CLASSIFICATION_CODE, @COMBINED_MARGIN_ACCOUNT_INDICATOR, @PLEDGE_COLLATERAL_ACCOUNT_INDICATOR, @FINRA_INSTITUTIONAL_ACCOUNT_CODE, @PROPOSED_ACCOUNT_REFERENCE_ID, @ADVISOR_MODEL_ID, @FIRM_MODEL_STYLE_ID, @DVP_RESTRICTION_CODE, @DVP_RESTRICTION_EXPIRATION_DATE, @ESCHEATMENT_WITHHOLDING_INDICATOR, @SOURCE_OF_ORIGINATION, @SOURCE_OF_PERSONA, @CLIENT_ON-BOARDING_METHOD, @TAX_FILING_CODE, @NON-PURPOSE_COLLATERAL_ACCOUNT_INDICATOR);"

$command = New-Object System.Data.SqlClient.SqlCommand($sql, $connection)

foreach ($row in $rows) {
   $command.Parameters.Clear()
   foreach ($property in $row.PSObject.Properties) {
       $command.Parameters.AddWithValue("@$($property.Name)", $property.Value)
   }
   $command.ExecuteNonQuery() 
}

after it runs i get the following error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '-'.
Must declare the scalar variable "@TRANSACTION_CODE"."

WHen i look at the $command variable’s parameter’s I see @Transaction_Code does exist with data:

$command.Parameters |?{$_.parametername -eq "@Transaction_code"}


CompareInfo                     : None
XmlSchemaCollectionDatabase     :
XmlSchemaCollectionOwningSchema :
XmlSchemaCollectionName         :
ForceColumnEncryption           : False
DbType                          : String
LocaleId                        : 0
ParameterName                   : @TRANSACTION_CODE
Precision                       : 0
Scale                           : 0
SqlDbType                       : NVarChar
SqlValue                        : CI
UdtTypeName                     :
TypeName                        :
Value                           : CI
Direction                       : Input
IsNullable                      : False
Offset                          : 0
Size                            : 2
SourceColumn                    :
SourceColumnNullMapping         : False
SourceVersion                   : Current

What am I doing wrong?

2

Hi, welcome back. What is in $rows?

3

UGH i acutally figured it out…i guess you cant use dashes “-” in an SQL parameter.

Rows contained data that looked like:

$rows[0]
THIRD_MOST_RECENT_RETURN_COMMENT                                 :
RECORD_ID_SEQUENCE_NUMBER                                        : 00000001
SEASONAL_ADDRESS_ID_3                                            :
ADDRESS_2_LINE_4                                                 :
TO_DATE_1                                                        : 00000000
ADDRESS_1_TRANSACTION_CODE                                       :
MOST_RECENT_RETURN_MAIL_TYPE                                     :

Where those names on the left are the same as the names of SQL columns, which i would then turn into parameters with the same name.

Problem was some of the SQL columns had hyphens in the name which would then turn into a parameter being passed with a hpyhen in the name which I didnt know wasnt allowed…changed all hypens to underscores and now it works

4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.