Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

one more question about running jobs 1

Status
Not open for further replies.

icemel

MIS
Oct 17, 2005
463
US
Hi,

I have this:

exec (insert into myTable sp_help_jobschedule @job_name = "job1")
exec (insert into myTable sp_help_jobschedule @job_name = "job2")
exec (insert into myTable sp_help_jobschedule @job_name = "job3")

but once again, syntax error on line 2. I'm sure it's something simple but not sure what.

Thanks
 
exec (insert into myTable sp_help_jobschedule @job_name = "job1")
go
exec (insert into myTable sp_help_jobschedule @job_name = "job2")
go
exec (insert into myTable sp_help_jobschedule @job_name = "job3")
go

-DNG
 
hmmm, the single quotes around causes a syntax error

and I don't know how to add a "go" AFTER each line in my spreadsheet without doing it manually

??
 
what about this?
exec ('insert into myTable sp_help_jobschedule @job_name = ''job1''')
exec ('insert into myTable sp_help_jobschedule @job_name = ''job2''')
exec ('insert into myTable sp_help_jobschedule @job_name = ''job3''')

Denis The SQL Menace
SQL blog:
Personal Blog:
 
SQLDenis, yes unfortunately it doesn't work...

it won't even execute the first line, believe me i wish it worked b/c now looks like i need to write a little sql script to do what i want
 
heheheh, I see the problem, you need exec in front of the sp_help....

exec ('insert into myTable exec sp_help_jobschedule @job_name = ''job1''')
exec ('insert into myTable exec sp_help_jobschedule @job_name = ''job2''')
exec ('insert into myTable exec sp_help_jobschedule @job_name = ''job3''')

Denis The SQL Menace
SQL blog:
Personal Blog:
 
In case anybody wants, here is how to create a table with the job schedules!

CREATE TABLE job_schedule
(
schedule_id int,
schedule_name sysname,
enabled int,
freq_type int,
freq_interval int,
freq_subday_type int,
freq_subday_interval int,
freq_relative_interval int,
freq_recurrence_factor int,
active_start_date int,
active_end_date int,
active_start_time int,
active_end_time int,
date_created datetime,
schedule_description nvarchar(4000),
next_run_date int,
next_run_time int
)

--build stmts

select 'exec (''insert into job_schedule exec sp_help_jobschedule @job_name = "' + name + '"' + '''' + ')'
from sysjobs

-- then run the output
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top