AccuMark V8.2 use MSDE and SQL
AccuMark V8.2 use MSDE, SQL 2005 Express for CAD System. These SQL databases are equipped to look and feel the same as userroot storage areas. Using SQL databases as storage areas allow for larger amounts of data to be stored in a single storage area (a maximum of 100,000 data items per data type). SQL storage areas can also eliminate the potential file locking problems that can occur when accessing data across networks, because it does not use separate control and data files.
Other advantages include:
- Eliminates the potential for file-locking problems that can occur across networks as with userroot-type storage areas
- Increased limit for data, now up to 100,000 data items per data type o Allows the ability to create sub-storage areas or sub-folders
- Allows the ability to set allow or deny permissions to individual storage areas o Allows the ability to set read or write permissions to individual data types
To use SQL or MSDE for your AccuMark data, one of the following SQL or MSDE versions should be installed on all workstations:
o AccuMark Version 8.1 supports SQL Server 2000 or MSDE 2000. o AccuMark Version 8.2 supports SQL 2005 or SQL 2005 Express in addition to SQL Server 2000 and MSDE 2000.
Both Microsoft SQL Desktop Engine (MSDE) and SQL 2005 Express are reliable storage engines and query processors and can provide the ability to create SQL storage areas. MSDE and SQL 2005 Express are easily redistributed royalty free, so they can provide a low cost option for a database server. They are language dependant and run on most operating systems.
There is a common technology base shared between SQL Express 2005 and SQL
Server 2000. If data storage needs grow beyond the storage and scalability of SQL Express, a system can be upgraded to SQL Server 2000, requiring only the purchase of SQL Server software.
However it is highly recommended to use the same version of SQL on all systems that will be accessing the same storage areas for compatibility purposes.
It is recommended to obtain training, usually someone from your IT department to be the SQL-server Administrator, in external (non-GT) SQL-server administration classes (2-3 days).
It is highly recommended to install the latest service packs and updates for SQL. It is recommended that the latest Windows service packs and critical updates be installed on the system. XP Home is not supported, and for XP Professional, simple file sharing must be turned off.
AccuMark requires that SQL Server 2000 has at least SP4 in order to properly access database items (otherwise you may receive permission errors, for instance, a user will appear to be able to Save As but not Save data).
AccuMark SQL Overview
AccuMark V8.2 will support SQL Server 2000, MSDE, SQL 2005 Server and SQL 2005 Express storage areas. To avoid compatibility issues, the same version of SQL must be running on all systems that will be accessing the same storage areas. Mixed versions are not supported.
Note: Most references to SQL in this document will refer to the version of SQL that is installed on the system, including SQL Server 2000 SP4, MSDE, SQL 2005 Server, SQL 2005 Express. All current Microsoft service packs must be applied.
Note: Subfolders for SQL storage areas have been implemented in AccuMark V8.2 however WebPDM will not support or recognize subfolders prior to the WebPDM 5.1 release. Please see the What’s New document for detailed information on AccuMark subfolders for SQL storage areas.
IMPORTANT NOTE: Accepting the upgrade to the new SQL database structure/schema will prevent older AccuMark versions from accessing the upgraded data. Therefore, the upgrade should be scheduled when all systems in the Workgroup / Domain can be updated.
Generating a List of SQL storage areas
Acmksetup is run automatically at startup to generate an initial list of AccuMark SQL Storage and subfolder (sub_storage areas) on local and network drives. This list is used to populate the local and network SQL drives (0-9) in AccuMark Explorer and
Applications. Local servers and any connected, running networked servers are scanned for the existence of AccuMark SQL storage areas (databases). If local or network drives are added or removed or if a new storage area is created on a network drive while AccuMark Explorer/Application are open, Acmksetup must be run to update the display.
If local or network drives are added or removed or if a new SQL storage area or subfolder is created on a network drive while AccuMark Explorer or other AccuMark Applications are open, Acmksetup will need to be exeuted to update the display.
To manually run Acmksetup:
- Select Tools\AccuMark Utilities\Storage Area\Refresh SQL from AccuMark Explorer
- Select View\Refresh SQL from AccuMark Explorer
Or
- Select Start\ All Programs\ Startup\ AccuMark Acmksetup
- Select View\Refresh from AccuMark Explorer
Generating a list of userooot storage areas
DataScan is run automatically at startup to generate an initial list of userroot storage areas on local and network drives. This list is used to populate the local and network userroot drives (A-Z) in AccuMark Explorer and Applications. Local servers and any connected, running networked servers are scanned for the existence of AccuMark userroot storage areas. If local or network drives are added or removed or if a new storage area is created on a network drive while AccuMark Explorer/Application are open, DataScan must be run to update the display.
To manually run DataScan:
- Select View\Refresh Storage Areas from AccuMark Explorer
- Select View\Refresh from AccuMark Explorer
Or
- Select Start\ All Programs\ Startup\ AccuMark DataScan
- Select View\Refresh from AccuMark Explorer
NOTES:
– Changes have been made to maintain data items’ original creation dates and times when copying information into SQL storage areas. Previously all dates were being changed to the copy date, which caused problems with automatic processes like auto-update marker and customer report information.
– User, date and time information is now consistently being stored in the CAD relational database. Previously some creation related information was missing when data was copied from userroot storage areas into SQL storage areas.
– Better checks and error message display have been made for read-only SQL storage areas. Previously no message was displayed nor was the data imported.
– When an AccuMark storage area is renamed using an AccuMark application, the corresponding database filenames (.mdf and .ldf) are no longer changed to match the new storage area name
– SQL/MSDE manager hangs when the server group in MSDE-Manager is double clicked if a server definition is not specified. Be sure to create at least one New Server Registration before double-clicking the server group.
AccuMark Explorer - New folder symbols
Additional visual information has been added to the folder icons in the AccuMark Explorer.
AccuMark Subfolders (Sub-storage areas)
AccuMark SQL storage areas now have the ability to create subfolders or sub-storage areas. This allows the user the ability to tree data for better manageability of a SQL storage area. For example, create subfolders under the parent folder for divisions, seasons, years, factories, etc. There is a maximum of ten levels of subfolders per parent folder - storage area.
Note: Subfolders are not implemented for userroot storage areas.
The procedure below assumes a version of SQL is installed and configured for use with the AccuMark through SQL Support.
To create a subfolder using the AccuMark Explorer highlight the SQL drive to create the storage area. Select File, New / Subfolder.Or right-click on an existing SQL/MSDE storage area to add:
The prompt appears for a name and optional description:
Here’s a sample of a new subfolder added to SQL storage area 812-v8:
For systems with pre-V8.2 SQL storage areas
V8.0, V8.1 cannot display data items in V8.2 SQL storage areas. V8.2 cannot display data items in V8.0 or V8.1 SQL storage areas however, and Upgrade SQL tool is provided so that these previous version storage areas can be ugraded to V8.2.
Copying SQL storage areas with subfolders
To copy a SQL storage area that has subfolders into a userroot location, each of the subfolders must be copied individually to new-different userroot storage area. SQL storage areas with subfolders may be copied to other SQL devices or storage areas.
If a SQL storage area contains subfolder(s), it is identified by a blue folder icon called SubFolders.
- There are current software programming limits with respect to the way subfolders can be displayed in various dialog boxes. This display issue will be addressed in future development releases when the applications can be restructured.
Information stored into a CAD relational database
New Metric ColumnsIn V8.2 certain marker and piece information has been separated into Imperial and Metric columns. WebPDM requires its information to be in Imperial. Other data access applications may use either the imperial or metric values for gathering of information.
TABLE: Cad_Marker_Details
Imperial: Marker_Width, Marker_Length, Marker_Total_Perim, Marker_Total_Area
Metric: mMarker_Width, mMarker_Length, mMarker_Total_Perim, mMarker_Total_Area
TABLE: Cad_MK_Piece
Imperial: Piece_Area, Piece_Perimeter
Metric:mPiece_area, mPiece_Perimeter
TABLE: Cad_Piece_Details
Imperial: Piece_Area, Piece_Perimeter
Metric: mPiece_Area, mPiece_Perimeter
See RDBMS-TableVariables.doc for detailed information on the data columns and information that is stored into the CAD relational database.
If an existing CAD relational database is defined, when an operation is performed that involves the updating of any relational database data (as in markers, models, or any related piece information) the system will detect that these new columns are missing and add them.
Also, the Sync Relational Data function in the AccuMark Utilities can be used to update the information all at once. Note that this function will update all relevant information in the relational database, including image generation for markers and pieces (if the image generation option is on) for all SQL storage areas.
IsActive
Previously when a marker, model or its related piece data was deleted from a SQL storage area the related information may have been removed from the relational database. Now the IsActive flag is set instead. The “Last_Updated” field is also updated indicating the date and time of the action.
Dates and Times
Reports have stated that the dates and times stored are inconsistent or do not reflect the local time. Some cases reported that the created time was wrong when copying a userroot storage area to a SQL storage area. Note that the time is stored in UTC (Coordinated Universal Time also know as Universal Time Coordinates or GMT). This behavior is specifically designed so that the values will be stored in a single time zone. It is up to the individual application’s interface to show these dates in the local time. Some of the functionality listed below also exists in pre-V8.2, but is listed here for clarification purposes.
AccuMark Utilities, Configuration
Hint: When specifying a local device name to use for SQL storage areas, copy and paste the server name from one of the following areas:
MSDE or SQL Server 2000: Right-click on the server icon in the system tray and select “Open SQL Server Service Manager”. Copy and paste the Server name into one of the device slots for SQL Support.
SQL 2005 Express or SQL 2005 Server: Open the Microsoft SQL Server Management Studio Express and a connect dialog is opened displaying the server name. Copy and paste this name into one of the device slots for SQL Support.
Generate Images for CAD Data Relational Database
The option called “Generate Images” will turn on/off the ability to store marker and piece images (graphic representation) to the CAD relational database.
This option needs to be checked for WebPDM, to display the Marker image on the “Marker usage” page and the Piece image on the “Pattern Sheet with info” page. If the purpose of the CAD relational database is for reporting, or querying data for ERP and MRP system uncheck the option to save disk space and time storing marker, model and related piece information to the CAD relational database.
To turn on/off Generate Images:
Select Tools\AccuMark utilities\Configuration\SQL Support from AccuMark Explorer Check or Uncheck Generate Images for desired result.
Image Files
In order to create the binary image data that will be stored in the CAD Relational database temporary .wmf and .bmp are used. These files are created in the \userroot\temp folder. Unless delete restrictions are enforced, these files will automatically get deleted the next time the system is rebooted.
User Authentication
SQL Support features two modes of user authentication: Windows and SQL. If Windows authentication is used, then the user’s credentials are verified based on the Windows login. If SQL authentication is used, a username must be specified with the password.
DSN (Data Source Administrator)
The DSN in SQL Support will allow the user the option to use SQL Server authentication or Windows authentication. Users may create a System ODBC connection to the SQL server and use that DSN name in SQL Support. The directions to create an ODBC connection to use a DSN are detailed in the following section. Be sure to check systems’ firewall settings as well.
To Create a System DSN
From the Control Panel select Administrative Tools > Data Sources. Then select the System DSN tab
Press the Add Button and highlight SQL Server.
Enter a name for the DSN, description and specify the SQL server to connect to. If DSN is the preferred method of communication: one connection per each server in the network needs to be established in all Workstations that will connect to SQL instances.
Do not Change the method of authentication unless your IT department instructs otherwise.
Accept the defaults in this dialog and click finish.
Changes are required only for non- English Windows environments.
System shows summary of the DSN creation
Press the Test Data Source to check the connection
Runs the “Check One Storage Area” function. Provides the ability to check Userroot, SQL storage and Subfolder areas, on an individual basis.
Check All
Runs a check on all userroot storage areas (cleans up)
Check All SQL
This function verifies that the SQL storage areas contain the proper database tables. The check is performed on the SQL storage areas in the locations defined in SQL support.
Any existing SQL storage areas that were made with pre-V8.2 software will be upgraded to the V8.2 database schema, with this dialogue message. This message is displayed regardless if all are up to date.
NOTE: Be sure no other users are accessing data within these storage areas while performing the schema upgrade!
Refresh SQL
Runs the acmksetup application to regenerate the list of SQL devices and their corresponding storage areas and subfolders for all the devices that are configured in SQL Support. This gives the user the opportunity to refresh a device’s list of storage areas whenever a device is added or deleted from the system (and for example, when a network connection has been lost or restored).
In AccuMark version 8.2 the list of connected SQL devices as defined in the SQL Support dialog is stored in the file c:\userroot\system\state\dsnmap.txt. If a CAD relational database is specified that information will also be stored in the file and preceded by an asterisk ( * ).
Each local SQL device will have a list of its storage area in an .lst file with its name as the filename. In this example, the file is called swlap.lst:
Network SQL devices will have a folder with the networked SQL server name and the
corresponding .lst file will be in that folder
Subfolder levels are separate by the pound sign character ( # ). These # characters are converted to slashes ( / ) for display in the AccuMark applications.
In AccuMark 8.3 this SQL storage area information is contained in the Registry, under the Current User Key, not in c:\userroot\system\state\dsnmap.txt as in AccuMark 8.2.
Upgrade SQL
Allows a user to upgrade any pre-V8.2 SQL storage areas to V8.2. Only pre-V8.2 SQL storage areas will be listed and allowed to be upgraded. The database schema has changed since V8.1 with the addition of some new data columns. In order for V8.2 to take advantage of the new data columns in existing storage areas, they must be updated to the new schema.
It is highly recommended to create a backup of existing pre-V8.2 storage areas first before proceeding. Ensure no other users are accessing data in these storage areas before upgrading.
Note that the AccuMark Explorer will list devices and storage area names that were made prior to V8.2, however the list of data items within the storage areas will not display until the storage areas are updated.
To perform an upgrade to an AccuMark SQL Storage Area, the user must be logged in as a user who has the Sysadmin role on the SQL Server.
The upgrade will also update any sql triggers that are used to put information into the CAD relational database (triggers are modified to reflect the updates made to the information that gets stored or updated)
Restore to Last SQL Schema
This is a procedure to “convert” a V8.2 database schema to a V8.1 database schema. It will take the contents of the storage area as is and put it into a V8.1 schema. It does not do a restore of data items, but only changes the table format.
If the storage area contains subfolders, each subfolder will have to be separately copied to a top-level storage area first, otherwise the data in the subfolders will be lost.
• All Storage Areas – all the existing storage areas on that device should be restored to a V8.1 schema.
• [storage area name] – select a storage area from the list to restore to a V8.1 schema.
NOTE: after running Restore to Last SQL Schema you will no longer be able to view the data items in that SQL storage area. Only pre-V8.2 applications will now be able to see the data.
For V8.2 run Refresh SQL, and then Refresh in the AccuMark Explorer. You will now see the upgrade symbol on the restored storage area.
Sync Relational Data
This function tells the software to write the information for existing markers and models from the SQL storage area(s) into the CAD relational database. Includes piece information for those that are named in the models and markers. Refer to the document “rdbms-table-variables.doc” for detailed information on the data characteristics that are stored in the CAD relational database.
Improvements in speed have been made when storing model and piece information and using the image generation feature.
Delete
You can delete a userroot storage area or any SQL storage area or subfolder.
If you are deleting SQL storage areas, the storage area along with any of its subfolders will be deleted. You can also pick an individual subfolder.
Use the View, Refresh in the AccuMark Explorer to update the changes made.
Rename
Currently only allows you to rename a userroot storage area.
Locks
Lock Storage Area
A SQL storage area and subfolders may be locked from the AccuMark Utilities or within AccuMark Explorer. Locking a storage area also locks its sub folders.
0 Komentar untuk "AccuMark V8.2 use MSDE, SQL 2005 Express"