T-SQL Script: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

scheduling stored procedure

Hi,
I am trying to schedule a stored procedure using SQL server Agent and i get the following error:
DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]
please help
regards,
hirenI found this in SQL Books Online.

Is it appropriate, ie do you have computed columns etc ?

If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.|||Well what's it set to?

Do you have a trigger on the table?|||Hi,

No there are no triggers. I have found the solution.
The connection related setting for the SQL server needs to be checked for the same. I performed the following steps...
1. In enterprise manager, right clicked on SQL Server i was accessing.
2. Selected properties->Connections.
3. Under 'default connection option', selected 'Arithmetic abort'
4. Clicked OK and restarted the server.

Thanks and regards,
henry

Wednesday, March 28, 2012

Scheduling outside SRS

Hello All!
I have a requirement to set a schedule to run a report on the Tuesday
following the last Saturday of the month. It appears that this is not
possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
and to make it more complex, if the Tuesday in in the new month, quarter, or
year then the parameter should select the previous month, quarter, or year
not the default of current month, current quarter, or current year. I think
the last part can easily be handled by getting the parameter values from the
date of the last saturday of the month, which would always give you the
correct month for month end reporting no matter if the next Tues is in the
next month or not.
what I need is the syntax for defining the parameters and rendering the
report. I would also like to specify a network drive where it can be
archived, email a link to an audience, and save a snapshot in history.
Can someone please help me with this? If not all then parts would be
appreciated!!!
AnthonyI am in the same situation. Were you able to resolve this?
--
---
Yes, I searched first :)
"anthonysjo" wrote:
> Hello All!
> I have a requirement to set a schedule to run a report on the Tuesday
> following the last Saturday of the month. It appears that this is not
> possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
> and to make it more complex, if the Tuesday in in the new month, quarter, or
> year then the parameter should select the previous month, quarter, or year
> not the default of current month, current quarter, or current year. I think
> the last part can easily be handled by getting the parameter values from the
> date of the last saturday of the month, which would always give you the
> correct month for month end reporting no matter if the next Tues is in the
> next month or not.
> what I need is the syntax for defining the parameters and rendering the
> report. I would also like to specify a network drive where it can be
> archived, email a link to an audience, and save a snapshot in history.
> Can someone please help me with this? If not all then parts would be
> appreciated!!!
> Anthony
>|||Yes. Here is what you do. Create a subscription or a shared schedule but
set it to run only once and make it in the past. This will create a Schedule
ID in the database that you can reference later. Then open the Report Server
Database and return all rows for either the Schedules or Subcriptions table
depending on what you created. Copy the Schdule ID out of the table and make
note of the Event Type.
Now that you have this you can use the following code to fire the report
manually:
exec ReportServer.dbo.AddEvent
@.EventType='SharedSchedule', --Enter the event type here from the schedule
table in ReportServer Database
@.EventData='29C3FF88-D0D4-4B8A-A5D3-55DCBA8C215D' --Enter the subscription
ID here from the schedule table in ReportServer Database
I put this code inside the following stored procedure that is executed every
tuesday by a SQL job. If the @.myoutput date = Getdate then it executes the
code above otherwise it does nothing.
CREATE PROC dbo.MONTH_END_SCHEDULE as
--Declare variables
declare @.myoutputdate datetime
declare @.mydate datetime
declare @.minus int
declare @.TuesdayFound char(1)
declare @.subject varchar (255)
--set variables
set @.TuesdayFound='N'
--seed the date with the last day of the month
set
@.mydate=dateadd(dd,-1,convert(datetime,convert(varchar(2),datepart(mm,dateadd(mm,1,getdate())))+'/1/'+
convert(varchar(4),datepart(yy,dateadd(mm,1,getdate())))))
--a variable to backwards through the days of the month
set @.minus=0
WHILE @.TuesdayFound = 'N'
BEGIN
if datepart(dw,dateadd(dd,(@.minus*-1),@.mydate))=7 -- Find Last Saturday
BEGIN
set @.myoutputdate=dateadd(dd, 3,(dateadd(dd,(@.minus*-1),@.mydate))) --This
will add 3 days to the last Saturday
set @.TuesdayFound = 'Y'
END
set @.minus=@.minus+1
END
print @.myoutputdate
if datepart(dy,(getdate()))=datepart(dy,(@.myoutputdate))
BEGIN
exec ReportServer.dbo.AddEvent
@.EventType='SharedSchedule', --Enter the event type here from the schedule
table in ReportServer Database
@.EventData='29C3FF88-D0D4-4B8A-A5D3-55DCBA8C215D' --Enter the subscription
ID here from the schedule table in ReportServer Database
PRINT datename(mm, @.mydate)+ ' ' + datename(yyyy, @.mydate)+ ' Reports Fired '
SET @.subject = datename(mm, @.mydate)+ ' ' + datename(yyyy, @.mydate)+ ' Month
End reports are ready for viewing '
Exec master..xp_sendmail
@.recipients = 'someone@.somewhere.com',
@.copy_recipients = 'someone@.somewhere.com',
@.subject = @.subject,
@.message = 'Month End reports are now available via reporting services.
You can click on the link below and you will be taken directly to the
Month-End reports folder where you may choose to view the most recient
reports or view the history for archived reports.
http://localhost/Reports
If you have any questions please send an email to
someone@.somewhere.com'
END
GO
"Kmistic" wrote:
> I am in the same situation. Were you able to resolve this?
> --
> ---
> Yes, I searched first :)
>
> "anthonysjo" wrote:
> > Hello All!
> >
> > I have a requirement to set a schedule to run a report on the Tuesday
> > following the last Saturday of the month. It appears that this is not
> > possible via the Web UI so I hear I need to schedule it outside SRS. Oh yeah
> > and to make it more complex, if the Tuesday in in the new month, quarter, or
> > year then the parameter should select the previous month, quarter, or year
> > not the default of current month, current quarter, or current year. I think
> > the last part can easily be handled by getting the parameter values from the
> > date of the last saturday of the month, which would always give you the
> > correct month for month end reporting no matter if the next Tues is in the
> > next month or not.
> >
> > what I need is the syntax for defining the parameters and rendering the
> > report. I would also like to specify a network drive where it can be
> > archived, email a link to an audience, and save a snapshot in history.
> >
> > Can someone please help me with this? If not all then parts would be
> > appreciated!!!
> >
> > Anthony
> >

