Anyway to use SSMS to connect to MySql?

Question

When I create a new dart web application with webui support, the generated project works fine. But when you rename for example the generated file "xclickcounter.html / .dart" with for example: "counter-element.html" resp "counter-element.dart" and change the corresponding occurrences accordingly, i get errors like:

`Unhandled exception:
FileIOException: Cannot retrieve full path for file 'web\packages' (OS Error: The system cannot find the file specified  
errno = 2)
Stack Trace:
_File.throwIfError (dart:io:1208:7)
_File.fullPathSync (dart:io:1097:17)
symlinkPubPackages (package:web_ui/dwc.dart:157:49)`

I dont mind, not being able to use "-element" in the file name, but there maybe many more other restrictions to filenames. Does anyone knows what all the restrictions are?

Solution 1

Does this help? :

Creating a Linked Server in SSMS for a MySQL database

1. Download the MySQL ODBC driver from mysql.com

2. Install MySQL ODBC driver on Server where SQL Server resides -Double Click Windows Installer file and follow directions.

3. Create a DSN using the MySQL ODBC driver Start-> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)

   • Click on the System DSN tab
   • Click Add
   • Select the MySQL ODBC Driver
   • Click Finish On the Login Tab:
   • Type a descriptive name for your DSN.
   • Type the server name or IP Address into the Server text box.
   • Type the username needed to connect to the MySQL database into the user text box.
   • Type the password needed to connect to the MySQL database into the password text box.
   • Select the database you'd like to start in. On the Advance Tab: Under Flags 1:
   • Check Don't Optimize column width.
   • Check Return Matching Rows
   • Check Allow Big Results
   • Check Use Compressed protocol
   • Check BIGINT columns to INT
   • Check Safe Under Flags 2:
   • Check Don't Prompt Upon Connect
   • Check Ignore # in Table Name Under Flags 3:
   • Check Return Table Names for SQLDescribeCol
   • Check Disable Transactions Now Test your DSN by Clicking the Test button

4. Create a Linked Server in SSMS for the MySQL database SSMS (SQL Server Management Studio -> Expand Server Objects

   • Right Click Linked Servers -> Select New Linked Server On the General Page:
   • Linked Server: Type the Name for your Linked Server
   • Server Type: Select Other Data Source
   • Provider: Select Microsoft OLE DB Provider for ODBC Drivers
   • Product name: Type MySQLDatabase
   • Data Source: Type the name of the DSN you created On The Security Page
   • Map a login to the Remote User and provide the Remote Users Password
   • Click Add under Local server login to remote server login mappings:
   • Select a Local Login From the drop down box
   • Type the name of the Remote User
   • Type the password for the Remote User

5. Change the Properties of the Provider MSDASQL Expand Providers -> Right Click MSDASQL -> Select Properties

   • Enable Nested queries
   • Enable Level zero only (this one's the kicker)
   • Enable Allow inprocess
   • Enable Supports 'Like' operator

6. Change settings in SQL Server Surface Area Configuration for Features

   • Enable OPENROWSET and OPENDATASOURCE support.

7. Change settings in SQL Server Surface Area Configuration for Services and Connections

   • Enable Local and Remote connections via TCP/IP and named pipes

8. Stop SQL Server and SQL Server Agent

9. Start SQL Server and SQL Server Agent

Solution 2

Yes, you can.

1. Download MySQL ODBC driver: https://dev.mysql.com/downloads/connector/odbc/ and install
2. Go to ODBC Data Source Administrator > User DNS > Add..., and fill all the fields wit the right informations. Then test if the connections works
3. Go to ODBC Data Source Administrator > System DNS > Add..., and fill all the fields wit the right informations. Then test if the connections works
4. In SSMS go to Server Objects > Linked Servers > New Linked Server...
5. Under General set
   • Linked server: (Put what you want, I put the name of the server for simplicity)
   • Provider: Microsoft OLE DB Provider for ODBC Drivers
   • Product name: (Put what you want, I put the name of the server for simplicity, you might want to put MySQL)
   • Data source: Must have the same name as the System Data Source's Name
6. Under Security select "Be made using this security context" and enter you user and password for remote login to MySQL
7. Under Server Options set RPC and RPC Out as True
8. Go to Server Objects > Linked Servers > Providers > MSDASQL, and enable: Nested queries, Level zero only, Allow inprocess, Supports 'Like' operator
9. Restart SQL Server and SQL Server Agent

10. You can now query MySQL from SSMS using this structure:

    select * from OPENQUERY(linked_server_name, 'SELECT * FROM database_name.table_name')

or simply:

SELECT * FROM linked_server_name...table_name

Comments

[MarioP]

This works just fine. Are you sure you have changed every occurence?

[Seth Ladd]

What OS version and Dart Editor version are you using? Also, are you sure the above is a full stack trace and error?

[Günter Zöchbauer]

As far as I know this was a bug which was fixed several months ago. In Dart according to the style guide file names should not contain a dash and therefore this wasn't tested at first.

[Danny Tuppeny]

Ah, that would also explain it! :D

[Chris Nevill]

This answer seems good however I think it could do with a refresh for later versions of SQL Server

[retif]

Beware when joining linked server objects with local DB objects. The performance may suffer.

[Francesco Mantovani]

MSDASQL is a deprecated technology: docs.microsoft.com/en-us/previous-versions/…

[Alex KeySmith]

Sorry I'm in a different world now, I totally forgot about this question. I'll mark this as the answer from yesteryear! Thanks! ! But +1 the new answer as well as it appears like it may have some new details in.

[Andrew]

This answer worked with SSMS v18.8 and MySQL ODBC v8.0.27 when the other answer did not (outdated settings).