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
Solved

MS SQL Server Agent Job not working

Posted on 2013-01-27
6
405 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
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 77

Expert Comment

by:arnold
ID: 38825575
You might be better off setting up a trigger on table1 insert that will update table2.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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 77

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

791 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