x

Cant use linked servers in view

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?

more ▼

asked Apr 29, 2010 at 07:19 AM in Default

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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 
more ▼

answered Apr 29, 2010 at 07:24 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

MSDTC is a bit of a 'black box' - and I don't know enough about it's internals to say for definite. Grant may know?
Apr 29, 2010 at 07:25 AM Matt Whitfield ♦♦
Nope. Grant doesn't know squat about MSDTC. As long as it works I try not to look at it too closely.
Apr 29, 2010 at 08:19 AM Grant Fritchey ♦♦
@Grant - yup - that's pretty much my excuse! :)
Apr 29, 2010 at 11:24 AM Matt Whitfield ♦♦
Glad it is working for you. I have created a couple of views using linked servers without problem.
Apr 29, 2010 at 12:49 PM TimothyAWiseman
Thanks Timothy, I am researching this as there seems to be a connection with some settings I had in place. I'll update this when I get to the bottom of it.
Apr 29, 2010 at 03:51 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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

  1. Go to "Administrative Tools > Services"
  2. 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

  1. Go to "Administrative Tools > Component Services"
  2. 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)
  3. Right click on "My Computer", select "Properties"
  4. Select "MSDTC" tab
  5. Click "Security Configuration"
  6. 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)
  7. The service will restart
  8. 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.

more ▼

answered Apr 29, 2010 at 07:23 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Cheers Matt, I've been up and down that page so many times this morning!!! Also, just for kicks and giggles - what is the SQL server service manager (last paragraph)?
Apr 29, 2010 at 07:29 AM Fatherjack ♦♦
@Fatherjack - I think that's the 'SQL Server Services' node in 'Sql Server Configuration Manager' - it used to be a separate app (but that's 2000 and prior)...
Apr 29, 2010 at 07:49 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x107

asked: Apr 29, 2010 at 07:19 AM

Seen: 3164 times

Last Updated: Apr 29, 2010 at 07:19 AM