April 20, 2017 at 7:20 am
Is there any patch made in SQL about handling temporary table?
Previously the SQL (2012) is seemingly forgiving that when you create a temp table like this posed no problem:
create table dbo.#tmp_bcp (
dataid int primary key identity(1,1),
data varchar(995)
)
--and if we insert a value with about 1000 characters:
insert into dbo.#tmp_bcp
select @TextWith1000Characters
The above existing code is running without a problem for years(recent values before this incident has more than 995 characters) until last week when it starts to
be complaining and reporting a truncation error when value has more than 995 characters.
Any ideas? We just don't change production script right away by immediately changing the declaration above from data varchar(995) to about data varchar(2000)
Any configuration need to be changed?
April 20, 2017 at 7:29 am
aasree - Thursday, April 20, 2017 7:20 AMIs there any patch made in SQL about handling temporary table?
Previously the SQL (2012) is seemingly forgiving that when you create a temp table like this posed no problem:
create table dbo.#tmp_bcp (
dataid int primary key identity(1,1),
data varchar(995)
)
--and if we insert a value with about 1000 characters:insert into dbo.#tmp_bcp
select @TextWith1000CharactersThe above existing code is running without a problem for years(recent values before this incident has more than 995 characters) until last week when it starts to
be complaining and reporting a truncation error when value has more than 995 characters.Any ideas? We just don't change production script right away by immediately changing the declaration above from data varchar(995) to about data varchar(2000)
Any configuration need to be changed?
Was the ANSI_WARNINGS turned OFF on that server?
😎
April 20, 2017 at 7:37 am
'Forgiving' is not the word I would use, more like 'silently truncating data'. Here is some code to demonstrate the validity of Eirikur's suggestion.
SET ANSI_WARNINGS OFF;
IF OBJECT_ID('tempdb..#tmp_bcp', 'U') IS NOT NULL
DROP TABLE #tmp_bcp;
CREATE TABLE #tmp_bcp
(
dataid INT PRIMARY KEY IDENTITY(1, 1)
, data VARCHAR(995)
);
DECLARE @TextWith1000Characters VARCHAR(1000) = REPLICATE('x', 1000);
INSERT #tmp_bcp
(
data
)
SELECT @TextWith1000Characters;
SELECT
tb.dataid
, tb.data
, LEN(tb.data)
FROM #tmp_bcp tb;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 20, 2017 at 9:30 am
In addition to other statements, I ALWAYS question this type of structure in temp objects in client code:
dataid int primary key identity(1,1),
In my experience (~45000 hours with SQL Server, most as a consultant) that is almost always a WASTE OF EFFORT for NO BENEFIT! If you do not have an explicit need for that, remove it. And if you think you do, try to figure out a way to not.
Never forget that the fastest thing you can do in SQL Server is ... wait for it ... NOTHING! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2017 at 5:35 pm
TheSQLGuru - Thursday, April 20, 2017 9:30 AMIn addition to other statements, I ALWAYS question this type of structure in temp objects in client code:
dataid int primary key identity(1,1),In my experience (~45000 hours with SQL Server, most as a consultant) that is almost always a WASTE OF EFFORT for NO BENEFIT! If you do not have an explicit need for that, remove it. And if you think you do, try to figure out a way to not.
Never forget that the fastest thing you can do in SQL Server is ... wait for it ... NOTHING! 😎
I appreciate your advise. Many developers nowadays are dealing with legacy codes 🙂
April 20, 2017 at 5:42 pm
Thanks Eirikur and Phil.
In some institutions sql devs and dba's are geographically dispersed.
Devs may not be aware of the sudden changes by DBA's in Server configuration manager settings that can impact the operation of stored procedures.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply