Results 1 to 5 of 5

Thread: Action Queries

  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: Action Queries

    Two questions.

    1. How do I create action queries in SQL Server? From Enterprise Manager -> view I can create, run but NOT save the query.

    2. How do I access an SQL server query from access like i would an Access query? Do i have to make a call to a stored procedure or something like that?

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    If you like to reuse a query you can create it in Query Analyzer and then save it to a file. Using Query Analyzer you can then open the file and run the query. You can use any text editor to create a query and save it. Also by using a stored procedure you are saving the SQL to the database so it can be used at any time just by calling it. Depending on how you setup permission you can let other users call your stored procedure.
    MCDBA

  3. #3
    Join Date
    Mar 2002
    Posts
    192
    I'm a little new at this so bear with me... OK.. I follow you up to the point of the stored procedure. Say i save that file as Test.. Is test.sql the stored procedure? I thought stored procedures were only available inside Enterprise manager. Lastly Once the file containing the sql statement is created, how do i reference the file from enterprise manager? i can figure out how to call a stored procedure in the enterprise manager from Access VBA code.

  4. #4
    Join Date
    Jul 2002
    Location
    Luxembourg
    Posts
    6
    Access_Dude,
    Action Queries are a bit different in SQL Server to Access. In access, it all gets saved down in the database. In SQL Server, you've got a choice: Either save it down as a .sql file (in effect a text file - rename the extension to .txt, will open it in Notepad, no problems). This allows you to have a permanent copy which you can open from other apps without needing SQL Server - useful for saving down scripts which you want to run again but don't want to keep in the database.
    Or, create a Stored Procedure in SQL Server. This will save the SQL into the database itself. As an example:

    select Name, Postcode
    from Customer
    where CustomerID = 123

    ... can become a stored proc ('action query')...

    create proc spGetCustomerNameAndPostcode
    as
    select Name, Postcode
    from Customer
    where CustomerID = 123

    ... it can then be simply called from an app, or from within SQL Server, using:
    exec spGetCustomerNameAndPostcode

    (Note that the exec isn't always needed, depends on what you're doing)

    Hope this helps
    Jon.

  5. #5
    Join Date
    Jul 2002
    Location
    Luxembourg
    Posts
    6
    Access_Dude,
    Action Queries are a bit different in SQL Server to Access. In access, it all gets saved down in the database. In SQL Server, you've got a choice: Either save it down as a .sql file (in effect a text file - rename the extension to .txt, will open it in Notepad, no problems). This allows you to have a permanent copy which you can open from other apps without needing SQL Server - useful for saving down scripts which you want to run again but don't want to keep in the database.
    Or, create a Stored Procedure in SQL Server. This will save the SQL into the database itself. As an example:

    select Name, Postcode
    from Customer
    where CustomerID = 123

    ... can become a stored proc ('action query')...

    create proc spGetCustomerNameAndPostcode
    as
    select Name, Postcode
    from Customer
    where CustomerID = 123

    ... it can then be simply called from an app, or from within SQL Server, using:
    exec spGetCustomerNameAndPostcode

    (Note that the exec isn't always needed, depends on what you're doing)

    Hope this helps
    Jon.

Posting Permissions

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