Scheduling Job sending email if datefield = today

Hi there,
I am wondering if the following is possible.

I would like to have a scheduled job that would check every day a date field of every record (on a specific table). If that date correspond to today then I would like to send an email with the information of that record.

Is this possible?

Btw if I mentionned Job Scheduling it's because I don't know if there is other ways available for such purpose, so if you think of an other way please let me know.The way I'd do it would be to have a SQL Agent job that is scheduled to run nightly. That agent would run a stored procedure to select today's records, like this:


DECLARE @.currentdate char(8)
DECLARE @.startdatetime varchar(21)
DECLARE @.enddatetime varchar(21)SELECT @.currentdate = CONVERT(char(8),GETDATE(),112)
SELECT @.startdatetime = @.currentdate + ' 00:00:00'
SELECT @.enddatetime = @.currentdate + ' 23:59:59.999'
SELECT
 *
FROM
 myTable
WHERE
 myDate BETWEEN @.startdatetime AND @.enddatetime

Terri|||

Is there a way to do this with out SQLAgent because tiscali's SQL Agent is not running and thy don't want to run it.
I need to call a webmethod in a webservice every 10 min. any help would be appreciated

|||Sorry, I have no idea. It's not possible through SQL withoutusing the SQL Agent AFAIK. It looks like you've asked thisquestionelsewhere so hopefully you'll get the answer you are looking for.
|||

Thanx

If you had to write a website withreminders so that the users can say I want a reminder on this day and thistime. What would you do to see if a reminder is due and send it?

I desperately have to find a way to do this.

|||Sorry, I'd use SQL Agent. :-( Where I work we host both thewebsite and SQL Server so I have all possible methods available to me.
FWIW There are several threads going on in the Getting Started forum right now related to this topic

http://forums.asp.net/944623/ShowPost.aspx
http://forums.asp.net/494136/ShowPost.aspx
http://forums.asp.net/945255/ShowPost.aspx (sort of)

Also, check out these articles:

Combine Web and Windows Services to Run Your ASP.NET Code at Scheduled Intervals
The Code Project - A New Task Scheduler Class Library for .NET

They both rely on a Windows Service to work, and it sounds like in your situation you do not have that option.

|||

Thank you for your help. I think I have toconvince my company to go to another hosting company where I can use a windowsservice.

Thank you Smile [:)]

|||I agree with you. I chatted with a pal about this and another suggestion he had wasPaul Wilson's Keep-Alive hack.Keep in mind this is indeed a hack and is prone to failure but it couldbe better than nothing as you try to implement a more robust solution.
|||

Thank you,you are a life saver

|||All the credit goes toSomeNewKid; he is one smart cookie. I'm glad this suggestion got you over the hump.

Friday, March 23, 2012

Scheduling a job !

hi,
i am trying to automate the following process using TSQL.
1. Import table 'A' from Database 'x' to my Database 'Y'
2. After the import is complete , insert an identity column in table A, make it a Primary key and then rename it as 'B'.

I was successful in Scheduling a job to import the table from database 'X' to "Y'. but i am not able to insert the identity column and rename the table.

Can anyone please help me !
thanks in advance !
AraoCan't you let the job first do a Create Table to create the new table with the name you want it to have, then imort into that table?|||i will try doing that. but i need to drop the previous table which has the same name. and the job is erroring out at this point. it says " the @.newname value is already in use as a object name and would cause duplicate that is not permitted. the step failed " but it's not in use by any of the users. i tried several times but it will never drop the table ! the following is my code.

if exists (select * from sysobjects where id = object_id(N'[dbo].[AR_ACCOUNTS') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AR_ACCOUNTS]
GO
EXEC sp_rename 'AR1_CUSTOMERMASTER', 'AR_ACCOUNTS'
GO

thanks,
anithasql

Scheduler not yielding?

All,

I'm getting the following error in my SQL logs:

Process 51:16 (2bc) UMS Context 0x1249F118 appears to be non-yielding on Scheduler 0.

I killed the process, but it's been rolling back for 3 hours. It's done zero IO and consumed a TON of CPU time.

Any suggestions as to what I can do?

Thanks,

Isaacpost details abt scheduler job.Are u executing DBCC DBREINDEX in that scheduler? what version of SQL server u are using? I hope it is SQL server 2000.If it is did u applied latest service pack?

Scheduler node deadlock message.

Hi:

We seem to be hitting a performance problem on SQL Server 2005. We encounter the following message frequently in the event log. We have update stats, reindexing and everything else in place. However this message keeps getting logged.

All schedulers on Node 0 appear deadlocked due to a large number
of worker threads waiting on LCK_M_IS. Process Utilization 25%%

MS Experts (Product Gurus)/MVPs can you please offer some explanation or insight into the error and also a possible resolution on how to fix it. I have the following questions:

1). Is this related to poor queries performing badly and needs to be tuned?.

2). Is this a more DIsk I/O Issue and whether we need faster disks or disk arrays?

3). Is something configured wrongly on the SQL Server?

4). Are we handling our transactions badly and thus resulting in blocking or deadlocks?.

Please provide your valuable suggestions.

Thanks

Ankith

I forgot to mention the system configuration.

The Server is Windows Server 2003 (x64 Edition) with AMD Quad (4) Processors (2.2 GHZ) and has apprx 16 GB of RAM.

Awe is enabled.

|||

This message indicates that there are some pretty long blocking chains on your server.

Have a look at the blocking section in http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx for methods of troubleshooting blocking issues.

|||

Hi Jerome:

Thanks for the info. Is this error anyway related to Hard disk contention (because I see worker threads waiting on LCK_M_IS). Just want to get your opinion and see if we can also take a look at our hard disks.

Thanks.

|||

Based on the wait type it's highly unlikely that disk issues have anything to do with the problem. Start by looking at the connection that's holding incompatible lock and determine what it's doing.

sql

Tuesday, March 20, 2012

Scheduled Jobs & emails ( Sql 2k)

Hi All,
The name of the Server was changed which in turn gave me the following error when I tried to delete the jobs

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
The job was not saved.

Knowing that it has to do with the Originator server in the sysjobs table; I hacked the table ( now I think not a good idea ) and deleted all the jobs that I wanted to delete and performed the same task on sysjobservers.

Now although I have accomplished the deletion of the jobs.
But I still get emails ( job failure notifications) for the deleted jobs exactly at the time they were scheduled.
This is driving me crazy as I do not how to stop these emails .

Any help is appreciated.

Thank you

1. Check sysnotifications table, if it still has notifications for those delted jobs, clean them as well.

2. If sysnotifications table does not have notifications then restarting SQL Agent service should defintely work.

How did you delete the job ? If you had used sp_delete_job SP then it would have taken care of cleaning all the dependent objects. Did you delete them usign TSQL delete statement ?

In case when server name was changed, instead of deleting the jobs, better option would have been to updating sysoriginatingservers table with the new server name.

Thanks,

Gops Dwarak, MSFT

|||

Thank you for your response.

The sysnotification table has been cleared.

I did restart that SQL Server Agent ( hoping this will fix the issue; but I guess I have to wait till tonight when the emails get generated ).

I did use TSQL to delete the jobs ( my bad).

Is there any other way to confirm that I will not be receiving those emails any more.

Thank you

|||

That is really surprising!

Did you try to recreate jobs that you deleted ? If not you can try recreating dummy jobs with same name and with no notification this time.

Other option I can think of is to disable the mail profile and restart SQL Agent . Now enable the profile back and restart SQL Agent and check for the behavior.

Gops Dwarak

|||

I did restart the SQL Server Agant and the notifications stoppped coming.

Thank you for your help.

|||

Gops,

I picked up this useful tip as am experiencing similar problems when server was cloned and renamed.

However, cannot find the sysoriginatingservers table.

Please tell me where this table resides.

Thanks,

Dickin

Scheduled Jobs & emails ( Sql 2k)

Hi All,
The name of the Server was changed which in turn gave me the following error when I tried to delete the jobs

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
The job was not saved.

Any help is appreciated.

Thank you

1. Check sysnotifications table, if it still has notifications for those delted jobs, clean them as well.

2. If sysnotifications table does not have notifications then restarting SQL Agent service should defintely work.

How did you delete the job ? If you had used sp_delete_job SP then it would have taken care of cleaning all the dependent objects. Did you delete them usign TSQL delete statement ?

In case when server name was changed, instead of deleting the jobs, better option would have been to updating sysoriginatingservers table with the new server name.

Thanks,

Gops Dwarak, MSFT

|||

Thank you for your response.

The sysnotification table has been cleared.

I did restart that SQL Server Agent ( hoping this will fix the issue; but I guess I have to wait till tonight when the emails get generated ).

I did use TSQL to delete the jobs ( my bad).

Is there any other way to confirm that I will not be receiving those emails any more.

Thank you

|||

That is really surprising!

Did you try to recreate jobs that you deleted ? If not you can try recreating dummy jobs with same name and with no notification this time.

Other option I can think of is to disable the mail profile and restart SQL Agent . Now enable the profile back and restart SQL Agent and check for the behavior.

Gops Dwarak

|||

I did restart the SQL Server Agant and the notifications stoppped coming.

Thank you for your help.

|||

Gops,

I picked up this useful tip as am experiencing similar problems when server was cloned and renamed.

However, cannot find the sysoriginatingservers table.

Please tell me where this table resides.

Thanks,

Dickin

Scheduled jobs

Hello people.

I have an SQL 2000 Server which was handle to me today out of the blue
and i was told that it has the following problem:

The SQL Server has several jobs that get executed in regular intervals
to extract data (to a text file) and send them to a specific directory.
It has been seen that as of May 30th, these jobs, although they seem to
complete OK, they do not actually produce the expected output files.

We have executed the jobs manually, and again the files are not
created. We have executed the DTS packages associated with the jobs
manually, and the files WERE CREATED.

This problem is causing issues in production.

ny ideas what might be causing this strange behaviour?

thanksSee if it might be some type of permission related problem.

I have an SQL 2000 Server which was handle to me today out of the blue
and i was told that it has the following problem:

We have executed the jobs manually, and again the files are not
created. We have executed the DTS packages associated with the jobs
manually, and the files WERE CREATED.

This problem is causing issues in production.

ny ideas what might be causing this strange behaviour?|||When you run a DTS package manually, it runs under your user ID; it
also runs under the context of the machine from which you are running
the package (i.e., if you are running Enterprise Manager on your PC
desktop to manage a remote server, and you execute a DTS package on
that server from your desktop, all file references are pointing from
your machine, not the server). When you schedule a DTS package or run
a package as a job, the package runs under the context of the owner of
the job; if it is sa, then it will run as the Windows Service Account.

If your Service Account or owner of the scheduled job does not have the
same permissions on a file folder that the person running the package
manually has, the package will not work as expected.

Maybe that will help you narrow it down; if not, post back.

Stu|||Stu wrote:
> When you run a DTS package manually, it runs under your user ID; it
> also runs under the context of the machine from which you are running
> the package (i.e., if you are running Enterprise Manager on your PC
> desktop to manage a remote server, and you execute a DTS package on
> that server from your desktop, all file references are pointing from
> your machine, not the server). When you schedule a DTS package or run
> a package as a job, the package runs under the context of the owner of
> the job; if it is sa, then it will run as the Windows Service Account.
> If your Service Account or owner of the scheduled job does not have the
> same permissions on a file folder that the person running the package
> manually has, the package will not work as expected.
> Maybe that will help you narrow it down; if not, post back.

I'd also say, if it does help, post back. It's amazing the number of
times I'm hunting for help through Google Groups, find someone who
seems to have an identical problem to me, and there's no actual
resolution. i.e. Did the suggestions help them out?, Was their problem
something different to what they thought?, Have they managed to solve
the problem or work around it?, etc.

Damien|||Hello again! Thanks a lot for the responses!

The cause was found and it comes down to insufficient space of internal
disks of the server. There are two partitions C:\ and D:\. The files
which the DTS was exporting are place on D:\ which has a lot of free
space.

The C:\ drive although had zero free space!!!! Thus when the job was
running it was completing successfully but could not produce the file.
Isnt that misleading?

Thanks a lot again!

Scheduled Job works when run manually but not when scheduled

Hi
I have a very simple local package, which performs the following:
1. Execute SQL Task
Delete from PURCHASE_LEDGER
Delete from SALES_LEDGER
2. Transform Data task
Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
There is a workflow between steps 1 and 2, so that step 2 only
executes if step 1 is successfull.
The connection to SAGE is ODBC (which I know works fine) and to SQL it
is OLE.
This package works perfectly if I right click on it and select
execute. However, if I schedule it, the first step works perfectly but
the second step fails. I know that the first step works because the
tables are empty. If I remove the first step and the workflow from the
package, the transform data task still does not work when scheduled,
but is fine when I run it manually. In the error log, it just says:
"SQL Server Scheduled Job 'Update SAGE
Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
was invoked by Schedule 6 (Update SAGE Tables). The last step to run
was step 1 (Update SAGE Tables)."
Any idea why this package should work manually but not when scheduled?
Thanks
ColinHi Colin,
Are the accounts you use to 1) execut the package manually and 2) the
account that runs it via a schedule, the same account?
Without digging too deeply to me it sounds like the account that is used for
the scheduled job, isnt the same as the one where you execute it manually.
Barry
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1175249472.172771.158350@.y80g2000hsf.googlegroups.com...
> Hi
> I have a very simple local package, which performs the following:
> 1. Execute SQL Task
> Delete from PURCHASE_LEDGER
> Delete from SALES_LEDGER
> 2. Transform Data task
> Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
> Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
> There is a workflow between steps 1 and 2, so that step 2 only
> executes if step 1 is successfull.
> The connection to SAGE is ODBC (which I know works fine) and to SQL it
> is OLE.
> This package works perfectly if I right click on it and select
> execute. However, if I schedule it, the first step works perfectly but
> the second step fails. I know that the first step works because the
> tables are empty. If I remove the first step and the workflow from the
> package, the transform data task still does not work when scheduled,
> but is fine when I run it manually. In the error log, it just says:
> "SQL Server Scheduled Job 'Update SAGE
> Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
> Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
> was invoked by Schedule 6 (Update SAGE Tables). The last step to run
> was step 1 (Update SAGE Tables)."
> Any idea why this package should work manually but not when scheduled?
> Thanks
> Colin
>|||Check the following knowledge base article for information
on troubleshooting this issue:
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/?id=269074
-Sue
On 30 Mar 2007 03:11:12 -0700, "Bobby"
<bobby2@.blueyonder.co.uk> wrote:
>Hi
>I have a very simple local package, which performs the following:
>1. Execute SQL Task
> Delete from PURCHASE_LEDGER
> Delete from SALES_LEDGER
>2. Transform Data task
> Copy everything from SAGE PURCHASE_LEDGER to SQL PURCHASE_LEDGER
> Copy everything from SAGE SALES_LEDGER to SQL SALES_LEDGER
>There is a workflow between steps 1 and 2, so that step 2 only
>executes if step 1 is successfull.
>The connection to SAGE is ODBC (which I know works fine) and to SQL it
>is OLE.
>This package works perfectly if I right click on it and select
>execute. However, if I schedule it, the first step works perfectly but
>the second step fails. I know that the first step works because the
>tables are empty. If I remove the first step and the workflow from the
>package, the transform data task still does not work when scheduled,
>but is fine when I run it manually. In the error log, it just says:
>"SQL Server Scheduled Job 'Update SAGE
>Tables' (0x3B6A429944F946459B15D8C8683396BA) - Status: Failed -
>Invoked on: 2007-03-30 05:00:00 - Message: The job failed. The Job
>was invoked by Schedule 6 (Update SAGE Tables). The last step to run
>was step 1 (Update SAGE Tables)."
>Any idea why this package should work manually but not when scheduled?
>Thanks
>Colin|||On 30 Mar, 12:52, Sue Hoegemeier <S...@.nomail.please> wrote:
> Check the following knowledge base article for information
> on troubleshooting this issue:
> INF: How to Run a DTS Package as a Scheduled Jobhttp://support.microsoft.com/?id=269074
> -Sue
Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
It's set up on my p/c, which is why it works manually, but when it's
scheduled it runs from the server and can't find the link to SAGE.
Thanks
Colin|||> Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
> It's set up on my p/c, which is why it works manually, but when it's
> scheduled it runs from the server and can't find the link to SAGE.
>
Hmmm... Not quite as simple as I thought. Anybody know how to install
the SAGE ODBC driver without installing the whole software package?
Colin|||On 30 Mar, 13:58, "Bobby" <bob...@.blueyonder.co.uk> wrote:
> > Thanks, that's the answer. I've not set up ODBC to SAGE on the server.
> > It's set up on my p/c, which is why it works manually, but when it's
> > scheduled it runs from the server and can't find the link to SAGE.
> Hmmm... Not quite as simple as I thought. Anybody know how to install
> the SAGE ODBC driver without installing the whole software package?
> Colin
Ok, forget it - I've worked it out
Colin

Wednesday, March 7, 2012

schedule new verion of DTS Package

I have several DTS Packages that are scheduled to run every night. If I
change the Packages, which version will be run the following night? If its
the old one, how to I update the schedule to recognize the new version?
--
SQL2K SP3
TIA, ChrisRIf you do not specify the version # then it is always the latest
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"ChrisR" <bla@.noemail.com> wrote in message
news:Oypg1xKBFHA.2792@.TK2MSFTNGP15.phx.gbl...
>I have several DTS Packages that are scheduled to run every night. If I
> change the Packages, which version will be run the following night? If its
> the old one, how to I update the schedule to recognize the new version?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Thanks Allen. Im still a bit in the dark though?
Is the version cotained in this type of deal?
DTSRun /~Z0x9F8F7D0B93BlaBlaBla
Is it updated when a new version is added?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eZh24$KBFHA.2788@.TK2MSFTNGP15.phx.gbl...
> If you do not specify the version # then it is always the latest
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:Oypg1xKBFHA.2792@.TK2MSFTNGP15.phx.gbl...
> >I have several DTS Packages that are scheduled to run every night. If I
> > change the Packages, which version will be run the following night? If
its
> > the old one, how to I update the schedule to recognize the new version?
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >
>|||yuck.
This will wrap version up for you and you cannoot change it unless you
regenerate the string (DTSRUNUI). Personally I prefer to use the DTSRUN and
the non-encrypted versions.
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"ChrisR" <bla@.noemail.com> wrote in message
news:%23QnYYxLBFHA.2112@.TK2MSFTNGP14.phx.gbl...
> Thanks Allen. Im still a bit in the dark though?
> Is the version cotained in this type of deal?
> DTSRun /~Z0x9F8F7D0B93BlaBlaBla
> Is it updated when a new version is added?
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eZh24$KBFHA.2788@.TK2MSFTNGP15.phx.gbl...
>> If you do not specify the version # then it is always the latest
>> --
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.SQLIS.com - You thought DTS was good. here we show you the new
>> stuff.
>> www.konesans.com - Consultancy from the people who know
>>
>> "ChrisR" <bla@.noemail.com> wrote in message
>> news:Oypg1xKBFHA.2792@.TK2MSFTNGP15.phx.gbl...
>> >I have several DTS Packages that are scheduled to run every night. If I
>> > change the Packages, which version will be run the following night? If
> its
>> > the old one, how to I update the schedule to recognize the new version?
>> >
>> > --
>> > SQL2K SP3
>> >
>> > TIA, ChrisR
>> >
>> >
>>
>|||Sorry to be such a pain Allan, but now Im really lost. Do you know if the
version gets updated when stored this way?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:ukprc3LBFHA.1564@.TK2MSFTNGP09.phx.gbl...
> yuck.
> This will wrap version up for you and you cannoot change it unless you
> regenerate the string (DTSRUNUI). Personally I prefer to use the DTSRUN
and
> the non-encrypted versions.
>
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:%23QnYYxLBFHA.2112@.TK2MSFTNGP14.phx.gbl...
> > Thanks Allen. Im still a bit in the dark though?
> >
> > Is the version cotained in this type of deal?
> >
> > DTSRun /~Z0x9F8F7D0B93BlaBlaBla
> >
> > Is it updated when a new version is added?
> >
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:eZh24$KBFHA.2788@.TK2MSFTNGP15.phx.gbl...
> >> If you do not specify the version # then it is always the latest
> >>
> >> --
> >>
> >>
> >>
> >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> >> www.SQLDTS.com - The site for all your DTS needs.
> >> www.SQLIS.com - You thought DTS was good. here we show you the new
> >> stuff.
> >> www.konesans.com - Consultancy from the people who know
> >>
> >>
> >> "ChrisR" <bla@.noemail.com> wrote in message
> >> news:Oypg1xKBFHA.2792@.TK2MSFTNGP15.phx.gbl...
> >> >I have several DTS Packages that are scheduled to run every night. If
I
> >> > change the Packages, which version will be run the following night?
If
> > its
> >> > the old one, how to I update the schedule to recognize the new
version?
> >> >
> >> > --
> >> > SQL2K SP3
> >> >
> >> > TIA, ChrisR
> >> >
> >> >
> >>
> >>
> >
> >
>|||No it does not.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"ChrisR" <bla@.noemail.com> wrote in message
news:ewbUGKMBFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Sorry to be such a pain Allan, but now Im really lost. Do you know if the
> version gets updated when stored this way?
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:ukprc3LBFHA.1564@.TK2MSFTNGP09.phx.gbl...
>> yuck.
>> This will wrap version up for you and you cannoot change it unless you
>> regenerate the string (DTSRUNUI). Personally I prefer to use the DTSRUN
> and
>> the non-encrypted versions.
>>
>> --
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.SQLIS.com - You thought DTS was good. here we show you the new
>> stuff.
>> www.konesans.com - Consultancy from the people who know
>>
>> "ChrisR" <bla@.noemail.com> wrote in message
>> news:%23QnYYxLBFHA.2112@.TK2MSFTNGP14.phx.gbl...
>> > Thanks Allen. Im still a bit in the dark though?
>> >
>> > Is the version cotained in this type of deal?
>> >
>> > DTSRun /~Z0x9F8F7D0B93BlaBlaBla
>> >
>> > Is it updated when a new version is added?
>> >
>> >
>> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
>> > news:eZh24$KBFHA.2788@.TK2MSFTNGP15.phx.gbl...
>> >> If you do not specify the version # then it is always the latest
>> >>
>> >> --
>> >>
>> >>
>> >>
>> >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> >> www.SQLDTS.com - The site for all your DTS needs.
>> >> www.SQLIS.com - You thought DTS was good. here we show you the new
>> >> stuff.
>> >> www.konesans.com - Consultancy from the people who know
>> >>
>> >>
>> >> "ChrisR" <bla@.noemail.com> wrote in message
>> >> news:Oypg1xKBFHA.2792@.TK2MSFTNGP15.phx.gbl...
>> >> >I have several DTS Packages that are scheduled to run every night. If
> I
>> >> > change the Packages, which version will be run the following night?
> If
>> > its
>> >> > the old one, how to I update the schedule to recognize the new
> version?
>> >> >
>> >> > --
>> >> > SQL2K SP3
>> >> >
>> >> > TIA, ChrisR
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

