We have 2 databases, for example DB1 and DB2.
DB1 contains all stored procedures that also access data in DB2.
DB1 uses synonyms for accessing tables in DB2.
(Using synonyms is a requirement in our situation)
This works great in all situations with SQL Server 2005 Developer Edition.
However, in Express Edition we get an exception when we do the following:
1 Restart SQL Server
2 Run the following code in DB1:
set snapshot of transaction isolation level
start transaction
declare @sQuery varchar (max)
set @sQuery = 'Select * from synToSomeTableInDB2'
exec (@sQuery)
complete the transaction
This will result in the following error:
The snapshot isolation transaction in database "..." could not be completed because the database was not restored when the current transaction was started. Retry the transaction after restoring the database.
The same select query goes fine when used without EXEC or when launched in Developer Edition.
Restarting the server in step 1 is important because, once the connection has been made to DB2, the code also works fine in SQL Server Express Edition.
- , ? EXEC .
MSDN, Google,...
.
--- : 10 09
, .
. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=422150