October 29, 2014 at 1:24 am
Hi,
I am running the below query, which joins multiple tables from different servers:
select job.Name
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity
on (job.job_id = activity.job_id)
where run_Requested_date is not null and stop_execution_date is null
and job.name like 'UGC Restores%'
union all
select job.Name
from RO.msdb.dbo.sysjobs_view job
inner join RO.msdb.dbo.sysjobactivity activity
on (job.job_id = activity.job_id)
where run_Requested_date is not null and stop_execution_date is null
and job.name like 'RO Restores%'
union all
select job.Name
from ODS.msdb.dbo.sysjobs_view job
inner join ODS.msdb.dbo.sysjobactivity activity
on (job.job_id = activity.job_id)
where run_Requested_date is not null and stop_execution_date is null
and job.name like 'ODS Restores%'
I am getting this error message:
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
If I enclose this query into:
begin distributed transaction
...
commit transaction
Everything runs fine.
However other queries start throwing this error message when I do this:
Cannot use SAVE TRANSACTION within a distributed transaction.
How do I run the distributed queries without:
begin distributed transaction
...
commit transaction
I am using SQL Server 2014 Ent edition.
Thanks.
October 29, 2014 at 2:31 am
Why do you need to wrap that query in a distributed transaction?
Have you checked to see what error DTC is actually throwing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2014 at 3:11 am
It doesn't sound like it's 2014. It sounds like your linked servers and your DTC are not set up correctly. It's also possible it's a security issue. I'd suggest double checking each configuration across the chain to ensure you're making the connections properly with appropriate permissions.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 29, 2014 at 7:02 pm
GilaMonster (10/29/2014)
Why do you need to wrap that query in a distributed transaction?Have you checked to see what error DTC is actually throwing?
I don't, this was just for the test. In fact I also don't want to, as this breaks my other queries.
Where do I check the error?
October 29, 2014 at 7:07 pm
Grant Fritchey (10/29/2014)
It doesn't sound like it's 2014. It sounds like your linked servers and your DTC are not set up correctly. It's also possible it's a security issue. I'd suggest double checking each configuration across the chain to ensure you're making the connections properly with appropriate permissions.
I did in place upgrade from 2008R2 to 2014 and those queries worked on 2008R2.
I've enabled MS DTC network access. I need to find out why I am getting this error:
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
October 29, 2014 at 8:20 pm
I've also found this in the system event log:
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{806835AE-FD04-4870-A1E8-D65535358293}
and APPID
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}
to the user MyDomain\MyLogin SID (S-1-5-21-1688516961-3049443350-111816674-1126) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.
I managed to find the app with id {EE4171E6-C37E-4D04-AF4C-8617BC7D4914}, which was Microsoft SQL Server Integration Services 12.0
As for {806835AE-FD04-4870-A1E8-D65535358293}, I can't find it in Component services, but looking through the registry it seems like it relates to Microsoft.SqlServer.Dts.Server.DtsServer, but I can't find this app name in component services.
Not sure if this is relevant, though.
October 29, 2014 at 8:44 pm
I've also checked the linked server code (below), which had @provider=N'SQLNCLI10' in the definition. I've changed it to @provider=N'SQLNCLI11', same error. Changed it to @provider=N'SQLNCLI12' (sql server 2014), and got a new error:
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI12" has not been registered.
EXEC master.dbo.sp_addlinkedserver @server = N'RO', @srvproduct=N'SQLServer', @provider=N'SQLNCLI10', @datasrc=N'MyServer1', @catalog=N'master'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RO',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'RO', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
October 26, 2015 at 11:34 am
What kind of service account are your services running under? Are you using Domain accounts or Network Service accounts? If you are using Network/machine accounts, try using a Domain account instead.
Kindest Regards,
Clayton
October 27, 2015 at 1:39 am
The error occurs due to following reasons: -
[font="Times New Roman"]The OLEDB provider is not yet installed.
The OLEDB provider is not registered.
[/font]
Review the list of linked servers defined on the SQL Server, and find out the OLE DB provider associated with it. Then install the OLE DB provider using the software from the corresponding vendor.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply