monthly database merging

Hi,

I have to set up a project in SQLServer Express that will allow the export of tables from multiple PC's running SQLServer Express each month.

These have to be loaded into a single identical database on SQLServer Express each month for combined reporting.

so its basicaly

insert update on PC

export

import overlaying last months data (handle dup keys from the other pc's etc)

report

I've had a look at the SQLServer replication docs and got confused....

So if anyone can point me at appropriate documentation, or suggest a good method for this it would be appreciated

Thanks

SQL Server 2005 Express to SQL Server 2005 Express does NOT support replication. However there is a Import/Export tool (DTSWizard.exe) included with the Express Toolkit (Note: Requires SQL Express SP1 or higher.)

Download the Express Toolkit (and SQL Express SP2) here: http://msdn.microsoft.com/vstudio/express/sql/download/

After installing the toolkit, the Import/Export tool (called DTSWizard.exe) is located at:

"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe".

Add a 'shortcut to SSMSE: [Tools], [External Tools], name and add the shortcut by linking to the DTS file above.

In order to Schedule the process, you may have to employ the Windows Scheduler and add a Task that will run DTSRun.exe.

I recommend creating 'staging' tables, identical schemas to the actual tables. Load your data into the staging tables, then you can move the conforming data to the actual tables, leaving behind the non-conforming data for 'clean-up'.

|||

Thanks

Peter

monthly database merge

Hi,

I have to set up a project in SQLServer Express that will allow the export of tables from multiple PC's running SQLServer Express each month.

These have to be loaded into a single identical database on SQLServer Express each month for combined reporting.

so its basicaly

insert update on PC

export

import overlaying last months data (handle dup keys from the other pc's etc)

report

I've had a look at the SQLServer replication docs and got confused....

Can I do this with replication ?

So if anyone can point me at appropriate documentation, or suggest a good method for this it would be appreciated

Thanks

Yes, it's possible to do it with replication.

Look up the topic on merge replication on MSDN.

Also check out the following links for some additional info:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1527294&SiteID=17

http://msdn2.microsoft.com/en-us/library/ms151819.aspx

Gary

|||

Hello Peter,

Express Edition is not able to be a Publisher. It's capable of being a Subscriber only.

Check header "Integration and Interoperability" from the following link:

Features Comparison:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

and these links to learn more about SQL Server Replication from BOL:

Replication Basics for Express Edition:

http://msdn2.microsoft.com/en-us/library/ms165700.aspx

SQL Server Replication:

http://msdn2.microsoft.com/en-us/library/ms151198.aspx

Ekrem ?nsoy

|||

Unfortunatly that was the documentation that confused me. Is there something a little more practical and less theoretical around.?

|||

The Remote PC's in my system are self sufficient (ie dont need recieve data back from the merge) and are not physically connected to the machine that aggregates the data.

The documentation seems to imply that although I can set up a copy of SQLServer Express to merge the data recieved on CD from the remote sites, that I cannot set up the SQLServer Express remote sites to generate the replication data to the CD's

Do you agree with that statement, from your experiance?