| Author |
Topic |
|
mmiller
Starting Member
6 Posts |
 Posted - 2010-03-26 : 17:19:05
|
| I need to replicate data from a SQL Server 2005 system to a SQL Server 2000 system. My problem is the names of the target database and table are not the same on the 2000 system. I thought I could just use a transformable subscription, but after spending a couple hours trying to figure out how to enable transformation for the publication, I found out that the transformable subscription feature was deprecated in SQL Server 2005. Is there some other way I can replicate data to a database and table that have different names from my source data?Thanks,Mike |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-26 : 20:02:55
|
| You can definitely replicate to a database with a different name. I am doing that to get data from one database to another on the same instance. I'm not sure if you can replicate to a table with a different name, but my guess is yes due to the replication stored procedures. What I would do as a test is create the replication on a test system so that the replication stored procedures are created. Then grab those stored procedures and customize them for your different object name. I don't have SQL Server in front of me at the moment, but the stored procedures are named similarly to sp_MS<dmlType>_ObjectName where <dmlType> is ins/del/upd, depending on the DML action. You'll need to modify all three stored procedures for this test.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
mmiller
Starting Member
6 Posts |
Posted - 2010-03-29 : 09:21:48
|
| Thanks, Tara. I'll give that a try. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmiller
Starting Member
6 Posts |
Posted - 2010-03-31 : 15:30:17
|
| I've got this working between two 2005 servers, I think. I'm not a DBA (I just ended up with this task because it's a very small company, and I'm the only one who can even spell SQL), but as far as I can tell it works the way I need it to.I haven't yet had the chance to try it out with the actual setup, though, where the destination is SQL Server 2000 instead of 2005. If I'm lucky, setting up the 2000 subscriber the same way won't be too different, and I'll be able to muddle my way through it without too much more difficulty :)Thanks again for your help, Tara.Mike |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmiller
Starting Member
6 Posts |
Posted - 2010-04-05 : 17:58:02
|
| Well, I'm still fighting with this. I think I'm very close, but I've run into a problem that I can't figure out. I've created the publication on the 2005 system, and created a push subscription for the 2000 system. The problem is when I try to modify the stored procedures.This is the beginning of the SP for updates (table1 is the name of the source table on the SQL Server 2005 machine, and user is the SQL Server username for this application): ALTER procedure [user].[sp_MSupd_dbotable1] @c1 int = null ... as begin update "dbo"."table1" set I changed this line: update "dbo"."table1" set to update "dbo"."table2" set where table2 is the name of the destination table on the SQL Server 2000 machine. This seems to work. Making a similar change to the delete procedure also seems to work.However, I run into problems when trying to modify the SP for inserts. This is the first part of the procedure: ALTER procedure [user].[sp_MSins_dbotable1] @c1 int, ... as begin insert into "dbo"."table1"( When I try to change insert into "dbo"."table1"( to insert into "dbo"."table2"( I get the following error when trying to save the changed procedure: "The request for procedure 'table2' failed because 'table2' is a table object."I tried logging into the 2000 machine and using Enterprise Manager to change the procedures, with similar results. I was able to change the procedures for updates and deletes, but when I tried to change the insert procedure I got this message: Error 208: Invalid object name 'sp_MSins_dbotable1'.So far I haven't been able to find a resolution. Any insight on how to resolve this would be greatly appreciated.Thanks,Mike |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmiller
Starting Member
6 Posts |
Posted - 2010-04-06 : 15:35:52
|
| It seems to be working now. I tried three times yesterday, checked and rechecked for typos, and I kept getting the error, regardless of whether I tried to make the change on the publisher or the subscriber. I tried it again this morning and I swear I did exactly the same thing I was doing yesterday (all I did was change the name of the table on one line), but it didn't bark at me this time.Now I have another question. I wasn't able to create the subscription until I granted execute permission for my user on sp_MS_replication_installed (on the subscriber). I did that explicitly for this one procedure just to get it working, because I couldn't figure out what role I should assign to this user in order for it to have the permissions necessary for replication, but not allow it a bunch of permissions it doesn't need. The dbcreator role seems like it might be the closest to what I want, but it doesn't seem like it's really the right one. Should I be using one of the standard roles, or just leave it the way it is?Thanks,Mike |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmiller
Starting Member
6 Posts |
Posted - 2010-04-06 : 16:26:50
|
| OK, thanks. I really appreciate your help (and your patience with the noob:).Mike |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Need help with transformable subscription