Hi everybody,
what is the best way to connect a MySQL-Database.
I know two ways
- With Ado.Net-Source and ODBC-Datasource
- With MySQLDataProvider
Are there some differences between this types? Or does it not matter which type i use?
Hi everybody,
what is the best way to connect a MySQL-Database.
I know two ways
Are there some differences between this types? Or does it not matter which type i use?
We just create a Linked Server, something like this:
-- Replace these strings globally:
--
-- RemoteName
-- MySQL_RemoteName
-- MyLocalLogin -- (Optional)
-- MyRemotePassword
-- MyRemotePassword
-- Create Linked Server
EXEC sp_addlinkedserver
@server = 'MySQL_RemoteName' -- local name for the linked server being created
, @srvproduct = 'MySQL' -- product name of the OLE DB data source to add as a linked server
-- Following parameters ONLY required if NOT connecting two SQL Servers
, @provider = 'MSDASQL'
, @datasrc = 'RemoteName' -- name of the data source as interpreted by the OLE DB provider (DBPROP_INIT_DATASOURCE property)
-- Alternative parameters are required for NON-Standard Port usage
-- Remove any existing Linked Server Login (optional)
EXEC sp_droplinkedsrvlogin
@rmtsrvname = 'MySQL_RemoteName'
, @locallogin = 'MyLocalLogin'
-- Create Linked Server Login
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'MySQL_RemoteName'
, @useself = 'false' -- true=Connect using current UserID/Password, false=use rmtuser/rmtpassword below
, @locallogin = NULL -- NULL=All local logins use this remote login account
-- otherwise local login UserName being set up (repeat for each user, as required)
-- , @locallogin = 'MyLocalLogin'
-- Following parameters ONLY required IF @UseSelf='FALSE' (above)
, @rmtuser = 'MyRemoteLogin' -- UserName on Remote corresponding to this @LocalLogin.
, @rmtpassword = 'MyRemotePassword' -- Ditto: password