Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zero1de
Posting Yak Master
105 Posts |
 Posted - 2012-12-06 : 06:26:21
|
| 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
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-06 : 07:18:15
|
| 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. |
 |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-12-06 : 07:57:22
|
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 !! THXquote:
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.
|
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-12-06 : 08:44:58
|
| 1) That's the error2) 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. |
|
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-12-06 : 08:53:16
|
Now it works after i set the query timeout=0Thx for your tip !quote:
Originally posted by nigelrivett
1) That's the error2) 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.
|
|
|
|
|
|
|
Agent-Job don't fill table