Odd Script Behavior - SQL Queries

[brandonbollin]

I have an odd one for you, fellow experts. In one of the lower environments on the project that I'm working at right now, IDM is suddenly being very picky about tables and view references in SQL queries when being executed from inside a script. Normally, when doing something like this:

select * from idmv_vallink_ext
where MSKEY = 12345

You can code the script like this:

var sql = "select * from idmv_vallink_ext where MSKEY = 12345";
uSelect(sql);

However, for reasons we don't understand, any script using that kind of query is suddenly erring out saying that the table / view can't be found. When we go into the script and update the SQL to include the prefix, then it starts working again:

var sql = "select * from dbo.idmv_vallink_ext where MSKEY = 12345";
uSelect(sql);

What would cause IDM to suddenly behave this way?

[former_member201064 · Accepted solution]

Hi Brandon,

my first thought would be to check whether the dispatcher are still running with the RT user.

Second check would be the rights of the RT user in the database. Maybe he lost his default scheme somehow

Best regards

Dominik

[brandonbollin · Accepted solution]

You're probably 100% right on this. When I did my troubleshooting, we looked to see what account was being used for database access and it's set to the mxmc_admin account. Someone must have gone in and changed it for me. When I saw that, however, this point didn't register in my mind. I just started looking at the admin account's rights in the mxmc_db.

I'll go switch it to mxmc_rt and, if it works, I'll report back. I'm pretty sure this is the culprit though. 

[brandonbollin · Accepted solution]

Turns out this did not do the trick. The whole environment at all levels in this company are all using mxmc_admin to access the database from IDM. Still not sure why the prefixes are suddenly being required when doing SQL queries from JavaScripts.

[former_member201064 · Accepted solution]

Does the mxmc_admin (still) have the mxmc_rt_role? (or whatever the Oracle equivalent was, I've simply forgotten)

[former_member2987]

Hey Brandon,

As noted, the dispatchers should be running with mxmc_rt.  The other thing I am wondering if maybe some other shared application or person (DBA) made changes.

You could try running the mxmc_update script to reset everything.  Data will not be affected by the update, but everything else should be re-established.  Or have a DBA compare the working environment to the strange one.

To me this feels like more of a database issue.

Matt