schedule job backup

Hi there
I want to implement a job command at SQL Server 2000 that should allow the
following:
1. Do a backup to "xpto" database
2. the name generated by the backup should be:
21112006xpto.bak
22112006xpto.bak
...
21022007xpto.bak
After archiving 3 months the first backup craeted "21112006xpto.bak" should
be replaced by "21022007xpto.bak"
once i just want to stay the earlier 3 months.
Actually i use the following command:
BACKUP DATABASE [xpto] TO DISK = N'C:\Microsoft SQL
Server\MSSQL\BACKUP\xpto.BAK' WITH INIT , NOUNLOAD , NAME = N'xpto
backup', SKIP , STATS = 10, NOFORMAT
Can somenone help me or give some tips?...
[]
RickyHi
To create the file using a current date then you would need to use dynamic SQL
e.g.
DECLARE @.cmd varchar(1000)
SET @.cmd = 'BACKUP DATABASE [xpto] TO DISK = N''C:\Microsoft SQL
Server\MSSQL\BACKUP\' + CONVERT(char(8),GETDATE(),112) + 'xpto.BAK'' WITH
INIT , NOUNLOAD , NAME = N''xpto backup'', SKIP , STATS = 10, NOFORMAT'
EXEC (@.cmd)
This will give you are filename YYYYMMDDxpto.BAK. This is a better format as
the files can easily be sorted.
To delete older files check out:
http://realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html
Alternatively a DTS package using an activeX script would be a way of
deleting old files see http://www.sqldts.com/default.aspx?292
John
"Ricky" wrote:
> Hi there
> I want to implement a job command at SQL Server 2000 that should allow the
> following:
> 1. Do a backup to "xpto" database
> 2. the name generated by the backup should be:
> 21112006xpto.bak
> 22112006xpto.bak
> ...
> 21022007xpto.bak
> After archiving 3 months the first backup craeted "21112006xpto.bak" should
> be replaced by "21022007xpto.bak"
> once i just want to stay the earlier 3 months.
> Actually i use the following command:
> BACKUP DATABASE [xpto] TO DISK = N'C:\Microsoft SQL
> Server\MSSQL\BACKUP\xpto.BAK' WITH INIT , NOUNLOAD , NAME = N'xpto
> backup', SKIP , STATS = 10, NOFORMAT
> Can somenone help me or give some tips?...
> []
> Ricky
>
>

Tuesday, February 21, 2012

Schedule and Delivery processor cannot connect

Hello,
I'm getting the following error on the application log of the event viewer:
Schedule and Delivery processor cannot connect to the report server database.
However I have no trouble to test some reports (e.g. the sample reports provided with reporting services).
What could be wrong with my configuration?
Thanks
CesarCan you look in the reportserverservice<date>.log files for more
information.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:BAF05421-386E-4712-8503-A304A1626C24@.microsoft.com...
> Hello,
> I'm getting the following error on the application log of the event
viewer:
> Schedule and Delivery processor cannot connect to the report server
database.
> However I have no trouble to test some reports (e.g. the sample reports
provided with reporting services).
> What could be wrong with my configuration?
> Thanks
> Cesar|||Hello daniel,
On one line of the log says:
"Cannot open database requested in login 'Report Server'. Login fails"
How can I fix that ?
But I had no errors during set up when I configured the account with which reporting services would use to connect to the database.
"Daniel Reib [MSFT]" wrote:
> Can you look in the reportserverservice<date>.log files for more
> information.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> news:BAF05421-386E-4712-8503-A304A1626C24@.microsoft.com...
> > Hello,
> >
> > I'm getting the following error on the application log of the event
> viewer:
> >
> > Schedule and Delivery processor cannot connect to the report server
> database.
> >
> > However I have no trouble to test some reports (e.g. the sample reports
> provided with reporting services).
> >
> > What could be wrong with my configuration?
> > Thanks
> > Cesar
>
>

Schedule a SQL statement to run from SQL Server ?

