March 1, 2014 at 9:55 pm
Please can someone tell me how to trigger an sp at a fixed time of day without using SQL server agent?
March 2, 2014 at 10:11 am
Create a *.bat file and use sql command to execute it - then create a scheduled task to execute it @Echo Off
set ServerName=YOURSERVER
set DBName=YOURDATABASE
set sql_login=USERNAME
set sql_passwd=PASSWORD
if exist *.outdel *.out
echo working... DO NOT CLOSE
REM ************************************************
sqlcmd -Q "exec YOURDATABASE.dbo.YOURPROCEDURE" -S %ServerName% -d %DBNAME% -o C:\YOURPROCDURE.out
echo All Scripts have been Executed
:end
pause
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2014 at 11:04 am
Tergum Rufus (3/1/2014)
Please can someone tell me how to trigger an sp at a fixed time of day without using SQL server agent?
Why? What's the problem with using SQL Server Agent?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2014 at 11:35 am
I thought the same thing Jeff, but figured it was probably because either they didn't have permissions to create an Agent job or because they have hundreds of them and didn't want to add another to the list :hehe:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2014 at 11:47 am
MyDoggieJessie (3/2/2014)
I thought the same thing Jeff, but figured it was probably because either they didn't have permissions to create an Agent job or because they have hundreds of them and didn't want to add another to the list :hehe:
One can only hope that if the OP doesn't have the privs to create an Agent job nor the nads to run it through the resident DBA, that he also wouldn't have the privs to do it through Windows Scheduler. :hehe: And, hopefully, a DBA doesn't have a problem with hundreds of jobs. 😉
@Tergum Rufus,
What say ye, man? Why do you need to schedule jobs without creating an Agent job?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2014 at 11:26 pm
Thanks everyone for responses. JM - it's for clients with SQL EXPRESS Editions
TR
March 2, 2014 at 11:35 pm
Hi
Thank you for the messages.
We don't like using sqlcmd cos credentials too exposed. As above the question relates to our clients who are using SQL express Editions without Agent.
Was looking for a time trigger to fire our BU script something like this [but can't do of course on sys.views]
DECLARE @T varchar (16),@Time varchar(16)
SET @Time= '11:55'
SET @T = (SELECT(CAST((SELECT TOP 1 [last_batch]
FROM [msdb].[sys].[sysprocesses]
ORDER BY last_batch desc) AS TIME)))
IF @T > @Time
BEGIN
PRINT 'Starting backup script at ' +@T
END
ELSE BEGIN
PRINT 'Skipped Backup DB'
END
March 2, 2014 at 11:44 pm
You should be able to use sqlcmd without specifying credentials (I just provided a bad example above). Here's an article pretty much outlining exactly what you are trying to accomplish:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2014 at 11:46 pm
Actually this is the better article (it's the source for the one I previously posted, and is much more detailed)
http://www.mssqltips.com/sqlservertip/1174/scheduling-backups-for-sql-server-2005-express/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 3, 2014 at 6:29 am
Tergum Rufus (3/2/2014)
Thanks everyone for responses. JM - it's for clients with SQL EXPRESS EditionsTR
Ah... got it. Thanks.
If you have Windows security running, SQLCmd does't require exposed credentials. Whatever user the Windows Scheduler job is using could use a "trusted connection". Or, the link in the post immediately above seems to be ok. It doesn't expose passwords.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2014 at 1:54 pm
Thank you folks. Use Trusted in the SQLCMD is something we have previously missed.
Pursuant to this, I would be interested to know a way to produce a server-wide trigger [or other device] on the current time of day, as illustrated above.
TR
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply