export data from mdf and ldf files

Hello,

My first post, I don't know much about MS SQL.

I have the .mdf and .ldf files from an MS SQL 2000 server. I don't have MS SQL server running on my home machine. I'd like to be able to extract the data from the .mdf file to something I could use on my home machine. XML would be good, CSV would be ok too, maybe even some way to import into MySQL. Any help would be appreciated, also if I've some how missed the forum faq, please point me to it.Where did you get the files? Do they know that you have them?|||Its from work, they asked me to see about moving the db to mysql. And since they asked me, I'm sure that qualifies as they know I have them. If it makes you feel better you can make your solution require that I go back to the machine with the MS SQL server. If there's some program on that machine that can export the data to a format I can use, that will be fine too. Not ideal, but I can do it.|||You can use "Proxy table" feature of MySQL, but you'll have to have network connectivity with SQL Server when you do that. If you have VPN connection configured to your work place, you're good to go. Defficiency of MySQL in that respect lies in the fact that you'll have to create those proxy tables one-by-one. But once you did them all, - do whatever you want with them using your favorite MySQL tools, - extract, import, transform, etc.

EDITED: I got totally confused. I was thinking about iSQLAnywhere, not MySQL. Check if MySQL has something like "linked server" to hook you up with the SQL Server.|||If you are taking the files home, I'll assume that they are smaller than 2 Gb. If the files are smaller than 2 Gb, you can attach them to MSDE on your home machine and use them "as is".

Or better yet, you can buy SQL Server Developer for around $49 US and then you can do all kinds of fun things with them! This would give you a lot of tools (such as DTS and SQL Enterprise Mangler) that will make the job of porting data to MySQL many, many times easier!

If you don't want to bother with that, you can go back to the orifice and use BCP to put the data into flat ASCII files, that you can import with MySQLAdmin or a similar tool.

-PatP|||The data is actually only a few megabytes, it was running on MSDE at the office. I'm reading up on bcp now, bcp looks like that'll be the easiest for me to do.|||If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@.dbName sysname
, @.fp varchar(255)
, @.User varchar(255)
, @.Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @.fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@.@.SERVERNAME+' -U'+@.User+' -P'+@.Pwd+' '
+ '-f'+@.fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @.fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @.CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @.CMD

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD
SELECT @.CMD = 'ECHO ' + @.CMD + ' > ' + @.fp + '\bcpout.bat'
EXEC master..xp_cmdshell @.CMD
SELECT @.CMD = @.fp + '\bcpout.bat'
SELECT @.CMD
insert a (s)
exec master..xp_cmdshell @.cmd

FETCH NEXT FROM bcpout INTO @.CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF

drop table emp2|||You're a genius Brett. Hey, I stole a script from someone that looks just like that. (grin)|||You're a genius Brett. Hey, I stole a script from someone that looks just like that. (grin)

Why Thank you..thank you very much...x002548 has left the building