Use ActivePos_server

BEGIN TRANSACTION 

DECLARE @ou_id INT 
EXEC Arizona.dbo.sp_bmc_Bmc_Applic_Default 
                    @in_job_type = 3,
                    @in_param_int_1 = null, /* Company */
                    @in_param_int_2 = null, /* Subsidiary */
                    @in_param_varchar_1 = 'cvCurrentOrganizationalUnit',
                    @out_default_value = @ou_id output,
                    @out_param_int_1 = NULL

DECLARE @newVal NVARCHAR(500);


/*Save the current value in [HCITools].[tmp].[TT_medifilm_setting]*/
IF OBJECT_ID('[HCITools].[tmp].[TT_medifilm_setting]') IS NULL 
BEGIN
    CREATE TABLE [HCITools].[tmp].[TT_medifilm_setting](
        oldValue VARCHAR(MAX) NULL, 
        dateChange DATETIME2(2) NOT NULL CONSTRAINT df_dateChange  DEFAULT GETDATE()
    )
END 

/*dynamic sql to avoid exception because of non existing table while compiling the batch*/
EXEC('
INSERT INTO [HCITools].[tmp].[TT_medifilm_setting]([oldValue])
SELECT [SE].[SettingValue]
FROM [dbo].[Settings] SE
where [SE].[SettingId] = ''Values.Global.SalesImportService.MainDirectory''
')

/*Craft the new URI from the OU ofthe pharmacy*/
SELECT @newVal = REPLACE('\\centralinfra.net\GAL\90_UserData\@ou@\POS\Medifilm', '@ou@', RTRIM(i.[Customer])+LTRIM(o.[OU_code]))
FROM [Arizona].dbo.[Organizational_unit] [o]
    CROSS JOIN [master].cfg.[Identity] [i]
WHERE [o].[Organizational_unit_ID] = @ou_id

/*Update the setting*/
UPDATE se SET [SE].[SettingValue]= @newVal
FROM [dbo].[Settings] SE (nolock)
where [SE].[SettingId] = 'Values.Global.SalesImportService.MainDirectory'

/*restart the service to apply the change*/
EXEC xp_cmdshell 'net stop arizonaServerService'
EXEC xp_cmdshell 'net start arizonaServerService'

--COMMIT TRANSACTION 
ROLLBACK TRANSACTION