These utilities (command-group) are dedicated to Transact-SQL language (to its enhancement) and to development in SQL Server DBMS. Except $SqlToCsvExp (which uses ADO.NET) all the other are based on well-known SQLCMD-tool (from MSSQL).
HERE IS SITUATED ONLY BRIEF INFORMATION ABOUT COMMAND USAGE (REVIEW).
See help-info of corresponding commands instead: “?”, “/?”, “-?” or without parameters (works in most of cases). Some utilities are affected by temporary/persistent environment variables (as additional parameters/options). List of command syntax also contains corresponding information about such advanced utility invocation (with usage of parameter variables).
For information concerning to SQL-programming (with help of these utilities) proceed following local page:
$SqlTrans is a key-tool in SQL-file technology. It provides so-called script-file(s) to database translation.
We may understand different things under such translation:
– stored procedure(s) creation and/or deletion;
– table structure creation and/or deletion;
– creation and/or deletion; of scalar/table function(s), view(s), trigger(s) etc.
This is a very useful abstraction (file-to-DB translation). It allows to obtain an independence in database development relying only on file/directory structure.
Another useful notion in SQL-file is Programmatic (sql-object with execution code). A database object (stored in file-script) which contains any functionality definitely should be labeled as programmatics: SP, function, table-trigger and even view (because views may access functions).
$SqlTrans–utility processes single SQL-file so as a group of files specified through file/subfolder mask. $sql_order.list–file may be used for careful source-file ordering before executing SQL on the server (during early preprocessing phase). This utility supports miscellaneous scenarios. They become understandable after viweing some of programming samples (numerous working examples contained in the SDK).
$SqlTrans–command depends on SqlCatPP.exe from “HandicraftSDK\CMD-utilities\Assemblies”-folder. This intermediate-level subprogram is responsible for source-files concatenation and for early SQL-preprocessing. SQL-execution itself is performed by SQLCMD. (Its parameters are accessible through “$sqltrans.AdditionalParams”-variable.)
1) SQL-script file/pattern (conditionally required);
2) SQL-report file (may be empty: "");
3) SQL-script file/pattern (may be empty: "");
4) SQL-subfolder name pattern of source files (*.sql, optional).
See utility syntax-print for information of how to use parameter variables (“$sql_…”, “$sqltrans.*” and other).
$SqlTrans "create_structure.sql" $SqlTrans "aux_*.sql" $SqlTrans "My Query.sql" "My Report.txt" $SqlTrans "*.sql" "" "..\!delete_procedures.sql" "Procedures_*."
$SqlUpload is old-styled utility which is based on the BCP-tool (Bulk Copy data unloading utility from MSSQL). It may use BCP-command in combination with preliminary and/or posteriour SQL-execution: $pre_upload.sql and $post_upload.sql (corresponding script-files). This approach may be suitable to organize table(s) uploading as single command.
$SqlUpload–command may process up to 4 files/tables at once. In accepts even number of parameters (from 2 to 8): <FileName> <TableName> [ <FileName> <TableName> … ].
CAUTION!!! Due to especial behaviour of BCP-command there are situations where its error code may not be handled in proper way. That is why the output of $SqlUpload–command always needs to be viewed manually!
Inspite of some imperfections this utility usually works quite sutisfactionary.
$SqlUpload "xtable.txt" "XTABLE_UPLOAD" $SqlUpload "data_1.txt" "IMPORT_1" "data_2.txt" "IMPORT_2" "data_3.txt" "IMPORT_3"
$SQL interactive command expects two parameters: SQL-server name and SQL-database name. It invokes SQLCMD in interactive mode after preliminary preparattion of code page for the console: chcp 65000 (unicode code page). Also some decorative prompt is printed as a header, with “SQL-server: <ServerName>” and “SQL-database: <DatabaseName>”. Previous code page then is restored on exit.
$SQL is called by $SqlCon–command (in newly created console).
$sql MercurySrv EmeraldDB
$SqlCon–command opens new interactive SQL-console in unicode code-page (65000).
1) SQL-server name;
2) SQL-database name;
3) Width for console in columns (optional);
4) Height for console in rows (optional).
Default width of newly created console is 1001 columns.
Decorative prompt is printed as a header, with “SQL-server: <ServerName>” and “SQL-database: <DatabaseName>”.
Many SP-scripts in the SDK are provided with exambles of how somebody can access stored procedure. Such examples are placed in comments at script bottom. $SqlCon–command is a good tool for testing of such examples through Copy/Paste. SQLQMD starts without significant delay and you alwais have this simple interactive SQL-console at hand.
It is useful to charge $SqlCon–command into Far Manager user menu (like this):
Hot key: Q
Label: $SqlCon (database TEST)
Commands: $sqlcon "(local)" "TEST"
$sqlcon MercurySrv EmeraldDB $sqlcon MercurySrv EmeraldDB "-" $sqlcon MercurySrv EmeraldDB 2001
$SqlToCsvExp–command is intednend for simple SQL-data downloading (in CSV-format). This is not deelpy customizable downloading tool, but it
may be useful however in cases without highly exacting demands.
Three or four parameters are expected for $SqlToCsvExp:
1) SQL-server name;
2) SQL-database name;
3) SQL-file name (<BaseName>.sql) or SP-name (stored procedure);
4) File-path for CSV-result or SP-parameters string (if applicable).
Additional parameters-options (environment variables):
For understanding how to use this utility investigate following sample-folder: “HandicraftSDK\SQL\Programming samples (SQL-file)\Lists downloading (sys-info)”.
Examples (invocation of sql-scripts / SP-invocation):
echo. echo *** DOWNLOADING DATABASE LIST: *** call $SQLTOCSVEXP.cmd "!$sql_server!" "!$sql_database!" "db_list_downloading.sql" if !errorlevel! neq 0 goto FAILURE echo. echo *** DOWNLOADING PROCESS LIST: *** call $SQLTOCSVEXP.cmd "!$sql_server!" "!$sql_database!" "process_list_downloading.sql" if !errorlevel! neq 0 goto FAILURE
echo. echo *** DOWNLOADING DATABASE LIST: *** call $SQLTOCSVEXP.cmd "!$sql_server!" "!$sql_database!" "Download_DB_list" if !errorlevel! neq 0 goto FAILURE echo. echo *** DOWNLOADING PROCESS LIST: *** call $SQLTOCSVEXP.cmd "!$sql_server!" "!$sql_database!" "Download_process_list" if !errorlevel! neq 0 goto FAILURE