| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2007-11-16 : 17:23:29
|
| 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. PLease note that user's use combiner tool in great plains which would temporarily add something to the table during the update process like changing schema. I tried setting up transactional replication from Great Plains to Reporting database but it ran fine until the user when trying to use combiner tool, gave the following error:SQL Server cannot alter the table RM00101 because it is being published for replication.After removing replication, they were able to alter the table.So due to this reason, I am not considering replication for copying the tables from great plains to reporting database.Could you please suggest me the best option, as I want to copy the tables every night. Please let me know. Thanks. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-17 : 01:36:12
|
| Use dts package. |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-18 : 09:40:23
|
| Currently I am using DTS packages to copy data, but the way I am doing is truncating the data in the destination table as the first step and then loading all the data in the destination table. But I want to make it better, by somehow copy only the new transactions/updated transactions in the destination table, but not sure how to do with SQL 2000 DTS. And also how about backup/restore option? Please let me know. Thanks for the help. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-18 : 20:52:24
|
| Then you can run sql query in dts, but that maybe slower than copy whole table. For backup/restore, you need restore whold db unless put those tables in separate file group. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-19 : 09:18:24
|
| In my case, the source db has only one filegroup, so do we have to change it to multiple filegroups for the source database, and then do backup/restore of the required tables based on the filegroups to the destination database. PLease let me know if this is what you meant? Thx. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-19 : 23:07:55
|
| Yes, have to create another filegroup in source db and move related tables to new filegroup if you like to do backup/restore. |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-20 : 09:35:33
|
| I do'nt think I will get permission to create filegroups in the GreatPlains db from the management, is there any other way to copy selected tables from Great Plains DB, if there are no other options, then I shall copy the entire db to the reporting server. Thx in advance. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-20 : 10:14:30
|
| If you only need static data, just restore the database on the reporting server from your last backup.CODO ERGO SUM |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-11-20 : 10:41:51
|
| I will have to restore the backup every night to the reporting server so that the reporting server will be in sync with the Great Plains production db as of that night. Thx. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-20 : 10:46:29
|
quote:
Originally posted by sqlserverdeveloper
I will have to restore the backup every night to the reporting server so that the reporting server will be in sync with the Great Plains production db as of that night. Thx.
That is fairly simple to setup, and will probably be just as fast as any other method.CODO ERGO SUM |
|
|
|
|
Setting up reporting database.