Command rule not working as expected

I've created several command rules which have worked and working as expected.  Despite some strange issues with semi colons if I remember correctly.

Now I've put in place a quite complex command rule which works when I run in SSMS against the target database.  When I run the rule using DataMasker it does not delete records as I would expect.  Any ideas what I'm doing wrong???
Tagged:

Best Answer

  • BobFBobF Posts: 23 Bronze 1
    Ok found the issue.  There is a rule which Truncates data in a processing table, then repopulates the table with data.  All the tasks use the data in that processing table.  When I added some new command rules they were not dependant on the first step completing.  So they ran the scripts against the processing table which had Zero records.  The deletion scripts ran successfully as they use the records in the processing table to identify the records to delete.  

    Yes Chris Unwin has done a nice video on setting up dependency in Redgate University.  Which I will need to study some more, to see if I can set up the dependency as suggested.

    Today was another learning opportunity which hopefully I have gained some knowledge from. 

Answers

  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @BobF,

    Are you using "Script mode" or "GO block mode" for the command rule? See this page for more information on this. This may be why things are not running as you expect.

    If that doesn't seem to be the case are you getting an error or is it just not deleting things as you expect?

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • BobFBobF Posts: 23 Bronze 1
    Hi @Alex B thanks for the reply.

    Most of the Command Rules that I have created till now have been Script mode (I think). That is to say on the 'Options' tab select
    "Check for errors in every...." line, ending with a semicolon?

    The only significant change that I have made in my approach till now is to use a CTE to populate a temp table.  I'm going to do some testing again this afternoon (hopefully) to see if I can reproduce the results I saw yesterday.
  • BobFBobF Posts: 23 Bronze 1
    Ok after some testing I was able to reproduce the error  :/ 

    I have two commands, one check's for table existence, the next one creates a temp table.  Then semicolonon and then a CTE.  With the semicolon at the start of a the CTE statement is the issue.   At least that's my assumption.  I am going to re-write the script and see if it I can get it to work 
  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @BobF,

    Not a problem at all!  Do let us know if you run into any other issues in the future!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.