SQL Server Performance

Link Excel file with MSSQL table or vice versa

Discussion in 'SQL Server 2005 General DBA Questions' started by bsethi24, Dec 31, 2009.

  1. bsethi24 New Member

    Dear all,
    Hello ! I am in a need to link an Excel file with a MSSQL table or vice versa.
    It means that when a user make some changes or insert new record(s) in Excel file then the same will automatically get reflected in MSSQL table (which has structrue same as Excel file) or when a user make some changes or insert new record(s) in MSSQL table then the same will automatically get reflected in Excel file.
    I don't want to use DTS or OpenDataSource for this working as both have either manual or scheduled working.
    Is there any way to do this on real time basis?
    Help me to solve this.
    Thanks
  2. arunyadav Member

    Hey,Kindly refer to the below links...http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=135
  3. MohammedU New Member

    You may follow the articles Arun mentioned but what happens when one user is writing (opened file) other user will get the error because I believe Excel will be locked.
    You are looking two transactional replication from SQL to Excel and I don't think there is any solution out there to satify your criteria...
  4. Madhivanan Moderator

    [quote user="bsethi24"]
    Dear all,
    Hello ! I am in a need to link an Excel file with a MSSQL table or vice versa.
    It means that when a user make some changes or insert new record(s) in Excel file then the same will automatically get reflected in MSSQL table (which has structrue same as Excel file) or when a user make some changes or insert new record(s) in MSSQL table then the same will automatically get reflected in Excel file.
    I don't want to use DTS or OpenDataSource for this working as both have either manual or scheduled working.
    Is there any way to do this on real time basis?
    Help me to solve this.
    Thanks
    [/quote]
    What is wrong with OPEROWSET and scheduling it periodically?

Share This Page