Updating SAS metadata server programatically with new stored process locations.

[Sajid01]

Hello
We are in the process of migrating SAS from windows server 2008 to WIndows server 2016.
The metadata was migrated.
However the path for store process source code on the new servers is different than in the old server.
The metadata has the old locations.
Using the code here (https://blogs.sas.com/content/sastraining/2012/06/11/locating_storedprocess_code/ method 3) obtained the stored process details.
Now want to update the  metadata with new source code locations using sas code.
Is there a way to do it?

[MichaelLarsen · Accepted solution]

Use the attached macro and the program below.

You need to add the old locations to the data step that contains the SELECT-WHEN statements.

Please make a backup of your metadata before running the program.

/* Extract information about Stored Processes in Metadata */
%meta_extractstoredprocesses(Table=StoredProcesses);

/* Extract information about Directories. A directory can be used by multiple STP's only select one unique. */
proc sql noprint;
  create table locations as
    select distinct
      dir_uri,
      SourceCodeDir
    from StoredProcesses 
    where SourceCodeDir ne '<In Metadata>'
  ;
quit;

/* Update the directories to new locations */
data newlocations;
  set locations;
  UpdateDirectory = 0;
  select (upcase(SourceCodeDir));
    /* Only for testing, do not use in production */
    when ('C:\PROGRAM FILES\SASHOME\SASFOUNDATION\9.4\INTTECH\SAMPLE2') 
      do;
        SourceCodeDir = 'C:\Program Files\SASHome\SASFoundation\9.4\inttech\sample';
        UpdateDirectory = 1;
      end;
    otherwise ;
  end;
  if UpdateDirectory then do;
    putlog 'Updating Directory';
    rc=metadata_getnobj(dir_uri,1,tran_uri);
    put rc=;
    rc=metadata_setattr(dir_uri,"DirectoryName",SourcecodeDir);
    put rc=;
  end;
run;

/* Extract information about Stored Processes in Metadata and verify the Directories were updated. */
%meta_extractstoredprocesses(Table=StoredProcesses);