Solved

MS SQL Server Agent Job not working

Posted on 2013-01-27
6
404 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

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.

Question has a verified solution.

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

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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

832 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