6

I have installed the latest .net connector, I can add MySQL databases as Data Sources, I can even browse through the data from Business Intelligence Studio.

The problem is that I cannot create a datasource view, or if I do create one without tables, trying to add them after the fact gives me the same error.

Specifically it looks like the data source view wizard tries to submit queries against the MySQL database using square brackets/braces, and the query bombs.

I get an error message like:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[my_db].[cheatType]' at line 2

How can this be done?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
coldilocks
  • 61
  • 1
  • 2

6 Answers6

9

Get the MySQL ( 6.3.5 ) .net connector ( from MY SQL ) Edit the connection string ( in the designer paste in the connection string ) so that it has the new "SQL SERVER MODE=True" option.

Apparently it affects performance - but what can you do

My sample connection string...(passwords & userids omitted )

server=svr215;User Id=;password=;Persist Security Info=True;database=dw;sql server mode=true

Richard
  • 91
  • 1
  • 2
  • Setting SQL SERVER MODE to True worked for me. Great tip, thanks Richard! – LeffeBrune Mar 15 '11 at 19:51
  • this worked for me too, but only when i toggled Sql server mode property to true from the dialog box. Simply editing the code for data source doesnt work. – Nikhil S Jan 12 '12 at 12:32
  • where I should set connection string?! I`ve set it in creating data source and it is like this: "Dsn=fard-pc/iranianMall32;uid=;sql server mode=true" but no thing has been changed!! – afsane Jul 26 '12 at 08:03
1

For any of you facing this issue more recently, there are two approaches that you may want to consider as well

  1. Use the DevArt dotConnect connector at http://www.devart.com/dotconnect/mysql/. Using the .NET data source, you can connect your SSAS cube directly to MySQL.
  2. Some have worked with doing OpenQuery connections to MySQL by connecting to SQL Server for the DSV and then using a linked server connection to MySQL. Some of those details are noted within http://dennyglee.com/2012/09/26/sql-server-analysis-services-to-hive/.
Denny Lee
  • 3,154
  • 1
  • 20
  • 33
1

Try using Caps when adding it to the connection string:"....;Sql Server Mode=True"

Sean Donders
  • 51
  • 1
0

Tried using named queries instead of tables?

Create a new named query in the DSV, as SELECT * FROM MySQLTable, then build in the DSV on top of that.

Meff
  • 5,889
  • 27
  • 36
0

I would recommend using SQL Server. You will save yourself a lot of time and headache trying to do it this way. If your tables don't need transforming, just use SSIS to move the data from MySQL to SQL Server.

This is what we did at my previous employer - OLTP was MySQL and we built the warehouse in SQL Server, then used SSAS off of that warehouse for the cube.

elgabito
  • 418
  • 5
  • 15
0

I have tried with Visual Studio 2015 and SQL Server Data Tools 2015 on Windows 7 Machine, experiencing problems with the following versions:

  • net connector 7.0.4
  • net connector 7.0.3
  • net connector 7.0.2
  • net connector 6.9.8
  • net connector 6.9.7
  • net connector 6.9.6
  • net connector 6.9.5
  • net connector 6.9.4
  • net connector 6.9.3
  • net connector 6.9.2
  • net connector 6.9.1
  • net connector 6.9.0
  • net connector 6.3.5 from here: https://downloads.mysql.com/archives/c-net/

As a result:

  • The SSIS is working but SQL builder always failing.
  • The SSAS is not woriking as it cannot retrieve the database schema.

Finally, after hours i got it working with the following installation of new product of MYSQL Community from here: http://dev.mysql.com/downloads/file/?id=466291

Now, the only problem i expirienced was that the connector was unable to translate some queries due schema mapping, so setting sqlservermode=true to the connection string will fix that problem too!

Stavros Koureas
  • 1,126
  • 12
  • 34