Problem getting execution status of a SQL Agent Job

I’m having trouble retrieving the execution status of a job – perhaps someone can help me.

I have a stored proc (on SQL 2005) that dynamically creates and executes a SQL Agent Job. The stored proc first has to check whether the job exists. If it already exists and is not active then I simply reuse the existing job. However if the job exists but is currently executing then I need to create a new job because there can be multiple instances of the program that the job runs. My problem is how to check whether the job is currently executing. I tried using sp_help_job as shown below but the rowcount seems to return 1 even when there are no jobs executing!

Exec msdb.dbo.sp_help_job

@.job_aspect = 'JOB',

@.execution_status = 0,

@.job_name = @.JobName

If @.@.rowcount > 0 Begin

…

End

I then tried this code…

If (select count(*) from msdb.dbo.sysjobs j

left outer join msdb.dbo.sysjobhistory h on j.job_id = h.job_id where name = @.JobName and

h.step_id = 1 and

run_status = 4) > 0

Begin

…

End

… but it doesn’t seem to pick up records even when the job is currently executing.

How can I, in T-SQL, get the execution status of the job?

I have the same issue. When I began to dig into sp_help_job, I noticed a call within the procedure to build a temp table:

exec master.dbo.xp_sqlagent_enum_jobs 1, {insert job owner here}

This procedure outputs a 'Running' field that will return a 1 if it the job is running, or otherwise a 0. This is not complete code for you, but should get you pointed in the right direction. I am going to build a temp table with this XP and filter on the job ID to return the status.

|||sp_help_jobactivity is the sp that should give status of jobs in SQL2005. See BOL for more info on this SP.sql

Problem copying table

We upgraded to SQL Server 2005 & I'm having trouble with the Import and Export Wizard in the Management Studio. We periodically need to export some data to a different database to save it while we update the 'real' database which basically starts it over with empty tables. Under SQL Server 2000 that wasn't a problem. Under 2005 it is.

The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.

Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)

Any suggestion how to get around this? Thanks.You cannot insert specific values to a column when the datatype is timestamp. Depending on the source of your data, you most likely want to change the datatype of the destination column to either binary(8), varbinary(8) or datetime. More information about timestamp columns in SQL Server is available in BOL (http://msdn2.microsoft.com/en-us/library/ms182776.aspx).|||You know, I thought my question was clear. I know what the problem is, what I don't know is how to solve it-and since the app comes from a vendor changing it myself isn't an option. (Is it really an option for you, roac, or do you just not work with applications you don't develop yourself?)

FWIW I submitted a request to the vendor to remove/change the timestamp column-no response, yet, which is why I'm looking for a solution that I can implement.

So far it looks like I'm stuck-either wait for the vendor to make the change or start writing queries. I figure at least two solid days' work to write the queries, and then there's testing & keeping them updated.|||Ah, I see now, I obviously overlooked a few details :)

Hopefully you have a test-environment where you can try the following procedure:

1. Backup the database
2. Restore on alternate location
3. Use ApexSQL Diff (http://www.apexsql.com/sql_tools_diff.asp) to script changes to structure
4. Restore database where backup was taken
5. Run script to apply changes in structure.

As far as I can see this approach should work. If the database is not deleted and recreated during upgrade, and you are using Enterprise Edition, you could use a Database Snapshot instead of backups.|||Wow, Calvin. That was a pretty rude response to someone who honestly was trying to help you.
I have a script that might assist you in writing queries for these lengthy tables, but I hesitate to post it because it may not be exactly what you want.|||roac, I'll take a look at ApexSQL. Right off I don't see how it will help as your description sounds like a way to change the timestamp column and what I need is a way to backup/restore all the data except the timestamp column. But maybe ApexSQL will let me do that-I'll take a look & thanks.

Blindman, I'd be happy to take a look at your script. Thanks.|||ApexSQL diff is a tool for scripting the code neccessary to change one version of a data structure to another. So, you can create a script that change the data structure of the data that you already have to the version created by the application's upgrade.|||Attached is a script for creating formatted lists of column names for easy cut-n-paste into code. Make sure when you run it that you have QA set to output results in text mode.

Problem constructing query to weed out some results...

Hi! I'm trying to put together some reports for an attorney's office. I'm having trouble constructing this query. What I'm doing is querying for defendants who do not have a particular charge against them.

Here are my tables:

DCase
----------
VBKey CaseNumber
1 33365
2 66585
etc..

DCharge
----------
VBKey ChargeNum
1 27
2 19
3 20
3 21
etc..
Since both tables are linked via VBKey, I joined them together and this is where I got stuck.

I tried using HAVING ChargeNum<>21 but it would still return a result b/c VBkey=3 has two charges against him. So, when do my queries, I'm still getting individuals who have multiple charges. I tried doing this with SELECT DISTINCT but it would still give me results from people with 3 or more charges. I'm lost at how to complete this query. Any help will be greatly appreciated! Thank you!SELECT *
FROM DCase
WHERE NOT EXISTS (SELECT *
FROM DCharge
WHERE DCharge.VBKey = DCase.VBKey
AND 21 = ChargeNum)-PatP|||Thank you very much Pat! That worked perfectly! Do you know of any good books I can use to better my sql skills?|||There are quite literally thousands of good books on using SQL. There are lots of tips and tricks that are product specific, and those are especially important for someone who's getting started.

If your primary environment is VB, I'd recommend Hitchhiker's Guide to Visual Basic and SQL Server (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&isbn=1572318481&itm=2). If you're looking for something a bit more generic, I'd suggest SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&isbn=0201433362&itm=1). If you really want to "grok" SQL, then I'd recommend almost anything by Joe Celko, but especially Joe Celko's SQL for Smarties: Advanced SQL Programming (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&isbn=0123693799&itm=1). In the interest of "full disclosure", while I count all of these folks as my friends or acquaintances, I make nothing from their books.

-PatP