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

Question

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

yrstruly

Hall of Fame

Points: 3440
More actions
June 1, 2023 at 8:56 am

#4202101
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 11 months ago by yrstruly.

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

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

Phil Parkin SSC Guru Points: 246705 More actions · Answer 1

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.

yrstruly Hall of Fame Points: 3440 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246705 More actions · Answer 3

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.

yrstruly Hall of Fame Points: 3440 More actions · Answer 4

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.

Phil Parkin SSC Guru Points: 246705 More actions · Answer 5

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.

yrstruly Hall of Fame Points: 3440 More actions · Answer 6

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 11 months ago by yrstruly.

Phil Parkin SSC Guru Points: 246705 More actions · Answer 7

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.

frederico_fonseca SSCoach Points: 16020 More actions · Answer 8

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.