Problem
Executed as user: CORP\SVC-UK-SVCEMI. Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241). The step failed.
The problem I am facing is that I have created a Stored Procedure which, when executing in a SSMS Query window, runs fine - but fails when ran ran from a SQL Agent job.
Why I'm Stuck
As the job is running the exact same procedure, on the same database and data, I am severely confused on why this wouldn't work.
My Thoughts
Logically, I would put it down to the user account (As this is the only possible different I had identified), thinking maybe the SQL Agent service account somehow has a difference date format setting when myself. However, I am under the impression that these are set at server level (Which we have recently changed to GB-English) - rather than account level.
As I can't manage to replicate this issue in the query window, it has become extremely difficult to debug which row is causing the issues
Running from SSMS Query Window
SQL Agent Jobstop Configuration
Error Generated When Running Job
As I believe this to be a SQL Agent configuration issue, I haven't added any code from the SQL Procedure. I can do this on request, but it is very long (Think I take readability over shortness - and possibly performance- when it comes to writing SQL)
Any ideas on what could be causing this problem would be greatly appreciated.
Many thanks.
Ths stored proc in the Job is probably running under a different user account than the one you use when launching it from SSIS.
and, probably, this user account has different settings
try "EXECUTE AS user=xxxx" for your stored proc and use your own user account, then see what happens...
see: EXECUTE AS