Solved

MS SQL Server Agent Job not working

Posted on 2013-01-27
6
407 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 38824608
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
ID: 38824953
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 78

Expert Comment

by:arnold
ID: 38825575
You might be better off setting up a trigger on table1 insert that will update table2.
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 11

Author Comment

by:gmbaxter
ID: 38829046
@ 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
ID: 38829067
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 78

Assisted Solution

by:arnold
arnold earned 100 total points
ID: 38829517
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL syntax for max(date) 3 39
SQL: get ride of blank rows 11 23
SQL - Subquery in WHERE section 4 34
ErrorKind in crystal reprot VB.Net 1 17
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

751 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