September 16, 2009 at 2:14 pm
Dear Sirs,
I have a too strange trouble here.. I hope someone can helpme...
In my environment there's a JOB that contains only one step and this one should execute a procedure. When whe run this job, indenpendently manually or scheduled, it returns the following error:
Msg 8152, Sev 16, State 2, Line 32 : String or binary data would be truncated. [SQLSTATE 22001]
Msg 3621, Sev 16, State 1, Line 1 : The statement has been terminated. [SQLSTATE 01000]
Job 'NOME_DO_JOB ' : Step 1, 'executar_step_1' : Began Executing 2009-09-15 11:18:07...
The question is, when this procedure is executed directly into a new query, the procedure is executed normally, and the due alterations is done in the database... When we try execute it on the JOB, I god an error and no alteration are done...
This is a huge problema and I need to solve it as soon as possible,
Any help are welcome...
Thank you so much...
att
Edvaldo Castro
September 16, 2009 at 2:42 pm
I recall having had a similar issue, and I am not sure if this was what solved the problem.
Alter the Stored procedure and add this above the ALTER line:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
and execute (save) the Stored Procedure.
Again, I am not 100% sure if this is what it was, but it was something along the lines...
September 16, 2009 at 5:03 pm
Is it SSIS job?
September 16, 2009 at 6:35 pm
Sergey Vavinskiy (9/16/2009)
Is it SSIS job?
No... It isn`t a SSIS job...
follow a part of the code
declare @v_Max_Lote int
declare @r1_cd_serie_doc_transporte int
declare @v_ini_faixa numeric(18,0)
declare @v_fim_faixa numeric(18,0)
set @v_Max_Lote = 977
set @r1_cd_serie_doc_transporte = 4
set @v_ini_faixa = 70031
set @v_fim_faixa = 70040
insert into TRA_LOTE_DOC_TRANSPORTE
(CD_LOTE_DOCUMENTO, CD_FORNECEDOR, CD_SERIE_DOC_TRANSPORTE,
NUM_INICIO_FAIXA, NUM_FIM_FAIXA, DATA_LIBERACAO,
DATA_REG, EST_REG, CD_TIPO_SITUACAO_INICIAL)
values(@v_Max_Lote, null, @r1_cd_serie_doc_transporte,
@v_ini_faixa, @v_fim_faixa, getdate(),
getdate(), 'A', 1)
September 17, 2009 at 8:53 am
September 17, 2009 at 10:36 am
Richard M. (9/17/2009)
Can you provide the table definition of TRA_LOTE_DOC_TRANSPORTE ?Also, did you try the commands I posted previously?
The commands you've posted previously, was already in the procedure...
the describe of the table is:
[CD_LOTE_DOC] [int] NOT NULL,
[CD_FORN] [int] NULL,
[CD_SERIE_DOC_TRANSP] [int] NOT NULL,
[NUM_INI_FAIXA] [numeric](18, 0) NOT NULL,
[NUM_FIM_FAIXA] [numeric](18, 0) NOT NULL,
[DATA_LIBER] [datetime] NULL,
[DATA_REG] [datetime] NOT NULL,
[EST_REG] [varchar](1) NOT NULL,
[CD_TIPO_SIT_INICIAL] [int] NULL,
Thanks for your help...
September 17, 2009 at 11:02 am
When you execute it manually, does it produce any warnings?
Also, are there any other inserts happening inside this proc?
Lastly, inside the sql agent job, what is the command you use to call the proc (or this section of code), or is that code listed the exact code you use in the sql agent job?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2009 at 11:07 am
It is weird.
I created a table with your table definition and was able to run your insert statement from the SSMS AND a job without any error messages. So, it should work.
Please make sure you use exactly the same code and a target database in both places.
September 17, 2009 at 12:27 pm
CirquedeSQLeil (9/17/2009)
When you execute it manually, does it produce any warnings?Also, are there any other inserts happening inside this proc?
Lastly, inside the sql agent job, what is the command you use to call the proc (or this section of code), or is that code listed the exact code you use in the sql agent job?
Question1: When you execute it manually, does it produce any warnings?
Answer:Nope, when I execute it manually, It doesn't returns any warning or error...
Question2: are there any other inserts happening inside this proc?
Answer:
declare @v_Max_Lote int
declare @r1_cd_serie_doc_transporte int
declare @v_ini_faixa numeric(18,0)
declare @v_fim_faixa numeric(18,0)
set @v_Max_Lote = 977
set @r1_cd_serie_doc_transporte = 4
set @v_ini_faixa = 70031
set @v_fim_faixa = 70040
insert into TRA_LOTE_DOC_TRANSPORTE
(CD_LOTE_DOCUMENTO, CD_FORNECEDOR, CD_SERIE_DOC_TRANSPORTE,
NUM_INICIO_FAIXA, NUM_FIM_FAIXA, DATA_LIBERACAO,
DATA_REG, EST_REG, CD_TIPO_SITUACAO_INICIAL)
values(@v_Max_Lote, null, @r1_cd_serie_doc_transporte,
@v_ini_faixa, @v_fim_faixa, getdate(),
getdate(), 'A', 1)
the inserts, are happening on this part of code...
Question3: inside the sql agent job, what is the command you use to call the proc (or this section of code), or is that code listed the exact code you use in the sql agent job?
Answer:
I'm calling the procedure using the follow command: "exec pr_tra_liberar_lote"
September 17, 2009 at 1:47 pm
edvaldocastro (9/17/2009)
CirquedeSQLeil (9/17/2009)
When you execute it manually, does it produce any warnings?Also, are there any other inserts happening inside this proc?
Lastly, inside the sql agent job, what is the command you use to call the proc (or this section of code), or is that code listed the exact code you use in the sql agent job?
Question1: When you execute it manually, does it produce any warnings?
Answer:Nope, when I execute it manually, It doesn't returns any warning or error...
Question2: are there any other inserts happening inside this proc?
Answer:
declare @v_Max_Lote int
declare @r1_cd_serie_doc_transporte int
declare @v_ini_faixa numeric(18,0)
declare @v_fim_faixa numeric(18,0)
set @v_Max_Lote = 977
set @r1_cd_serie_doc_transporte = 4
set @v_ini_faixa = 70031
set @v_fim_faixa = 70040
insert into TRA_LOTE_DOC_TRANSPORTE
(CD_LOTE_DOCUMENTO, CD_FORNECEDOR, CD_SERIE_DOC_TRANSPORTE,
NUM_INICIO_FAIXA, NUM_FIM_FAIXA, DATA_LIBERACAO,
DATA_REG, EST_REG, CD_TIPO_SITUACAO_INICIAL)
values(@v_Max_Lote, null, @r1_cd_serie_doc_transporte,
@v_ini_faixa, @v_fim_faixa, getdate(),
getdate(), 'A', 1)
the inserts, are happening on this part of code...
Question3: inside the sql agent job, what is the command you use to call the proc (or this section of code), or is that code listed the exact code you use in the sql agent job?
Answer:
I'm calling the procedure using the follow command: "exec pr_tra_liberar_lote"
From what you have shown us, the variables match the columns and the code works just fine for two of us. So, that leads me to believe that there is something else in that proc that might be causing this error to be thrown. Could you post the entire code from the proc (top to bottom)?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2009 at 6:15 pm
Remembering...
the Error only happens when I run it using a JOB...
September 18, 2009 at 9:59 am
Will you also script the job and show us that?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 18, 2009 at 10:11 am
One more thing to try is to check your ansi_warnings setting.
Try
set ansi_warnings on
execute your proc
If you get an error, then reverse the setting. And then add set ansi_warnings off to the job just before the proc execution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 18, 2009 at 11:20 am
September 18, 2009 at 11:48 am
CirquedeSQLeil (9/18/2009)
One more thing to try is to check your ansi_warnings setting.Try
set ansi_warnings on
execute your proc
If you get an error, then reverse the setting. And then add set ansi_warnings off to the job just before the proc execution.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply