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.
    Thanks
    UPDATE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',
    'Data Source=C:WorkData.xls; extended Properties=Excel 8.0')...AllBonds$
    set Bid_Price =
    Ask_Price =
    where
    Security_ID =
    The result is coming from this sql:
    select
    sec.Security_ID--,
    sec.Security_Name,
    p.Bid_Price,
    p.Ask_Price
    from
    tblPrices p right join tblSecurities sec on sec.Security_ID = p.Security_ID
    where
    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
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Share This Page