August 12, 2009 at 2:25 pm
I have a painful problem - but the doc says it will go if I keep taking the tablets.
Anyway, I am trying to set up a linked server to MySQL from SQL server 2000 64 bit running on Itanium.
I just seem to be stumped at every turn. I managed to get hold of the MSDASQL for 64 bit - and set up the driver as you specified.
If I try to look at the catalogue, I get "OLE DB Provider 'MSDASQL' reported an error. the provider did not give any information about the error (Microsoft SQL server, error: 7399)
I am using the root user (to get it set up - I will choose a suitable user once I have a connection)
Any ideas where I could look guys?
"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
August 12, 2009 at 2:47 pm
So you are trying to pull data from SQL Server ???? to mysql version? (Querying SQL Server from mysql?)
My bad, read and comprehend Jim, SQL Server 2000 64 bit.
If so do you have a driver for SQL Server on the box that houses the mysql instance?
SQL Server Native Client or something like that for windows, FreeTDS for *NIX
August 12, 2009 at 2:52 pm
hkhaar,
It seems like you could have the .asp code post the data to the mysql server right after it posts to the SQL Server. Basically you'd have two db calls from one post to the form:
1. to the SQL server
1 to the mysql server
I don't know how soon you need the data on the mysql instance, but if it could wait you could always do some batch processing after every so many inserted records.
I guess you could write a trigger that would get the id of the inserted record and call a command line argument that would do a wget on a web service that could insert the record into the mysql db, but I'd only do that if I had no other way. Allowing sql server to hit the command line is not good form.
p.s. My posted thoughts are of no monetary value, they are free. I've gotten help from many individuals over the years, I'm just trying to give back.
August 12, 2009 at 3:04 pm
Hi - its late and I'm not making much sense - been a VERY long day 🙂
I am trying to pull data from a MySQL instance living on a Windows 2003 box that it shares with a SQL server 2000 instance, into SQL Server 2000 64 bit box.
I found the MSDASQL driver - took a while 🙂 and installed it. I am using the MySQL 5.1 driver - should I uninstall it and go back to 3.51?
I can set up the driver and test it using its set-up GUI and it works fine - under the 32 bit ODBC administrator.
Now I am wondering - is this caused by 64 bit SQL Server not being able to see/use the 32 bit driver? Even with the MSDASQL installed?
"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
August 12, 2009 at 3:26 pm
I'm pretty sure you'll need a 64 bit driver for mysql on the box that hosts the 64 bit SQL Server 2000 instance.
Then use the OLEDB provider on the 64 bit box to connect to the 64 bit driver via the dsn.
I think there are some others that posted on this thread that were able to pull data from mysql 32 bit to SQL Server 64 bit, but it seems that one had to purchase a driver (Ouch).
Maybe you'll have better luck finding one now that 64 bit SQL Servers are more prevalent these days.
September 15, 2009 at 6:54 pm
So i have my linked server all set up successfully and am trying to update a mediumtext field in MYSQL from a char(100) in MSSQL but it never updates, it will update numeric data but never the text.
Any ideas?
November 3, 2009 at 8:05 am
Hi, I just set up a MYSQL linked server and any query I pass to it just endlessly runs, never times out or gives an error.
I used the new 5.31 MYSQL OBDC driver
Example:
Select * from SSWEBAPP...ss_user
any help would be greatly appreciated
November 3, 2009 at 8:10 am
If you run "SHOW PROCESSLIST" on your mysql server, can you see the SQL server connection?
"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 8:29 am
WiltsDBA (11/3/2009)
If you run "SHOW PROCESSLIST" on your mysql server, can you see the SQL server connection?
Thanks for the reply!
SHOW PROCESSLIST only shows the local connection.
November 3, 2009 at 8:36 am
Shouldn't be.
If you can only see the local connection in your processlist, then the linked server isn't making a connection.
If you run management studio, expand server objects, linked servers, and then your MySQL server, can you see the catalogues?
Also (just thought here...) have you tried using "OPEN ROWSET" instead of a straight select?
Syntax is here : http://msdn.microsoft.com/en-us/library/ms190312.aspx"> http://msdn.microsoft.com/en-us/library/ms190312.aspx
"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 8:47 am
WiltsDBA (11/3/2009)
Shouldn't be.If you can only see the local connection in your processlist, then the linked server isn't making a connection.
If you run management studio, expand server objects, linked servers, and then your MySQL server, can you see the catalogues?
Also (just thought here...) have you tried using "OPEN ROWSET" instead of a straight select?
Syntax is here : http://msdn.microsoft.com/en-us/library/ms190312.aspx"> http://msdn.microsoft.com/en-us/library/ms190312.aspx
1) no the catalogues do not expand. Just shows the linked server object
2) same result using OPENROWSET or OPENQUERY
this is sorta baffling since the OBDC connection test works fine
November 3, 2009 at 8:50 am
Can you script out your Linked server and post it here please (* out any passwords...)
Which ODBC conector are you using?
"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 8:54 am
Oh, and a thought:
If you right-click on the linked server, choose properties and click "security" in the left pane, at the bottom do you have "Be made using this security context" and have a valid user name and password there?
Also, on your MySQL server, have you got the server/user set up? What I mean is that MySQL validates not just the user, but also where you are logging on from. If you can get away with it make sure that your user has '%' set up as the location (thats everywhere).
Another "gotchya" is that MySQL usernames and passwords are case sensitive - so UserName is not the same as USERNAME is not the same as username....
"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 8:54 am
Here's the script of the linked server. For whatever reason it does not include the user I mapped.
/****** Object: LinkedServer [SSWEBAPP] Script Date: 11/03/2009 10:52:07 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SSWEBAPP', @srvproduct=N'MySQLDatabase', @provider=N'MSDASQL', @datasrc=N'SS-WEBAPP'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SSWEBAPP', @optname=N'use remote collation', @optvalue=N'true'
November 3, 2009 at 8:58 am
WiltsDBA (11/3/2009)
Oh, and a thought:If you right-click on the linked server, choose properties and click "security" in the left pane, at the bottom do you have "Be made using this security context" and have a valid user name and password there?
Also, on your MySQL server, have you got the server/user set up? What I mean is that MySQL validates not just the user, but also where you are logging on from. If you can get away with it make sure that your user has '%' set up as the location (thats everywhere).
Another "gotchya" is that MySQL usernames and passwords are case sensitive - so UserName is not the same as USERNAME is not the same as username....
again thank you very much for the help.
1) under the linked server I do have the "Be made using this security context" checked and a valid user/PW set up there
2) also I do have the user mapped to all hosts under MYSQL
3) yep 🙂 made sure to be implicit with the case sensitivity. I'm using root, so pretty hard to screw up.
Viewing 15 posts - 61 through 75 (of 128 total)
You must be logged in to reply to this topic. Login to reply