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
The script I am trying to run is
The linked server objects to Alpha\Bear are identical on Beta\Snake and Beta\Monkey
The odd things are:
Does anyone have any ideas what I am missing please, is there a 2008 setting that I need to configure?
asked Apr 29, 2010 at 07:19 AM in Default
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
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:
I got around it by removing it all together but the proper way to resolve the situation is to alter it to
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
If it is running and client application is not on the same computer as the database server, on the computer running database server
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.
answered Apr 29, 2010 at 07:23 AM
Matt Whitfield ♦♦