[CLUE-Tech] OT: SQL Server dump-to-text

[Chris K. Chew]

Well yes, and no.

To clarify, I found that SQL Server is able to dump the database to a
records-only sql text file.  The file will contain no information about the
table structures, however.  I was hoping that ms would remain faithful to
the SQL standard and give me a genuine sql dump of the database including
CREATE TABLE statements.  I searched both the Internet and every related
book in the local computer bookstore, but couldn't find anything telling me
that it can do it, much less how to do it.  I like to know how, if it is
indeed possible.

But I did get the database transferred into mysql!  The solution was a
combination of the sql server exporter into an access .mdb file, and then
from access to mysql using the demo version of Intelligent Converters'
Access To MySQL converter [ http://www.convert-in.com/acc2sql.htm ].  I
attached a text file howto for anyone that is interested.

But the question still remains:  Can MS SQL Server dump all the contents of
a database, including table structure, to an SQL text file?  While this
isn't a linux question directly, it is an ANSI standards question that most
linux and unix based database people will run into sometime.  Consequently,
it may be of interest to many of us.

Thanks,

Chris

-----Original Message-----
From: clue-tech-admin at clue.denver.co.us
[mailto:clue-tech-admin at clue.denver.co.us]On Behalf Of David Willson
Sent: Saturday, December 22, 2001 10:14 PM
To: clue-tech at clue.denver.co.us
Subject: [CLUE-Tech] OT: SQL Server dump-to-text


Do you still have a need to dump your SQL Server database to text?  If so, I
may be able to help.  I am fairly certain I have seen the feature in there
someplace.

-----Original Message-----
From: clue-tech-admin at clue.denver.co.us
[mailto:clue-tech-admin at clue.denver.co.us]On Behalf Of Chris K. Chew
Sent: Wednesday, 19 December, 2001 14:45
To: clue-tech at clue.denver.co.us
Subject: RE: [CLUE-Tech] Kernel Load Error: Can't locate module
Char-major 10-135


This is my favorite part of open source software.  For instance, I had to
work on a ms sql server yesterday for the first time (I have a been a MySQL
dba for almost two years now), trying to convert a database into a plain
text sql file.  Try googling for "Dump sql server to sql text file" and you
find nothing except plugs for a few books you can by for $50+ that don't
have the answers.  But google for "can't locate module char_major_10_135"
and not only do you get a solution, but also a detailed explanation of what
the module does and why it does it.

Incidentally, ms sql server is UNABLE to dump its database contents and
structure to a simple SQL text file.  Now last I checked, this was pretty
much the #1 requirement for SQL compliance.  SQL - yet another standard
stolen and then broken by microsoft.  I sure hope the public catches on
soon...

But I digress, thanks for listening.

Chris

-----Original Message-----
From: clue-tech-admin at clue.denver.co.us
[mailto:clue-tech-admin at clue.denver.co.us]On Behalf Of BOF
Sent: Wednesday, December 19, 2001 1:13 PM
To: clue-tech at clue.denver.co.us
Subject: Re: [CLUE-Tech] Kernel Load Error: Can't locate module
Char-major 10-135


Chris K. Chew wrote:

>A Linux From Scratch Faq explains what is happening:
>
>	http://www.linuxfromscratch.org/faq/#char-major-10-135
>
The thing that I like about the CLUE and BLUG lists is that there's
almost always someone who knows the answer! Thanx.

>FYI I just googled for the error message.
>
<Blush!> I would have never considered this: isn't it amazing what one
can find on the Internet? <g>

_______________________________________________
CLUE-Tech mailing list
CLUE-Tech at clue.denver.co.us
http://clue.denver.co.us/mailman/listinfo/clue-tech


_______________________________________________
CLUE-Tech mailing list
CLUE-Tech at clue.denver.co.us
http://clue.denver.co.us/mailman/listinfo/clue-tech

_______________________________________________
CLUE-Tech mailing list
CLUE-Tech at clue.denver.co.us
http://clue.denver.co.us/mailman/listinfo/clue-tech

-------------- next part --------------
IMPORTING FROM MS SQL SERVER TO MySQL:

12/18/2001

1)  Create an empty Access mydb.mdb file from access

2)  Transfer the mdb file onto the MS SQL database server or share it on the network.

3)  Use the MS SQL exporter to export the database into the mdb file.

4)  Open the newly-filled Access database to check that the data is there.

5)  Use Intelligent Converters Access To MySQL 1.5 Demo to dump table structure into a an mydb.sql file.
	NOTE that the demo will only export 5 records for each table, which causes the need for
	steps 6, 8 nad 9.
	NOTE also that I tried two visual basic modules.  Neither of them would run, however, and were apparently form Access 97,
	whereas I use Access 2000.

6)  Edit mydb.sql and remove all records, leaving only the structure-related SQL.

7)  Cat the sql file into MySQL, thereby creating the tables:
	# cat mydb.sql | mysql -uusername -p mydb

8)  Use Access to export each table into a CSV file (the default export should work fine).

9)  Put the files into a folder called mydb_data and move the folder to the MySQL server box.

10) Use mysqlimport to import the csv data:
	# mysqlimport -uusername -p mydb /tmp/mydb_data/*

11) Check your work using:
	echo "show tables status;" | mysql -uusername -p mydb