SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Agent-Job don't fill table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zero1de
Yak Posting Veteran

Germany
77 Posts

Posted - 12/06/2012 :  06:26:21  Show Profile  Reply with Quote
Hi,

I've created an agent job to update a table over a View. There is a stored procedure which truncate and insert rows from a View into the table.
Everthing works fine, when I call the sp manuell on the db server. The problem is when I try to schedule(over linked Server) the job it runs for 20 minutes. There is no error message.
It confirms the job succeeded. The Job was invoked by user xxx \ xxx. The last step to run was step 1 (SP_Update_Metadata). But the table is emtpy !!
I do not understand why the table can not be filled with the job agent ?

The Agent-Job runs over linked Server ! and sp is on sql express edt.

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/06/2012 :  07:18:15  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Does it work when you run it manually using the linked server?
Are you checking for errors in the stored procedure?
Does it get any rows from the view when run from the job - i.e. is it getting no data or failing to insert the data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

zero1de
Yak Posting Veteran

Germany
77 Posts

Posted - 12/06/2012 :  07:57:22  Show Profile  Reply with Quote
1) Oh i get this msg :The OLE DB provider "SQLNCLI" for linked server "xxxx" returned message "Query timeout expired"....
2) sp gives no error msg. it's runs succesfull.
3) View has nothing to do with the query. I have created the view, only for that reason, that I can update the table faster with the same data. And yes i allways get rows from the View.

!! I will try to set Remote query timeout=0 to get unlimited time !!

THX

quote:
Originally posted by nigelrivett

Does it work when you run it manually using the linked server?
Are you checking for errors in the stored procedure?
Does it get any rows from the view when run from the job - i.e. is it getting no data or failing to insert the data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.


Edited by - zero1de on 12/06/2012 08:01:28
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/06/2012 :  08:44:58  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
1) That's the error
2) No it doesn't - you haven't trapped the error.

Time might not help it's proibably an issue with the update. Try it with just one row.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

zero1de
Yak Posting Veteran

Germany
77 Posts

Posted - 12/06/2012 :  08:53:16  Show Profile  Reply with Quote
Now it works after i set the query timeout=0
Thx for your tip !

quote:
Originally posted by nigelrivett

1) That's the error
2) No it doesn't - you haven't trapped the error.

Time might not help it's proibably an issue with the update. Try it with just one row.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000