April 14, 2008 at 1:49 pm
Tim Cooke (3/25/2008)
HiThis 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.
I'm having the same trouble. Any ideas?
April 14, 2008 at 2:05 pm
We have a linked server setup to access a MySQL database. The view that queries the MySQL database accesses the data with the following format: FROM MySQLServer...MySQLTable.
Hope this helps. (The names have been changed to protect the guilty).
April 14, 2008 at 2:55 pm
To elaborate on Lynn's post:
I was able to get it working when I setup the ODBC DSN to connect to a default database, then use
SELECT * FROM PJSR...tblafe
The problem occurs when attempts are made to specify a database different from the default (even if a default isn't set).
April 16, 2008 at 2:28 am
Thanks All
This has been excercising the old grey cells for some time
Problem solved
April 29, 2008 at 8:45 am
This topic saved me big time. Thanks to everyone who took the time to participate!!
April 29, 2008 at 9:12 pm
Hi everybody,
FYI, Microsoft has released a 64-bit OLEDB Provider for ODBC recently. The URL for downloading the driver is:
I have installed the driver on 64-bit Windows 2003 and able to set up a linked server from 64-bit SQL server 2005 to MySQL without any problem.
May 7, 2008 at 12:48 am
Your instructions saved my butt. Thanks a lot.
Although your instructions are outdated now because there is a new version of the MySQL driver, and so I had to guess at some of the flag options.
Still, it seems to work. Thanks.
May 13, 2008 at 9:43 am
Hello, could you please document your steps to create a linked server in 64 bit? I haven't had any luck.
My Steps
1. Download and install mysql-connector-odbc-5.1.4-win32.msi on my Windows 2003 x64 machine.
2. Went to Start/Run, pasted in C:\WINDOWS\SysWOW64\odbcad32.exe to bring up the ODBC administrator.
3. added system dsn, tested connection works just fine.
4. installed WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe to get MSDASQL provider for SSMS.
. Open SSMS, try to add linked server, provide DSN and login. When I click OK, I get:
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL".
OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)
It seems like the Provider is not looking for the DSNs set up in the 32 bit administrator, or did I just miss something? Thanks!
bigcraiginjax
May 13, 2008 at 10:14 am
Update--I found the correct driver and installed mysql-connector-odbc-5.1.4-winx64.msi
and created the ODBC connection in the 64 bit ODBC administrator--test connection worked fine. When I went back to SQL to try again, I still got the same error. Do I need a reboot or restart of the SQL Server box?
bigcraiginjax
June 17, 2008 at 3:23 am
Hi,
I have managed to successfully make a connection with a mySQL database server.
However, when attempting to expand the Catalogs, It just seems to hang or indefintely attempts to expand.
Catalogs (expanding...)
I have waited 15 mins so far and nothing has happened.
Any advice would be appreciated.
thanks.
June 17, 2008 at 3:26 am
Aaah, just got a message.....
"Failed to retrieve data for this request. (Microsoft.SQlServer.SmoEnum).
Timeout expired.
Actually, all queries are timing out.
June 17, 2008 at 5:35 pm
I remember rebooting the box after I installed the Microsoft 64-bit ODBC driver. So, just try that, it might help.
June 17, 2008 at 8:20 pm
I can second that.
I definitely had to restart the SQL Server service after installing the driver.
-Simon
June 19, 2008 at 2:04 am
THanks for the advise. I restarted the machine and still no joy.
When I run the command "EXEC sp_tables_ex is2" I get the following error:
OLE DB provider "SQL Server" for linked server "(null)" returned message "Unspecified error".
OLE DB provider "SQL Server" for linked server "(null)" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.1.24-rc-community-log]Table 'video.TABLES' doesn't exist".
Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 41
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.
When i use query "SELECT * FROM IS2...Campaigns" I get a "Catastrophic failure" - rather dramatic error!
Not sure what to try next
June 25, 2008 at 2:20 pm
Hi All:
I'm working with a MySQL database that serves a website, and need to periodically transfer data from our local SQL Server 2005 server to the MySQL server.
I've setup the MySQL server as a linked server using SMSS, using the directions provided here, and things are working great. (Thanks again to everyone for the contributions.)
What I'm concerned about now is performance.
When I run INSERT statements that insert rows from the MsSQL Server (SQL Server 2005) server to the MySQL server, the performance is terrible.
For example, on average it takes almost 8 minutes to insert about 5,000 rows into an empty table in MySQL with no indexes.
I've tried using the following two methods, with identical results:
INSERT MySQLServer...MySQLTable (Columns, ...)
SELECT (Columns, ...)
FROM MsSQLServer
and
INSERT OPENQUERY (MysQLServer, 'SELECT [Columns,...] FROM MySQLSchema.MySQLTable'
SELECT (Columns,...)
FROM MsSQLServer
Can anyone offer any thoughts or insights?
Any input would be greatly appreciated.
-Simon
Viewing 15 posts - 31 through 45 (of 129 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy