"Tav" wrote:

>
> Vito Corleone wrote:
> > I had created a view on a sql server 2000 (BigDaddy)
> > select * from employee
> > and I want to schedule this view to be executed daily at 2am.
> > The results must be saved on a workstation, AAA1009.
> > How do I schedule so sql server can execute the view and save the results
> > over network?
> >
> > Your help is always highly appreciated.
> > Vito Corleone
>
> Vito,
>
> Create an audit table, e.g.:
>
> CREATE TABLE EmployeeLog(
>     EmployeeLogID        int        IDENTITY(1,1)        NOT NULL
>         CONSTRAINT pk_EmployeeLogID PRIMARY KEY NONCLUSTERED,
>     EmployeeColumns...
>     LogDate            datetime    DEFAULT(GETDATE())    NOT NULL
> ) ON [PRIMARY]
>
> Create an audit stored proc., e.g.:
>
> CREATE PROCEDURE dbo.spAuditEmployees
>
> AS
>
> INSERT INTO EmployeeLog(EmployeeColumnscolumns...)
>     SELECT * FROM Employee
>
> RETURN 1
> GO
>
> Create a job to run at 2pm every night that runs the following T-SQL:
>
> EXEC spAuditEmployees
>
> Write an app. on your workstation to read from the audit log.
>
> Regards,
>
>     -Tav.-
>
> Tavis Pitt
>
>

"Erland Sommarskog" wrote:

> Vito Corleone (Vito Corle***@discussions.microsoft.com) writes:
> > I had created a view on a sql server 2000 (BigDaddy)
> > select * from employee
> > and I want to schedule this view to be executed daily at 2am.
> > The results must be saved on a workstation, AAA1009.
> > How do I schedule so sql server can execute the view and save the results
> > over network?
>
> First of all, you cannot execute a view. But you can run a query on it.
>
> My strong recommendation is that you set up a job from the Windows scheduler
> on the workstation in question. This job would run OSQL or a small
> application program to get the data.
>
> You could set up a job from SQL Server Agent on the SQL Server box and
> under the advanced tab specify that the output file should be on the
> workstation. However, this requires that the service account for SQL
> Server Agent has write permission on the directory on the workstation
> in question. There is also a risk that the job would fail, if the SQL Server
> machine cannot reach the workstation.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>