Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    Brazil
    Posts
    4

    Unanswered: Exporting to MS Access from Query Analyser

    Hi folks

    Ive a simple doubt (I hope) ... is it possible export the results from a simple query to a ms access file using just the query analyser??? Im using SQL Server 2000 and MS Access XP

    thanks for your help!!!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not use DTS?

    Or...why not set Access up as a linked Server...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Location
    Brazil
    Posts
    4
    Thanks for your answer Brett. Ill put a terminal with query analyser and write the script for user, he will just change some parameters.

    I dont want the final user using enterprise manager

    I tried to link tables from MS Access and run this query (is more friendly to final users), but exists a big performance diference.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you probably tried to run it in the jet engine in access

    define the query as a "pass through" query, and it will run in sql server

    Query > SQL Specific > Pass-Through

    the performance will be the same as in sql server because, it's, um, running in sql server

    then the results will be brought straight into access

    "no fuss, no muss, no DTS"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by r937
    you probably tried to run it in the jet engine in access

    define the query as a "pass through" query, and it will run in sql server

    Query > SQL Specific > Pass-Through

    the performance will be the same as in sql server because, it's, um, running in sql server

    then the results will be brought straight into access

    "no fuss, no muss, no DTS"
    I think he's going down to Access...not up to SQL Server....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "down to access"? as in
    export the results from a simple query to a ms access file
    what is it about an access pass-through query that is incompatible with this?

    a pass-through query runs in sql server and returns the results to access

    that is what the original poster seemed to be trying to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nothing.....Just got all turned around...

    Think that s/he needed to push to access...not pull from sql server....

    If it needs to be pushed, then what?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by Brett Kaiser
    If it needs to be pushed, then what?
    then the system architect needs a good talking to, push went out with the nineties (remember Pointcast?)

    if i'm a user, i do *not* want stuff inserted into my MDB when i'm not doing it

    okay, maybe DTS could push an excel file, and i might decide if i want to import it

    but pushing to MDB? is that even possible?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, well, I was think Access as a linked server....

    And what's replication (not that I use it) if not a push....granted their called subscribers...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2004
    Location
    Brazil
    Posts
    4
    Hi guys

    Ill explain my problem: Ive a table with 40 milion lines and the users needs to run some querys in this table (like choose a vendor, or date range, etc).

    The most friendly interface for them is MS Access or MS Excel, so I need to get this data from SQL but manipulate in one of these programs.

    R937: I tried to use Pass-Through in MS Access query and the results cames faster, but its slow comparing with results in query analyser interface (7 minutes x 1:20 minute).

    If I could export the results to MS Access or Excel file from query analyser, Ill working faster.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the only way reason that i can think of why a pass-through query is taking so much longer than running in sql server, is the time required to transfer the results -- how many rows is the query returning?

    there could also be other reasons, like a config option (i'm guessing) in sql server that lets QA or EM queries have plenty of cycles per second, whereas queries submitted in other ways (e.g. odbc) get low priority
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2004
    Location
    Brazil
    Posts
    4
    Back with 2200 lines in result.

    is it possible set the time required to transfer results?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no idea

    you need a DBA

    which i'm not, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •