I fixed the problem part....Just joined with another table....Please review it...It is ok now...
FROM msdb..sysjobschedules x, msdb..sysschedules y
WHERE x.schedule_id = y.schedule_id
and job_id = @jobID
Rabia
------------------------------------------------------------------------
-- Name : gera_jobs.sql
-- Author : Edu F Mont November 24, 2004
-- Mail : edu_dba@hotmail.com
-- RDBMS : SQL Server 2000
-- Desc : This SQL statement will provide script for backup all your jobs.
------------------------------------------------------------------------
-- IMPORTANT : Grow-up "maximum characters per column" (for 8192) and disable "print column headers" in 'tools' / 'options'
------------------------------------------------------------------------
SET NOCOUNT ON
GO
SET QUOTED_IDENTIFIER OFF
GO
declare @jobID nvarchar (40)
declare @cmd varchar (2000)
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = '##jobids')
DROP TABLE ##jobids
SELECT @cmd = 'SELECT distinct (job_id) as job_id, 0 AS lido
INTO ##jobids
FROM msdb..sysjobs
ORDER BY 1'
EXEC (@cmd)
WHILE EXISTS (SELECT * FROM ##jobids WHERE lido = 0 )
BEGIN
SELECT @jobID = job_id
FROM ##jobids
WHERE lido = 0
SELECT
' -- DECLARACOES DE VARIAVEIS
BEGIN TRANSACTION
DECLARE @jobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'''+a.name+''') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'''+a.name+''''
FROM msdb..syscategories a, msdb..sysjobs b
WHERE a.category_id = b.category_id
and b.job_id = @jobID
SELECT
'-- Delete the job with the same name (if it exists)
SELECT @jobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'''+name+''')
IF (@jobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N''Unable to import job '''''+name+''''' since there is already a multi-server job with this name.'', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'''+name+'''
SELECT @jobID = NULL
END
BEGIN'
FROM msdb..sysjobs
WHERE job_id = @jobID
IF (SELECT COUNT(*) FROM msdb..sysjobs WHERE job_id = @jobID and notify_email_operator_id 0) > 1
BEGIN
SELECT
'
-- Adiciona o job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @jobID OUTPUT , @job_name = N'''+a.name+''', @owner_login_name = N'''+REPLACE (b.name,'dbo','sa')+''', @description = N'''+a.description+''', @category_name = N'''+c.name+''', @enabled = '+cast (a.enabled as nvarchar (4))+', @notify_level_email = '+cast (a.notify_level_email as nvarchar (4))+', @notify_level_page = '+cast (a.notify_level_page as nvarchar (4))+', @notify_level_netsend = '+cast (a.notify_level_netsend as nvarchar (4))+', @notify_level_eventlog = '+cast (a.notify_level_eventlog as nvarchar (4))+', @delete_level= '+cast (a.delete_level as nvarchar (4))+', @notify_email_operator_name = N'''+d.name+'''
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback '
FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c, msdb..sysoperators d
WHERE a.owner_sid = b.sid
and a.category_id = c.category_id
and d.id = a.notify_email_operator_id
and job_id = @jobID
END
ELSE
BEGIN
SELECT
'
-- Adiciona o job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @jobID OUTPUT , @job_name = N'''+a.name+''', @owner_login_name = N'''+REPLACE (b.name,'dbo','sa')+''', @description = N'''+a.description+''', @category_name = N'''+c.name+''', @enabled = '+cast (a.enabled as nvarchar (4))+', @notify_level_email = '+cast (a.notify_level_email as nvarchar (4))+', @notify_level_page = '+cast (a.notify_level_page as nvarchar (4))+', @notify_level_netsend = '+cast (a.notify_level_netsend as nvarchar (4))+', @notify_level_eventlog = '+cast (a.notify_level_eventlog as nvarchar (4))+', @delete_level= '+cast (a.delete_level as nvarchar (4))+'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback '
FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c
WHERE a.owner_sid = b.sid
and a.category_id = c.category_id
and a.job_id = @jobID
END
SELECT
'
-- Adiciona o job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = '+cast (a.step_id as nvarchar (3))+', @step_name = N'''+a.step_name+''', @command = N'''+REPLACE (a.command,"'","''")+''', @database_name = N'''+ISNULL (a.database_name,'') +''', @server = N'''', @database_user_name = N'''+ISNULL (a.database_user_name, '')+''', @subsystem = N'''+a.subsystem+''', @cmdexec_success_code = '+cast (a.cmdexec_success_code as nvarchar(2))+', @flags = '+cast (a.flags as nvarchar(2))+', @retry_attempts = '+cast (a.retry_attempts as nvarchar(2))+', @retry_interval = '+cast (a.retry_interval as nvarchar(2))+', @output_file_name = N'''+ISNULL (a.output_file_name, '')+''', @on_success_step_id = '+cast (a.on_success_step_id as nvarchar(3))+', @on_success_action = '+cast (a.on_success_action as nvarchar(2))+', @on_fail_step_id = '+cast (a.on_fail_step_id as nvarchar(2))+', @on_fail_action = '+cast (a.on_fail_action as nvarchar(2))+'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback'
FROM msdb..sysjobsteps a
WHERE a.job_id = @jobID
SELECT
'
-- Adiciona o job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'''+name+''', @enabled = '+cast (enabled as nvarchar (1))+', @freq_type = '+cast (freq_type as nvarchar (2))+', @active_start_date = '+cast (active_start_date as nvarchar(10))+', @active_start_time = '+cast (active_start_time as nvarchar(10))+', @freq_interval = '+cast (freq_interval as nvarchar(3))+', @freq_subday_type = '+cast (freq_subday_type as nvarchar(5))+', @freq_subday_interval = '+cast (freq_subday_interval as nvarchar(10))+', @freq_relative_interval = '+cast (freq_relative_interval as nvarchar(5))+', @freq_recurrence_factor = '+cast (freq_recurrence_factor as nvarchar(10))+', @active_end_date = '+cast (active_end_date as nvarchar(10))+', @active_end_time = '+cast (active_end_time as nvarchar(10))+'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback '
--FROM msdb..sysjobschedules
--WHERE job_id = @jobID
FROM msdb..sysjobschedules x, msdb..sysschedules y
WHERE x.schedule_id = y.schedule_id
and job_id = @jobID
PRINT '
-- Adiciona o Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N''(local)''
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO'
UPDATE ##jobids
SET lido = 1
WHERE job_id = @jobID
END
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER ON
GO