2

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 Running from within SSMS Query Window

SQL Agent Jobstop Configuration SQL Agent Jobstop Configuration

Error Generated When Running Job 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.

Scott Allen
  • 513
  • 2
  • 13
  • Improve your date handling sql code... – ErikEJ Nov 21 '14 at 18:01
  • 1
    1) Show us the code. If you can indicate where the failure is, that would be helpful 2) you say "*I am under the impression that these are set at server level..*" There's a sever-level default, but they can also be set at the session-level and upon connection, IIRC. – RBarryYoung Nov 21 '14 at 18:06
  • @RBarryYoung - Looks like you're right. I have set up a trace and when the procedure is ran through the Agent, it seems to set itself as "Changed language setting to us_english" on running. How can I identify the cause? – Scott Allen Nov 21 '14 at 18:08
  • Thats a connection setting coming from your SSIS package. If you can show us the line that is failing and what the date strings look like, we may be able to show you how to code around this problem. – RBarryYoung Nov 21 '14 at 18:12
  • 1
    Cheers RBarry, Looks like when the languages were set on the database the service account was missed out. Hope not for any valid reason. Running the below on master db fixed my issue ALTER LOGIN [CORP\SVC-UK-SVCEMI] WITH DEFAULT_LANGUAGE=[British] – Scott Allen Nov 21 '14 at 18:30

1 Answers1

0

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

Antonio
  • 535
  • 10
  • 26