SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
export job database
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite View previous topic
View next topic
export job database
Author Message
kyofm



Joined: 30 Sep 2014
Posts: 21

Post export job database Reply with quote
We are having issues with exporting the jobs database into MS Access 2013. We currently have an ODBC connection to an existing database and the when we test the connection in the database profile properties everything tests good. However, when I run the export jobs database in the Tools section I get the following error:

"Cannot export job database: Wrapped com.softtreetech.jssceduler.com.schedException: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same."

How do I reset the table so that all fields are represented?

Any help is appreciated.

Thx

Version: Windows Multi 5.2.421
Wed Sep 16, 2015 9:17 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Please try dropping all existing tables having names starting with t24x7_ prefix and then exporting again. If that doesn't help, the export function isn't compatible with the selected database type or version and you may need to choose a different database.
Wed Sep 16, 2015 9:30 am View user's profile Send private message
kyofm



Joined: 30 Sep 2014
Posts: 21

Post export job database Reply with quote
thank you for the quick reply. I deleted all tables (t24x7_xx) and then re-ran the export job database utility and received the following message:

"Cannot export job database: Wrapped com.softtreetech.jssceduler.com.schedException: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot find the input table or query 't24x7_jobs'. Make sure it exists and that its name is spelled correctly."

I also tried exporting to a new database (.mdb and .accdb)(two different access odbc drivers as well) and received the same results as above.
Wed Sep 16, 2015 9:41 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
That's weird. How did you get them created in the first place? Were they created in a previous version?

Please try restarting the scheduler and trying again. If that doesn't help, click Tools/Options menu, then Log tab, then select your database profile, and then clock Create Tables button. Hope that does the trick.
Wed Sep 16, 2015 12:03 pm View user's profile Send private message
kyofm



Joined: 30 Sep 2014
Posts: 21

Post Reply with quote
The existing table was created in MS Access 2013 on a new driver in the ODBC. Only difference is the person who created it is no longer in our office, so I'm not 100% confident that something else was done.

I've completed a couple of restarts and different profiles with different databases/odbc with no luck. I received the fillowing message in the log tab once I clicked create table: "Unable to create log table {0}"

thank you.
Wed Sep 16, 2015 1:06 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
The error message indicates it cannot create new tables in that Access database. Does the account you use for the ODBC connection have sufficient privileges to create new tables?
Wed Sep 16, 2015 1:11 pm View user's profile Send private message
kyofm



Joined: 30 Sep 2014
Posts: 21

Post Reply with quote
best I can tell. I was able to create and write to a new table and save the database.
Wed Sep 16, 2015 1:13 pm View user's profile Send private message
kyofm



Joined: 30 Sep 2014
Posts: 21

Post Reply with quote
I have complete uninstalled and reinstalled the software just to rule that variable out. I also moved the .mdb to the local drive instead of a network drive. No change in the results...
Wed Sep 16, 2015 2:24 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
But still same ODBC profile. Please enble the tracing option, reproduce the issue and post contents of your debug.log file.
Wed Sep 16, 2015 2:52 pm View user's profile Send private message
kyofm



Joined: 30 Sep 2014
Posts: 21

Post Reply with quote
there are no tables in the .mdb, I deleted all t24x7_xx tables previously.

2015-09-16 15:12:17,473 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.log.AbstractLogAdapter - Deinitializing Database log adapter...
2015-09-16 15:12:17,473 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.log.AbstractLogAdapter - Initializing Database log adapter...
2015-09-16 15:12:25,164 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.ui.dialogs.importers.JobDbExportDialog - job database export started; entering JS context;
2015-09-16 15:12:25,164 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.runner.javascript.JavaScriptRunner - runScript: JavaScriptRunner
2015-09-16 15:12:25,179 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.runner.javascript.JavaScriptRunner - Enabling JDBC trace
2015-09-16 15:12:25,179 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.runner.javascript.JavaScriptRunner - Connecting to database...
2015-09-16 15:12:25,179 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.sql.SqlConnector - loadDriver() driverPath= driverClass=sun.jdbc.odbc.JdbcOdbcDriver
2015-09-16 15:12:25,179 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.sql.SqlConnector - parsed urls: 0
2015-09-16 15:12:25,179 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.sql.SqlConnector - java.net.URLClassLoader@82bf18
2015-09-16 15:12:25,179 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.sql.SqlConnector - sun.jdbc.odbc.JdbcOdbcDriver@b29ac6
2015-09-16 15:12:25,195 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.runner.javascript.JavaScriptRunner - Connected
2015-09-16 15:12:25,195 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.runner.javascript.JavaScriptRunner - Executing CREATE TABLE t24x7_jobs(job_id INT, property_name VARCHAR(50), property_value VARCHAR(4000))
2015-09-16 15:12:25,195 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.runner.javascript.JavaScriptRunner - Database error: [Microsoft][ODBC Microsoft Access Driver] Size of field 'property_value' is too long.
2015-09-16 15:12:25,211 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.runner.javascript.JavaScriptRunner - Executing DELETE FROM t24x7_jobs
2015-09-16 15:12:25,211 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.business.runner.javascript.JavaScriptRunner - Database error: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 't24x7_jobs'. Make sure it exists and that its name is spelled correctly.
2015-09-16 15:12:25,211 [AWT-EventQueue-0] DEBUG com.softtreetech.jscheduler.ui.dialogs.importers.JobDbExportDialog - job database export failed: Wrapped com.softtreetech.jscheduler.common.SchedException: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 't24x7_jobs'. Make sure it exists and that its name is spelled correctly.
Wed Sep 16, 2015 3:01 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
It appears that your version of Access doesn't support long VARCHAR fields and as a result the scheduler is unable to create the required table.


