sql server replication: We are trying to create generic scripts to enable/configure replication at
various customer sites that purchase our product. As I have no way of
knowing which drive they will use for data and log file storage I would like
to set the @working_directory parameter for sp_adddistpublisher to null.
Will this cause the default UNC (\\<servername>\<drive letter>$\Program
Files\Microsoft SQL Server\MSSQL\ReplData) to be used?

Thanks,
Mark

Hi Mark, the SQL2005 version of sp_adddistpublisher will provide the
behavior that you want but unfortunately the change has not been (and will
unlikey be) backported to any versions of SQL2000. In SQL2005, we use the
following xp_instance_regread call to find out the root data folder path of
SQL Server so you may be able to do something similar:

EXECUTE @retcode = master.dbo.xp_instance_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLDataRoot',
@param = @working_directory OUTPUT,
@no_output = 'no_output'

Hope that helps.

-Raymond

[quoted text, click to view]

Raymond,

As it needs to be a UNC, I'm doing this? Look reasonable?

DECLARE @retcode INT
DECLARE @pubworkingdir SYSNAME
EXECUTE @retcode = master.dbo.xp_instance_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Replication',
'WorkingDirectory',
@pubworkingdir OUTPUT,
'no_output'

SET @pubworkingdir = '\\' + @@SERVERNAME + '\' + REPLACE(@pubworkingdir,
':', '$')

Thanks,

You may want to use serverproperty('MachineName') instead of @@servername to
get the "real" server name. Other than that, your code snippet looks fine to
me (you should, of course, test it extensively in your environment...)

[quoted text, click to view]