Need to ignore missing SQL tables on reload of data

[stevedark]

Hi there,

I am producing a QlikView document that I wish to point to many different servers with similar but not identical data layouts. I wish to have the complete set of tables that I may wish to pull in in the script - but the script to ignore tables that are not there.

Happy to poll the sysdatabases and sysobjects tables to find if the tables exist - but I don't know how to then do a conditional statement around the load of the missing table.

Would even be equally happy with just putting an 'on error' / 'try catch' type construct around that table - but I am not sure if one of those exists.

Obviously the desired result could be achieved by commenting parts of the script when pointing to different servers, but I would rather not have users having to do that.

Many thanks in advance for any pointers you can give me.

Cheers,
Steve

[Anonymous · Accepted solution]

Steve,
I think what you're looking for is the ErrorMode. See online help on this topic, I'm sure you'll figure out how to use it.
The simple way is to use ErrorMode=0, but you can do much more with it.

[Oleg_Troyansky]

Search the On-line Help for an article "Error Variables" between ErrorMode and ScriptError, you can construct syntax similar to try... catch - there is a nice example there.

cheers,

Oleg

[stevedark]

Thanks guys for the quick response. After your pointers the finished chunk of code looks like this:

set ErrorMode=0;
ErrorTemp:
SQL SELECT *
FROM DBName.dbo.tbl_ErrorCode
WHERE 0 = 1;

if ScriptError <> 4 and ScriptError <> 5 then
set ErrorMode = 1;
ErrorCode:
LOAD ErrorCode as ErrorCodeId,
ErrorDesc as ErrorMessage;
SQL SELECT *
FROM DBName.dbo.tbl_ErrorCode;
end if

set ErrorMode = 1;
drop table ErrorTemp;

I don't tend to like relying on turning off error trapping - it is there for a reason after all, but using the first query to check for a specific couple of error conditions before retrying means that it won't skip over a table because of a mistyped field name.

I guess this could be re-written to use a select from sysdatabases and sysobjects, and then take a 'peek' at the data - but this solution does me fine for the demo I am putting together.

Cheers,
Steve

[rwunderlich]

Steve,

Useful bit of code.

How about writing this up as a wiki page?
http://community.qlik.com/wikis/qlikview-wiki/default.aspx

-Rob

[stevedark]

Hi Rob,

Thanks for the tip about putting the code on a wiki - but I am sure there are more elegant solutions. Feel free to post the code elsewhere though if you wish.

It's worth noting that the second instance of setting the error mode back to 1 is better off after the drop - as the temporary table is not created if none of the tables being checked for in that way are referenced. In the scenario I am using the code it is an either one table or the other situation - so it makes little difference.

Cheers,
Steve

[stevedark]

Hullo,

The error trap suggested above worked fine, and following the motto 'if it ain't broke' I have run with it for some time. However, as soon as I tried to get Publisher to do a reload with the error trap in place it failed - seemingly ignoring the fact that I was instructing it to ignore errors.

I have therefore coded it with a reference to Sysdatabases, and it works a treat:

Sysdatabases:
LOAD
 name as DatabaseName;
SQL SELECT *
FROM master.dbo.sysdatabases;
let vHasBanking = FieldIndex('DatabaseName', 'ABCBanking');

if vHasBanking > 0 then
 ErrorCode:
 LOAD ErrorCode as ErrorCodeId,
 ErrorDesc as ErrorMessage;
 SQL SELECT *
 FROM ABCBanking.dbo.tbl_ErrorCode;
end if

Obviously both developer and publisher user accounts must have read rights to the system tables, and that was already the case in this environment.

Cheers,
Steve