Please create that table manually by executing the following query in your Access database
CREATE TABLE t24x7_jobs(job_id INT, property_name VARCHAR(50), property_value VARCHAR(4000))


Before execution replace 4000 with the maximum size supported by your version, I guess that would be just 255, or better replace VARCHAR(4000) with Access specific datatype MEMO.
hope that works for you.
Thu Sep 17, 2015 8:31 am View user's profile Send private message
kyofm



Joined: 30 Sep 2014
Posts: 21

Post Reply with quote
Here is the VB that I ran from a seperate database in order to create the table:

Sub CreateTableX1()

Dim dbs As Database

' Modify this line to include the path to the database on your computer.
Set dbs = OpenDatabase("C:\24x7mdb.mdb")

' Create a table
dbs.Execute "CREATE TABLE t24x7_jobs(job_id INT, property_name VARCHAR(50), property_value VARCHAR(255));"

dbs.Close

End Sub

I was then able to successfully run the Export Job Database module from the 24x7 scheduler, HOWEVER, the results are not what I was looking for, below you will find a sample:

job_id property_name property_value
275 ID 275
275 JOB_TYPE S
275 MSG_JOB_ID
275 MSG_PROFILE
275 MSG_PAGER
275 MSG_NET_RECIPIENT

Looks like the data that was populated is not all of the data generated by the Export function.

Any additional thoughts or help would be greatly appreciated.

Thanks
Thu Sep 17, 2015 9:38 am View user's profile Send private message
kyofm



Joined: 30 Sep 2014
Posts: 21

Post Reply with quote
in response to my previous post... I now see how the data is presented, it looks to be ok. the data is presented vertically for each job...

I will continue to tinker with this dataset and attempt present it horizontally.

Thanks
Thu Sep 17, 2015 9:46 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Hi. The data is stored more or less like key,value pairs.
Take a look at how to pivot data in access
https://support.office.com/en-in/article/Create-PivotTable-or-PivotChart-views-in-a-desktop-database-83e524df-dfbd-456d-9dd0-0a48c1aa6752
Hope that helps
Thu Sep 17, 2015 9:57 am View user's profile Send private message
kyofm



Joined: 30 Sep 2014
Posts: 21

Post Reply with quote
Looks like Pivots have been removed from Access 2013....

Back to the drawing board.

I found a template buried in the 24x7 maintenance called export job descriptions, does the JobArray command included in this template attempt to breakout the properties in a more presentable horizontal fashion:

var jobArray = Scheduler.getJobs( );
var propertyArray = new Array("ID", "JOB_TYPE", "MSG_JOB_ID", "MSG_PROFILE", "MSG_PAGER", "MSG_NET_RECIPIENT",
"MSG_PROGRAM_NAME", "MSG_PROGRAM_TIMEOUT", "MSG_RECIPIENT", "MSG_NET", "MSG_ACCOUNT", "MSG_FILE_NAME",
"MSG_EMAIL", "MSG_PAGE", "MSG_DATABASE", "MSG_FILE", "MSG_SCRIPT", "MSG_TRAP", "MSG_SQL",
"MSG_PROGRAM", "MSG_JOB", "MSG_START", "MSG_FINISH", "MSG_ERROR", "MSG_LATE", "MSG_NOTFOUND",
"MSG_ACTIONS", "FOLDER_ID", "IGNORE_ERRORS", "SKIP", "DELAY", "LOG", "DESCRIPTION", "MOVE_DIR",
"RENAME_SUFFIX", "NUMBER_OF_RETRIES", "RETRY_ON_ERROR", "RETRY_INTERVAL", "DISABLE_ON_ERROR",
"SIZE_CHECK_INTERVAL", "SAVE_ATTACHMENT", "ACCOUNT", "MESSAGE", "SUBJECT", "FILE", "POLLING_INTERVAL",
"DELETE_RULE", "DAY_NUMBER", "DAY_NAME", "MONTHLY_TYPE", "SLIDE_HOLIDAY", "DAY_LIST", "SUNDAY",
"MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SKIP_HOLIDAY", "TIME_LIST",
"INTERVAL", "START_DATE", "START_TIME", "END_DATE", "END_TIME", "ALL_DAY_TYPE", "DAY_END_TIME",
"DAY_START_TIME", "REBOOT", "RUNAS_USER", "RUNAS_DOMAIN", "ARCHIVE_OUTPUT", "OUTPUT_PATTERN",
"KEYSTROKE", "INIT_TIMEOUT", "SEND_KEYSTROKE", "EXIT_CODE", "TIMEOUT", "WINDOW", "START_IN",
"COMMAND", "SCHEDULE_TYPE", "QUEUE", "PRIORITY", "DETACHED", "ASYNC", "PROFILE", "SCRIPT_TYPE",
"HOST", "BACKUP_HOST", "SCRIPT", "SQL", "MSG_SCRIPT_TYPE", "MSG_SCRIPT_CODE", "NAME", "CALENDAR");
for( var j = 0; j < jobArray.length; j ++)
{
for (var p = 0; p < propertyArray.length; p ++)
{
var value = Scheduler.getJobProperty(jobArray[j], propertyArray[p]);
var sql = "INSERT INTO t24x7_jobs VALUES (" + jobArray[j] + ", '" + propertyArray[p] + "', '"
+ value.replaceAll("'", "''") + "')";
Database.execute(sql);
}
}
Thu Sep 17, 2015 10:29 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » 24x7 Scheduler, Event Server, Automation Suite All times are GMT - 4 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.