Solved

MS SQL Server Agent Job not working

Posted on 2013-01-27
6
400 Views
Last Modified: 2013-02-05
I have 2 tables within a database, lets call them table1 and table2. They are related and both have a primary key. Table1 is populated by an application which audits the computers on the network at startup, and table2 is there for me to front-end an audit application to.

I want to populate table2 from table1 every 15 minutes with records which are not already in table2. I have written the following SQL query to do this:

insert into table2(MAC_Address)
select table1.MAC_Address from table1
where MAC_Address <> table1.MAC_Address;

Open in new window


MAC_Address is the primary key in table1 and links to MAC_Address in table2

When i run this statement manually it works fine, however I have created a SQL Server Agent Job with a step to run it and it "succeeds" but never updates table2. I have set the owner of the job to the dbo of the specific db, I have the job step type set to T-SQL - however "run-as" is blank, and I have set the DB to the correct DB.

I am far from an expert at this - database design is not my skillset, however any help will be greatly appreciated.

Thanks.
0
Comment
Question by:gmbaxter
6 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
Comment Utility
Try wrapping the statement in a Try...Catch and either log it in a table or send you an email notification. The 'success' you are seeing is only that the job completed, not that the query did what you intended.

http://technet.microsoft.com/en-us/library/ms179296%28v=sql.105%29.aspx
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
I don't see how the statement you've shown here would accomplish what you're looking for - as I understand it, it would never insert anything into table2. If you run it manually, it would succeed, but not write any records, just like it succeeds as an agent job.

The INSERT and SELECT portion of the query are separate, and the SELECT you've provided would never return any records to insert. Instead, try the following format:

insert into table2(MAC_Address)
select t1.MAC_Address
  from table1 t1
  left
  join table2 t2
    ON t1.MAC_Address = t2.MAC_Address
 WHERE t2.MAC_Address IS NULL

Open in new window


Using the LEFT JOIN would take the rows from table1 and match them to rows in table2 (on MAC_Address), but the WHERE clause (WHERE t2.MAC_Address IS NULL) returns only rows that don't match - the value from table2 is null when that address isn't already in table2.

I hope this helps!
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
You might be better off setting up a trigger on table1 insert that will update table2.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 11

Author Comment

by:gmbaxter
Comment Utility
@ jmiller1979
You're right it doesn't work!

@ ryan mccauley

Can you please explain the t1 and t2 - why are they being substituted instead of the table names?

@ arnold

Sounds good - instead of my agent job? How is that achieved please?

Thanks for the assistance so far.
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 400 total points
Comment Utility
They don't need to be - they're just table aliases, and make it quicker to refer to the tables later in the query. If you'd prefer not to use them, you can instead substitute the table names themselves:

insert into table2(MAC_Address)
select table1.MAC_Address
  from table1
  left
  join table2
    ON table1.MAC_Address = table2.MAC_Address
 WHERE table2.MAC_Address IS NULL

Open in new window


However, since you're dealing with columns that are identically named in both tables, you need to specify to the query engine which table you're referring to - that's why I've preceded every column with a table reference. However, it's personal preference about whether you use an alias or the full table names.
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 100 total points
Comment Utility
http://msdn.microsoft.com/en-us/library/ms189799.aspx
http://www.devguru.com/technologies/t-sql/7138.asp

Create trigger updatetanle2 on database.table1 for insert
As
Declare @macaddress <data type>
select @macaddress= (select mac_address fom inserted)
insert into database.table2 (mac_address) values (@macaddress)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now