You have an error in your SQL syntax; check the manual that corresponds

  • I am having great trouble creating a data pipeline using MariaDB connector 3 with SSIS/Mysql connector 8. I can view the tables, but it gives me this error: "TITLE: Microsoft Visual Studio ------------------------------

    ERROR [42000] [ma-3.1.18][10.4.24-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"sample_table"' at line 1 Error at Data Flow Task [ADO NET Source [16]]: System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [ma-3.1.18][10.4.24-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"sample_table"' at line 1 at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostReinitializeMetaData(IDTSManagedComponentWrapper100 wrapper)

    ------------------------------ BUTTONS:

    OK ------------------------------ "

    I tried using SQL specifically for MariaDB/Mysql, but it does not work.

    I create the table manually in MariaDB/MySQL and the code is correct and provide me with the table(s). Does anybody know why this error happen>?

    • This topic was modified 1 year ago by  yrstruly.
  • Can you post the SQL which is causing the error?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Even when i create the table first in Mysql/MariaDB it gives the error:

    -- Create the sample database
    CREATE DATABASE testdb;

    -- Switch to the test database
    USE testdb;

    -- Create the sample table
    CREATE TABLE SampleTable (
    DateValue DATE,
    CopperHeadGrade DOUBLE,
    LeadHG_Pb DOUBLE,
    ZincHG DOUBLE,
    SilverHG DOUBLE,
    DeliveredToPlant DOUBLE,
    Milled DOUBLE,
    CopperCuPercentage DOUBLE,
    CopperCuRecovery DOUBLE,
    LeadPbPercentage DOUBLE,
    LeadPbRecovery DOUBLE,
    ZincZnPercentage DOUBLE,
    ZincZnRecovery DOUBLE,
    TonsSilverAg DOUBLE,
    SilverAgRecovery DOUBLE,
    Source NVARCHAR(255),
    CopperInCopper DOUBLE,
    LeadInCopper DOUBLE,
    ZincInCopper DOUBLE,
    SilverInCopper DOUBLE,
    pH DOUBLE,
    OilInFeed DOUBLE,
    Measure_150microns DOUBLE,
    RedoxPotential_eH DOUBLE
    );

    -- Insert sample data into the table
    INSERT INTO SampleTable VALUES
    ('2023-05-31', 1.5, 2.3, 3.1, 4.2, 100, 200, 60, 80, 20, 70, 30, 90, 500, 85, 'Sample Source', 0.8, 1.2, 2.5, 3.8, 7.2, 0.5, 5.5, 600);

    -- Query the sample data
    SELECT * FROM SampleTable;

     

    maria test db

  • You have created a table in MariaDb and you are using an ExecuteSQL task containing

    INSERT INTO SampleTable VALUES
    ('2023-05-31', 1.5, 2.3, 3.1, 4.2, 100, 200, 60, 80, 20, 70, 30, 90, 500, 85, 'Sample Source', 0.8, 1.2, 2.5, 3.8, 7.2, 0.5, 5.5, 600);

    and that is what is causing the error. Is that right?

    But the error message you have posted comes from a Dataflow, not an ExecuteSQL task. So I am confused about what is going on.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I created a simple pipeline in SSIS, moving data from a source like SQL Server or a flat-file. I am having trouble when I am connecting to the destination table that is in Mariad DB I am getting these errors or when I run the above script in SSIS to create the destination table.

  • SSIS is not the best place for DDL – it's best to do your table creates before going near SSIS.

    Were you able to successfully create a Connection Manager in SSIS? Could you select a database and successfully test the connection?

    When you created the data flow, did the error occur at design time, or at run time?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I do create the table first in MariaDB as seen in the screenshot above.

    Yes, I connect to tables. ado

    ado2

    ado3

    ado4

    ado5

    Latest error:

    ado6

    • This reply was modified 1 year ago by  yrstruly.
  • Thank you for the screenshots, that helps explain what is going on. But I don't think I can help. I do not understand why it is complaining about SQL syntax when you haven't even written any SQL!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • SSIS has behind the scenes to retrieve the table metadata - this error seems to indicate a mismatch between the server and the client driver - or some parameters need to be set on the connection string to make it behave differently.

     

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply