Cant use linked servers in view

Question

I have a process on every server that collects meta data into a local database table and on one server I have a view that selects data across all of those tables so that I can compare/review results, it uses linked server objects. I am moving from 2005 to 2008 and am in the process of moving this view. For this example we have two hardware servers Alpha and Beta. Alpha has two SQL 2005 instances (Wolf and Bear), Beta has one SQL 2005 instance (Snake) and the new 2008 instance (Monkey). The view on Snake works fine but I cannot create it on Monkey. The CREATE script errors with

OLE DB provider "SQLNCLI" for linked server "Alpha\Bear" returned message "The partner transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2, Procedure cx_uvw_DatabaseSize_AllServers, Line 21 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "Alpha\Bear" was unable to begin a distributed transaction.

The script I am trying to run is

CREATE VIEW uv_DatabaseSizes
AS
SELECT  'Beta\Monkey' AS [server],
        ud.UsID,
        ud.Dbname,
        ud.SizeDb,
        ud.UnlockDB,
        ud.ScanDate,
        d.state_desc AS [StatusDesc]
FROM    [Beta\Monkey].CentralDB.SQLServerStats.USED_DISK AS ud
        INNER JOIN sys.databases AS d ON REPLACE(REPLACE(ud.Dbname, ']', ''),
                                                 '[', '') COLLATE SQL_Latin1_General_CP1_CI_AS = [d].[name]
UNION
SELECT  'Beta\Snake' AS [server],
        ud.UsID,
        ud.Dbname,
        ud.SizeDb,
        ud.UnlockDB,
        ud.ScanDate,
        d.state_desc AS [StatusDesc]
FROM    [Beta\Snake].CentralDB.SQLServerStats.USED_DISK AS ud
        INNER JOIN [Beta\Snake].centraldb.sys.databases AS d ON REPLACE(REPLACE(ud.Dbname, ']', ''),
                                                 '[', '') COLLATE SQL_Latin1_General_CP1_CI_AS = [d].[name]
UNION
SELECT  'Alpha\Bear',
        ud.UsID,
        ud.Dbname,
        ud.SizeDb,
        ud.UnlockDB,
        ud.ScanDate,
        d.state_desc
FROM    [Alpha\Bear].[CentralDB].[SQLServerStats].[USED_DISK] AS ud
        INNER JOIN [Alpha\Bear].centraldb.sys.databases AS d ON REPLACE(REPLACE(ud.Dbname, ']', ''), '[', '') = [d].[name]
GO

The linked server objects to Alpha\Bear are identical on Beta\Snake and Beta\Monkey

The odd things are:
- The individual SELECT in the view that references Alpha\Bear works with no issues when executed in isolation, outside the view
- There is no issue connecting to the 2005 instance on the same hardware (Beta) - Removing Alpha\Bear from the above view script allows it to be created and it works fine

Does anyone have any ideas what I am missing please, is there a 2008 setting that I need to configure?

Answer 1

Well, that's 2hrs of my life I wont get back.

This isnt an issue now. I copied the script into a new query window and it ran without issue. Is it possible some config settings are persisted with the connection and I have started a new one without previous restrictions?

OK, some research and a call in to the software developers that make SQL Compare (Hello to everyone at Red Gate) it turns out the issue is outside the issues put forward in my question.

I pasted the create script but didnt explain it was an extract from a script created by SQL Compare which I was using to transfer objects to my new server. SQL Compare creates a script that manages the create scripts and ensures that all changes are successful or get rolled back if any part of it fails. The output from the application is actually

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * 
FROM tempdb..sysobjects 
WHERE id=OBJECT_ID('tempdb..#tmpErrors')) 
DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating <viewname>'
GO
CREATE VIEW

now it turns out that when I copied (only) the CREATE section to a new window I was removing the line of code that caused the issue. The offending line is:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

I got around it by removing it all together but the proper way to resolve the situation is to alter it to

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Answer 2

A quick search reveals this page, which looks right - it's definitely an MSDTC issue...

From that page:

First verify the "Distribute Transaction Coordinator" Service is running on both database server computer and client computers

Go to "Administrative Tools > Services"

Turn on the "Distribute Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as the database server, on the computer running database server

Go to "Administrative Tools > Component Services"

On the left navigation tree, go to "Component Services > Computers > My Computer" (you may need to double click and wait as some nodes need time to expand)

Right click on "My Computer", select "Properties"

Select "MSDTC" tab

Click "Security Configuration"

Make sure you check "Network DTC Access", "Allow Remote Client", "Allow Inbound/Outbound", "Enable TIP" (Some option may not be necessary, have a try to get your configuration)

The service will restart

BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK (This is the thing drove me crazy before)

On your client computer use the same above procedure to open the "Security Configuration" setting, make sure you check "Network DTC Access", "Allow Inbound/Outbound" option, restart service and computer if necessary.

On you SQL server service manager, click "Service" dropdown, select "Distribute Transaction Coordinator", it should be also running on your server computer.

Cant use linked servers in view

Follow this question

Related Questions