Can you use column aliases in Replication?

[Catadmin]

I'm working with Transactional Replication. I've got it all set up and working beautifally. Now the boss wants to know if we can alias some of the column headers in the Subscription database.

For example. A table called Participant has a column called Enroller_ID. We are replicating this column (and many others) to a second machine with row filters to pull out only one customer's data. The second machine is then backing up Transaction Logs which get sent over to the customer's machine. We don't want the customer to see the column name Enroller_ID as it won't make sense to them. We want them to see User2_ID.

But short of using a DTS job, I'm not sure how to accomplish this. And using DTS job is out of the question as circumstances require I do a delete of rows in the destination table, which get recorded in the Transaction Logs, which would delete rows in the customer's DB which is unacceptable.

Did I confuse you all yet? @=)

Thanks in advance.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[clapag22]

I'm not sure what your customers are doing with their data but one option would be to create a view of the table in question and then you can name each field whatever you wish. Then have the customers access the view.

Of course, if you're talking about some sort of front end app that accesses this table and has that table name hardcoded then this is really not an option. Unless you change the table name and rename the view to the name of the table.

Just a thought...

 

[Catadmin]

Clapag22, I appreciate the suggestion of creating the view, but since the data is going over to the customer's machine, they would get both the table and the view and would probably ignore the view.

BUT....

I cannot tell a lie. I also posted this over at MSDN.Microsoft.net and got a very interesting couple of answers. See what you think about this.

Option 1: Create an Indexed View which does NOT require the base tables sent over via replication, but does leave the burden of maintaining the Index View on the publisher. Of course, there are a ton of other requirements for using Indexed Views, which my data only fits half of, but it definately is an option for someone else in the future.

Option 2: Use an @creation_Script ??? - Not sure what he meant by this one.

Option 3: Replicate the table to another named table and have a view on the Subscriber with the exact same name as the table. (This one sounds like it could cause complications).

Option 4: Modify the Replication Stored Procedures to reflect the new names on the Subscriber. Use a pre-creation script (maybe to delete and recreate the tables on the Sub) containing the schema of these tables and the new names. Then use the "Delete existing data" in the table option in the snapshot tab of your table article properties dialog box.

Paul Ibison and Hilary Cotter were the two people at MSDN.Microsoft.com SQLServer:Replication newsgroup who were kind enough to answer this question for me. Strangely enough, neither of them recommended using the DTS with Replication option. Probably because I wasn't changing any data.

If anyone wants to look up this conversation, the title of the thread was "Can Columns be aliased in Transactional Replication" and my handle is the same there as it is here.




Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!