Connecting to your SQL Server Database

If you have AccuSQL and have set up the MS SQL Server database and transferred your database to it, then you are now ready to connect other installations of AccuSQL to this SQL Server database.  This blogs shows you how to do this.

NOTE: The steps are the same whether you are connecting a regular AccuSQL installation to the SQL Serer database or if you are connecting the SQL Computer Lab Plug-in Advanced software. 

These steps assume that you already have completed transferring your database and have an existing AccuSQL database on the SQL Server.  If you have not done this already, then start by checking the “Creating your SQL Server Database” blog.

There are more than one way to connect the AccuSQL or Plugin SQL software its SQL Server database. First lets take a look how to do that using AccuSQL or Plug-in software itself. Here are the steps:

1)  Launch AccuSQL software and log in as a root administrator.

2) In the bottom left area of AccuSQL, click on the Expanded View radio button.

3) Now access the Database --> Database Location screen and click the SQL Server radio button and click the Next button.

4) Select “SQL Server” option and click “Next”.

5) In the Application Share Path area specify a shared network folder that everyone has full access rights (Full control, read, write, modify).

6) In the SQL server connection info area, enter your server name, authentication mode (Windows authentication is preferred but SQL server authentication needs less expertise), and if authentication is SQL server mode your username and password – limited account’s username and password if you opted to create it.

7) In the Database drop-down menu, select the database you created earlier. Tip: After entering server, username, password information click on Database drop down’s ‘down arrow’. If it displays a list populated with your database in it then that verifies that you have a valid connection to the SQL Server database.

8) Now press the Test Connection button. If all is well, you will get a "Test connection succeeded" message. If not, you will get an error message. Verify all the information is correct and retest until you get a success message.

9) Press the Save button and then press Close Form.

10) Now press the main “Exit AccuSQL” button to close the application.

11) When you re-launch AccuSQL, you will be connected to the SQL Server database. Congratulations!

A second way to connect

The second way is easier and faster.

1. Go to your application folder using windows explorer (right click application shortcut, select properties and then “Find target”. In Windows 7 you can also right click and directly select “Open file location”). The application folder is typically “c:\Program Files\AccuSQL”.

2. Locate “SetDataPath.exe” file and run it by double clicking it.

3. You would see the same user interface that you get when you use Database -> Database Location in AccuSQL.

4. Fill in the information as explained above and click “Save”.

Yet a third way to connect!

The third way doesn’t actually create and save the connection information as the previous two above did. Instead it creates an encrypted connection information file in XML format that you can copy and use on multiple computers. This is especially useful to create the connection information on one computer and then deliver it to multiple Plug In computers without manually entering the information for each computer. Here is how you do it:

1) Go to your application folder using windows explorer (right click application shortcut, select properties and then “Find target”. In Windows 7 you can also right click and directly select “Open file location”). The application folder is typically “c:\Program Files\AccuSQL”.

2) Locate “CLDataPath.exe” file and run it by double clicking.

3) You would see the same user interface that you get when you use Database -> Database Location in AccuSQL.

4) Fill in the information and click “Save”.

5) You will see a ‘Save as’ dialog box prefilled with “DbCon.XML”. Change the location if you need to and save. This will create a file named “DBCON.XML”. Important: Do NOT rename that file.

6) Copy this file to a USB stick or network drive which you can access from other computers (you may also want to copy “DbConnector.exe” file from your application folder. This file is normally installed on initial installation and already exists on other computers but you may have a later version on your application folder. It is recommended that you copy and use the one from your application folder).

7) Go to any of the client computers and copy “DbCon.xml” file to application folder (and “DbConnector.exe” as well). Important: Copy the file(s) into application folder and not into any other folder.

8) After the file(s) are copied, double click dbconnector.exe to run it. It would create the necessary

Technorati Tags: AccuSQL,Connecting

registration for connecting to database. You are done. Repeat steps 7 and 8 on other computers.

Creating your SQL Server Database

 

Technorati Tags: AccuSQL,SQL Server,Database Setup

If you are an AccuSQL user, your first step after installing the software is to either create a new AccuSQL database in your Microsoft SQL Server or transfer an existing AccuTrack database to the SQL Server.  Either way, the procedure is the same with the only difference is that with the former you would transfer a blank AccuTrack database instead of one with your data.

The following are the steps to transfer an AccuTrack database to MSSQL Server using AccuSQL. These steps assume that:

1. You already setup MS SQL Server 2005/2008 (standard, enterprise, or Express) with TCP and/or Named Pipes and you allow for remote connections to this server.

2. You have an "sa" or equivalent account that you can use for the data transfer process (you can use the preferred Windows Authentication as well).

3. You have the SQL Server 2005 OLE DB Provider installed (check tips section for more information).

Database Creation Steps

1) Launch AccuSQL and log in as a root administrator.

2) In the bottom left area of AccuSQL, click on the Expanded View radio button.

3) Click the Database --> Transfer database to SQL server option.

4) In the Native data path to transfer area, click the Select Path button.

5) In the Data Folder Path pop-up screen, navigate to the database you would like to transfer and press OK. NOTE: You can either transfer an existing AccuTrack version 7,8, 9, 10 or 11 database or you can transfer a blank database (e.g. C:\Program Files\AccuSQL11\AccuData).

