I am trying to set up mysql as linked server for SQL Server 2000.
These are the steps I already have taken.
1- Installation of MyODBC 3.51 driver
2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
'mysql' data source
Now what I need to have is a SQL commands to add it as a linked server?
OR
What do I specify for the following fields by using Enterprise Manager
a) Provider Name
b) Data source
c) Provider string
d) Location
e) Catalog
I am using default 'root' account without any password.Asaf,
Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
is what you have to use to create the link
Asaf wrote:
> I am trying to set up mysql as linked server for SQL Server 2000.
> These are the steps I already have taken.
> 1- Installation of MyODBC 3.51 driver
> 2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
> 'mysql' data source
> Now what I need to have is a SQL commands to add it as a linked server?
> OR
> What do I specify for the following fields by using Enterprise Manager
> a) Provider Name
> b) Data source
> c) Provider string
> d) Location
> e) Catalog
> I am using default 'root' account without any password.|||Thanks for your reply Dennis but my problem is still the same. Being novice
I
have no idea what I specify for all sp_addlinkedserver parameters such as
sp_addlinkedserver [ @.server = ] 'server'
[ , [ @.srvproduct = ] 'product_name' ]
[ , [ @.provider = ] 'provider_name' ]
[ , [ @.datasrc = ] 'data_source' ]
[ , [ @.location = ] 'location' ]
[ , [ @.provstr = ] 'provider_string' ]
[ , [ @.catalog = ] 'catalog' ]
I would appreciate if someone out there could provide me a complete
sp_addlinkedserver with all parameters for MySQL.
"Dennis Black" wrote:
> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>|||Dennis, this is what I have done
exec sp_addlinkedserver
@.server = 'DefaultMySQL'
, @.srvproduct = 'MySQLProv'
, @.provider = 'MSDASQL'
, @.datasrc = 'MySqlDSN'
, @.location = NULL
, @.provstr = NULL
, @.catalog = 'mysql'
This is the error message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'MSDASQL' in sysservers.
Would you please help
"Dennis Black" wrote:
> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>|||Here is an example where I link a mysql database to SQL Server:
sp_addlinkedserver
@.server='YouServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
@.provstr='DATABASE=mydb;DSN=My
DSN;OPTION=0;PWD=password;SERVER=localho
st;UID=myuser'
I use a program called WinSQL (http://synametrics.com/winsql), which
creates the Connection string. I used this connection string for the
'provstr' parameter.
Asaf wrote:[vbcol=seagreen]
> Dennis, this is what I have done
> exec sp_addlinkedserver
> @.server = 'DefaultMySQL'
> , @.srvproduct = 'MySQLProv'
> , @.provider = 'MSDASQL'
> , @.datasrc = 'MySqlDSN'
> , @.location = NULL
> , @.provstr = NULL
> , @.catalog = 'mysql'
> This is the error message
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'MSDASQL' in sysservers.
> Would you please help
> "Dennis Black" wrote:
>|||Dear Dennis,
I must say you have been a great help. I have managed to successfully create
a linked server by using the following setting
exec sp_addlinkedserver
@.server='MySQLServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='MySqlODBC', -- This should be the ODBC System DSN.
@.provstr='DATABASE=mySql;DSN=MySqlODBC;O
PTION=0;PWD=;SERVER=localhost;UID=ro
ot'
There is only one more issue; I could query mysql by using e.g user Table
from mysql db.
Select * from OPENQUERY(MySQLServer, 'select * from user')
but usual sql server syntax
SELECT * FROM MySQLServer.mySQL.dbo.[user]
generates an error message
"Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema."
What is it that I am doing wrong ?
"Dennis Black" wrote:
> Here is an example where I link a mysql database to SQL Server:
> sp_addlinkedserver
> @.server='YouServer', -- This could be anything
> @.srvproduct='MySQL', -- This could be anything
> @.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
> @.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
> @.provstr='DATABASE=mydb;DSN=My
> DSN;OPTION=0;PWD=password;SERVER=localho
st;UID=myuser'
>
> I use a program called WinSQL (http://synametrics.com/winsql), which
> creates the Connection string. I used this connection string for the
> 'provstr' parameter.
>
>
>
> Asaf wrote:
>
Showing posts with label myodbc. Show all posts
Showing posts with label myodbc. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
MySQL Linked Server
I am trying to set up MySQL as linked server for SQL Server 2000.
These are the steps I already have taken.
1- Installation of MyODBC 3.51 driver
2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
'mysql' data source
Now what I need to have is a SQL commands to add it as a linked server?
OR
What do I specify for the following fields by using Enterprise Manager
a) Provider Name
b) Data source
c) Provider string
d) Location
e) Catalog
I am using default 'root' account without any password.
Asaf,
Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
is what you have to use to create the link
Asaf wrote:
> I am trying to set up MySQL as linked server for SQL Server 2000.
> These are the steps I already have taken.
> 1- Installation of MyODBC 3.51 driver
> 2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
> 'mysql' data source
> Now what I need to have is a SQL commands to add it as a linked server?
> OR
> What do I specify for the following fields by using Enterprise Manager
> a) Provider Name
> b) Data source
> c) Provider string
> d) Location
> e) Catalog
> I am using default 'root' account without any password.
|||Thanks for your reply Dennis but my problem is still the same. Being novice I
have no idea what I specify for all sp_addlinkedserver parameters such as
sp_addlinkedserver [ @.server = ] 'server'
[ , [ @.srvproduct = ] 'product_name' ]
[ , [ @.provider = ] 'provider_name' ]
[ , [ @.datasrc = ] 'data_source' ]
[ , [ @.location = ] 'location' ]
[ , [ @.provstr = ] 'provider_string' ]
[ , [ @.catalog = ] 'catalog' ]
I would appreciate if someone out there could provide me a complete
sp_addlinkedserver with all parameters for MySQL.
"Dennis Black" wrote:
> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>
|||Dennis, this is what I have done
exec sp_addlinkedserver
@.server = 'DefaultMySQL'
, @.srvproduct = 'MySQLProv'
, @.provider = 'MSDASQL'
, @.datasrc = 'MySqlDSN'
, @.location = NULL
, @.provstr = NULL
, @.catalog = 'mysql'
This is the error message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'MSDASQL' in sysservers.
Would you please help
"Dennis Black" wrote:
> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>
|||Here is an example where I link a MySQL database to SQL Server:
sp_addlinkedserver
@.server='YouServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
@.provstr='DATABASE=mydb;DSN=My
DSN;OPTION=0;PWD=password;SERVER=localhost;UID=myu ser'
I use a program called WinSQL (http://synametrics.com/winsql), which
creates the Connection string. I used this connection string for the
'provstr' parameter.
Asaf wrote:[vbcol=seagreen]
> Dennis, this is what I have done
> exec sp_addlinkedserver
> @.server = 'DefaultMySQL'
> , @.srvproduct = 'MySQLProv'
> , @.provider = 'MSDASQL'
> , @.datasrc = 'MySqlDSN'
> , @.location = NULL
> , @.provstr = NULL
> , @.catalog = 'mysql'
> This is the error message
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'MSDASQL' in sysservers.
> Would you please help
> "Dennis Black" wrote:
>
|||Dear Dennis,
I must say you have been a great help. I have managed to successfully create
a linked server by using the following setting
exec sp_addlinkedserver
@.server='MySQLServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='MySqlODBC', -- This should be the ODBC System DSN.
@.provstr='DATABASE=mySql;DSN=MySqlODBC;OPTION=0;PW D=;SERVER=localhost;UID=root'
There is only one more issue; I could query mySQL by using e.g user Table
from mySQL db.
Select * from OPENQUERY(MySQLServer, 'select * from user')
but usual sql server syntax
SELECT * FROM MySQLServer.mySQL.dbo.[user]
generates an error message
"Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema."
What is it that I am doing wrong ?
"Dennis Black" wrote:
> Here is an example where I link a MySQL database to SQL Server:
> sp_addlinkedserver
> @.server='YouServer', -- This could be anything
> @.srvproduct='MySQL', -- This could be anything
> @.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
> @.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
> @.provstr='DATABASE=mydb;DSN=My
> DSN;OPTION=0;PWD=password;SERVER=localhost;UID=myu ser'
>
> I use a program called WinSQL (http://synametrics.com/winsql), which
> creates the Connection string. I used this connection string for the
> 'provstr' parameter.
>
>
>
> Asaf wrote:
>
These are the steps I already have taken.
1- Installation of MyODBC 3.51 driver
2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
'mysql' data source
Now what I need to have is a SQL commands to add it as a linked server?
OR
What do I specify for the following fields by using Enterprise Manager
a) Provider Name
b) Data source
c) Provider string
d) Location
e) Catalog
I am using default 'root' account without any password.
Asaf,
Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
is what you have to use to create the link
Asaf wrote:
> I am trying to set up MySQL as linked server for SQL Server 2000.
> These are the steps I already have taken.
> 1- Installation of MyODBC 3.51 driver
> 2- Creation of ODBC Data source with name 'MySQLODBC' and connecting to
> 'mysql' data source
> Now what I need to have is a SQL commands to add it as a linked server?
> OR
> What do I specify for the following fields by using Enterprise Manager
> a) Provider Name
> b) Data source
> c) Provider string
> d) Location
> e) Catalog
> I am using default 'root' account without any password.
|||Thanks for your reply Dennis but my problem is still the same. Being novice I
have no idea what I specify for all sp_addlinkedserver parameters such as
sp_addlinkedserver [ @.server = ] 'server'
[ , [ @.srvproduct = ] 'product_name' ]
[ , [ @.provider = ] 'provider_name' ]
[ , [ @.datasrc = ] 'data_source' ]
[ , [ @.location = ] 'location' ]
[ , [ @.provstr = ] 'provider_string' ]
[ , [ @.catalog = ] 'catalog' ]
I would appreciate if someone out there could provide me a complete
sp_addlinkedserver with all parameters for MySQL.
"Dennis Black" wrote:
> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>
|||Dennis, this is what I have done
exec sp_addlinkedserver
@.server = 'DefaultMySQL'
, @.srvproduct = 'MySQLProv'
, @.provider = 'MSDASQL'
, @.datasrc = 'MySqlDSN'
, @.location = NULL
, @.provstr = NULL
, @.catalog = 'mysql'
This is the error message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'MSDASQL' in sysservers.
Would you please help
"Dennis Black" wrote:
> Asaf,
> Check help for "sp_addlinkedserver" in MS SQL Server documentation. This
> is what you have to use to create the link
>
> Asaf wrote:
>
|||Here is an example where I link a MySQL database to SQL Server:
sp_addlinkedserver
@.server='YouServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
@.provstr='DATABASE=mydb;DSN=My
DSN;OPTION=0;PWD=password;SERVER=localhost;UID=myu ser'
I use a program called WinSQL (http://synametrics.com/winsql), which
creates the Connection string. I used this connection string for the
'provstr' parameter.
Asaf wrote:[vbcol=seagreen]
> Dennis, this is what I have done
> exec sp_addlinkedserver
> @.server = 'DefaultMySQL'
> , @.srvproduct = 'MySQLProv'
> , @.provider = 'MSDASQL'
> , @.datasrc = 'MySqlDSN'
> , @.location = NULL
> , @.provstr = NULL
> , @.catalog = 'mysql'
> This is the error message
> Server: Msg 7202, Level 11, State 2, Line 1
> Could not find server 'MSDASQL' in sysservers.
> Would you please help
> "Dennis Black" wrote:
>
|||Dear Dennis,
I must say you have been a great help. I have managed to successfully create
a linked server by using the following setting
exec sp_addlinkedserver
@.server='MySQLServer', -- This could be anything
@.srvproduct='MySQL', -- This could be anything
@.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@.datasrc='MySqlODBC', -- This should be the ODBC System DSN.
@.provstr='DATABASE=mySql;DSN=MySqlODBC;OPTION=0;PW D=;SERVER=localhost;UID=root'
There is only one more issue; I could query mySQL by using e.g user Table
from mySQL db.
Select * from OPENQUERY(MySQLServer, 'select * from user')
but usual sql server syntax
SELECT * FROM MySQLServer.mySQL.dbo.[user]
generates an error message
"Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema."
What is it that I am doing wrong ?
"Dennis Black" wrote:
> Here is an example where I link a MySQL database to SQL Server:
> sp_addlinkedserver
> @.server='YouServer', -- This could be anything
> @.srvproduct='MySQL', -- This could be anything
> @.provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
> @.datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
> @.provstr='DATABASE=mydb;DSN=My
> DSN;OPTION=0;PWD=password;SERVER=localhost;UID=myu ser'
>
> I use a program called WinSQL (http://synametrics.com/winsql), which
> creates the Connection string. I used this connection string for the
> 'provstr' parameter.
>
>
>
> Asaf wrote:
>
MYODBC Drivers for sql server 2005
SO I installed the MyOdbc drivers 5.0 and 3.51 and then went into my Data Sources(ODBC) drivers in my Administrative Control Panel
I then proceeded to add the DSN Under System DSN and I also tried User DSN
When I try to use the import/export tool in my SQL Server 2005 Management studio I don't get presented with the MySql drivers at all for a source
why is this?
Trust me you don't need MySQL ODBC driver in SQL Server to move MySQL data to SQL Server, so tell me what you are trying to do and I will tell you how to do it. Hope this helps.|||
oh well that's pretty much it
The sites php with a mysql DB
I'm making it .NET with an MS SQL DB
So just copying the table data and structure to MS SQL
|||You can use OLE-DB driver to import MySQL data and table structure if it is not in the import/export wizard look for it in DTS/SSIS, another option is to use OPENROWSET or OPENQUERY in SQL Server to connect to MySQL run a search for all of the above in SQL Server BOL(books online). Hope this helps.
Subscribe to:
Posts (Atom)