SQL Server Performance

Running several update queries & generate an excel file

Discussion in 'SQL Server DTS-Related Questions' started by GAR, Aug 4, 2007.

  1. GAR New Member

    Hello everyone..
    actually am new to DTS..
    my question is, how to run several update queries on a database(can also be on 2 Databases) and generating an excel file using DTS
    What am doing now, is that i have a small VBA application that run the update queries and generate the excel file, but that takes a lot time in access.
    I believe that using DTS will be much faster.
    any suggestion about a book or a web site to learn DTS
    plz help
    thx in advance
  2. satya Moderator

    Best Practice is to perform the udpate using stored procedures and call them within the DTS workflow, that will save lot of server resources as and when that update query runs from DTS the optimizer will nto be able to use best plan to execute for optimum performance.
    Also do not use office automation on the server, you may need to install office tools in this case. You could design DTS package using Enterprise Manager and schedule the same as a job or call them from the web page or application.
    SQLDTS website is best to go for such snippets in additon to the updated books online for SQL Server.
  3. GAR New Member

    hii satya
    thanks for the reply
    an example will be highly appreciated on your part
  4. satya Moderator

    What am doing now, is that i have a small VBA application that run the update queries and generate the excel file, but that takes a lot time in access.
    Have you tested the same using DTS in SQL SErver?
  5. Adriaan New Member

    You could also use a pass-through query in Access to let SQL Server do the updates. This will be your biggest time saver. Then export as usual.
    You always have the option to do this from DTS, but if your users need to be able to initiate the procedure then Access seems like an easier option.
  6. GAR New Member

    ya it work using dts
  7. satya Moderator

    SO does it fail when running as a Job?
    Also you could go thru the link I gave above for such code examples in thsi regard.
  8. GAR New Member

    I run the DTS directly on the sql server and it works fine
  9. satya Moderator

    If its failing with the scheduled jobs then ensure to check permissions for SQLAgent account in this regard, also having enabled DTS package log will give information about the failure.

Share This Page