| Author |
Topic |
|
jar21
51 Posts |
 Posted - 2008-09-24 : 08:06:11
|
| I just created a scheduled T-SQL statement that is suppose to run daily. The reason it didnt run is because I used the maintainence plan to run a t-sql statement and its running against the master database when it needs to run against a different database, can anyone tell me how to make this t-sql statement run ona scheduled manner against a database other than the master?thanks in advanceEnjoy Life This Is Not A Rehearsal. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-24 : 08:12:00
|
| Create a sql agent job to execute this statement and schedule it accordingly. |
 |
|
|
jar21
51 Posts |
Posted - 2008-09-24 : 08:22:26
|
| I have had problems with doing it that way, I will try again and if an error occurs tonight I will let you know.Is there a way to do this without using an agent? perhaps writing it in the t-sql statement itself to reference another database??Enjoy Life This Is Not A Rehearsal. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-24 : 08:29:01
|
quote:
Originally posted by rabino67
I have had problems with doing it that way, I will try again and if an error occurs tonight I will let you know.Is there a way to do this without using an agent? perhaps writing it in the t-sql statement itself to reference another database??Enjoy Life This Is Not A Rehearsal.
you can write it in t-sql. but how will you schedule to run at defined times. that can be done only as a sql agent job. |
|
|
|
jar21
51 Posts |
Posted - 2008-09-24 : 08:37:46
|
| Under maintenance plan you are able to "execute T-SQL statement task" and schedule the maintenance plan to run the statement accordingly.so can you explain how I can write it in the t-sql statement???Enjoy Life This Is Not A Rehearsal. |
|
|
|
lepeniotis
Yak Posting Veteran
75 Posts |
Posted - 2008-09-24 : 10:10:23
|
| Create new job. At the create new job click on steps on the left of the new job window. at the steps click on new. On the command box paste your code and then if you click on the advanced you will choose what exactly you want to do in case of success or failure. Then the only thing that you have to do is to schedule it or to include it on a maintenace plan as you describe above>>Under maintenance plan you are able to "execute T-SQL statement task" The scheduled jobs can't be run without the SQL agent RegardsMSc Advanced Computing Science MSc Database ProfessionalSheffield Hallam UniversityMCP (70-229, 70-228)Industrial IT Engineer |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-24 : 10:26:12
|
| Remember to Specify :Database as well. |
|
|
|
jar21
51 Posts |
Posted - 2008-09-24 : 10:44:59
|
| Okay I remade them I'll let you know the result tomorrow.thanks |
|
|
|
jar21
51 Posts |
Posted - 2008-09-25 : 09:07:23
|
| One Half worked, the other statement has no history at all. with the t-sql statement I think it would be a lot easier, can you tell me how to specify a database within a t-sql statement? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-25 : 09:09:24
|
quote:
Originally posted by rabino67
One Half worked, the other statement has no history at all. with the t-sql statement I think it would be a lot easier, can you tell me how to specify a database within a t-sql statement?
USE databasename changes context to specified database |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-25 : 09:25:55
|
| While setting up job step, there is a place where it says Database:. |
|
|
|
jar21
51 Posts |
Posted - 2008-09-25 : 09:36:41
|
| yes, I did that, there was a third party company working with us who created this database, I have never touched a database before besides setting it up and configuring it to accept information from another applicaiton. There is something wrong with setting up job agents ebcause they ahve not worked and rather then go into it I will attempt to simply ahve my new t-sql queries be ran as a maintenance plan and we will see the result, otherwise I will have to continue running this daily every night :(Enjoy Life This Is Not A Rehearsal. |
|
|
|
jar21
51 Posts |
Posted - 2008-09-29 : 08:21:28
|
| referencing the database in the T-SQL worked! I no longer ahve to run this every day when I get home! thanks for your help. :) |
|
|
|
jar21
51 Posts |
Posted - 2008-10-07 : 15:58:32
|
| okay, now it no longer works, for some reason these scripts stopped working 6 days ago and no error was reported... is there any reason why a script would work fine then suddenly stop working for no reason at all??? is there an error log that may say something about this? I checked the sql server lgos and the error logs and it had nothing from when the last time it worked... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-07 : 16:02:45
|
| what service pack level you have on SQL Server? Is database name still exists in T-SQL? |
|
|
|
jar21
51 Posts |
Posted - 2008-10-07 : 16:09:48
|
| Microsoft SQL Server Management Studio 9.00.3042.00Microsoft Analysis Services Client Tools 2005.090.3042.00Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 7.0.5730.11Microsoft .NET Framework 2.0.50727.832Operating System 5.2.3790the name still exists, and is still the same. |
|
|
|
jar21
51 Posts |
Posted - 2008-10-08 : 11:41:44
|
| Its solved - I was cleaning up the job agents and I thought that those weren't a part of it , clearly I completely didn't pay attention to the name when I disabled the agent - therefore it wouldn't run even though it was a maintenance task. thanks though!Enjoy Life This Is Not A Rehearsal. |
|
|
|
|
[SOLVED] Scheduled T-SQL statement