December 6, 2007 at 4:36 am
Hi there,
Very interesting article. I was attempting to add a linked server and wanted to confirm something.
I did not create a mapped login and instead specified the remote login and password for the option "Be made using this security context".
I can connect successfully - however - when attempting to expand the "catalogs" folder, I receive the error: "Cannot obtain the schema rwoset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used"
ANy ideas? I would appreciate any help.
Also, I then attempted to create a login on the SQL Server and then tried to map it - but it gave me an error "Access to the remote server is denied becuase no login-mapping exists."
I created a login on the sql server called "root" and then mapped it to the remote server with username "root" along with password - but no joy.
Any help would be appreciated.
thanks,
KS
January 21, 2008 at 4:54 am
Many thanks Mr Dillon. Your advice uses the MySQL 3.51 ODBC driver. Have you found any reason to upgrade to the 5.1 driver? If so, a similar help sheet would be totally welcome!
Regards
Charlie Howard
January 22, 2008 at 10:11 am
Jim:
Your post has been incredibly helpful - thanks so much.
I was in meetings yesterday that revolved around integrating live MySQL data from the web with a local MsSQL database. I was scratching my head wondering how that was going to happen, until I came across your article.
Thanks a million!
-Simon Doubt
February 15, 2008 at 7:42 am
Hi Jim,
Thanks for providing the detailed instructions. But are these instructions for SQL 2005 32 bit or 64 bit. Because I tried it on SQL 2005 64 bit and I still get the following errors -
OLE DB provider "SQLNCLI" for linked server returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server 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].
Another thing is, when i go to set up the linked server in SSMS, i don't even see the Provider "Microsoft OLE DB Provider for ODBC Drivers" in the drop down list. So i was selecting SQL Native Client as the provider. FYI, I have installed the MySQL 64 bit version driver donwloaded from MySQL site. Please advise if these instructions are for SQL 2005 32 bit or 64 bit?
February 15, 2008 at 8:59 am
We had the same problem with our SQL Server 2005 64 bit server. After some research, it seems that Microsoft got rid of their OLE DB Provider for ODBC Drivers. We purchased the OpenLink OleDB Provider for ODBC from Openlink Software for a minimal fee.
http://uda.openlinksw.com/odbc/st/odbc-mysql-st/
http://download.openlinksw.com/download/download.vsp
We had a few issues at first, but their support was great and we didn't even purchase the extra support.
Wendy Schuman
February 15, 2008 at 9:02 am
Definitely 32 bit. I couldn't find a 64 bit driver.
Thanks,
jim
February 15, 2008 at 7:00 pm
Thanks to all. FYI, We heard from Microsoft that the 64 bit ODBC driver will be available starting Windows 2008 only.
February 21, 2008 at 8:54 am
Great post! Have you had any experience doing this with SQL Server 2000? Is there a way to do this with 2000 or just with 2005?
February 21, 2008 at 9:02 am
I have not tried this with SQL Server 2000. However, if you are able to get it to work, please post your findings.
Thanks,
jim
February 21, 2008 at 9:30 am
Hi,
I'm a newbie to this forum, so please be gentle with me 😎
Great (original) post that was really helpful to me in getting started, but...
I am trying to regularly update a MySQL db with data from M$SQLServer2005 (testing on EE, but will be live on 'full').
I have managed to configure a Linked Server and updated some tables without any problems, but am now hitting a problem I cannot seem to find any answer to.
I have a MySQL table defined as(in UTF8)
-- Table structure for table `fm_composer`
CREATE TABLE fm_composer (
id int(11) NOT NULL auto_increment,
lastName tinytext NULL,
firstName tinytext NULL,
dob tinytext NULL,
biography text NULL,
additionalInfo text NULL,
PRIMARY KEY (id),
FULLTEXT KEY lastName (lastName),
FULLTEXT KEY firstName (firstName)
) TYPE=MyISAM;
and the MSSQL table defined as
CREATE TABLE [dbo].[contacts](
[contact_id] [int] NOT NULL,
....
[biography] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[composer_date_of_birth] [datetime] NULL,
[composer_notes] [varchar](5120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
....
[name_first] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[name_last] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
....
)
[lots of extra columns as well...]
I installed & configured the latest 3.51.23 MySQL ODBC driver to work with the 'Linked Server'
If I use the statement
INSERT OPENQUERY(mylinkedserver, 'SELECT lastName, firstName, dob FROM fm_composer') SELECT name_last, name_first, composer_date_of_birth FROM contacts WHERE contact_type_id = 4 ;
everything is fine, but if I do
INSERT OPENQUERY(mylinkedserver, 'SELECT lastName, firstName, dob, additionalInfo FROM fm_composer') SELECT name_last, name_first, composer_date_of_birth, composer_notes FROM contacts WHERE contact_type_id = 4 ;
I get this error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "mylinkedserver" reported an error. The provider did not give any information about the error.
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "mylinkedserver" could not INSERT INTO table "[MSDASQL]". Unknown provider error.
The same thing happens if I try using the 'biography' column instead of 'additionalInfo /composer_notes'
If I change the MySQL column to be the same VARCHAR(5120) type as composer_notes, it works fine, so it seems to be the issue converting MS VARCHAR into MySQL TEXT. (It looks like MS VARCHAR into MySQL TINYTEXT seems to be OK, as the tables I have got to work are using TINYTEXT or INT columns)
I have tried using CAST & CONVERT in various ways, but still no luck & I don't think it is an issue with the COLLATION difference in the systems.
I tried using 'SQLyog Enterprise' Trial and set up an ODBC DSN for MSSQL along with the MySQL DSN and used the Migration Powertool in it and it worked fine, so it seems ODBC to ODBC is fine, but I can't use that product in my system as I need to do it by just using SQL calls (I am developing a solution using Servoy and using the rawSQL plugin to do the SQL calls, but am testing this in MS SQL Server Management Studio (Express) to make sure it isn't a Servoy problem.
I would REALLY appreciate any help or advice anyone can give me as this part of my solution is VITAL!!!
Thanks,
Rafi
February 21, 2008 at 9:47 am
I think what you're looking for is:
INSERT INTO [Linked Server Name].catalog.[my_sql_table_name] (my_sql_columns)
SELECT [MS SQL SERVER columns]
FROM [Your MS SQL SERVER table]
I believe you can use a three part identifier here, the syntax is listed earlier on in this post.
Good Luck!
Thanks,
jim
February 21, 2008 at 9:56 am
Jim Dillon (2/21/2008)
I think what you're looking for is:INSERT INTO [Linked Server Name].catalog.[my_sql_table_name] (my_sql_columns)
SELECT [MS SQL SERVER columns]
FROM [Your MS SQL SERVER table]
I believe you can use a three part identifier here, the syntax is listed earlier on in this post.
Good Luck!
Thanks,
jim
Thanks, I'll give it a try later/tomorrow & let you know.
Rafi
February 21, 2008 at 10:44 am
Jim Dillon (2/21/2008)
I think what you're looking for is:INSERT INTO [Linked Server Name].catalog.[my_sql_table_name] (my_sql_columns)
SELECT [MS SQL SERVER columns]
FROM [Your MS SQL SERVER table]
I believe you can use a three part identifier here, the syntax is listed earlier on in this post.
Good Luck!
Thanks,
jim
I've tried this syntax before, and it works great - quick and easy!
If only you could write INSERT and UPDATES to MySQL from SQL Server as seamlessly...
-Simon
March 25, 2008 at 2:32 am
Hi
This info for linking MySQL Server is realy helpful and I almost have it working. Unfortunately I seem to be stuck at the last hurdle.
I have a working connection to the MySQL server and can retrieve and display the Schema, however, I get an error if I try to perform a simple SELECT on any of the tables I get an error.
If I use the "Sctript table as SELECT to..." from enterprise manager I get:
-- [MYSQL_MAGSUBS].[gdspub]..[econference] contains no columns that can be selected or the current user does not have permissions on that object.
If I try to run a SELECT directly I get:
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "MSDASQL" for linked server "MYSQL_MAGSUBS" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 3
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "MYSQL_MAGSUBS". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
The "CREATE" for the linked server looks as follows:
/****** Object: LinkedServer [MYSQL_MAGSUBS] Script Date: 03/25/2008 08:30:34 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL_MAGSUBS', @srvproduct=N'MySQLDatabase', @provider=N'MSDASQL', @datasrc=N'GDS_MySQL2'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSQL_MAGSUBS',@useself=N'False',@locallogin=NULL,@rmtuser=N'gdspub',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MYSQL_MAGSUBS', @optname=N'use remote collation', @optvalue=N'true'
Can you shed any light on where I'm going wrong
Cheers
Tim
April 7, 2008 at 2:38 am
Hi this inital post is really useful I have found that I couldn't use three part naming convention with 3.51 driver but can with 5.1 what joy!
Viewing 15 posts - 16 through 30 (of 128 total)
You must be logged in to reply to this topic. Login to reply