RPOWER.SQL Database QuickFixes

From RPOWERWiki

Jump to: navigation, search

The purpose of this page is to provide information on how to fix common problems one might encounter when running the RPOWER Database Importer.

Store not in database. If store is a valid store, add it to the tbl_store table:...

Solution: This is done on purpose to prevent an accidental export into a database.

The first step is to verify that the cg is correct. The Cg Rule is that if this store is the first one to export data to us, the cg in the tbl_store record in the export file needs to match the store serial number. You can check whether this is the first store, or a consecutive store by (these instructions are for SQL Server Management Studio):

  1. Log into the SQL database as the root user.
  2. Open a new query window by pressing Ctrl + N
  3. Type use rpower. Hit F5 to run command. You are now logged into the rpower database.
  4. Type exec rpGetStoresUsingCG, a space, and then the number of the cg you want to check. Hit F5 to run command. If the cg is not in the database, then the returned result set will be empty. If the cg is already in the database,then you will see a set of tbl_store records for the stores in that cg.

Once the cg has been corrected, and you have a good export, you can then authorize the store for import into our SQL database. First posts for stores which are not in the database will be in the RPOWER.Central\RFX\DATACNX\SERVERIN\FAILED folder. Navigate to that folder, right click on the file, and select Edit from the popup menu. Do NOT select Open, because that will open the file in a web browser, and we cannot edit the when it is open in a web browser.

To allow the store to import, add is_valid="1" to the end of the tbl_store xml element. Example:

<tbl_store mid="" cg=""... lupdttm_zdttm="" is_valid="1" />

Once that is done, move the file out of the Failed folder and into the ServerIn folder. It should now import.

Authorize store to appear in reports

These instructions are for SQL Server Management Studio. Some of the keyboard shortcuts referenced below may be different in the application you are using to connect to the SQL database.

  1. Log into the SQL database as the root user.
  2. Open a new query window by pressing Ctrl + N
  3. Type use rpower. Hit F5 to run command. You are now logged into the rpower database.
  4. Type exec rpActivateStoreForReports, a space, and then the number of the store's cg, a comma, and the store serial number. Hit F5 to run command.

If you want to check that the store was stamped, you can delete the prior command from the query window, and type exec rpGetStoreUsingCG, a space, and then the serial number of the store you want to check. Scroll over to the right until you see a field named xtype. Under that field, you should see a |RPT| if the store was successfully authorized.

Yellow circle with exclamation point on the RPOWER Database Importer icon, meaning files are sitting in the Pending folder.

Solution: Typically, this will take care of itself in an hour or so. But, if files have been sitting in the Pending directory for a day or more you can force them to import by doing the following:

  1. Identify the store that is not importing. The serial number of the store is embedded in the names of the files sitting in the Pending directory.
  2. Log into the fileserver at the store and log into Manager Functions.
  3. Click System Status.
  4. Press Alt-U to bring up a set of brown buttons.
  5. Click Reset Export Flags.
  6. Click OK.
  7. Click System Maintenance...
  8. Click Execute SQL Exports
  9. On the SQL Exports dialog it will ask if you want to Report date range?. Click No.

RPOWER will then generate a DATACNX export file containing first post information.

NOTE: It is recommended that you either do this when the store is closed, or when it is slow. The probability exists that doing this can cause a file lock error which could possibly bring the system down. Also recommended is that you only do this on the fileserver since it will run faster than performing this task from another workstation.

The file RPOWER generates will be sent to the RPOWER Database Importer and once this file has been imported, the importer will automatically import the files sitting in the Pending directory.

Views
Personal tools