Export to New Roads SQL Server

Please notify support@rtvision.com to update the export settings for the OneOffice and Timecard exports for New Roads for SQL Server. Once you complete the tasks below, you will need to have an Administrator update the NewRoads connection in Timecard and also OneOffice.

The export to New Roads SQL Server will require a SQL login. The SQL Server will need "SQL Server and Windows Authentication mode" enabled. Your IT administrator can use the following SQL script to create a SQL user that only has access to the import table(s). The script will create a SQL user named RtVision with a password determined by your IT administrator, just replace "password-here" where the script sets the password (...PASSWORD=N'password-here'). To run the script below, open SQL Server Management Studio from the New Roads SQL Server, click the "Connect" button when prompted, select File => New => Query, and paste the script in the query window. Then run the script by clicking the "Execute" button or pressing the F5 key.

USE [NRSDTA]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = N'RtVision')
BEGIN
CREATE LOGIN [RtVision] WITH PASSWORD=N'password-here', DEFAULT_DATABASE=[NRSDTA], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sp_grantdbaccess N'RtVision', N'RtVision'
END
GO

Skip to the Timecard section if not exporting Contract Payments from OneOffice to New Roads.

For OneOffice:

Using SQL Server Management Studio, as described above, run the following script granting permission to the contract payment import table.

USE [NRSDTA]
GO
GRANT SELECT, INSERT, DELETE ON [IMPVOUCHER] TO [RtVision]
GO

For any Computer Using the API Integration, Create the DSN for the New Roads SQL Server

• From each client computer using the API integration, go to Control Panel => Administrative Tools => Data Sources (ODBC).
• Select the System DSN tab at the top
• Delete any existing DSN for New Roads that is still using the iSeries driver
• Click the Add button.
  • Select the SQL Server driver from the list (bottom).  Be sure to use 64-bit. platform; then click the Next button.
  • Enter 'NewRoads', for example, for the name and enter the IP Address of the SQL Server where the New Roads database resides.  Do not enter any spaces or special characters in the name. Click the Next button.
  • Select the second option to use SQL Server authentication.
    • Enter the SQL login name and password specified above.
    • Click the Client Configuration button and verify that TCP/IP is the selected Network Library. Click the OK button and then click the Next button.
  • Check the box at the top to change the default database and select the New Roads database from the list. Click the next button. Click the Finish button.
  • Click the Test Data Source button to verify the DSN. Then click the OK button three times to exit all screens.

For Timecard:

Using SQL Server Management Studio, as described above, run the following script granting permission to the timecard import tables.

USE [NRSDTA]
GO
GRANT SELECT, INSERT, DELETE ON [IMPEMPTIME] TO [RtVision]
GO
GRANT SELECT, INSERT, DELETE ON [IMPEQPTIME] TO [RtVision]
GO
GRANT SELECT, INSERT, DELETE ON [IMPINVWITH] TO [RtVision]

Update the connection setting in Timecard:

1. Go to Administration
2. Click Admin. Util.> Connections
3. Change the Type to Microsoft SQL Server
4. Update the IP address
5. Change the Username to RtVision
6. Change the Password to match what was set up above
7. Click Save changes to connections

You can test by exporting data to New Roads, and then deleting the batch.

'