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:
SELECT * FROM OPENQUERY (NameOfTheLinkedServer, 'SELECT * FROM TableName')
Run it and you will get the data from the linked database.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.
first of all thanks for posting this. you are probably the only guy on internet who used acucobol with sql server. I am trying to follow your instructions but when trying to "test connection" management studio hangs. If I try to "expand cataloges", it keeps working for hours and then gives "no response" error. Any ideas?
ReplyDeleteIs the linked server in the linked server branch on the left hand panel?
DeleteYou cannot expand catalogue. Try to query your linked db via openquery
El proveedor OLE DB "MSDASQL" del servidor vinculado "cobol" devolvió el mensaje "[Microsoft][Administrador de controladores ODBC] La arquitectura del DSN especificado no coincide entre el controlador y la aplicación.". error 7303
ReplyDeleteI understand you had problem. Please follow carefully the instruction.
DeleteERROR: DSN architecture mismatch between the controller and the application
Deleteprovider: Microsoft OLE DB Provider for ODBC Drivers
Product name: DSN create (ccobol)
DataSource: DSN create (ccobol)
sorry: I do not speak English.
There's a DSN error. I cannot help you from here. As said try to follow the above instruction.
Deleteyou can send me a picture of your linked server? my email is pablomunoz1979@hotmail.com
DeleteI can't because the server is owned by a private company.
DeletePlease follow the instructions on the blog. I assure you it's working.
question.? you windows 7 64 bit?
DeleteWindows server 2003. No demo.
DeleteReally can't help you.
Encontre mi solucion.
Deletesi a alguien le sirve. tengo instalado
WINDOWS 8 SERVER 64 BIT
SQL SERVER 2008 32 BIT
ACUODBC 32 BIT. y funciona..
o puede ser :
WINDOWS 64 BIT
SQL SERVER 64 BIT
ACUODBC 64 BIT
TIENE QUE HABER COMPATIBILIDAD
Hi Thanks for this article. Do you need to buy AcuODBC or is it a free download. And where can you get it? The link in the article led to nowhere.
ReplyDeleteAs far as I know, you need to pay for it. Sorry the link is not working anymore.
DeleteAny idea about where to buy it?
DeleteI'm sorry, no.
Delete