Wednesday 22 September 2010

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:
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.

15 comments:

  1. 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?

    ReplyDelete
    Replies
    1. Is the linked server in the linked server branch on the left hand panel?
      You cannot expand catalogue. Try to query your linked db via openquery

      Delete
  2. 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

    ReplyDelete
    Replies
    1. I understand you had problem. Please follow carefully the instruction.

      Delete
    2. ERROR: DSN architecture mismatch between the controller and the application

      provider: Microsoft OLE DB Provider for ODBC Drivers
      Product name: DSN create (ccobol)
      DataSource: DSN create (ccobol)

      sorry: I do not speak English.

      Delete
    3. There's a DSN error. I cannot help you from here. As said try to follow the above instruction.

      Delete
    4. you can send me a picture of your linked server? my email is pablomunoz1979@hotmail.com

      Delete
    5. I can't because the server is owned by a private company.
      Please follow the instructions on the blog. I assure you it's working.

      Delete
    6. question.? you windows 7 64 bit?

      Delete
    7. Windows server 2003. No demo.
      Really can't help you.

      Delete
    8. Encontre mi solucion.

      si 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

      Delete
  3. 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.

    ReplyDelete
    Replies
    1. As far as I know, you need to pay for it. Sorry the link is not working anymore.

      Delete
    2. Any idea about where to buy it?

      Delete

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.