November 20, 2007 at 7:28 am
We have sql server 2000, I want to copy selected tables from Great Plains database into the reporting database, so that we can run resource intensive queries/reports against the reporting database which resides in a different server.
Currently I am using sql DTS packages to copy the data of the selected tables into the reporting database, the way I am doing in DTS is: truncate the table in the destination db as step1, then loading the entire data into the reporting tables.
I am thinking of doing that in a better way.
I also tried using transactional replication between great plains db and reporting db for replicating selected tables, it worked fine until the user's started using combiner tool.
Note: The user's use combiner tool in great plains which would temporarily add something to the table during the update process.
When they used combiner tool, the following was the error:
SQL Server cannot alter the table RM00101 because it is being published for replication.
After removing replication, they were able to use combiner tool successfully. So due to this reason I am not considering replication. Please suggest me the best way of copying selected tables from Great Plains to Reporting db, as I want to copy the tables every night. Thanks in advance.
November 26, 2007 at 10:48 am
Is there a date/time field for updates on the source? If so, that makes it easy.
I'd still use DTS. If the table is small, the truncate works fine. If it's not, then I'd move data into a staging table (either all or changed data). then do an update to your reporting table of all fields, matching on primary key. Delete based on the same criteria from the staging table. What's left is the stuff that needs to be inserted.
If you need to handle deletes, you might use a delete trigger in the source to capture the PKs of these rows and a separate DTS package or step to delete those rows in the reporting table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply