Archive for the ‘SQL 11’ Category

Windows 2008R2 MSTDC Clustering how hard can it be. Linked SQL Servers

The DTC option is a simple feature just turn it on and it works eh yeh sometimes. but in advanced environments you need often some tweaks and how to’s

The following errors are common on misconfiguration on the SQL Cluster / MSDTC source

MSDTC encountered an error (HR=0x80000171)  Or if you are using linked SQL servers If the MSDTC is not working most of them solve the error with code, actually it is a misconfiguration of the MSDTC How to solve this error ?

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session.".

Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SQL instance name". A nested transaction was required because the XACT_ABORT option was set to OFF.

Or error 7391

Well here is a little tip on how to solve this.


OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session 

Typical MSDTC cluster resource , But in the resource kit there is a little tool that is named SUBINACL

SubInACL is a command-line tool that enables administrators to obtain security information about files, registry keys, and services, and transfer this information from user to user, from local or global group to group, and from domain to domain. For example, if a user has moved from one domain (DomainA) to another (DomainB), the administrator can replace DomainA\User with DomainB\User in the security information for the user’s files. This gives the user access to the same files from the new domain.
SubInACL enables administrators to do the following:

  • Display security information associated with files, registry keys, or services. This information includes owner, group, permission access control list (ACL), discretionary ACL (DACL), and system ACL (SACL).
  • Change the owner of an object.
  • Replace the security information for one identifier (account, group, well-known security identifier (SID)) with that of another identifier.
  • Migrate security information about objects. This is useful if you have reorganized a network’s domains and need to migrate the security information for files from one domain to another.

This update addresses the following issues:

  • Fixed bug where subinacl.exe failed to process command line arguments
  • Fixed bug where subinacl.exe failed to function correctly with cluster file shares


So now that we have the tool let us use this filling in the following command

subinacl /service msdtc /grand=”network services”=qsetil

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session 

Access denied ! , I am an Admin the CMD is a Admin why the syntax is correct .

Yes all above is ok except the MSDTC part it is a clusterd so you need to use the cluster part.

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session

Ok and now with the Clustered DTC Winking smile 

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session

Ok done now the error below is gone.

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session.".

Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SQL instance name". A nested transaction was required because the XACT_ABORT option was set to OFF.

Msg 7395, Level 16, State 2, Line 3

But what about the XACT_ABORT option was set to OFF and the error 7391 ?

Yes easy to solve turn it on.

  • Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers that are involved in the distributed transaction.
  • Issue this statement before you run your query:

    The XACT_ABORT option must be set to ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. This option is not required if the provider supports nested transactions.

  • Check whether any of the servers are on a cluster. The DTC on the cluster must have its own IP address. You must verify proper name resolution of the DTC service on each server. The IP address of the DTC must be defined in your name resolution system (such as WINS, DNS or LMHosts). Verify that each server can communicate with the other’s MSDTC by name, not just by IP address. Check in both directions. For example, check from server A to server B’s MSDTC, and then check from server B to server A’s MSDTC. You must resolve all name resolution problems on the network before you run your distributed query.


Below is a little example of how to test your transactions just change your servername [SQL instance name].



UPDATE [SQL instance name].test_DTC.dbo.Table1

SET Field1 = ‘MSDTC is a pain if it is not working but now it is working like a charm’

Where Field1 = ‘Second';




Select * from [SQL instance name].test_DTC.dbo.Table1


OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session

Play with the new SQL denali Server Current version CTP3

Today we are excited to announce the release of SQL Server Code Name “Denali” Community Technology Preview 3 (CTP3). This CTP release represents a significant milestone for customers and partners looking to experience the extensive new value delivered in Denali.

SQL Server Code Name “Denali” CTP3 brings a huge number of new capabilities for customers to explore and test. Product highlights for this CTP include:

  • Greater availability. Deliver the required 9s and data protection you need with AlwaysOn which delivers added functionality over CTP1.
  • Blazing-fast performance. Get unprecedented performance gains with Project “Apollo,” a new column store index that offers 10-100x star join or similar query performance improvements available for the first customer preview.
    Breakthrough insight. Unlock new insights with rapid data exploration across your organization with Project “Crescent,” available to customers for the first time. Get your hands on the data exploration and visualization tool that everyone is talking about and enables end users to explore data at the speed of thought.

  • Credible, consistent data. Provide a consistent view across heterogeneous data sources with the BI Semantic Model (BISM) a single model for Business Intelligence applications, from reporting and analysis to dashboards and scorecards. Make data quality a part of everyday life with the Master Data Services add-in for Excel and new Data Quality Services that is integrated with third party data providers through Windows Azure Marketplace. Customers can test this functionality for the first time and put data management and cleansing tools in the hands of those who need it. Available integrated Marketplace providers include:
    • Cdyne: CDYNE Phone Verification will validate the first 7 digits of your phone number(s) and return what carrier the phone number id is assigned to, whether it is a cellular number or a land line, the telco, and additional information including time zone, area code and email address if it is a cellular number
    • Digital Trowel: Powerlinx allows users to send data for refinement and enhancement and receive back cleansed and enriched data. The database contains 10 million company website addresses, 25 million detailed company profiles, and 25 million executives, including 5 million in-depth profiles with email addresses and phone numbers.
    • Loqate: The Loqate Verify enables users to parse, standardize, verify, cleanse, transliterate, and format address data for 240+ world countries.
    • Loqate: The Geocode enables a latitude-longitude coordinate to be added to any world address with worldwide coverage to city or postal code for over 120 countries
    • Melissa Data: WebSmart Address Check parses, standardizes, corrects and enriches U.S. and Canadian addresses to increase deliverables, reduce wasted postage and printing, and enhance response
  • Productive development experience. Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Developer Tools code name “Juneau” for a unified and modern development experience across database, BI, and cloud functions. Additionally, Express customers can test a new LocalDB version for fast, zero-configuration installation.

