Delete A Record From Two Separate SQL Data Sources

Question

I have a slight issue with deleting 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.

Answer 1

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

Best regards, Ladislav

Answer 2

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

Answer 3

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.

Answer 4

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).