SQL Server: connect to an Acucobol database with AcuODBC
In a SQL Server environment you can connect to external databases using ODBC. You can actually connect to any external database, but I will explain how to use AcuODBC to link an Acucobol database.
After installing AcuODBC, you need to create a new System or User DSN with the ODBC Data Source Administrator, using the correct parameters like the vision and data directories. What we basically need, in order to link the db to SQL Server, is the name of the new DSN.
After creating the DSN, open SQL Server Management Studio and, using the navigation tree on the left, expand Server Objects and then Linked Servers. Select Linked Servers and right click it. Choose New Linked Server and a properties menu will appear. Insert the name you want to give to the linked server, choose 'Microsoft OLE DB Provider for ODBC Drivers' from the provider drop down menu, insert the DSN you created into Product name and Data source. And... voilà... you are done!
You will now see the linked server in the navigation menu of Management Studio (just under the Provider folder).
To test the connection and if everything is working properly, we can use a simple query:
Sometimes linked servers are not as fast as expected. When you query a large amount of data, you will notice that the overall performance is not very exciting. In my experience, it is better to create scheduled insert or update queries to import tables directly into SQL Server, as explained in Schedule SQL queries over night. When imported, the tables are not linked anymore and the performance increases a lot. On top of that, you can actually use the tables as SQL Server tables, creating indexes and relations as needed.
After installing AcuODBC, you need to create a new System or User DSN with the ODBC Data Source Administrator, using the correct parameters like the vision and data directories. What we basically need, in order to link the db to SQL Server, is the name of the new DSN.
After creating the DSN, open SQL Server Management Studio and, using the navigation tree on the left, expand Server Objects and then Linked Servers. Select Linked Servers and right click it. Choose New Linked Server and a properties menu will appear. Insert the name you want to give to the linked server, choose 'Microsoft OLE DB Provider for ODBC Drivers' from the provider drop down menu, insert the DSN you created into Product name and Data source. And... voilà... you are done!
You will now see the linked server in the navigation menu of Management Studio (just under the Provider folder).
To test the connection and if everything is working properly, we can use a simple query:
SELECT * FROM OPENQUERY (NameOfTheLinkedServer, 'SELECT * FROM TableName')Sometimes linked servers are not as fast as expected. When you query a large amount of data, you will notice that the overall performance is not very exciting. In my experience, it is better to create scheduled insert or update queries to import tables directly into SQL Server, as explained in Schedule SQL queries over night. When imported, the tables are not linked anymore and the performance increases a lot. On top of that, you can actually use the tables as SQL Server tables, creating indexes and relations as needed.