Get it Here :

Posted July 13, 2011 by Robert Smit in Denali, SQL 11, SQL Denali

What can go Wrong during the SQL 11 upgrade ( Beta SQL 11 Denali )

Now that there is a beta of SQL 11 ( denali ) I want to see if I can break my Cluster with SQL 2008 on it during the upgrade. Yes I can.


These are testing machines ( snapshot of production ) and the screenshot are showing you what can go wrong. It was hard to make a blog post about this there was so much to fix and to do and to explane how to solve things.  At the end I deside to create a fresh cluster ;-(



I use a 2 node cluster windows 2008r2 with SQL 2008 on it 2 named sql instances.image

Lesson 1 be prepared and no rush during de upgrade.

Lesson 2 Make sure your current SQL cluster is fine if not do not upgrade !

In my post and I guessed it would be fun to do this with SQL Denali.

Well It is fun but the things I want to fix and to make sure the I can fix it and write a blog about this was not easy.

Well this blog is holding a lot of screen shots to see what and how the installation can be done.

So my 2 node cluster that I used for the SQL 2008 what can go wrong is up and running and ready for the beta sql 11.

I downloaded the SQL denali beta mounted the ISO in my hyper-V and started the splash.

But first let me test this on a brand new cluster and see how things are going.

Except I use the Same IP for the SQL instance ( typo on perpes )

clip_image002 clip_image004 clip_image006 clip_image008 

Ok I needed a reboot package after the reboot I’m ready to go


Fill in your key or leave it this way ( give your key later )

clip_image022 clip_image024

clip_image026 clip_image028

Give the file location and name the instance

clip_image030 clip_image032


clip_image036 clip_image038

I give the disk and the IP number ( make sure it is a free IP )

clip_image040 clip_image042 

Put in the service account

clip_image044 clip_image046

Some new screens

clip_image048 clip_image050

clip_image052  Well All set and ready to go But during the install there was this error, What is it and why ?

clip_image054  clip_image058

Well It seams that I used a Ip adress that was not Free but why this error and why not installing and leave the resource off line. So what is the status of the install.

Well there is no cluster good that this was not the production SQL so lets remove it an reinstall

clip_image056 clip_image062

clip_image064 clip_image066

Can’t find the SQL , yes the setup did not install it but why ?

clip_image068 clip_image070 clip_image072

clip_image074 But on more error messages to go there is no clean fix and this is just for not putting in a right Ip adress. next step clean out the register and delete the files, and rerun the setup again.



After playing with denali and upgrading , deinstall , install, fresh install , Win2008R2 Sp1

If you are beta testing denali on a cluster do not use the setup for cluster node install but choose the prepare for cluster install and after that use the finish cluster install. this works fine and fast.

Both my test clusters are broken during the upgrade It is all my fault I did this and not all the errors are SQL denali problems. but the main problem is make sure you have a plan and if something goes wrong make sure you can fixit or do a rollback.

In my case the only thing I did was a taken IP address and running an instance that is clustered and running a non clustered instance on both nodes.  seems not logical at all. But have you seen crappy sql clusters ? Or are all your Clusters in excellent shape ?

dumb questions do not exist, but have you ever worked on a helpdesk ?



Windows Live Tags: Wrong,Beta,Denali,Cluster,node,Lesson,Make,installation,Except,Same,instance,typo,Fill,Give,location,disk,account,Some,error,Free,resource,status,production,setup,messages,files,Both,problems,instances,seams,errors,blog,reboot
WordPress Tags: Wrong,Beta,Denali,Cluster,node,Lesson,Make,installation,Except,Same,instance,typo,Fill,Give,location,disk,account,Some,error,Free,resource,status,production,setup,messages,files,Both,problems,instances,seams,errors,blog,reboot

  • Tag