Sage 100

v2014 Premium. Automating some imports for the customer, and they've asked for an alert if there are any line failures. The VI_JobLog table is in Providex, even on Premium, so there is zero exposure to ODBC (for use in building an alert report using CR / VisualCut). Any ideas on how to generate this kind of alert would be appreciated.

Can you do a record count compare and use the results for alerting?

We're importing Sales Orders, from a text file, in the middle of the day with an unknown number of lines in the import file. I wouldn't have any idea how to build a report based on SO SQL data compared to a text file.

Not sure what you mean by ""zero exposure to ODBC"". You can set up an ODBC data source to access the VI_JobLog. Alternatively, avoid ODBC and use a ProvideX utility program to read the log and search for strings.

VI_JobLog.m4t is inaccessible, as far as I know, using ODBC. On a Premium system SY_User is in SQL, so I didn't even try to set up a Providex DSN, thinking it would be ineffective without a way to authenticate a user. The SQL databases do not refer to this table, even in a View. I also know nothing about ProvideX utilities. Is this something a non-developer could set up? (And thanks for the replies).

@DougHiggs ""You can set up an ODBC data source to access the VI_JobLog."" How? Am I missing something?

You would need a developer to write the program or for someone to show you how, or to give you couple of samples as a template. You can download a trial version of the ProvideX interpreter from www.pvxplus.com and you can also download the language manual. Attached is a utility program I wrote several years ago for version 3.71. Quick and dirty but effective and can be automated using a bat file and windows scheduler. I now outsource almost everything unless I am desperate.

DSD Also has an error to file solution if you don't want to write it yourself or don't have the means. http://www.dsdinc.com/enhancement/vi-1001-enhanced-visual-integrator-log/

Kevin, I thought you meant because the system was premium the file wasn't exposed using odbc. I see what you mean now... the file isnt exposed via the data dictionary... premium doesn't have anything to do with it. Maybe the dde and ddf files can be hacked to allow the file to be accessible.

The only way I have done it is to use a staging table to import the data from and write back a status of imported. I then run tests on the leftover records (unimported) using queries testing all the failure points ( bad Vendor, out of balance, bad GL account... etc) not perfect. Visual cut can be used for reporting.

Thanks all for the replies. I'm going to look into the DSD enhancement. This customer has enough automated imports that they might be interested (after it's upgraded... I see only a 2013 version is posted). I'd have to script something that checks for new records in the txt file, then do something with the data so I can run a report off it, but that should be possible. (How this file is not available for reporting is beyond me... silly. I mean, who would ever want to know if their batch file imports had any errors? *eye roll*).

Digging a bit further, it seems the logs I want aren't even in VI_JobLog (oops!). They're in the individual job's own file under \MAS90\VI\VIWI##.m4t (This doesn't change the problem, but I thought I'd mention it anyways).

Why don't you use odbc to access the VI_JobLog?

That's actually a blank file. (My mistake originally assuming the job logs would be in the JobLog file). Edit: And with Premium, it isn't in the table list and there is no SOTAMAS90 DSN.

You've got to kidding. If it mattered you can create a dsn and replicate the settings on a standard installations and you could most likely access the file. No sense in accessing an empty file.