0

I have enabled my repl_distributor linked server for data access in both publisher_server and distributor_server.

Both servers are also configured to allow remote connections

On both servers I have the same output and error messages:

Server 'repl_distributor' is not configured for DATA ACCESS.

USE [master]
GO
EXEC sp_configure 'remote access', 1; -- UI checkbox checked
reconfigure

EXEC master.dbo.sp_serveroption @server=N'repl_distributor', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_checkLinkedServer @server_name=N'repl_distributor'

enter image description here

 SELECT 
   name,
   is_data_access_enabled 
 FROM sys.servers
 where name = 'repl_distributor'

enter image description here

I have no problems with the replication I just wanted to use this repl_distributor as a linked server in a query.

The question is:

Is there a way to enable repl_distributor as a good linked server?


the query where I need to use a linked server to the distributor server is obviously when the distributor_server is a different server to the publisher_server.

I want to find the name of the distributor job.

A - when the distribution database is on the same server - no problem:

   use publisher_database
   go

select s.srvid
      ,s.artid
      ,[is article marked for reinitialization]= CASE WHEN s.queued_reinit = 1 THEN 'Yes' ELSE 'No ' END
      ,s.dest_db
      ,s.status
      ,s.distribution_jobid
      ,[distribution job name]=j.name
      ,[is job enabled]= CASE WHEN j.enabled = 1 THEN 'Yes' ELSE 'No ' END
      ,[Subscription Server Name]=s.srvname
from syssubscriptions s 
INNER JOIN msdb.dbo.sysjobs j
        ON job_id = s.distribution_jobid

enter image description here

B. when instead the distribution database is on the distributor server then I need to use a linked server:

   use publisher_database
   go
select s.srvid
      ,s.artid
      ,[is article marked for reinitialization]= CASE WHEN s.queued_reinit = 1 THEN 'Yes' ELSE 'No ' END
      ,s.dest_db
      ,s.status
      ,s.distribution_jobid
      ,[distribution job name]=j.name
      ,[is job enabled]= CASE WHEN j.enabled = 1 THEN 'Yes' ELSE 'No ' END
      ,[Subscription Server Name]=s.srvname
from syssubscriptions s 
INNER JOIN [my distributor server].msdb.dbo.sysjobs j
        ON job_id = s.distribution_jobid

enter image description here

3
  • yes, both in the distributor and the publisher Sep 8, 2022 at 19:08
  • 1
    Sorry I misread your first sentence. I thought you said Publisher and Subscriber.
    – J.D.
    Sep 8, 2022 at 19:09
  • 1
    no worries, it is indeed a very very specific point, I would probably find an alternative instead of fixing it, but I will leave it for tomorrow. Sep 8, 2022 at 19:22

1 Answer 1

1

try to use this to check first

 SELECT 
   name,
   is_data_access_enabled 
 FROM sys.servers;

then if value is 0 run:

 EXEC sp_serveroption
   @server = 'repl_distributor',
   @optname = 'DATA ACCESS',
   @optvalue = 'TRUE';
1
  • that's very good Luca. welcome to dba.stackexchange.com. and what would you do if it would show yes is_data_access_enabled is 1 but when you try to use the linked server is still complains? have a look again at the question. thanks for answering though. Sep 9, 2022 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.