What are the challenges you face when working across database platforms? Take the survey
Options

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

TobiasFreiTobiasFrei Posts: 4
edited September 30, 2014 4:31AM in SQL Compare Previous Versions
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.

Comments

  • Options
    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.
  • Options
    What is the effect if i change the code?
  • Options
    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.
  • Options
    Thank you very much for your explanations!
  • Options
    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.
Sign In or Register to comment.