Solved

MS SQL Server Agent Job not working

Posted on 2013-01-27
6
406 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can I skip a node in XML? 9 32
sql query 5 44
denied execute as 13 26
How can I delete dublicated rows by using for loop in SQL? 2 14
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

756 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