SP_EXECUTESQL insert to temp table cause ‘String or binary data would be truncated.’
SQL dynamically query was quite powerful but sometimes we need pay attention to it. The below SQL code snippet example just perform execute dynamic command and then insert into temp table. The stored procedure execute over production environment just return empty query but without any errors coming back to the application layer.
This behavior was quite danger cause something went wrong while invoked the stored procedure but just return no result to application layer. This make the issue/problem hard to detect and monitor:(.
As below screen-shot shown, if we copy and paste the same stored procedure into ‘SQL Server Management Studio’ and see we have got some message level data captured but the results still remain blank.
Why got the type of error? It might simply because that one of your columns in the source table is bigger than your destination columns.
The next step we need to find out which column(s) lead to the problem occurred. We could rewrite the script and return the dynamical query result as JSON format:
declare @Date varchar(26) = '2023-11-03';
declare @runSQL nvarchar(500);
declare @jsonResult nvarchar(max);
set @runSQL =
N'set @json = (select * from OPENQUERY(LS_JDE_CDC,''EXEC JdeStore_FE.dbo.Usp_ReadJDEByInvoiceDate '''''
+ @Date + N''''''') for json path)';
exec [dbo].[sp_executesql] @runSQL, N'@json nvarchar(max) output', @json = @jsonResult output;
-- print the original json result to observe
--print @jsonResult
-- insert to destination table to compare which column(s) cause the issue?
select *
into [#tempInvoice]
from openjson(@jsonResult)
with
(
[Order Number] numeric(8, 0)
, [Customer PO] char(25)
, [MRID] char(25)
, [Branch] char(12)
, [Line Number] numeric(12, 6)
, [Item] char(25)
, [Desc2] char(30)
, [Currency] char(3)
, [Ordered Qty] numeric(15, 0)
, [Ordered Qty UOM] char(2)
, [Shipped Qty] numeric(15, 0)
, [Shipped Qty UOM] char(2)
, [Unit Price] numeric(15, 6)
, [Extended Price] numeric(30, 6)
, [Invoice No] numeric(8, 0)
, [Requested Date] date
, [Invoice Date] date
, [Sold to] numeric(8, 0)
, [Ship to] numeric(8, 0)
);
select * from [#tempInvoice];
drop table [#tempInvoice];
Finally, we need the fixed the issue by expend our destination columns length and then wrapper our script with proper try-catch:
declare @Date varchar(26) = '2023-11-03';
declare @runSQL nvarchar(500);
begin try
set @runSQL =
N'select * from OPENQUERY(LS_JDE_CDC,''EXEC JdeStore_FE.dbo.Usp_ReadJDEByInvoiceDate '''''
+ @Date + N''''''')';
create table [#tempInvoice]
(
[Order Number] numeric(8, 0) not null
, [Customer PO] char(25) not null
, [MRID] char(25) not null
, [Branch] char(12) not null
, [Line Number] numeric(12, 6) not null
, [Item] char(25) not null
, [Desc2] char(30) not null
, [Currency] char(3) not null
, [Ordered Qty] numeric(15, 0) not null
, [Ordered Qty UOM] char(2) not null
, [Shipped Qty] numeric(15, 0) not null
, [Shipped Qty UOM] char(2) not null
, [Unit Price] numeric(15, 6) not null
, [Extended Price] numeric(30, 6) not null
, [Invoice No] numeric(8, 0) not null
, [Requested Date] date not null
, [Invoice Date] date null
, [Sold to] numeric(8, 0) not null
, [Ship to] numeric(8, 0) null
);
insert into [#tempInvoice]
(
[Order Number]
, [Customer PO]
, [MRID]
, [Branch]
, [Line Number]
, [Item]
, [Desc2]
, [Currency]
, [Ordered Qty]
, [Ordered Qty UOM]
, [Shipped Qty]
, [Shipped Qty UOM]
, [Unit Price]
, [Extended Price]
, [Invoice No]
, [Requested Date]
, [Invoice Date]
, [Sold to]
, [Ship to]
)
exec [dbo].[sp_executesql] @runSQL;
select *
from [#tempInvoice];
drop table [#tempInvoice];
end try
begin catch
select error_number() as [ErrorNumber]
, error_severity() as [ErrorSeverity]
, error_state() as [ErrorState]
, error_procedure() as [ErrorProcedure]
, error_line() as [ErrorLine]
, error_message() as [ErrorMessage];
throw;
end catch;
As above screen shot showed that with proper try-catch session that we have chance to notice application layer to handle it:).
