Problem adding job to server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem adding job to server

Hello,
I have posted the follwoing question in the General Developer questions forum but I then after search I thought that it should have been posted in Sql server Adminstraion forum because I think it is an adminstration or installation issue. You can skip the code of the job and go directly to the problem at the end. (I prefered to show the code of the job) The job is called ‘OrdersTransfer’ and it has 2 steps:
First step: is called ‘Transfer’ and it transfers all records from a table called ordersbyday to a master table called orders
Second step: is called ‘DropandCreate’ and it drops table called ordersbyday and then recreates it again
Code: use msdb
EXEC sp_add_job @job_name = ‘OrdersTransfer’,
@enabled = 1,
@description = ‘daily orders transfer from OrderbyDay table to master Orders table’,
@delete_level = 1 use msdb
EXEC sp_add_jobstep @job_name = ‘OrdersTransfer’,@step_id = 1,
@step_name = ‘Transfer’,
@subsystem = ‘TSQL’,
@command =
‘INSERT INTO basmala_38.db:confused:rders (OrderId, OrderDate, OrderTime, BranchId, MemberId, EmpId, DocDate, Status,TotalPoints, TotalMoney)
SELECT OrderId, OrderDate, OrderTime, BranchId, MemberId, EmpId, DocDate, Status, TotalPoints, TotalMoney
FROM basmala_38.db:confused:rdersByDAy’
use msdb
EXEC sp_add_jobstep @job_name = ‘OrdersTransfer’, @step_id = 2,
@step_name = ‘DropandCreate’,
@subsystem = ‘TSQL’,
@command =

Declare @dynSQL1 varchar (100)
,@dynSQL2 varchar (100)
,@dynSQL3 varchar (100)
,@dynSQL4 varchar (100)
,@dynSQL5 varchar (100)
select @dynSQL1 = ”if exists (select * from dbo.sysobjects where id = object_id ”
select @dynSQL2 = @DynaSql1 + ”(N”[basmala_38].[dbo].[OrdersByDay]”) and OBJECTPROPERTY(id, N”IsUserTable”) = 1) ”
select @dynSQL3 = @DynaSql2 + ”drop table [basmala_38].[dbo].[OrdersByDay] Go”
exec(@dynsql3) select @dynSQL5 = ”
CREATE TABLE [basmala_38].[dbo].[Orders] (
[OrderId] [int] NOT NULL ,
[OrderDate] [smalldatetime] NOT NULL ,
[OrderTime] [smalldatetime] NULL ,
[BranchId] [tinyint] NULL ,
[MemberId] [bigint] NULL ,
[EmpId] [int] NULL ,
[DocDate] [smalldatetime] NULL ,
[Status] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalPoints] [int] NULL ,
[TotalMoney] [money] NULL
) ON [PRIMARY]
” select @dynSQL5 = @dynSQL5 + ”Go”
exec(@dynSQL5) select @dynSQL5 = ”
ALTER TABLE [basmala_38].[dbo].[OrdersByDay] WITH NOCHECK ADD
CONSTRAINT [PK_OrdersByDay] PRIMARY KEY CLUSTERED
(
[OrderId],
[OrderDate]
) ON [PRIMARY] ” ‘ use msdb
EXEC sp_add_jobserver @job_name = ‘OrdersTransfer’,
@server_name = N'(ServerIp Address)’ use msdb
EXEC sp_start_job @job_name = ‘OrdersTransfer’
USE msdb
EXEC sp_update_jobschedule @job_name = ‘OrdersTransfer’,
@name = ‘ScheduledOrdersTransfer’,
@freq_type = 4, — daily
@freq_interval = 1,
@active_start_time = 00000 Problem
All steps run successfully execpt one step which is:
use msdb
EXEC sp_add_jobserver @job_name = ‘OrdersTransfer’,
@server_name = N'(Server IP Address)’
N.b I write the server name as as the server Ip Address
The error generated is:
Only members of the sysadmin role can execute this stored procedure. I tried to enter with sa login and execute the sp but it also failed. I checked the account of the sql server agent and I found it a local system account (This is on the hosting SQL Server).
Should it be a Domain User Account?
and if so does this make a security hole or somthing like this?
and if domain user account is used does this mean that user name and password that should be used by me to execute the job are the same used to open the computer that sql server is installed on and if so does this mean that server support people have to execute it by themselves. I hope my understanding is not bad.
Thanks I do not know what is wrong, any help will be appreciated. Thanks a lot
Rasha zaki
Web Developer
Cairo, Egypt
As already the discussion is underway this thread is locked and further information, referhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5765 and this thread is locked. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>