BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  How can I create a SQL Server's job          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message YangZhouYuan Guest Posted: Sun Jun 05, 2005 1:45 am    Post subject: How can I create a SQL Server's job Hi: How can I create a SQL Server's Job in delphi7 Back to top Stig Johansen Guest Posted: Sun Jun 05, 2005 6:15 am    Post subject: Re: How can I create a SQL Server's job YangZhouYuan wrote: Quote: How can I create a SQL Server's Job in delphi7 I would try the following: 1) Create the job in Enterprise Manager. 2) Script the job, and use the output in Delphi. -- Best regards Stig Johansen Back to top YangZhouYuan Guest Posted: Mon Jun 06, 2005 12:57 am    Post subject: Re: How can I create a SQL Server's job Stig Johansen: Thank you for your help,I have already created the job in Enterprise Manager and Script the job to a textfile, but I don't understand the mean of 'use the output in Delphi.',could you tell me the details steps. Best regards "Stig Johansen" <aaa (AT) bbb (DOT) com> ??????:42a298df (AT) newsgroups (DOT) borland.com... Quote: YangZhouYuan wrote: How can I create a SQL Server's Job in delphi7 I would try the following: 1) Create the job in Enterprise Manager. 2) Script the job, and use the output in Delphi. -- Best regards Stig Johansen Back to top Stig Johansen Guest Posted: Mon Jun 06, 2005 4:03 am    Post subject: Re: How can I create a SQL Server's job YangZhouYuan wrote: Quote: Stig Johansen: Thank you for your help,I have already created the job in Enterprise Manager and Script the job to a textfile, but I don't understand the mean of 'use the output in Delphi.',could you tell me the details steps. By output, i ment the generated SQL from the scripting. Take the generated SQL, and put it in a TxxQuery* component. Then in your program, call YourQuery.ExecSQL * The name depends on which library you use. -- Best regards Stig Johansen Back to top YangZhouYuan Guest Posted: Mon Jun 06, 2005 6:15 am    Post subject: Re: How can I create a SQL Server's job Stig Johansen: According your advice,I put the SQL script into the Query.SQL component, then,I call the Query.ExecSQL method,a error appear like following: Project yichangPrg.exe raised exception class EBDEngineError with message 'Invalid use of keyword. Token:BEGIN like number:1',process stopped,Use step or Run to continue Best regards "Stig Johansen" <aaa (AT) bbb (DOT) com> ??????:42a3cb70 (AT) newsgroups (DOT) borland.com... Quote: YangZhouYuan wrote: Stig Johansen: Thank you for your help,I have already created the job in Enterprise Manager and Script the job to a textfile, but I don't understand the mean of 'use the output in Delphi.',could you tell me the details steps. By output, i ment the generated SQL from the scripting. Take the generated SQL, and put it in a TxxQuery* component. Then in your program, call YourQuery.ExecSQL * The name depends on which library you use. -- Best regards Stig Johansen Back to top Heinrich Braun Guest Posted: Mon Jun 06, 2005 7:40 am    Post subject: Re: How can I create a SQL Server's job Hi, it works fine with Tado-components, us Tadoquery or Tadocommand, you should disable Parametercheck otherwise it mistakes a label in TSQL as parameter. The following script creates a simple job every hour which calls a stored procedure, where you may do the final things. it is easier to change the SP than to change a job. The following is directly generatet by Enterprisemanager: --TSQL: -- Skript erstellt auf 06.06.2005 09:16 -- Durch: xxx -- Server: (LOCAL) use master BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' -- Auftrag mit demselben Namen löschen (falls vorhanden) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'testjob') IF (@JobID IS NOT NULL) BEGIN -- Überprüfen, ob es sich um einen Multiserverauftrag handelt IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- Dies ist der Fall, daher Skript abbrechen RAISERROR (N'Auftrag ''testjob'' kann nicht importiert werden, da bereits ein Multiserverauftrag mit diesem Namen vorhanden ist.', 16, 1) GOTO QuitWithRollback END ELSE -- [Lokalen] Auftrag löschen EXECUTE msdb.dbo.sp_delete_job @job_name = N'testjob' SELECT @JobID = NULL END BEGIN -- Auftrag hinzufügen EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'testjob', @owner_login_name = N'sa', @description = N'testjob comment', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Auftragsschritte hinzufügen EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'step1', @command = N'exec sp_myjobprocedure', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 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 -- Auftragszeitpläne hinzufügen EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'mytimer', @enabled = 1, @freq_type = 4, @active_start_date = 20050606, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Zielserver hinzufügen 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: --END TSQL If you get the source inside a TMemo you just do //DELPI-Code ADOQuery1.SQL.Text:= Memo1.Text; ADOQuery1.ExecSQL; //alternative use of AdoCommand ADOCommand1.CommandText:= Memo1.Text; ADOCommand1.Execute; hopethishelps Heinrich YangZhouYuan schrieb: Quote: Stig Johansen: According your advice,I put the SQL script into the Query.SQL component, then,I call the Query.ExecSQL method,a error appear like following: Project yichangPrg.exe raised exception class EBDEngineError with message 'Invalid use of keyword. Token:BEGIN like number:1',process stopped,Use step or Run to continue Best regards "Stig Johansen" <aaa (AT) bbb (DOT) com> ??????:42a3cb70 (AT) newsgroups (DOT) borland.com... YangZhouYuan wrote: Stig Johansen: Thank you for your help,I have already created the job in Enterprise Manager and Script the job to a textfile, but I don't understand the mean of 'use the output in Delphi.',could you tell me the details steps. By output, i ment the generated SQL from the scripting. Take the generated SQL, and put it in a TxxQuery* component. Then in your program, call YourQuery.ExecSQL * The name depends on which library you use. -- Best regards Stig Johansen Back to top YangZhouYuan Guest Posted: Mon Jun 06, 2005 9:43 am    Post subject: Re: How can I create a SQL Server's job Hi, Thanks a lots, according your steps, I have already created the SQL Server Job successfully, but I found that the BDEQuery doesn't support like this operation. Best regards yangzhouyuan Beijing China. "Heinrich Braun" <heinrich.braun (AT) homagSPAMM (DOT) de> ??????:42a3fdeb (AT) newsgroups (DOT) borland.com... Quote: Hi, it works fine with Tado-components, us Tadoquery or Tadocommand, you should disable Parametercheck otherwise it mistakes a label in TSQL as parameter. The following script creates a simple job every hour which calls a stored procedure, where you may do the final things. it is easier to change the SP than to change a job. The following is directly generatet by Enterprisemanager: --TSQL: -- Skript erstellt auf 06.06.2005 09:16 -- Durch: xxx -- Server: (LOCAL) use master BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' -- Auftrag mit demselben Namen löschen (falls vorhanden) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'testjob') IF (@JobID IS NOT NULL) BEGIN -- Überprüfen, ob es sich um einen Multiserverauftrag handelt IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- Dies ist der Fall, daher Skript abbrechen RAISERROR (N'Auftrag ''testjob'' kann nicht importiert werden, da bereits ein Multiserverauftrag mit diesem Namen vorhanden ist.', 16, 1) GOTO QuitWithRollback END ELSE -- [Lokalen] Auftrag löschen EXECUTE msdb.dbo.sp_delete_job @job_name = N'testjob' SELECT @JobID = NULL END BEGIN -- Auftrag hinzufügen EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'testjob', @owner_login_name = N'sa', @description = N'testjob comment', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Auftragsschritte hinzufügen EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'step1', @command = N'exec sp_myjobprocedure', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 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 -- Auftragszeitpläne hinzufügen EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'mytimer', @enabled = 1, @freq_type = 4, @active_start_date = 20050606, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Zielserver hinzufügen 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: --END TSQL If you get the source inside a TMemo you just do //DELPI-Code ADOQuery1.SQL.Text:= Memo1.Text; ADOQuery1.ExecSQL; //alternative use of AdoCommand ADOCommand1.CommandText:= Memo1.Text; ADOCommand1.Execute; hopethishelps Heinrich YangZhouYuan schrieb: Stig Johansen: According your advice,I put the SQL script into the Query.SQL component, then,I call the Query.ExecSQL method,a error appear like following: Project yichangPrg.exe raised exception class EBDEngineError with message 'Invalid use of keyword. Token:BEGIN like number:1',process stopped,Use step or Run to continue Best regards "Stig Johansen" <aaa (AT) bbb (DOT) com> ??????:42a3cb70 (AT) newsgroups (DOT) borland.com... YangZhouYuan wrote: Stig Johansen: Thank you for your help,I have already created the job in Enterprise Manager and Script the job to a textfile, but I don't understand the mean of 'use the output in Delphi.',could you tell me the details steps. By output, i ment the generated SQL from the scripting. Take the generated SQL, and put it in a TxxQuery* component. Then in your program, call YourQuery.ExecSQL * The name depends on which library you use. -- Best regards Stig Johansen Back to top Heinrich Braun Guest Posted: Mon Jun 06, 2005 9:56 am    Post subject: Re: How can I create a SQL Server's job With MSDE or SQL-Server you should take ADO, BDE is only good for lokal file-databses like paradox. ADO is already included in windows from win2k onwards. For all others install MDAC. No need to install BDE on target system, just deliver your exe-files. heinrich YangZhouYuan schrieb: Quote: Hi, Thanks a lots, according your steps, I have already created the SQL Server Job successfully, but I found that the BDEQuery doesn't support like this operation. Best regards yangzhouyuan Beijing China. "Heinrich Braun" <heinrich.braun (AT) homagSPAMM (DOT) de ??????:42a3fdeb (AT) newsgroups (DOT) borland.com... Hi, it works fine with Tado-components, us Tadoquery or Tadocommand, you should disable Parametercheck otherwise it mistakes a label in TSQL as parameter. The following script creates a simple job every hour which calls a stored procedure, where you may do the final things. it is easier to change the SP than to change a job. The following is directly generatet by Enterprisemanager: --TSQL: -- Skript erstellt auf 06.06.2005 09:16 -- Durch: xxx -- Server: (LOCAL) use master BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' -- Auftrag mit demselben Namen löschen (falls vorhanden) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'testjob') IF (@JobID IS NOT NULL) BEGIN -- Überprüfen, ob es sich um einen Multiserverauftrag handelt IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- Dies ist der Fall, daher Skript abbrechen RAISERROR (N'Auftrag ''testjob'' kann nicht importiert werden, da bereits ein Multiserverauftrag mit diesem Namen vorhanden ist.', 16, 1) GOTO QuitWithRollback END ELSE -- [Lokalen] Auftrag löschen EXECUTE msdb.dbo.sp_delete_job @job_name = N'testjob' SELECT @JobID = NULL END BEGIN -- Auftrag hinzufügen EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'testjob', @owner_login_name = N'sa', @description = N'testjob comment', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Auftragsschritte hinzufügen EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'step1', @command = N'exec sp_myjobprocedure', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 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 -- Auftragszeitpläne hinzufügen EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'mytimer', @enabled = 1, @freq_type = 4, @active_start_date = 20050606, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Zielserver hinzufügen 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: --END TSQL If you get the source inside a TMemo you just do //DELPI-Code ADOQuery1.SQL.Text:= Memo1.Text; ADOQuery1.ExecSQL; //alternative use of AdoCommand ADOCommand1.CommandText:= Memo1.Text; ADOCommand1.Execute; hopethishelps Heinrich YangZhouYuan schrieb: Stig Johansen: According your advice,I put the SQL script into the Query.SQL component, then,I call the Query.ExecSQL method,a error appear like following: Project yichangPrg.exe raised exception class EBDEngineError with message 'Invalid use of keyword. Token:BEGIN like number:1',process stopped,Use step or Run to continue Best regards "Stig Johansen" <aaa (AT) bbb (DOT) com> ??????:42a3cb70 (AT) newsgroups (DOT) borland.com... YangZhouYuan wrote: Stig Johansen: Thank you for your help,I have already created the job in Enterprise Manager and Script the job to a textfile, but I don't understand the mean of 'use the output in Delphi.',could you tell me the details steps. By output, i ment the generated SQL from the scripting. Take the generated SQL, and put it in a TxxQuery* component. Then in your program, call YourQuery.ExecSQL * The name depends on which library you use. -- Best regards Stig Johansen Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.