MYSQL - DTS - MSSQL help needed Warning novice inside!

Hi,
**I appologise as this is posted in microsoft.public.sqlserver.dts
also**
Would anyone be so kind and point me in the rigth direction?
I have a MYSQL server and a MSSQL 2000 server.
MYSQL
Database name is Membership
Table is Users
Column is email_address.
I want to transfer the email_address data over to a column in an
existing MSSQL Database.
When I run the DTS wizard I can successfully connect to the MYSQL
database server, but the only option I get is to use a SQL Statement.
So this is what I have started with...
mysql_select_db('membership');
SELECT * FROM users WHERE email_address='"*"
If i try and preview I get an unexpected errror and if i run through
the wizard I get wrong systax at line 1.
I am a total novice at this and am stumbling around in the dark...
Any help much appreciated...
Thanks
Col
**Sorry this is posted in microsoft.public.sqlserver.dts also..**Sorry to add I need this to be automated which is why I am trying to
use DTS
Thanks
Col|||Do you want to transfer all email addresses?
If so, I think this is what you want to do
SELECT * FROM membership.users
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"techcs" <colinsealeaf@.blueyonder.co.uk> wrote in message
news:1158045662.036499.225710@.e3g2000cwe.googlegroups.com...
> Hi,
> **I appologise as this is posted in microsoft.public.sqlserver.dts
> also**
> Would anyone be so kind and point me in the rigth direction?
>
> I have a MYSQL server and a MSSQL 2000 server.
>
> MYSQL
> Database name is Membership
> Table is Users
> Column is email_address.
>
> I want to transfer the email_address data over to a column in an
> existing MSSQL Database.
>
> When I run the DTS wizard I can successfully connect to the MYSQL
> database server, but the only option I get is to use a SQL Statement.
> So this is what I have started with...
>
> mysql_select_db('membership');
> SELECT * FROM users WHERE email_address='"*"
>
> If i try and preview I get an unexpected errror and if i run through
> the wizard I get wrong systax at line 1.
>
> I am a total novice at this and am stumbling around in the dark...
>
> Any help much appreciated...
>
> Thanks
> Col
> **Sorry this is posted in microsoft.public.sqlserver.dts also..**
>|||techcs wrote:
> Sorry to add I need this to be automated which is why I am trying to
> use DTS
> Thanks
> Col
>
Automation doesn't require DTS. If you can connect to MySQL with DTS,
then you can connect with other components as well. Create a linked
server that points to the MySQL instance, and then you can use a simple
SELECT statement to grab the data. The SELECT statement can then be
scheduled as an Agent job. Something like this:
SELECT email_address FROM linkedservername.Membership.dbo.Users
I don't know MySQL, so I'm not sure of the exact syntax, you might not
need the schema reference, i.e. "dbo".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks will have a look at that now... Sounds good...
Col