Converting Data from Access 2000 to SQL Server 2000

Hi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.

Thanks.
HOW TO: Convert an Access Database to SQL Server

Convert SqlExpress to Sql 2000

Some thread searches here say conversion (downgrade) of a SqlExpress database to Sql Server 2000 is trivial. I haven't yet figured out an efficient way of doing so beyond using someone's Database Publishing Wizard utility (http://www.codeplex.com/sqlhost) to create a sql 2000 compatible script, replete with schema *and* data, then running that script against the Sql 2000 database (freshly created without any structure). I've done this successfully with smaller databases, but the larger ones really tax and drain the server resources and scripts have failed with "out of memory" errors. We're putting more memory in the Sql 2000 server, which may avert memory errors, but there's still the issue of server load. Has anybody since come up with or know of a better method that's quicker, more efficent and less drain on the server?

TIA,

Rick
Nevermind. Was able to use 2000's DTS in conjunction with Database Publishing wizard. Much faster and easier on the server than INSERT scripts.

Convert SqlExpress to Sql 2000

Some thread searches here say conversion (downgrade) of a SqlExpress database to Sql Server 2000 is trivial. I haven't yet figured out an efficient way of doing so beyond using someone's Database Publishing Wizard utility (http://www.codeplex.com/sqlhost) to create a sql 2000 compatible script, replete with schema *and* data, then running that script against the Sql 2000 database (freshly created without any structure). I've done this successfully with smaller databases, but the larger ones really tax and drain the server resources and scripts have failed with "out of memory" errors. We're putting more memory in the Sql 2000 server, which may avert memory errors, but there's still the issue of server load. Has anybody since come up with or know of a better method that's quicker, more efficent and less drain on the server?

TIA,

Rick
Nevermind. Was able to use 2000's DTS in conjunction with Database Publishing wizard. Much faster and easier on the server than INSERT scripts.

Convert DBs numeric date to SQL datetime

Hi,
I'm moving some data from AS400 DB2 to SQL Server and run
into date conversion problems.
My date from DB2 is numeric 8 characters eg "20031231".
How can I convert it to either datetime or smalldatetime
format in SQL server ?
If my table in SQL is defined as datetime/smalldatetime or
numeric, I will get error during the data pump process.
If I define in char format, data pump works fine but data
isn't the format I want ?
Can someone help ? Thanks.If the date is part of the PK for a fact or dim table, I would use an intege
r key and not datetime. INT or DECIMAL(9,0) should be sufficient for your ne
eds if that is the case. I sometimes import .txt files from AS/400 and the s
ource table must not allow
packed signs. In the activex script transforming the data from source to des
tination you could convert int to datetime or char to int to datetime if you
wish.
You might want to consider importing textfiles and not use the source table
directly since this option gives you a snapshot of the data. If the import f
ails you will always have a source file to check for errors. A transactional
source table might change
and the error be corrected without your knowledge.