SQL Server Performance

update opendatasource

Discussion in 'SQL Server 2005 General Developer Questions' started by arkiboys, Feb 4, 2008.

  1. arkiboys Member

    An excel file has columns such as :
    Security_ID, Security_Name, Bid_Price, Ask_Price
    If possible please let me know how to correct this query to update the spreadsheet from sql server 2005 data.
    UPDATE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',
    'Data Source=C:WorkData.xls; extended Properties=Excel 8.0')...AllBonds$
    set Bid_Price =
    Ask_Price =
    Security_ID =
    The result is coming from this sql:
    tblPrices p right join tblSecurities sec on sec.Security_ID = p.Security_ID
    convert(varchar(11), Price_Quote_Date) = dateadd(d, -4, convert(varchar(11), getdate()))
  2. Adriaan New Member

    Is that a question? I doubt that you can use UPDATE OPENDATASOURCE but perhaps it is perfectly valid syntax.
    If not, then create some VBA code to interface using the Excel libraries, and refer to that from your SQL 2005 project.
    The trick is to do this manually in Excel (connect to SQL Server, identify the Excel cells to be updated, and the new values from SQL) whilst recording your actions with the fabulous macro recorder in Excel. This recorder produces the full VBA syntax, which you can then include in your SQL 2005 object (although you'll need to initialize an Excel object, and fine-tune the code to work off that object).
  3. Madhivanan Moderator

    or somewhere in this topic you may find it

Share This Page