Kick off Stored Procedure to run Nightly?

I have a stored procedure that updates a table. I want that table to be updated nightly. I looked into the DTS package but it seems a bit difficult. Is there some other schedule in Enterprise that I can kick off my stored proc without having to build a DTS package?

In DTS, they ask for all of these symbols and want the query, but my code is already in stored proc form.

Help?What you need to do is use SQL Agent. You can schedule the job to run nightly at your prferred time. When you create a new SQL Agent job, you then ad a job step. In this job step you specify the SQL command you want to run. I'm assuming you are using SQL Server 2000 since you refer to DTS. Using SQL Server Enterprise manager, expand the SQL Server you are working with, then expand the 'Managment' tree, then expand 'SQL Agent' tree and right click on Jobs and choose New Job... The tabs here should be self explanatory.|||Ok I see where the job steps are. In the command window, I can type in my proc name sp_DailyOrders and it will know how to kick it off?|||In the step name tab, just give the step a name like 'Execute procedure', make sure the database is the correct database where your procedure lives, and type in exec sp_your_proc_name in the command section. Then move to the Scheduile tab and click the button to add a new schedule and choose the frequency etc that you want the job to tun. If you have operators setup on your server, then you can use the notification tab to have the job email you when it completes or fails.|||Thanks soooooooo much! You are awesome!

Josql

Keeping disconnected servers in sync

Hello all.
Because I work at home quite often, I keep updated copies of the databases I
normally work on in the office on my servers at home. This is accomplished
by backing up databases to DAT and restoring when I get home.
These databases have gotten quite large, as databases are wont to do and
while I can still get them on a tape, the backup and restore time is too
much to live with.
Does anyone know of a method to keep these two disconnected servers and/or
databases in sync without a lengthy backup and restore?
Thanks.
David
--
ROT13 my email address to reply directly: qnircra@.gpd.argDavid
Perhaps you want to compress your backup with WINZIP or WINRAR.
"David Pendleton" <qnircra@.gpd.arg> wrote in message
news:#G4Cb6TjDHA.1696@.TK2MSFTNGP12.phx.gbl...
> Hello all.
> Because I work at home quite often, I keep updated copies of the databases
I
> normally work on in the office on my servers at home. This is accomplished
> by backing up databases to DAT and restoring when I get home.
> These databases have gotten quite large, as databases are wont to do and
> while I can still get them on a tape, the backup and restore time is too
> much to live with.
> Does anyone know of a method to keep these two disconnected servers and/or
> databases in sync without a lengthy backup and restore?
> Thanks.
> David
> --
> ROT13 my email address to reply directly: qnircra@.gpd.arg
>|||I'm using SQL Backup, so compression is not an option. Backing up 18GB to a
disk device, compressing, and backing up to tape takes longer still.
Thanks anyway.
--
ROT13 my email address to reply: qnircra@.gpd.arg
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#iAGMSWjDHA.2500@.TK2MSFTNGP10.phx.gbl...
> David
> Perhaps you want to compress your backup with WINZIP or WINRAR.
>
>
> "David Pendleton" <qnircra@.gpd.arg> wrote in message
> news:#G4Cb6TjDHA.1696@.TK2MSFTNGP12.phx.gbl...
> > Hello all.
> >
> > Because I work at home quite often, I keep updated copies of the
databases
> I
> > normally work on in the office on my servers at home. This is
accomplished
> > by backing up databases to DAT and restoring when I get home.
> >
> > These databases have gotten quite large, as databases are wont to do and
> > while I can still get them on a tape, the backup and restore time is too
> > much to live with.
> >
> > Does anyone know of a method to keep these two disconnected servers
and/or
> > databases in sync without a lengthy backup and restore?
> >
> > Thanks.
> >
> > David
> > --
> > ROT13 my email address to reply directly: qnircra@.gpd.arg
> >
> >
>|||David
I don't agree.
> I'm using SQL Backup, so compression is not an option.
What did you mean?
I backup my 15 GB database with compression. You need to write batch file
and place it in system directory.
If you want I can show the example,let me know.
> Backing up 18GB to a
> disk device, compressing, and backing up to tape takes longer still.
After compression I delete .bak file ,so I keep on a device .RAR file of 2
MB.
Regarding to the time belive me it is almost the same.
"David Pendleton" <qnircra@.gpd.arg> wrote in message
news:e9DLmJhjDHA.688@.TK2MSFTNGP10.phx.gbl...
> I'm using SQL Backup, so compression is not an option. Backing up 18GB to
a
> disk device, compressing, and backing up to tape takes longer still.
> Thanks anyway.
> --
> ROT13 my email address to reply: qnircra@.gpd.arg
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#iAGMSWjDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > David
> > Perhaps you want to compress your backup with WINZIP or WINRAR.
> >
> >
> >
> >
> > "David Pendleton" <qnircra@.gpd.arg> wrote in message
> > news:#G4Cb6TjDHA.1696@.TK2MSFTNGP12.phx.gbl...
> > > Hello all.
> > >
> > > Because I work at home quite often, I keep updated copies of the
> databases
> > I
> > > normally work on in the office on my servers at home. This is
> accomplished
> > > by backing up databases to DAT and restoring when I get home.
> > >
> > > These databases have gotten quite large, as databases are wont to do
and
> > > while I can still get them on a tape, the backup and restore time is
too
> > > much to live with.
> > >
> > > Does anyone know of a method to keep these two disconnected servers
> and/or
> > > databases in sync without a lengthy backup and restore?
> > >
> > > Thanks.
> > >
> > > David
> > > --
> > > ROT13 my email address to reply directly: qnircra@.gpd.arg
> > >
> > >
> >
> >
>

Keep remote SQL data tables updated

Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain.

Any ideas on how to keep the data updated on the website?

How updated do you want?|||Within an hour perhaps. I have a remotely hosted sql server at discountasp.net and a sql server in my domain. The hosting provider does not allow dts or replication.|||

I would say create a web service at your hosting provider. Then create a service you can run on your local sql 2000 machine that does the pull/push at intervals. Just make sure that all your important tables have auto-incremented id fields so you know what you are missing since last pull/push.

It sounds like the hosting provider is the master table for orders, and your local sql server is the master for products. Create a trigger on the ISP's order table that records all insert/delete/updates to that table to an auditOrders table that contains an id field (Autoincrement int), a changetype field (char) constrained to 'I','U', or 'D', a ChangeTime field (datetime with default of GetUTCDate()), and a field for every field in Orders. Make a similiar trigger on your local products table going into auditProducts. Then create a webservice at the hosting provider that has 3 methods:

public function GetNewOrders(LastId as integer) As Dataset

public function GetLatestProductChangeID() as integer

public sub SaveProductChanges(ds as dataset)

Make a copy of auditProducts and auditOrders at the other location. To pull changes to orders, select the highest id from your local auditOrders table. Then call GetNewOrders with that id. Take the dataset and record by record (It should be in id order) start a transaction, perform the Insert/Update/Delete on the local table, and then insert the record into the local audit table, and commit the transaction. To push Products, call the GetLatestProductChangeID, then create a dataset containing all the records from the local auditProducts table where the id is larger than the id you got from the webmethod, and call SaveProductChanges with the dataset.

Alternatively from having to do the transaction, and manually record the record into the audit table, you can also copy the triggers from the other side, and it (SHOULD) recreate the record, all except for the ChangeTime which will be different obviously.

|||

Take a look at my Remote SQL data provider, WebSql Data Provider, for secure, efficent access to SQL Server over HTTP, as simple as using the native SQL provider in System.Data.SqlClient namespace.