November 3, 2009 at 11:44 am
So as a hail mary I decided to reboot my machine, now a catolog folder shows up, but when I try to expand it, I get the following error:
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB Provider "SQL SERVER" for linked server (null). The provider supports the interface but returns a failure code when used. (Microsoft SQL Server, Error 7311)
EDIT: Queries seem to be working, but the catalog folder still yields the above message.
at least the queries are working 🙂
November 3, 2009 at 1:48 pm
Check out this link:
http://support.microsoft.com/kb/959031"> http://support.microsoft.com/kb/959031
Also, try this on the SQL server:
CREATE PROCEDURE [sp_tables_info_rowset_64]
@table_name sysname
, @table_schema sysname = NULL
, @table_type nvarchar(255) = null
AS
DECLARE @Result int
SELECT @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO
It may well the the bug in management studio mentioned above
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
November 3, 2009 at 1:54 pm
that's actually really depressing since I already upgraded to SP3 but it seemed to describe my exact problem.
I ran the stored procedure you posted, but still no joy on the catalog folder.
November 4, 2009 at 1:44 am
Have you tried running an "OpenRowset" query over your linked server?
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
December 19, 2009 at 2:10 pm
This is the script for creating a linked server after you have followed the instructions for configuring ODBC and OPENROWSET etc. Replace @datasrc=N'YourODBCDataSource' with the name of your odbc data source. Replace username and password in the last statement. You can also change @server=N'LinkToMySQL' to be any name you want. In an unrelated note...it is useful to install "Navicat for MySQL" query tool so you can have an easier time of identifying the tables etc in the MySQL system.
--Drop it if it exists
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LinkToMySQL')EXEC master.dbo.sp_dropserver @server=N'LinkToMySQL', @droplogins='droplogins'
--Create it...don't forget to change values to your environment
EXEC master.dbo.sp_addlinkedserver @server = N'LinkToMySQL', @srvproduct=N'mysql', @provider=N'MSDASQL', @datasrc=N'YourODBCDataSource', @provstr=N'MSDASQL'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkToMySQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC sp_addlinkedsrvlogin LinkToMySQL, 'false', NULL, 'yourusername', 'yourpassword'
GO
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
December 21, 2009 at 4:37 am
Another thought - this isn't the same as my issue is it? As in is it 64 bit SQL Server connecting to 32 bit MySQL?
There is no 64 bit MySQL driver for windows Itanium currently (unless you compile it yourself)
Problem is that the 64 bit op system (and 64 bit SQL Server) cannot see (and therefore use) 32 bit ODBC drivers.
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
December 31, 2009 at 1:43 pm
Other useful info. I found a way to create the linked server without creating a System DSN. I have created a stored procedure that allows you to pass in the parameters required. Why? Well in my case I need to be able to create linked server connections programmatically, use them for a short duration and then remove them.
This proc makes the assumption that you are using the 3.51 driver and that you are using port 3306. Proc code is as follows:
CREATE PROC uspCreateLinkToMySQLDB @linkedservername varchar(50), @mysqlip varchar(50), @dbname varchar(100), @username varchar(50), @password varchar(50) AS
--@linkedservername = the name you want your linked server to have
--@mysqlip = the ip address of your mysql database
--@dbname = the name of the mysql database you want to operate against. Without this, some of the features of openquery fail
--@username = the username you will use to connect to the mysql database
--@password = the password used by your username to connect to the mysql database
BEGIN
--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
DECLARE @ProviderString varchar(1000)
Select @ProviderString = 'DRIVER={MySQL ODBC 3.51 Driver};SERVER=' + @mysqlip + ';Port=3306;OPTION=3;DATABASE=' + @dbname + ';'
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
Once you have the Linked Server in place I have found a few different ways of interacting with the mysql database:
Select Statements:
select * from mysql5...country
select * from OPENQUERY(mysql5, 'select * from country')
Insert Statements:
insert mysql5...country(code,name)
values ('US', 'USA')
insert OPENQUERY(mysql5, 'select code,name from country;')
values ('US', 'USA')
Other Statements:
EXEC('truncate table country') AT mysql5;
Resources:
http://213.136.52.24/bug.php?id=39965
Good luck!
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
January 13, 2010 at 8:45 am
First time post here and I pray that you guys can help.
I am using SQL Server 2005 32 bit on Windows server 2003 32 bit machine. I have installed the MySQL ODBC 5.1 driver (again - 32 bit).
When I create a System DSN to a remote MySQL machine the test succeeds. But, if I try to use the DSN (watching my case very carefully and naming the linked server with the same name as the DSN) when creating my linked server I get this message:
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "<linkedsrvname>".
OLE DB provider "MSDASQL" for linked server "<linkedsrvname>" returned message "[MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on '<remotelocation>' (10060). (Microsoft SQL Server, Error: 7303).
I have tried so many different combinations and always get the same error message. I have tried mapping a user ('sa') to the username/password combo in the DSN, I've tried just using the username/password combination after selecting 'Be made using this security context', and I've tried it w/ no mappings. Also, I have tried it using an absolute provider string, different names for my linked server, etc .. still, same message.
You would think that there is a credential problem BUT, the test succeeds in ODBC administrator ?? Also, if I have written a test vb.net app using the mysql.data.dll and created a connection that will return data no problem so there shouldn't be any firewall/network issues.
Any ideas?
I'll be checking back here frequently as this is driving me crazy!! Thanks!
January 13, 2010 at 8:48 am
When you map your user name and password, it has to be a user in the MySQL instance.....
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
January 13, 2010 at 8:57 am
Ok. That's what I thought that I had done.
Under LocalLogin, I chose 'sa' which is the account used to connect to the MS SQL Database. I left Impersonate blank and under Remote User and Remote Password I used the same credentials as in the System DSN. Is that right?
January 13, 2010 at 9:02 am
In an example of one that I have working, I didn't map local login/Impersonate/Remote User etc at all. Try removing that user you have there and in the bottom half of that security tab, I selected "Be made using this security context" and entered my remote login name (don't use root if you can get away with it - as easy as it could be that is a huge security hole...) and password
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
January 13, 2010 at 9:09 am
Thanks for your help. I really appreciate you taking some time to help me troubleshoot.
I removed all of the 'mapped' accounts, selected the bottom radion that you suggested and placed in my account information (i.e. The same info used in the DSN) - still no luck.
I am getting the same error as above.
January 13, 2010 at 9:23 am
OK,
If you right click on your linked server and choose to script it to new window, does the "EXEC master.dbo.sp_addlinkedserver" line look similar to this:
EXEC master.dbo.sp_addlinkedserver @server = N'MYSQLLINKEDSERVERNAME', @srvproduct=N'SYSTEMDSNNAME', @provider=N'MSDASQL', @datasrc=N'SYSTEMDSNNAMEAGAIN', @provstr=N'Driver={MySQL ODBC 5.1 driver};Server=MYSQLSERVERNAME;Port=3306;Option131072;Stmt=;Database=mysqldbname;Uid=useridhere;Pwd=passwordhere'
If not, can you post it here please?
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
January 13, 2010 at 9:34 am
Yes, it does look similiar to that. At first the product name was different than the DSN so I deleted the linked server and created a new one. Here is the cut and pasted line you requested.
EXEC master.dbo.sp_addlinkedserver @server = N'DENSONAIL', @srvproduct=N'DENSONAIL', @provider=N'MSDASQL', @datasrc=N'DENSONAIL', @provstr=N'Driver={MySQL ODBC 5.1 driver};Server=<remotesrvname>;Port=3306;Option131072;Stmt=;Database=densonail;Uid=remoteacct;Pwd=<password>'
Still no luck. Thanks again for your help!
January 13, 2010 at 9:41 am
Is your MySQL server instance listening on port 3306, and does the remoteacct user have '%' access? (As in access from anywhere)? And access to that database?
And now for daft question time - where are you creating the DSN - on the SQL Server itself?
"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
Viewing 15 posts - 76 through 90 (of 128 total)
You must be logged in to reply to this topic. Login to reply