0

I'm trying to create a snapshot of a database that I will use for reporting. The requirements are to capture specific tables in the production database. Afterwards, I need to execute a stored procedure that will normalize a particular table column (also captured from the snapshot) that has XML data. I also need to keep track of that normalized data to the snapshot using a primary key.

I have no issues in parsing the XML, I do XQuery a lot. The challenge for me is to execute this in a scheduled task that runs on specific hours. So I'm reading through documentation about Transactional Replication. I hope I'm on the right track.

Another limitation is to support MS SQL 2005 databases, so I would need a solution that is backward compatible to that as well. Most of the solutions I've seen on the web requires a lot of physical tinkering in the management studio, perhaps, you can guide me how I could script that for automated deployment?

I hope you guys can guide me to the best solution. I can always use MERGE scripts but if there's a more standardized way, I'm up to it.

Martin Ongtangco
  • 22,657
  • 16
  • 58
  • 84

1 Answers1

0

I don't think transactional replication is the right tool.

I would create a scheduled SSIS package ( http://technet.microsoft.com/en-us/library/dd440761(v=sql.100).aspx ) that runs at the time you want.

Elements of that task may include copying the data, transforming it as appropriate, and executing your stored procedure (with an Execute SQL task).

podiluska
  • 50,950
  • 7
  • 98
  • 104