7 Replies Latest reply on Sep 4, 2019 3:20 PM by Brian McKenzie

    Dynamic SQL Mapping Disappears On Creation

    Damon Stennett
      Share This:

      Hi there,

       

      We've been having some trouble with our Dynamic SQL Mappings ever since the password for the account used to access the database was changed. As part of my troubleshooting I deleted one of the mappings after recording all the settings, then went to recreate it. Everything works fine up to and including the final step when you click on Save in the main Dynamic SQL Mapper window where it shows you all your mappings. When you click save it takes you back to the Workspace Administration menu page. I click on Dynamic SQL Field Mapper to double check that my mapping is A-OK and find it is not there.

       

      Any thoughts?

       

      Cheers,
      Damon

        • 1. Re: Dynamic SQL Mapping Disappears On Creation
          Sridhar Sadhasivam

          Can you check the footprints.log and see if there are any errors/exceptions reported when you saved the new settings?

          • 2. Re: Dynamic SQL Mapping Disappears On Creation
            Damon Stennett

            Hi Sridhar,

             

            Nothing showing in the Logs.

             

            Thanks,

            Damon

            • 3. Re: Dynamic SQL Mapping Disappears On Creation
              Damon Stennett

              Also,

               

              I tried changing the account used in one of the existing mappings and even though it accepted the new account and saved, when I went back into the mapping the account had reverted back to the old one.

               

              Settings are not sticking. Weird.

              • 4. Re: Dynamic SQL Mapping Disappears On Creation
                John Ossmann

                Damon,

                 

                Does the password have special characters in it?  If possible, try changing it to something alphanumeric.   Some places in FP tend to have problems in this area.

                 

                Which exact version of FP are you on?  I'm not sure, but the older versions may have had a defect in this area (and possibly the newer ones).

                 

                Regardless, if you haven't already, you should contact your support provider.

                • 5. Re: Dynamic SQL Mapping Disappears On Creation
                  Damon Stennett

                  Hi John,

                   

                  Thanks for your help. We've reduced the password to letters and numbers and still no luck. We are on 11.6.9 and I've opened a case with BMC support. Just waiting for a reply.

                   

                  I'll post the solution when they find it!

                   

                  Cheers,

                  Damon

                  • 6. Re: Dynamic SQL Mapping Disappears On Creation
                    Damon Stennett

                    TWIMC,

                     

                    Update - I've been going back and forth with BMC support for a few weeks now. Challenging when you are on opposite sides of the country. We found a work-around, but no solution (don't know why it's happening).

                     

                    Investigation has revealed that when you create a dsql mapping it creates a number of files in the Footprints\db\MASTER1\MR folder (replace MASTER1 with the appropriate directory that corresponds to your workspace). These files are:

                    • ExternalTableLookup_[name of DSQL mapping].yaml
                    • ExternalTableLookup_[name of DSQL mapping].yaml.ycache
                    • ExternalTableLookup_[name of DSQL mapping].yaml.temp
                    • ExternalTableLookup_[name of DSQL mapping].yaml.temp.ycache.

                    Sometimes there will be a ExternalTableLookup_[name of DSQL mapping].yaml.bak as well. In any case, the ExternalTableLookup_[name of DSQL mapping].yaml is the one that the mapping depends on. That's the one that is not being created when I set up a mapping.

                     

                    The work around is to grab ExternalTableLookup_[name of DSQL mapping].yaml.temp, and rename it to ExternalTableLookup_[name of DSQL mapping].yaml. Couple of things I noticed after the last support call from BMC. I have two other mappings that I haven't touched and which are still working as they are using the original password that was in place before it was changed and caused this whole fiasco to start. In the ExternalTableLookup_[name of DSQL mapping].yaml.temp file for the new mappings I am trying to make there are two lines:

                    • EXTERNAL_CONNECTION_HOST: ~
                    • EXTERNAL_CONNECTION_PORT: ~

                    Pay attention to the tilde

                     

                    In the working mappings these two settings have an actual host name and port entered. I copied these over to the new ExternalTableLookup_[name of DSQL mapping].yaml and that seemed to stick. The mapping is still there and working happily. I dare not try to modify it though ;-)

                     

                    The case has been passed upward to the developers as the two levels of support that I worked with were not able to find any reference to this situation ever happening before. It's great being unique! (Heavy sarcasm)

                     

                    I will post again when and if a resolution is found.

                     

                    Cheers,
                    Damon

                    • 7. Re: Dynamic SQL Mapping Disappears On Creation
                      Brian McKenzie

                      Looks like this is Footprints 11??

                       

                      If support still has access to the archived Footprints tickets from the Numara Support days....There is a resolution to this issue if i recall correctly. I haven't been there for a couple years so I don't know but, I am pretty sure I tackled this issue 5-6 years back....sorry I don't remember the resolution been too long, I've slept too many nights and I am generation Geritol now....LOL