Conference amcucs::ms-sqlsvr

    
    I would look at bcp and compare the datatypes you have in Rdb with 
    what is supported in SQL Server.  
    
    Or, you could use something like DBI  (DB Integrator now owned by 
    Oracle) to connect to both databases and do a load that way. 
    
    Or you could write an application that would read the data and load
    into SQL.
    
    Mary Jo 
    

    When I had to migrate an RDB database to SQLServer 6 15 months ago the
    process I went through was something like:
    
    1) Go into SQL on the VAX and dump the database structure (tables,
       constraints, triggers, stored procedures) to a text file.
    2) After some analysis go into SQL Enterprise Manager on the PC and 
       construct the tables and constraints (keys, foreign keys etc.)
    3) After a lot more analysis write the triggers and stored procedures
       for SQLServer. There are subtle differences between the two which
       require some careful thought at this stage.
    4) Write a C program (using MS VC++) to run on the PC to read data from
       the RDB database, do any minor munging required and write the data
       to the SQLServer database using the ODBC API. Oh yes, it's a good 
       idea at this stage to drop all the constraints on the SQLServer
       database *before* running the program and then re-impose them
       afterwards to avoid a lot of extra processing by SQLServer and
       consequent poor performance of the conversion program.
    
    All-in-all not a trivial process. Of course if your database is a
    simple one with no stored procedures and no triggers and no changes
    required in table structure then there is probably a quicker way using 
    Unload and BCP.
    
    Cheers,
    Brian
    

Thank you both for your responses relative to the exporting/importing process.

Would either of you happen to know if MS SQL Server supports the data types
I specified in my base note (quadword scaled (-6), signed word, etc.)?  I'm
having a tough time finding equivalents in the documentation, etc.  

Thanks once again for the help,
Patti

    
    Whatever the exact details of the question... bcp is the answer.
    
    :-)
    
    I just come back from toying with a customer who had the primary
    data on an IBM AS400 and labourisly copied it with their own
    utility into SQLserver. It took days. They then needed to do the
    same towards Oracle on HP (and Digital eventually). It started 
    out to take days again. I used SQLserver to generate the script
    to define all the tables. Then used an AWK script to transform
    that into multiple SQL*Loader CTL files to load from flat files.
    The bcp commands to generate the flat files and to run the loader
    were ofcourse generated by Isql with commands like:
    
    	select "bcp xxx." + rtrim(u.name) + "." + rtrim(o.name)
    	     + " out d:\bcp\" + o.name + ".bcp -Usa -P -Sxxx -c"
        from sysobjects o, sysusers u
    	where u.name like '%xxx%'
    	and u.uid = o.uid
    	order by o.name
    
    	/* xxx must be replace by db name, servername, strings to look for */
    
    think time: 1 hour, script generations: 1 hours, bcp unload for 2.5GB
    of flat data (800 MB compressed) from a 6GB databse: < 2 hours, FTP to
    Unix box approx 2 hours, SQLload: < 2 hours. It was all over and done
    before the official procedure had transferred 10% of the records.
    You can also nicely overlap operations based tom time/space needs
    
    Process	|  Table being worked on in time -->
    ------------+--------------------------------------------
    bcp		| a	b	c	d	e
    ftp		|	a	b	c	d	e
    (delete 1)	| 		a	b	c	d	e
    ldr		|		a	b	c	d	e
    (delete 2)	|			a	b	c	d	e
    
    
    
    
    Hint... mark the directoried to hold the flat bcp files COMPRESSED.
    
    fwiw,
    	Hein.