2

This organization has data in various places including MySQL 5 databases and SQLServer 2000 (developer edition) on Win2k Server. For the data in MySQL I have used PHPMyAdmin, mysqldump, and so on to get data out and transform it. I've also written some simple Ruby Rails code to work with the MySQL data and used SQLYog (pro) to transfer data from SQLServer 2000 to MySQL 5. Now I need to get some data from the SQL Server 2000 databases and I'd like to do it without having to write code (or make changes to legacy ColdFusion app's codebase). This doesn't seem unreasonable ...

SQL Server's Query Analyzer can probably save query output somehow. I'm pretty sure I could use the SQL Server console as well and pipe that into a file in a console and then do transforms on that. What's a better tool? Is there a tool like PHPMyAdmin for SQLServer 2000 or some other quick way to get CSV from SQLServer 2000 with little or no code?

Thanks,

adric

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
adric
  • 531
  • 2
  • 7
  • SQL Server 8? Judging by the reference to Query Analyzer, I assume you mean SQL Server 2000? Or possibly 7? – MartW Sep 28 '09 at 06:40
  • Yes, they are SQL Server 2000 Developer edition. I'll use that if it's more clear and feel free to retag. Thanks! – adric Sep 28 '09 at 07:20
  • Nobody uses numbers since SQL Server 2000 anymore. Before that there's been 6.5, 7... But after it was 2000, 2005, 2008, 2010. So please replace 8 with 2000. It will de-confuse people. – Robert Koritnik Sep 28 '09 at 08:59
  • be confused no more! yes, even though sql server 2000 is sql server version 8, there's something very unusual about calling it sql server 8, possibly it sounds too much like sybase 8 ? – Nick Kavadias Sep 28 '09 at 12:58

3 Answers3

4

SQL Server 2000 came with Data Transformation Services (DTS)1 which allows data to be moved from one database to another,2 potentially including transformations.

This is designed to move bulk data, Query Analyzer isn't.

1 Replaced with SQL Server Intergration Services (SSIS) in newer versions.

2 Or anything with an ADO driver. I've used it to move data to from Excel files in the past.

Richard
  • 5,324
  • 1
  • 23
  • 20
3

The closet tool you'll find in SQL Server 2000 to mysqldump is the BCP utility You can export tables out of the database and into csv without writing any code, although it will require at least some batch files.
eg. 

BCP northwind.dbo.customers out c:\customers.txt -c -t, -T –S<servername>

If you want to pipe output from queries, then the command line version of query analyzer is called osql. You can run this from the command prompt & do things like pipe output, or specify an output file.

IMHO the least painful way of getting sql server data into mysql is by using linked servers. I answered a similar question about getting mysql data into sql server, but the same method can be applied. This will of course, require more code writing.

Community
  • 1
Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
  • BCP manpage you linked looks promising. I'll see if it's available on one of our servers next time I'm on site. Re: linked server Yes, I can use SQLYog to push the tables into a MySQL database local or remote, but the users are updating the SQL server database so I'd have to do that regularly/before every data pull. I'm hoping not to have to do all that for this. Thanks! – adric Sep 28 '09 at 16:28
  • BCP works well. I may have a followup question on it (field names in first row)in a few minutes. Thanks! – adric Sep 28 '09 at 17:03
  • 1
    If you want to run queries you should still be using bcp, not osql. osql isn't designed to output files correctly like that. Change the object name "northwind.dbo.customers" to a query, then change "out" to "queryout". BCP is installed on all SQL servers from at least 6.5 and up. – mrdenny Sep 28 '09 at 21:03
0

There is as mentioned previously the DTS option, or I believe the simpler GUI incarnation of it via right click and export. You can do this to excel, CSV or other file formats but I believe that you will need to recreate the table structure manually. One tool I have used before for migrating SQL Server databases (structure as well) was Apex software's SQL diff, as far as moving to another platform, well I don't know exactly how that would work, it may be clever enough to "do what you Mean"

Dan
  • 852
  • 1
  • 8
  • 28
  • You don't need to recreate the schema manually - Rt-Click (on database) > Tasks > Generate Scripts (as well as a host of 3rd Party tools) can be used to script the structure in seconds. – CJM Sep 28 '09 at 15:51
  • I know I can right click in Enterprise Manager and do some things. I'll see what my export options are in there. Thanks! – adric Sep 28 '09 at 16:22