SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Statement Task not running when scheduled


T-SQL Statement Task not running when scheduled

Author
Message
Luv SQL
Luv SQL
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 99
I had the same issue when just pasting in syntax as a SQL job where it runs the syntax as the SQL Agent Service Account, which is not helpful at all. Creating Credentials and Proxy Accounts is WAY too complicated just to run one large insert statement, so I tried creating it in SSIS.

I've added one T-SQL Statement Task and pasted in my large insert statement. This runs fine and inserts rows if I run this in Query Analyzer. If I run the sql job, again, it does nothing. I setup the Connection Manager to use a SQL login that I have setup on other SSIS tasks that insert data, so I don't understand why this doesn't work. I am able to save the password with SSIS as I've updated the DataSource in AGENT and added the password so it's not a password issue. The SQL Login is a sysadmin and it does have permissions in SQL to insert. The syntax itself just does not seem to run. I also tried creating a stored procedure to run the insert but that doesn't run it properly either as a scheduled task.

I'm literally giving up and manually opening my syntax from a text file and manually running it each day. You'd think using SSIS would be more automated than that.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109464 Visits: 22278
Luv SQL - Tuesday, November 14, 2017 1:39 PM
I had the same issue when just pasting in syntax as a SQL job where it runs the syntax as the SQL Agent Service Account, which is not helpful at all. Creating Credentials and Proxy Accounts is WAY too complicated just to run one large insert statement, so I tried creating it in SSIS.

I've added one T-SQL Statement Task and pasted in my large insert statement. This runs fine and inserts rows if I run this in Query Analyzer. If I run the sql job, again, it does nothing. I setup the Connection Manager to use a SQL login that I have setup on other SSIS tasks that insert data, so I don't understand why this doesn't work. I am able to save the password with SSIS as I've updated the DataSource in AGENT and added the password so it's not a password issue. The SQL Login is a sysadmin and it does have permissions in SQL to insert. The syntax itself just does not seem to run. I also tried creating a stored procedure to run the insert but that doesn't run it properly either as a scheduled task.

I'm literally giving up and manually opening my syntax from a text file and manually running it each day. You'd think using SSIS would be more automated than that.

SSIS is as 'automated' as you make it.

If you cannot make a SQL Agent job work because of security issues, it seems that you need to learn more about security. But if that's 'way too complicated', and given that you have not actually asked for any assistance, I guess you should stick with doing things manually.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Sue_H
Sue_H
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28168 Visits: 8123
Luv SQL - Tuesday, November 14, 2017 1:39 PM
I had the same issue when just pasting in syntax as a SQL job where it runs the syntax as the SQL Agent Service Account, which is not helpful at all. Creating Credentials and Proxy Accounts is WAY too complicated just to run one large insert statement, so I tried creating it in SSIS.

I've added one T-SQL Statement Task and pasted in my large insert statement. This runs fine and inserts rows if I run this in Query Analyzer. If I run the sql job, again, it does nothing. I setup the Connection Manager to use a SQL login that I have setup on other SSIS tasks that insert data, so I don't understand why this doesn't work. I am able to save the password with SSIS as I've updated the DataSource in AGENT and added the password so it's not a password issue. The SQL Login is a sysadmin and it does have permissions in SQL to insert. The syntax itself just does not seem to run. I also tried creating a stored procedure to run the insert but that doesn't run it properly either as a scheduled task.

I'm literally giving up and manually opening my syntax from a text file and manually running it each day. You'd think using SSIS would be more automated than that.


Are you just frustrated?
Executing a query using a Agent job shouldn't be that painful. If you decide you want to automate it, give it a try and capture the full, exact error message from the job history and plenty of folks up here can help. Also provide any information about the insert and where that data comes from. Sometimes it's a matter of figuring out the right tool for the job - maybe it is SSIS that is the better option.

Sue



Luv SQL
Luv SQL
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 99
There is no error when running the job in SQL Agent that's the problem. It says it ran successfully yet no inserts were done. If I copy and paste the syntax from the job into query analyzer, the inserts occur. If you google this, it seems to be a common problem with agent truncating text yet no real solutions. I don't have security issues. The syntax runs and inserts. I've changed the owner of the job to be a valid SQL login that has full permissions, even tried sa, yet it will not run the insert.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109464 Visits: 22278
Luv SQL - Wednesday, November 15, 2017 8:13 AM
... it seems to be a common problem with agent truncating text ...

Can you provide a link or two to elaborate on what you mean by this? I'm not familiar with Agent truncating text, so I'd like to know more.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Luv SQL
Luv SQL
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 99
https://www.sqlservercentral.com/Forums/1697723/Scheduled-Job-runs-successfully-but-table-does-not-update
https://stackoverflow.com/questions/34161158/sql-server-job-runs-successfully-but-doesnt-execute-packages
https://community.spiceworks.com/topic/91933-sql-server-job-activity-monitor-says-suceeded-but-jobs-did-not-ru
https://dba.stackexchange.com/questions/89587/update-code-works-in-manual-stored-procedure-but-not-when-run-as-sql-server-agen

These are all relatively the same. I've also tried the stored procedure route but doesn't work either. There are many more of the same.
Sue_H
Sue_H
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28168 Visits: 8123


I'm aware of a few scenarios where it can happen but all of them have workarounds so it won't prohibit anything. Some of it due to Agent using a default textsize of 1024 so the limit can also be 512 if using unicode data types. But you will generally get an error or warning when you hit that. You just preface whatever your doing by setting a larger textsize. The SSIS ones are mostly not related to Agent but rather package and/or job configurations.
The only true "silent" failure I'm aware of is the truncation of parameters but that's really a coding error. You can have a variable that is larger than the declared size and it Agent will truncate it to the correct size which won't be logged anywhere.
But it does depend on what's being done - which we have no idea whatsoever other than it's an insert.
Keep in mind though that the stored procedure one is addressed just by changing the textsize in the job step.
SET TEXTSIZE 10000 EXEC dbo.YourStoredProcedure parm1, parm2....etc.

Sue




Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109464 Visits: 22278

The word 'truncate' appears in precisely zero of these links.
In cases where something works when executed by a person, but fails when executed by SQL Agent, the reason is almost always permissions ... especially if anything is being accessed outside of the SQL Instance where SQL Agent is running – and this explains a couple of the links.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Luv SQL
Luv SQL
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 99
If there are permission issues, should I not see "permission denied on.." or "insert failed on.." etc? There are no errors in the agent log or sql log. It says it is successful.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)SSC Guru (109K reputation)

Group: General Forum Members
Points: 109464 Visits: 22278
Luv SQL - Wednesday, November 15, 2017 12:25 PM
If there are permission issues, should I not see "permission denied on.." or "insert failed on.." etc? There are no errors in the agent log or sql log. It says it is successful.

I agree that you should see an error.

But in my experience, you don't always see one. It seems, at least sometimes, that when SSIS cannot access something because of permissions, it just completes the task successfully, without actually doing anything (because no data or files were found).



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search