July 8, 2010 at 4:19 am
Hi,
Hope this is the right place to post.
I was wondering what the performance/limitations of using TransactionScope in .NET to call stored procedures that contain OPENQUERY statements is. 99% of our OPENQUERY statements are 'SELECT's. We basically have another system from another vendor and we bring data from that system into ours. I am in the process of building a data access layer for this, but am worried about the performance and if TransactionScope will work over OPENQUERY/linked servers.
Any advice would be great.
July 8, 2010 at 4:45 am
Openquery and link server is not the way forward; I wouldn’t advice for you to proceed with the current approach in transforming data. I would recommend you to do a data transformation service (using ssis ) to extract the data from the venders data source to your data source.
July 8, 2010 at 5:15 am
Thats more the approach I would like, but some people in my team require "up to date" information. Its not in all our stored procedures, only a few.
July 8, 2010 at 7:00 am
Ok; if you have no options I’d say stage the data in your data source before inserting it directly from the vendors data source. This will help not to lock your transactions tables for too long.
I’m not sure if transactions (BEGIN TRAN ….. COMMIT TRANS ) can be maintained on the application side in this scenario , but be sure not to have maintain it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply