sql server merge replication - how to find out where the snapshot folder is what are the permissions for that folder

Question

I am looking for the location of the snapshot folder for my merge replication

I have tried:

select top 5 * FROM sysmergepublications pub
select *       FROM dbo.sysmergeSubscriptions

but that does not show me the folder name and how needs permissions on it

where can I get this information from using t-sql?

Answer 1

one way if to find that info is to query the msdb, but that doesnt always work, but it works when the distributor is in the same server as the publisher

select *  FROM msdb.dbo.MSdistpublishers  

sp_helpdistributor sp_changepublication - not sure if this would work for merge though

this is my favourite way:

declare @snapshot_folder   nvarchar(255)
exec sp_helpdistributor @directory=@snapshot_folder output
select [@snapshot_folder]=@snapshot_folder

most often than not you will need to know the account that will need permissions on the snapshot folder, for that I add an extra parameter and that gives me that account (in this particular case it is the sql agent account):

declare @snapshot_folder   nvarchar(255)
declare @account     nvarchar(255)
exec sp_helpdistributor @directory=@snapshot_folder output, @account =@account  output
select [@snapshot_folder]=@snapshot_folder,[@account ]=@account