How can I schedule a SQL Statement to run from SQL Server say every day at 4
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.
Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at 4 pm ?
> For example, I would like to run the following sql statement every day at 4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule it to run automatically
> on SQL server ?
> Thanks.
>
|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve
|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>
|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>
|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
at
>
|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> 4
> 4
>
|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A
|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Management
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>
|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:[vbcol=seagreen]
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...

Schedule a SQL statement to run from SQL Server ?

How can I schedule a SQL Statement to run from SQL Server say every day at 4
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...[
vbcol=seagreen]
> How can I schedule a SQL Statement to run from SQL Server say every day at
4 pm ?
> For example, I would like to run the following sql statement every day at
4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically
> on SQL server ?
> Thanks.
>[/vbcol]|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
at[vbcol=seagreen]
>|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> 4
> 4
>|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Management
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:[vbcol=seagreen]
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...

Schedule a SQL statement to run from SQL Server ?

How can I schedule a SQL Statement to run from SQL Server say every day at 4
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at 4 pm ?
> For example, I would like to run the following sql statement every day at 4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule it to run automatically
> on SQL server ?
> Thanks.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every day
>> at 4 pm ?
>> For example, I would like to run the following sql statement every day at
>> 4 pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule it to run automatically on SQL server ?
>> Thanks.
>|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
--
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> > Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "fniles" <fniles@.pfmail.com> wrote in message
> > news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> >> How can I schedule a SQL Statement to run from SQL Server say every day
> >> at 4 pm ?
> >> For example, I would like to run the following sql statement every day
at
> >> 4 pm:
> >> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
> >> schedule it to run automatically on SQL server ?
> >>
> >> Thanks.
> >>
> >
> >
>|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every day
>> at
> 4
>> pm ?
>> For example, I would like to run the following sql statement every day at
> 4
>> pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule
>> it to run automatically on SQL server ?
>> Thanks.
>>
>|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
>> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
> Management
>> / SQL Server Agent / Jobs are the same, right ?
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every
>> day at 4 pm ?
>> For example, I would like to run the following sql statement every
>> day at 4 pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule it to run automatically on SQL server ?
>> Thanks.|||> Just for my curriosity - What would be the adavantage of putting the
DELETE
> statement in a stored proc? I know that a Stored Proc often performs a bit
> faster and it's good practice to create stored proc's for recurring
scripts,
> but is there any specific reason here that I'm missing?
- Maintainability, encapsulation
- easy ability to call the DELETE independent of the job
IMHO, all SQL statements should be in stored procedures.

schedule

I'm running a job from a schedule which runs a .vbs script
In this script I have the following script to include another file
Function GetFileContents(sFileName)
Dim FSO, ScriptFile
Set FSO = CreateObject("Scripting.FileSystemObject")
Set ScriptFile = FSO.OpenTextFile(sFilename,1)
GetFileContents = ScriptFile.ReadAll
Set ScriptFile = Nothing
Set FSO = Nothing
End Function
-> ExecuteGlobal GetFileContents("db_conn.vbs")
When I run the job from sql enterprise manager it stops at the row above
If I run the script from the command line, all is good
What's the problem
Thanks
DenisWhat would you expect the current directory to be when running it from
enterprise manager?
"Microsoft" <dzoddi@.mvmnet.com> wrote in message
news:OjDx0yQ8FHA.1032@.TK2MSFTNGP11.phx.gbl...
> I'm running a job from a schedule which runs a .vbs script
> In this script I have the following script to include another file
> Function GetFileContents(sFileName)
> Dim FSO, ScriptFile
> Set FSO = CreateObject("Scripting.FileSystemObject")
> Set ScriptFile = FSO.OpenTextFile(sFilename,1)
> GetFileContents = ScriptFile.ReadAll
> Set ScriptFile = Nothing
> Set FSO = Nothing
> End Function
> -> ExecuteGlobal GetFileContents("db_conn.vbs")
>
> When I run the job from sql enterprise manager it stops at the row above
> If I run the script from the command line, all is good
> What's the problem
> Thanks
> Denis
>

Friday, March 30, 2012

scheduling stored procedure

Wednesday, March 28, 2012

Scheduling outside SRS

Scheduling Job sending email if datefield = today

Friday, March 23, 2012

Scheduling a job !

Scheduler not yielding?

Scheduler node deadlock message.

Tuesday, March 20, 2012

Scheduled Jobs & emails ( Sql 2k)

Scheduled Jobs & emails ( Sql 2k)

Scheduled jobs

Scheduled Job works when run manually but not when scheduled

Wednesday, March 7, 2012

schedule new verion of DTS Package

schedule new verion of DTS Package

schedule new verion of DTS Package

schedule job backup

Tuesday, February 21, 2012

Schedule and Delivery processor cannot connect

Schedule a SQL statement to run from SQL Server ?

Schedule a SQL statement to run from SQL Server ?

Schedule a SQL statement to run from SQL Server ?

schedule

T-SQL Script

Blog Archive

About Me