First a little about our environment.
We have 4 servers (think Federated data).
Server 1 is the traffic cop and contains a database with all of the information used by the applications telling them where to get data (which server and which databases on that server).
Server 2 is our ETL server. We import raw files (from XML, .csv, dbf, etc) from our clients into "scratch DBs" prep the data and then insert the data into either Server 3 or Server 4 (based on the information in Server 1) into a Detail database. We then run a stored proc to aggregate the data and insert it into a "Reporting" database. There is a Windows NT Service (written in .NET) that does the import, executes the SQL to prep the data and then inserts the data into Detail Database. Then executes a stored proc that moves the data to the Reporting DB.
Servers 3 and 4 are the main data storage servers and contain a series of Detail and Reporting Databases.
Here is the problem. We did a new release to the .NET service last week. And everything works perfectly on Server 3, however Server 4 throws the following error when ever we run the stored proc that moves data from Detail to Reporting on Server 4:
Error Number: 7420
Error Provider: .Net SqlClient Data Provider
Remote access is not supported for transaction isolation level "SNAPSHOT".
The Stored Proc in question has a Transaction built into it (and yes we use Snapshot Transactions) and it calls 10 other procs. If everything works, the transactions is committed.
Now here is where this gets interesting.....I can run the stored proc without error in Management Studio, we have another application we wrote that can run the stored proc in question, but when our primary application calls it, we get this error.
Any ideas\suggestions on what this might be? What is frustrating is both servers are setup identically, but it all works fine on one and fails on the other.
We have 4 servers (think Federated data).
Server 1 is the traffic cop and contains a database with all of the information used by the applications telling them where to get data (which server and which databases on that server).
Server 2 is our ETL server. We import raw files (from XML, .csv, dbf, etc) from our clients into "scratch DBs" prep the data and then insert the data into either Server 3 or Server 4 (based on the information in Server 1) into a Detail database. We then run a stored proc to aggregate the data and insert it into a "Reporting" database. There is a Windows NT Service (written in .NET) that does the import, executes the SQL to prep the data and then inserts the data into Detail Database. Then executes a stored proc that moves the data to the Reporting DB.
Servers 3 and 4 are the main data storage servers and contain a series of Detail and Reporting Databases.
Here is the problem. We did a new release to the .NET service last week. And everything works perfectly on Server 3, however Server 4 throws the following error when ever we run the stored proc that moves data from Detail to Reporting on Server 4:
Error Number: 7420
Error Provider: .Net SqlClient Data Provider
Remote access is not supported for transaction isolation level "SNAPSHOT".
The Stored Proc in question has a Transaction built into it (and yes we use Snapshot Transactions) and it calls 10 other procs. If everything works, the transactions is committed.
Now here is where this gets interesting.....I can run the stored proc without error in Management Studio, we have another application we wrote that can run the stored proc in question, but when our primary application calls it, we get this error.
Any ideas\suggestions on what this might be? What is frustrating is both servers are setup identically, but it all works fine on one and fails on the other.
