Transfer data from sql server to DBISAM database

  • hisham2778

    SSC Veteran

    Points: 260

    Hi,

    I have two software systems as the following:

    1) Main software, Building on Sql Server 2005.

    2) Second Software, Building on DBISAM version 4.1

    On main software (SQL server), I have table including the name of staff, sex, nationality, age ... etc, and I want to transfer any change on this table (insert\update\delete) to same table was built on second software (DBISAM). I mean that I need online transfer.

    How can I do that?

    I am new in SQL server and I need your help.

    Regards

    Hesham

  • Robert-378556

    SSCertifiable

    Points: 5542

    In sql server create insert/update/delete triggers that insert primary keys and operation kind of changed records into a log table. Create and application that reads this log table joined to original table and applies changes to dbisam tables and make it a scheduled task.

    I believe this can be done with integration services, if you have a common provider (odbc, oledb) for dbisam.

  • hisham2778

    SSC Veteran

    Points: 260

    Hi,

    Thank you Robert.

    How I can connect to new database during SQL server? I mean, what is the syntax to do that ?

    Regards

  • Robert-378556

    SSCertifiable

    Points: 5542

    You should rephrase your question.

    I really don't know nor are willing to guess what are you asking for.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    I'm not familiar with DBISAM. Can you create a linked server from the SQL Server to the DBISAM? If so, you may be able to use triggers on the staff table to copy changes over.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Key DBA

    SSCertifiable

    Points: 6029

    hisham2778,

    I am not familiar with DBISAM either, and Googling for information about a linked server against a DBISAM system didn't come up with any decent examples.

    In order to be able to allow SQL Server to "see" the DBISAM system, you will need to configure several connection components.

    1) Install DBISAM ODBC Drivers on the SQL Server machine.

    2) Create an ODBC DSN (I recommend making a System DSN), using DBISAM ODBC drivers on the SQL Server database machine.

    How To Create a System Data Source Name (DSN) in Windows 2000

    http://support.microsoft.com/kb/300596

    3) Create an ODBC Linked Server that points to your ODBC DSN.

    SQL Server 2005 Books Online (September 2007)

    Linking Servers

    http://msdn2.microsoft.com/en-us/library/ms188279.aspx

    4) From there you can use either OPENQUERY or 4 part naming to access the DBISAM system.

    SQL Server 2005 Books Online (September 2007)

    OPENQUERY (Transact-SQL)

    http://msdn2.microsoft.com/en-us/library/ms188427.aspx

    SQL Server Four-part naming

    http://www.mssqltips.com/tip.asp?tip=1095

    --

    This process will allow you to select/insert/update/delete data from SQL Server to/from the DBISAM system.

    As both Robert and GilaMonster have mentioned, you would be able to use Trigger logic, based on this connection process.

    Hope This Helps,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • hisham2778

    SSC Veteran

    Points: 260

    Thank you Damon, I will try to do that

  • Key DBA

    SSCertifiable

    Points: 6029

    hisham2778,

    Do keep us posted on your progress. If you are successful in getting your connection to work with your DBISAM system, please post your notes and findings so that others can benefit from our collective work.

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply