DTS Package Not running from sp_start_job

[MrJRW]

Good Evening

This one has me baffled.

I've developed a simple DTS package (called Copy Table) that establishes an ODBC connection with a Sybase Server/Database, and copies a table to a table on my MS SQL Server Server/Database.

My intent is that a Powerbuilder client will associate a button click event with running this package.

An earlier thread gave me idea of scheduling the copy package, then going into the job details and deleting the schedule. Executing the package could then be accomplished by executing the stored procedure sp_start_job from the msdb database.

So I created a stored procedure:

Create Procedure Copy_Sybase_Table
AS
EXEC msdb.dbo.sp_start_job @job_name = "Copy Table"
GO

Clicking the button on my Powerbuilder client logins in to my SQL server as "SAM" and executes the Copy_Sybase_Table stored procedure.

Now the login "SAM" has been granted access to the msdb database, and exec rights to sp_start_job stored procedure
"SAM" has also been given rights to read and write to the target table on SQL 2K. The Sybase ODBC connection is connecting with a sa login.

Now when I login to SQL 2K as "SAM" and open the "Copy Table" package and click on the green arrow to run, the package works !

But... when I execute the stored procudure Copy_Sybase_Table from my Powerbuilder Client I get the following error message:

DB Error Code 14262
DB Error Message: Select error SQLSTATE = 37000
The specified @job_name('Copy Table') does not exist.

Huh ????

O.K. I then logged into Query Analyzer as "SAM", ran the following SQL script:

EXEC msdb.dbo.sp_start_job @job_name = 'Copy Table'

The message pane returned with:
"Job 'Copy Table' started successfully"
But the execution timer on the bottom showed 00:00

Logging in to Enterprise Manager as "sa" and going to Jobs showed the execution of the package as Failed !

So I'm thinking it's a rights issue, but where ?

Any Ideas ???

MrJRW

 

[gradley]

Try adding "SAM" to the TargetServersRole in the msdb database. As I found out recently, it comes with serveral other procedures that are required to run SQL Server Agent jobs without being a sysadmin. Depending on the service pack of SQL Server 2000 you are using, you may need to modify the TargetServersRole to grant "EXEC" rights to sp_StartJob and sp_StopJob

Hope this helps!

 

[MrJRW]

Gradley,

First off, Thank-you for responding, and
Second, my apologizes for responding so late.

I got my package to work. It ultimately came down to giving "SAM" sysadmin rights....
The security officer wasn't too pleased, but, Oh well.

I also had problems establishing a proxy account, so giving "SAM" sysadmin rights also solved the problem of a non-sysadm executing ExecCmd (?) commands

Again, Thanks for the help.

MrJRW