DISTRIBUTED TRANSACTION - errors and fixes

Open DISTRIBUTED TRANSACTION between servers

http://copypastenet.blogspot.co.il/2016/06/transaction-between-servers-distributed.html

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

http://copypastenet.blogspot.co.il/2016/06/ole-db-provider-sqlncli11-for-linked.html

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

http://copypastenet.blogspot.co.il/2016/06/the-microsoft-distributed-transaction.html

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

Try to do:

DISTRIBUTED TRANSACTION in try-catch block.

Error message:

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

Solution:

remove the try-catch block and see the exact error. then fix it - it probably some 'standard' SQL error.

but - if you want/need to see the errors:

run dynamic sql from the linked server to the current one:

http://copypastenet.blogspot.co.il/2016/06/run-dynamic-sql-from-linked-server-to.html

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

Try to do:

DISTRIBUTED TRANSACTION

Error message:

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

Solution:

In order to enable the distributed transactions set:

SET XACT_ABORT ON;

Run dynamic sql from the linked server to the current one

SET XACT_ABORT ON; -- In order to enable the distributed transactions

BEGIN DISTRIBUTED TRANSACTION

DECLARE @SQL nvarchar(max)

BEGIN TRY

       set @SQL = 'REVERT; EXECUTE AS LOGIN = ''SA''

              EXECUTE(''

       UPDATE / INSERT / .....

       FROM [LinkedServerDataBaseName].[LinkedServerSchemaName].[LinkedServerTableName] b

       JOIN [MyServerNameAsLinkedServerName].[DataBaseName].[SchemaName].[TableName] ns  ON b.BRN_ID = ns.BRN_ID

       WHERE .......

              '') AT [LinkedServerName]';

       exec sp_executesql @SQL

       COMMIT TRANSACTION

END TRY

BEGIN CATCH

       -- ROLLBACK TRANSACTION

       -- do something....

END CATCH

SET XACT_ABORT OFF;

LinkedServerDataBaseName - database name in the linked server

MyServerNameAsLinkedServerName - my server has to be declared as linked server in the other server, this is it's linked server name there.

LinkedServerName - the linked server name in my server

TRANSACTION between servers - DISTRIBUTED TRANSACTION

SET XACT_ABORT ON; -- In order to enable the distributed transactions

BEGIN DISTRIBUTED TRANSACTION

-- do something

ROLLBACK/COMMIT TRANSACTION

SET XACT_ABORT OFF;

Remote table-valued function calls are not allowed

Error message:

Remote table-valued function calls are not allowed

The reason:

When selects from remote tables (linked servers) - the table must be called with an alias.

--error:

SELECT ... FROM LinkedServer.DBNAME.dbo.Tablename

-- ok:

SELECT ... FROM LinkedServer.DBNAME.dbo.Tablename t

Explanation:

I don't know, and I also don't know if anyone knows.

Errors and optional solutions while querying LDAP

ERROR:

An error occurred while preparing the query ... for execution against OLE DB provider ADSDSOObject for linked server.

Optional Solutions/problems:

Add the server name to the LDAP address: LDAP://CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2 --> LDAP://SERVERNAME/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2.

ERROR:

The OLE DB provider "ADSDSOObject" for linked server "adsi_test" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Optional Solutions/problems:

Run the query with user that have relevant permissions in the LDAP.

ERROR:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "adsi_test".

Optional Solutions/problems:

Maybe the data from the LDAP is wrong.

ERROR:

An error occurred while preparing the query "

...........

" for execution against OLE DB provider "ADSDSOObject" for linked server "adsi_test". 

Optional Solutions/problems:

- Wrong syntax of the query.

- Maybe there is a problem to run the query from other machine that it's not the SQL Server.

Querying LDAP through SQL Server

In order to querying LDAP (Active Directory, LDS, etc.) through SQL Server, we need to create a Linked Server to the LDAP, and use OPENROWSET statement to select data from it.

-- create new linked server

exec sp_addlinkedserver  

  @server = 'adsi_test',-- linked server name

  @srvproduct = 'LDAP',  

  @provider ='ADSDSOObject',-- provider name

  @datasrc = ''  

go

-- create linked server login for sa

exec sp_addlinkedsrvlogin

  @rmtsrvname = 'adsi_test',-- linked server

  @useself = false,

  @locallogin = 'sa',-- sqlUser

  @rmtuser = 'domain\master_user',-- domainName\userName

  @rmtpassword = 'password'-- domain User Password

go

-- 2 options of query from LDAP:

--1:

select * FROM OPENQUERY(adsi_test, '<LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2>;(&(name=*));name,distinguishedName;subtree

')

--2:

select *  

from  openquery(adsi_test, '  

select  userPrincipalName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Person'' and objectClass = ''user''  

')

• CN is containers that are usually defined as users, contacts, groups.
• OU is organizational units that usually contain objects such users, contacts, groups and other OUs.
• DC is domain containers that are created by separating the full internal domain name.
• LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2 = LDAP:// SERVER NAME : port / Root of the LDS/AD tree
• (&(name=*)) = WHERE NAME = * (no filter by name)
• ;name,distinguishedName - fields to select from LDS
• ;subtree - include all the tree from the declared root

Examples:

-- select all groups:

select distinguishedName from  openquery(adsi_test, '  

select name, distinguishedName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Group''  

and  objectClass = ''Group''

')

-- select all users of group 'Managers' in AAA

select *

from  openquery(adsi_test, '  

select objectGUID, name, givenname, sn, distinguishedName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Person''  

and  objectClass = ''user'' 

AND memberOf = ''cn=Managers,CN=AAA,CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''

')

-- list of the groups that the user itaig is a member of: (the function 'member')

select * FROM OPENQUERY(adsi_test, 

'<LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2>;

(&(objectCategory=group)(member=CN=itaig,CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2));name,distinguishedName,objectGUID

')


Search Filter Syntax (msdn):
http://msdn.microsoft.com/en-us/library/Aa746475