proc metalib – ERROR: Invalid use of sign notation

by | Sep 22, 2016 | ERROR: Invalid use of sign notation. | 2 comments

proc metalib – ERROR: Invalid use of sign notation

So today I dropped a column in a SQL Server table (using SSMS) and duly checked out the SAS metadata in DI Studio in order to ‘Update Metadata’.

It failed with a strange message:

ERROR: Invalid use of sign notation.

Curious, I extracted the code and ran it in Enterprise Guide (the repository ID would ensure it ran against my checkout).  Same result, so following the advice in usage note 40429 I ran with maximum logging (for 9.3) as follows:

options sastrace=‘,,,d’sastraceloc=saslog nostsuffix;
proc metalib tl=4095; /* max is 4094 for 9.2 */
  omr (libid=“B900004R” repid=“A5HOSDWY”);
  report(type = summary);
  update_rule = (noadd);
  select (A5GF9HGW.AD00016K);
run;

The last operation before my SAS error was:

(MSCMLB) getREPindices: Enter
(MSCMLB) getREPindices: Index object name is pk_rm_objects
(MSCMLB) getREPindices: Index metaid is A5GF9HGW.AI00015K
(MSCMLB) getREPindices: IndexName is pk_rm_objects
(MSCMLB) getREPindices: Exit;  Return Code is -2147475442

This indicated an issue with the index.  I tried various ways of redefining the index in SQL Server (from clustered to non-clustered, uppercase name to lowercase etc) to no avail.  In the end, the solution was simply to right click the metadata item in DI Studio, Properties, selecting the Index tab.  At this point I got a popup (similar to below) which mentioned the index needing to be marked as unique, and also about re-ordering columns.  I clicked yes, and – voila – I was then able to update the metadata!

 

2 Comments

  1. sathya
    sathya on October 25, 2016 at 10:01 am

    It's like you read my mind! You seem to know a lot about this, like you wrote the book in it or something. I think that you can do with some pics to drive the message home a little bit, but instead of that, this is fantastic blog. A great read. I will definitely be back.

  2. Allan Bowe
    Allan Bowe on October 25, 2016 at 11:58 am

    Thanks Sathya! I think it would be a very short book 🙂