Overview
...
Purpose
1.1.1. The utility (— shift2sql.exe and its renamed copies ) will replace — replaces the regular data unloading in SQL (the directory/report sever unloads the data in . In a normal mode), the data is uploaded by the reference or report server.
1.1.2. Under the settings described below, the data unload will start automatically using the utility which . It will help unburden to reduce the load of the server and speed up the process.
1.1.3. The utility is launched manually only for experiments and/or mass upload of shift files into another database. I.e. That is, all other mechanisms remain are the same (: the files are processed from incoming, the Incoming folder, the system uses the same SQL database, which is used by the report server is used, etc. ). Only the mechanism of transmitting data to the SQL database is changed. All you need to do is to make changes into to the .ini INI file and restart the server. The Further, the operation will be launched automatically from there on.1.2.
Utility's Advantages
...
- It does not overload the server's *.exe (refsrv.exe or rkReportsServer.exe)
...
...
- You can modify the unloading algorithm without affecting the server
...
...
- You can simultaneously launch several data loading processes
...
...
- You can use the memory without shortchanging the server
...
...
- Memory leaks become less critical.
...
Utility Types
1.3.1. In relevant In update RK7 versions there are three copies of the described app files that have been renamed:
- refsToSQL.exe for
...
- uploading references
- shift2sql.exe for
...
- uploading cumulative data
...
- logsToSQL.exe for
...
- uploading the log.
1.3.2. There is a utility variety version with a graphic graphical user interface.1.4.
Operation Algorithm
1.4.1. The app makes copies of the input data sets.
1.4.2. Transmits Then, it transmits the data which is , subject to different checks checkings, into memory data sets.
1.4.3. It then relocates After that, the data is transmitted from the memory to MS SQL.1.5.
Conditions
1.5.1. The utility can be used in directory/the reference or report server starting since version 7.4.21.265 and higher. If it is you have an older version you need , it is necessary to upgrade the server which unloads the data into an external database (— consequently, there may be a need to upgrade the upstream servers).
1.5.2. Starting Since version 7.5.4.0, shift2sql is installed by default, i.e. no additional settings are required for the .ini INI file.
...
Utility Usage
2.1. Automatically
Automatic
The 2.1.1. The (shift2sql.exe) file should be listed present in the catalog directory alongside the *.exe file of the server unloading the data into an external database. Note: for versions older
| Info |
|---|
For versions higher than 7.4.21.289 the file is delivered as part of a standard package, so there is no need for manual updating (unless you are testing a new version). |
2.1.2. The UseShift2SQL option should be added in the CONFIG section in the .ini INI file of the report /directory or reference server (, depending on the server used ) (for versions older lower than 7.5.4.xxx):
| Code Block |
|---|
[CONFIG] |
...
... |
...
UseShift2SQL = "1" |
...
... |
...
Manual
2.2.1. To launch it the utility manually you need to enter a text into the command line or an executable file according to the following template:
shift2sql.exe "ConnectionString" "shiftsFileName" "C:\SomePath\chckconv.xml"2.2.2. A total of
There are five parameters are to be used:2.2.2.1. The "ConnectionString" is the
- The first parameter
...
- is
- ConnectionString — a connection string of the following
...
- type: Provider=SQLNCLI10.1;Persist Security Info=True;Initial Catalog=RK7xBASEUPGx20;Data Source=310-RK7-ALAN\SQLEXPRESS;User ID=SA;Password=SomeStrongPAssword
...
- — the full ADO connection string, includes the username and password
...
...
- or dataBaseConnection of the ByHandle=2888
...
- type
...
- , where the value of the ByHandle parameter is an indicator of the open temporary file. The temporary file is created with the FILE_FLAG_DELETE_ON_CLOSE flag, and with bInheritHandle=
...
- true in the _SECURITY_ATTRIBUTES structure
...
- The second parameter
...
- is
- shiftsFileName — the name of the unloaded shift's name (shift.udb)
...
- . Use with the full path, if necessary.
- . Use with the full path, if necessary.
...
- or shiftsDataFileName — the data source file
...
- name
- The third parameter is
- dbExportConfigFile —the full path to the chckconv.xml structure description file.
...
- dbExportConfigFile contains the data conversion rules
...
- and is
...
- created by default in the /base/
...
- folder (if the
...
- reference server is used for reports) after the data on the first shift is loaded using the
...
- shift2sql.exe
...
- utility
...
- or
...
- LOGToSQL.xml in the \base\refs2sql\ subfolder
...
- The fourth parameter is
...
- a numeric one — parentProcIDToWait — an identifier of the OS parent process.
...
- In case of the parent process
...
- death, shift2sql.exe stops
...
- operating as well. An optional parameter, not obligatory to
...
- be specified.
...
- The fifth parameter (optional, not obligatory) — sqlDataLoadingMode — is the SQL data loading
...
- mode that regulates the overwriting. It can have the following value:
...
- dataover — uploads the entire file deleting the data in MS SQL
...
- . Thus, carries out data overwriting: the data
...
- is deleted from all tables and then added from the selected file
...
- datasync — checks the entry keys and stamps (RECSTAMP) and adds
...
- , deletes
...
- , or updates only the parts which have been changed
...
...
- schmover —creates a structure: all the tables will be created
...
- again, all the data
...
- is deleted
...
- — schmmake
...
- dataload — adds the data: the data
...
- is added to that available in the tables. The values with intersecting keys will be updated.
...
- schmsync — creates a database structure: the existing tables
...
- are modified (update mode for a cumulative
...
- database).
If needed, it is possible to enable Optional: not obligatory.
2.2.3. You can include logging into the file by specifying the log file as the location for relocating the command line output:
shift2sql.exe "ConnectionString" "shiftsFileName" "C:\SomePath\chckconv.xml" > logs.txt
2.4.
To enable batch processing of several shifts you can use the bat file containing the following:
| Code Block |
|---|
@echo start |
...
for %%i in (shift*.udb) do ( |
...
echo %%i |
...
call "full_path_to_shift2sql.exe" "ConnectionString" "%%i" "full_path_to_chckconv.xml" >logs.txt |
...
) pause |
...
| Note |
|---|
| The .bat file should be kept in the same folder as the shift data! |