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?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.|||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.|||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.|||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.