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: 3475
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 1 year, 5 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: 246960 More actions · Answer 1

Can you post the SQL which is causing the error?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yrstruly Hall of Fame Points: 3475 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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yrstruly Hall of Fame Points: 3475 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: 246960 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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yrstruly Hall of Fame Points: 3475 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 1 year, 5 months ago by yrstruly.

Phil Parkin SSC Guru Points: 246960 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!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 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.