November 14, 2017 at 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.
November 14, 2017 at 2:20 pm
Luv SQL - Tuesday, November 14, 2017 1:39 PMI 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 14, 2017 at 3:15 pm
Luv SQL - Tuesday, November 14, 2017 1:39 PMI 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
November 15, 2017 at 8:13 am
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.
November 15, 2017 at 10:53 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 15, 2017 at 11:00 am
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.
November 15, 2017 at 11:15 am
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
November 15, 2017 at 11:36 am
Luv SQL - Wednesday, November 15, 2017 11:00 AMhttps://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-agenThese 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.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 15, 2017 at 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.
November 15, 2017 at 12:43 pm
Luv SQL - Wednesday, November 15, 2017 12:25 PMIf 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).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 15, 2017 at 1:52 pm
I know super frustrating. If I change the owner of the job, is there a reason why it won't use this account to run the actual transact-sql statement within the steps? I've changed the owner of the job to be a specific sql login, but it still shows "executed as NT AUTHORITY\NETWORK SERVICE" ie the SQL Agent account. Since I can't add this account to the permissions of a sql view, I can't modify the permissions. We don't have any Windows Authenticated users to use either.
November 15, 2017 at 1:56 pm
is it possible the SQL Agent job is setup to "go to the next step" on step failure? Maybe you can look at the job history a different way:SELECT j.name, jh.step_id, jh.step_name, jh.sql_message_id, jh.sql_severity, jh.message,
msdb.dbo.agent_datetime(jh.run_date,jh.run_time) AS run_datetime, run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 AS run_duration
FROM msdb.dbo.sysjobhistory AS jh
INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = jh.job_id
WHERE jh.run_date >= CONVERT(int, CONVERT(varchar(8),GETDATE()-1,112))
AND (jh.run_status = 0 OR jh.message like '%error:%')
AND jh.step_id > 0
ORDER BY jh.run_date DESC, jh.run_time DESC;
November 15, 2017 at 2:03 pm
Failures are set to quit job reporting failure.
November 15, 2017 at 2:09 pm
Is it possible to post the actual code?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 15, 2017 at 2:18 pm
Luv SQL - Wednesday, November 15, 2017 1:52 PMI know super frustrating. If I change the owner of the job, is there a reason why it won't use this account to run the actual transact-sql statement within the steps? I've changed the owner of the job to be a specific sql login, but it still shows "executed as NT AUTHORITY\NETWORK SERVICE" ie the SQL Agent account. Since I can't add this account to the permissions of a sql view, I can't modify the permissions. We don't have any Windows Authenticated users to use either.
The owner of a job does not dictate the context under which it runs, I'm afraid – it just changes who is allowed to view or modify the job itself. The job will run under the context of the user running the SQL Agent service, or a proxy user if you have configured one.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply