SQL Question

I am new to 24x7. I have created a SQL to execute everyday at 2:00am, however, when I test it, it states that it has ran, but where does the query go? How do I tell 24x7 to put the result into a .txt or .xls file?

In addition to the above question, is it possible for this software to look at a SQL and at every change in say "Name" it will export to a different file.

Thanks for your help.

Database jobs cannot save results; they simply execute commands and analyze/capture error codes and in case of errors do other related processing.

If you need to save results of a query or stored procedure into an external file, you shouldn't use database type jobs for that. Use script type jobs with DatabaseRetrieve, DatabaseExport, and/or other database related commands, the complete script examples are available in the on-line help, use the Example button in the help when you are on the right topic to copy the example code and then modify it for your database profile name, file format or file name as required.

Where would I do this?

Is there any documentation that would help?

Thanx

Would I have to build a script into the script editor using either JAL or SQL language?

JAL.

In the editor, type DatabaseSave then press Ctrl+F1 to open help-topic for this command; click Examples button in the help window toolbar. Copy and paste the example script; customize it as needed.

Hope this helps.

I'm assuming if I don't want to update my database but return the results, I won't use the databaseupdate option in the code. Correct?

When I am returning my data to an excel spreadsheet, it isn't giving me my column headers from the database. How do I fix this?

I really appreciate your help.

DatabaseSave is not for saving changes to the database, it is for saving retrieved data to a file. There is no such option "databaseupdate" available in this command. I am not sure what you are referring to. If you simply want to save the data without column headers in as a plain vanilla text file, which you can also open in Excel, use DatabaseExport command. In case if you don't want to export the entire table, you can always use DatabaseExport with a view or temporary table populated before the command is executed.

The data is exporting correctly, except it isn't exporting Headers. How can I tell it to export them also?

Thanks again for your help.

I believe the "XLS" format supports headers, so if you export to "XLS" format you are going to see column in Excel as they are retuned by the query, in other words as they are named in the database.

In case if you need to export to TXT or CVS, you can add headers yourself. Headers can be returned as part of the query, for example,

SELECT "col head 1", "col head 2"
UNION ALL
SELECT col1, col2
FROM my_table

Another way to get column headers is to save the file as you are doing it now and after that add headers directly to the file. Just prepare a 1-line text file with headers and after saving the data file concatenate them together, for example,
Dim( pid , number)
RunAndWait( "cmd /C copy headers.csv /A + data.csv /A result.csv", "C:\\path\\to\\files", 0, pid)

Here, headers.csv is the static file with column header names as you want them in the result, data.csv is the file created using DatabaseSave, and result.csv is the resulting file you need.

When I am using the databasesave JAL code, is there anyway to DIM a SQL statement?

I.e. Dim MySql, string

MySql="Select..................from..........."

I have a robust SQL that would be better suited if it wasn't in a line as the code is giving me?

Any thoughts on how I can do this?

You have actually answered your own question

Dim MySql, string
Set MySql, "Select..................from..........."

You can also use string concatenations if you need to build dynamic SQL, use line breaks \n special symbol, line wrapping using & and more. For details on how to use variables in JAL please see JAL syntax description topic in the online help. also see Concat and ConcatEx topics, and so on... Tons of examples are available in the on-line help. Every JAL topic provides at least one example.

I don't see any examples pertaining to my situation. I must be looking into the wrong place..........

Dim MySql,string

set MySql,"Select SELECT AC_BI.ACCOUNT_NUMBER, AC_BI.short_name, AC_BI.ADDRESS_01, AC_BI.ADDRESS_02"
mysql+mysql" FROM SQLUser.AC_BI AC_BI"

I have tried both concant and concantex but am getting errors.

There is Examples button displayed on the help system toolbar. Navigate to the topic you want, for example, ConcatEx and when in this topic click Examples button.

Hope this helps,

By the way, you can code something like

	Code:
	Dim MySql, string ConcatEx "SELECT AC_BI.ACCOUNT_NUMBER, AC_BI.short_name, ", & "AC_BI.ADDRESS_01, AC_BI.ADDRESS_02 ", & "FROM SQLUser.AC_BI AC_BI", & MySql DatabaseRetrieve MySQL, rows

I appreciate your help, I found the help files, however when I used your example I am getting an error that states "keyword not found" on the following line:

"AC_BI.ADDRESS_01, AC_BI.ADDRESS_02 ", &

Any thoughts?

Thanks again.