sp_help_job (HELP)

[Guest]

Can anyone get sp_help_job to execute via ado.net ??

The following code produces a "severe error occurred on the command object"

SqlConnection connection = new SqlConnection(connectionString)
connection.Open()
SqlCommand cmd = new SqlCommand()
cmd.Connection = connection
cmd.CommandText = "exec msdb.dbo.sp_help_job @job_name='My SQL Job',@job_aspect='JOB'"
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandTimeout = 90
SqlDataAdapter da = new SqlDataAdapter(cmd)
DataSet ds = new DataSet()
da.Fill(ds)
connection.Close()

I can execute the same command Text in SQL Query Analyzer without a problem (using the same connection id and pwd)
Even if I don't pass any parms I get the error. I've tried to execute it as a proc and as text

Any ideas ??

 

[William Ryan eMVP]

Hi Jzink:

I just ran this (I took out the conn.open adn close b/c the dataadapter will
do it, but that's not the problem) and had no problems:

SqlCommand cmd = new SqlCommand("exec msdb.dbo.sp_help_job
@job_name='DailyDefrag',@job_aspect='JOB'", cn);

cmd.CommandType = System.Data.CommandType.Text;

cmd.CommandTimeout = 90;

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

Verify the names and the permission...otherwise the syntax and all should
work. This is the exact code I used and it's fine.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

Can anyone get sp_help_job to execute via ado.net ???

The following code produces a "severe error occurred on the command object":

SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = "exec msdb.dbo.sp_help_job @job_name='My SQL Job',@job_aspect='JOB'";
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandTimeout = 90;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
connection.Close();

I can execute the same command Text in SQL Query Analyzer without a

problem (using the same connection id and pwd).

 

[William Ryan eMVP]

More:

I wrapped it in a try catch block and tried to break it. If you don't have
it spelled right it will tell you it can't find the job. The only thing
that I can get to mimc this is with insufficient permissions, but to do that
the account still has to have db persmissions to open the connection (sinc
eyou are opening it early on, this isn't the problem.

I looked through our stuff yesterday and can't find it but just to be sure,
it's defintely the Fill line that's causing this right?

For easy, why not try catch it and see if we can garner some more info from
the exception...I already wrapped it in the previous snippet.

Bill

www.devbuzz.com
www.knowdotnet.com

Can anyone get sp_help_job to execute via ado.net ???

The following code produces a "severe error occurred on the command object":

SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = "exec msdb.dbo.sp_help_job @job_name='My SQL Job',@job_aspect='JOB'";
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandTimeout = 90;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
connection.Close();

I can execute the same command Text in SQL Query Analyzer without a

problem (using the same connection id and pwd).

 

[Guest]

Here's some more info. If I use your code and put an invalid job name in I get back "invalid job name" from the sp call.
If I connect using the "sa" account it works. Obviously I can not use the "sa" account in my production code, but how can I track down the problem if it works in query analyzer with my other account that I use in my application ?

 

[William Ryan eMVP]

If you connect to the job as the person the owner of the job, you should be
ok. I"m not using SA in my invocations. Depending on what the job does you
may need admin privileges but I don't believe you do just to invoke it. I
guess the good news is that now it's narrowed down to a permissioning
problem.

Here's some more info. If I use your code and put an invalid job name in

I get back "invalid job name" from the sp call.

If I connect using the "sa" account it works. Obviously I can not use the

"sa" account in my production code, but how can I track down the problem if
it works in query analyzer with my other account that I use in my
application ?

 

[William Ryan eMVP]

If you can run it programatically using SA, and it doesn't work with the
other account, it really points to an ownership/permission problem.

more info...I am using an account called tqis_admin. If I change the job

I am querying to use tqis_admin as the owner it works. Still stumped why it
works using query analyzer. Also, all our production sql jobs have sa as
the owner. I can run my front-end code with sa as the login id.

 

[Guest]

It appears that via ado.net you can only query jobs using sp_help_job if your ado connection userID is the owner of the sql job. Is this by design or is this a bug ???