SQL Query construction 1

[DotNetNewbie]

Hi,

Firstly; my apologies if this is posted in the wrong section.

I have 2 SQL servers, one is SQL 2000 (Alpha) and the other is 2005 (Omega).

Alpha holds our main CRM database, which sadly cannot be upgraded to any newer version of SQL...

What I would like to do is construct a query that runs overnight to extract information from the CRM DB on Alpha and create/replace a database on Omega.

I think I know how to achieve the extraction, its the creation/replacement of the database on Omega I do not know how to do, any solutions most welcome.

Many thanks in advance.

.net

 

[ousoonerjoe]

Look into DTS packages that copy objects and set up a schedule to push it over to the 2005 server.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[cmartin75]

Also you can use the SQL Server 2005 Import Wizard and check the option "Save as SSIS package" at the last step of the wizard.
You can schedule this SSIS package generated by the wizard

 

[ousoonerjoe]

Would it be faster to Push or Pull the data? Obviously both will get the job done, but I wonder which would be more efficient?

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[DotNetNewbie]

That is a very good question, any ideas?

.net

 

[SQLSister]

SSIS is generally more efficient than DTS. I would go for setting up the move in SSIS.

"NOTHING is more important in a database than integrity." ESquared

 

[ousoonerjoe]

Thanks Sister. I suspected the newer version should be faster, but I learned long ago, never assume anything regarding MS releases.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[SQLSister]

Plus I'm not sure if DTS would even send to a 2005 database as it wouldn't have a driver for that.

"NOTHING is more important in a database than integrity." ESquared