August 17, 2010 at 9:24 am
what about going the other way?
I'm familiar w/ sql server and not so much in mysql. Therefore if or when i have to work in mysql which isnt very ofter i try and use sql server to do it.
In this instance all that i have left is to insert my final results into a mysql db table using linked server. i just dont know how to do that. I'm using linked server and open query to pull down the original information. i make changes to it then need to reinsert my changed recs back into the mysql db table. I have everything done except the insert. Below are some more details.
linkserver: LOCALXML_Stage
Table whos contents in sql server that i'm trying to insert into mysql: LocalXMLclaimed_business_record
Table on mysql db i'm inserting into: localxml.claimed_business_record
Anyone know how to write this insert into mysql using linked sql and i suppose open query?
March 3, 2011 at 7:30 am
Hi,
Need your Help!!!!!
i have installed MYSQL Connector and created New Data Source but when i a try to create new linked server i am not seeing the "OLE DB Provider for ODBC Drivers" in the Provider Drop Down.
Please Suggest !!
thanks
Puneet
September 3, 2011 at 3:46 am
I found this answered my questions - http://www.geakeit.co.uk/installations/how-to-add-a-mysql-server-as-a-linked-server-in-sql-server-management-studio.html
September 5, 2011 at 3:06 am
Has anyone tried doing an insert into the linked MySQL server from within a trigger?
An insert works fine if I execute it from the query window in SSMS but the same statement in a trigger gives the following error
Msg 7391, Level 16, State 2, Procedure update_trig, Line 7 The operation could not be performed because the OLE DB provider "MSDASQL" for linked server "MYSQL" was unable to begin a distributed transaction.
Someone suggested adding a COMMIT statement before the insert -
SET XACT_ABORT ON
COMMIT
INSERT INTO OPENQUERY(MYSQL, 'select data from db.test') values ('testing trigger')
This does do the insert in the linked MySQL server but gives the error that causes some problems with replication -
Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted.
If anyone has any suggestions, or if it is just not possible please let me know.
September 28, 2011 at 10:37 pm
I had to spend a bit of time trying to set up a link server from SQL Server 2008 R2 to MySQL 5.5.14. Finally I made it.
The problem was the MySQL ODBC driver v5.1.8. After I went back to v 3.51.28 the linked servers started working. Then I had to pick the right options. Unfortunately MySQL/Oracle documentation of the options was incomplete and I had to read the C source code of the driver to figure out which options will I need. Here is my store procedure to set up a link server from SQL Server 2008 R2 to MySQL 5.5.14, you might like different options so I listed them all.
Cheers
Theo
exec sp_linkMySQL
@linkedservername = 'MySQL',
@mysqlip = 'MySQLserver',
@dbname = 'cloud',
@username = 'root',
@password = 'root';
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_linkMySQL] Script Date: 08/25/2011 16:02:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_linkMySQL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_linkMySQL]
GO
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_linkMySQL] Script Date: 08/25/2011 16:02:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[sp_linkMySQL]
@linkedServerName as varchar(50),
@mysqlIP as varchar(50),
@dbName as varchar(100),
@userName AS varchar(50),
@password AS varchar(50)
as
begin
-- MySQL ODBC options
declare @FLAG_FIELD_LENGTH bigint = 1;
declare @FLAG_FOUND_ROWS as bigint = 2;
declare @FLAG_BIG_PACKETS as bigint = 8;
declare @FLAG_NO_PROMPT as bigint = 16;
declare @FLAG_DYNAMIC_CURSOR as bigint = 32;
declare @FLAG_NO_SCHEMA as bigint = 64;
declare @FLAG_NO_DEFAULT_CURSOR as bigint = 128;
declare @FLAG_NO_LOCALE as bigint = 256;
declare @FLAG_PAD_SPACE as bigint = 512;
declare @FLAG_FULL_COLUMN_NAMES as bigint = 1024;
declare @FLAG_COMPRESSED_PROTO as bigint = 2048;
declare @FLAG_IGNORE_SPACE as bigint = 4096;
declare @FLAG_NAMED_PIPE as bigint = 8192;
declare @FLAG_NO_BIGINT as bigint = 16384;
declare @FLAG_NO_CATALOG as bigint = 32768;
declare @FLAG_USE_MYCNF as bigint = 65536;
declare @FLAG_SAFE as bigint = 131072;
declare @FLAG_NO_TRANSACTIONS as bigint = 262144;
declare @FLAG_LOG_QUERY as bigint = 524288;
declare @FLAG_NO_CACHE as bigint = 1048576;
declare @FLAG_FORWARD_CURSOR as bigint = 2097152;
declare @FLAG_AUTO_RECONNECT as bigint = 4194304;
declare @FLAG_AUTO_IS_NULL as bigint = 8388608;
declare @FLAG_ZERO_DATE_TO_MIN as bigint = 16777216;
declare @FLAG_MIN_DATE_TO_ZERO as bigint = 33554432;
declare @FLAG_MULTI_STATEMENTS as bigint = 67108864;
declare @FLAG_COLUMN_SIZE_S32 as bigint = 134217728;
declare @FLAG_NO_BINARY_RESULT as bigint = 268435456;
declare @FLAG_DFLT_BIGINT_BIND_STR as bigint = 536870912;
declare @FLAG_NO_INFORMATION_SCHEMA as bigint = 1073741824;
DECLARE @ProviderString varchar(4000);
DECLARE @odbcOptionsInt bigint;
DECLARE @odbcOptions varchar(200);
SET @odbcOptionsInt = @FLAG_FIELD_LENGTH + @FLAG_FOUND_ROWS + @FLAG_BIG_PACKETS + @FLAG_NO_PROMPT + @FLAG_PAD_SPACE + @FLAG_FULL_COLUMN_NAMES + @FLAG_IGNORE_SPACE
+ @FLAG_NO_BIGINT + @FLAG_FORWARD_CURSOR + @FLAG_AUTO_RECONNECT + @FLAG_AUTO_IS_NULL + @FLAG_MULTI_STATEMENTS + @FLAG_COLUMN_SIZE_S32 + @FLAG_NO_BINARY_RESULT;
SET @odbcOptions = ';OPTION=' + CAST(@odbcOptionsInt as varchar(20) ) + ';';
select '@ProviderString', @ProviderString, '@odbcOptions', @odbcOptions;
--DROP THE LINKED SERVER IF IT EXISTS
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @linkedServerName)
EXEC master.dbo.sp_dropserver @server=@linkedServerName, @droplogins='droplogins';
--ADD THE LINKED SERVER
-- Select @ProviderString = 'Provider=MSDASQL;DRIVER={MySQL ODBC 5.1 Driver};SERVER=' + @mysqlIP + ';Port=3306;DATABASE=' + @dbName + ';UID='+@userName+'PWD='+@password+@odbcOptions;
set @ProviderString = 'Provider=MSDASQL;DRIVER={MySQL ODBC 3.51 Driver};SERVER=' + @mysqlIP + ';Port=3306;DATABASE=' + @dbName + ';UID='+@userName+'PWD='+@password+@odbcOptions;
EXEC master.dbo.sp_addlinkedserver @server=@linkedServerName, @srvproduct='MySQL',@provider='MSDASQL', @provstr=@ProviderString;
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'collation compatible', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'data access', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'dist', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'pub', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'rpc', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'rpc out', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'sub', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'connect timeout', @optvalue=N'0';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'collation name', @optvalue=null;
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'lazy schema validation', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'query timeout', @optvalue=N'0';
EXEC master.dbo.sp_serveroption @server=@linkedServerName, @optname=N'use remote collation', @optvalue=N'true';
EXEC sp_addlinkedsrvlogin @linkedServerName, 'false', NULL, @userName, @password;
end
GO
January 6, 2012 at 12:34 pm
Thanks Jim, this saved me a LOT of time and aggravation today.
Ken Stelter
No good deed goes unpunished.
February 17, 2012 at 12:12 am
As per your steps, I have created the linked server for MySQL in sql server management studio.
I can able to see the database and also the tables.
But i cant able to view the records and also i cant able to view/alter the table columns.
Is this any permission that i need to give.. and where to assign this..?
help plz...
February 17, 2012 at 3:09 am
the USerid and password you are using to connect to Mysql dont have appropriate permission
February 17, 2012 at 3:12 am
how and where to make that user permission in Linked server...?
February 17, 2012 at 3:19 am
This needs to be done at MYSQL server end, i never worked on MYSQL so cannot tell you how to create user or give permission at MYSQL. In my case MYSQL DBA in our company created a USerid and password and handed over to me.
February 17, 2012 at 3:26 am
ya i have mysql userid and password..when i used that credentials i cant able to create lincked server.
so what i do is , i created a new user in sql server and add that user to the linqed server.
i also tried to map the sql server user to mysql server user..its connecting.
but i didnt able to see tables columns...and fetch datas
April 18, 2012 at 12:54 am
Excellent!!!!
May 18, 2012 at 12:36 pm
This was really helpful guys. Thanks. But I am stuck with this error now
OLE DB provider "SQLNCLI" for linked server "MISSERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MISSERVER" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
Please HELP !
July 25, 2012 at 2:13 pm
I recently went to install the ODBC connector and create a linked server to MySQL as described here http://qa.sqlservercentral.com/Forums/FindPost1332192.aspx. I ran into and solved 2 problems, so I figured I'd post them here.
1. You get this error when you try to create the DSN on a 64-bit machine connected to a 32-bit server:
The specified DSN contains an architecture mismatch between the Driver and Application". (Microsoft SQL Server, Error: 7303)
The solution is here http://msdn.microsoft.com/en-us/library/ms712362%28VS.85%29.aspx. Basically, you have to use the WOW64 version of the ODBC editor to create the DSN.
2. Once your DSN and linked servers are created, you can SELECT from the linked server, but you cannot INSERT or UPDATE. You get this error when you try:
OLE DB provider "MSDASQL" for linked server "mylinkedserver" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "mylinkedserver" could not UPDATE table "[mylinkedserver]...[mytable]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
This is apparently quite common, and I read at least 3 different fixes for it. The one that worked for me, however, was to edit the Cursors/Results tab of the DSN connection and check the box for "Return matched rows instead of affected rows"
Hope this helps someone else out,
Rich
December 27, 2012 at 11:14 am
Thanks a bunch! This was extremely helpful as I am not very versed in MySQL!
Viewing 15 posts - 106 through 120 (of 128 total)
You must be logged in to reply to this topic. Login to reply