OLE DB provider "SQLNCLI" for linked server ("null") returne

TobiasFrei

Hello,
i have problems using sql-compare...

i tried to auto-build a script against a database with views, which have openrowset commands. Result is the following error:


OLE DB provider "SQLNCLI" for linked server ("null") returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure sp_refreshview, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server ("null") was unable to begin a distributed transaction.


This is the part of the generated script which is relevant. If i run the script without the "set transaction isolation level serializable", everything works fine.

What could be the solution for this puzzle?

Thanks in advance.

Tobias

---

code begin

---

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
EXEC sp_refreshview N'[dbo].[view_blabla]'
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
go

---

code end

---

view_blabla uses an openrowset sql command.

chriskelly

If you have distributed queries in your database that reference other ODBC drivers (such as the ADS provider), and if these do not support transaction isolation level serializable, then you will need to continue to do what you have been doing (namely deleting the "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" line) or you can save the generated script to disk, and change the line 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' to another isolation level, such as READ COMMITTED, and then execute the script.

TobiasFrei

What is the effect if i change the code?

chriskelly

Read Committed is the default isolation level of SQL Server. Assuming that you have distributed queries that do reference other ODBC drivers then this should allow the script to run correctly.

The isolation level will still prevent dirty reads and other concurrency problems but it is not quite as high as Serializable.

This page from msdn describes the different isolation levels that are available:
http://msdn.microsoft.com/en-us/library ... 80%29.aspx

In your original post you stated that you were effectively making this change as you were deleting the line in question and therefore allowing sql server to use the default level of Read Committed.

TobiasFrei

Thank you very much for your explanations!

memcache

Guys,

After much digging around, our DBAs pencilled it down to permissions on a specific object which you need to grant the user permission on if you are to use the SERIALIZABLE isolation level.

The EXECUTE permission was denied on the object 'sp_table_statistics2_rowset', database 'mssqlsystemresource', schema 'sys'.

Regards,

Kwex.