6) In the SQL server connection info area, enter the SQL Server name and authentication mode (Windows Authentication or SQL server authentication with username and password). NOTE: The account needs to have ‘Create’ and ‘Alter’ permissions. Preferably use windows authentication. If you are using SQL Server Authentication then built-in “sa” account is the preferred account (in SQL Express installations please note that “sa” account is disabled by default, you may need to enable it).

7) In the Database field, type the name for the database you are creating. The Owner field is optional, but typically would be dbo.

8) Now press the Test Connection button. If all is well, you will get a "Test connection succeeded" message. If not, you will get an error message. Verify all the information is correct and retest until you get a success message.

9) Optionally check the “Create a public account for this database” and fill in public account username and password. The privileged account would be used during creating and transferring the database while the limited public account would be used to access data during daily operations of the software.

10) Press the Transfer Data button. You will get a "Transferring data to SQL Server" message and AccuSQL will initiate the database creation in SQL Server. Depending on your existing version, AccuSQL may first initiate a data conversion routine and then do the transferring to SQL server. In that case, AccuSQL does its work in a temporary location that it creates and doesn’t directly modify the data in the location you pointed to – in other words your data is left intact even if something goes wrong during conversion and transfer.

11) After the process completes, press Close Form in the Transfer database to SQL Server window.

12) You can transfer as many databases as you want but each database should have new unique name. Transfer operation doesn’t support transferring into an already existing database.

Congratulations, you have finished creating the AccuSQL database in the SQL Server!

Next you need to point AccuSQL installations to use the SQL Server database.  We will start with the installation you just used to create the AccuSQL database.

Setting AccuSQL to Use the MS SQL Database

1. Access the Database --> Database Location screen and click the SQL Server radio button and then click the Next button.

2. In the Application Share Path area specify a shared network folder that everyone have full access rights (Full control, read, write, modify). AccuSQL will use this path to create shared folders that all AccuSQL installations sharing the same database needs.  For example, if you set up the software with student or tutor photos, AccuSQL will save a copy of the photos there. 

NOTE: Each AccuSQL database you create in your MS SQL Server needs its own Share Path Area.  However, you can put all shared path folders on the same server.  For example:

• Center 1 Application Shared path: Y:\SharedFiles\AccuSQL_Shared\Center1
• Center 2 Application Shared path: Y:\SharedFiles\AccuSQL_Shared\Center2
• Center 3 Application Shared path: Y:\SharedFiles\AccuSQL_Shared\Center3

3. In the SQL server connection info area, enter your server’s name, authentication mode (Windows authentication is preferred but SQL server authentication needs less expertise), and if authentication is SQL server mode your username and password – limited account’s username and password if you opted to create it.

4. In the Database drop-down menu, select the database you created during transfer. Tip: After entering server, username, password information click on Database drop down’s ‘down arrow’. If it comes up with a list populated with your database in it then it verifies that you have a valid connection.

5. Now press the Test Connection button. If all is well, you will get a "Test connection succeeded" message. If not, you will get an error message. Verify all the information is correct and retest until you get a success message.

6. Press the Save button and then press Close Form.

7. Now press the main “Exit AccuSQL” button to shutdown the application.

8. When you relaunch AccuSQL, you will be connected to the SQL Server database. Congratulations!

Troubleshooting Tips

If you are sure you have the correct credentials but are having trouble with the transfer, there are a few things you should check.

• You may need to install the SQL Server native connectivity client. To do so, double-click the sqlncli.msi (or sqlncli64.msi if your OS is 64 bits) installer in the <ROOT>\program files\Accu11Sql\ directory of your installation CD (the exact location on CD might be different due to rearranging the distribution files after writing of this document). You can use the default installation options.
• Make sure you have SQL Server correctly setup with TCP and Named Pipes and that you allow for remote connections. You can access the ConfiguringAccuTrackSQL.doc document in the <ROOT>\program files\Accu10Sql\Manuals directory of your installation CD for detailed information. Also for troubleshooting the connection you may refer to this information from Microsoft:

        http://support.microsoft.com/kb/914277

• You may have an outdated native file set. These files are created automatically when AccuSQL is first opened. To recreate the native files, exit AccuSQL then in Windows Explorer, navigate to the C:\Documents and Settings\All Users\Documents\accutrack directory and delete any files and/or directories located under it. Typically the temp directories start with an "i3" folder name. After deleting, restart AccuSQL and the temp files will be regenerated and you can proceed with the transfer steps above.
• Finally, if you still are having trouble, just contact AccuSQL support and they will be happy to assist.

Welcome to AccuTrack's Ideas Blog!

We created this site to help AccuTrack and AccuSQL users exchange usage ideas.  We are constantly impressed by the creativity of these users and wanted to give them a medium for sharing these ideas with other software users. 

Use this blog to share:
1) Software usage tips that you were glad to discover.
2) Implementation ideas on how you use the software.
3) How you use the reports to enhance your services.
4) Solutions you came up with for challenges.
5) How you trained your staff and students on using the software.
6) Anything elseo of interest to other users!

So what are you waiting for?  Start blogging :-)