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.