Delete A Record From Two Separate SQL Data Sources

I have a slight issue with de开发者_运维问答leting a record that is stored on two separate SQL servers.

I am using an ASP.NET databound control on a datagrid with delete button commandfield linked to one data source.

Is there a way of deleting the same record from another data source at the same time?

Any advice would be much appreciated.

In MSSQLServer the correct way to do this, if you want to use the SQLDataSource, is to add the servers to each others' instances using the "Linked Servers" features.

Then generate a stored procedure which deletes whichever records required from both databases. Attach the stored procedure to your grid's delete method.

EDIT: I should have noted that this depends on your SQLServer Version. But the stored procedure, I think, is by far the most maintainable way to do this if supported.

Modify your code to use ObjectDataSource and run delete on both servers in distributed transaction.

Can you write a layer that either manages/inherits/facades to get the functionality you require?... looks like :Ladislav Mrnka beat me to it.

Depending on your specific version of SQL there may be a way to do this (I don't know of one, but it could exist). The most basic way using MSSQL from .NET code is to run your query twice against your two different SQL Connections (since you mentioned the data is on two different servers, not just different tables within a DB or even across two DBs on the same server).