How to have strings automatically truncated?

[John Bokma]

"String or binary data would be truncated"
Can MS SQL automatically do the truncation?

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

[ZeldorBlat]

John Bokma wrote:

"String or binary data would be truncated"
Can MS SQL automatically do the truncation?

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

Sometimes.

[Erland Sommarskog]

John Bokma (jo**@castleamber.com) writes:

"String or binary data would be truncated"
Can MS SQL automatically do the truncation?

Yes, if you issue the command SET ANSI_WARNINGS OFF.

However, this is not really recommendable, as there are functionality
in SQL Server that requires ANSI_WARNINGS to be on.

So you are better off by truncating the data yourself, for instance
with cast or convert. Or bounce thata over a variable. SQL Server does
not complain if you assign a variable with an overlong value.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[John Bokma]

Erland Sommarskog <es****@sommarskog.se> wrote:

John Bokma (jo**@castleamber.com) writes:

"String or binary data would be truncated"
Can MS SQL automatically do the truncation?

Yes, if you issue the command SET ANSI_WARNINGS OFF.

However, this is not really recommendable, as there are functionality
in SQL Server that requires ANSI_WARNINGS to be on.

So you are better off by truncating the data yourself, for instance
with cast or convert. Or bounce thata over a variable. SQL Server does
not complain if you assign a variable with an overlong value.

Thanks. Is there an easy way to insert and truncate the data via SQL in
one go (or give something like an "It's ok to truncate hint"? I don't want
to hardcode the max length of a text column (for example) in the program
code.

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

[Erland Sommarskog]

John Bokma (jo**@castleamber.com) writes:

Erland Sommarskog <es****@sommarskog.se> wrote:

John Bokma (jo**@castleamber.com) writes:

"String or binary data would be truncated"
Can MS SQL automatically do the truncation?

Yes, if you issue the command SET ANSI_WARNINGS OFF.

However, this is not really recommendable, as there are functionality
in SQL Server that requires ANSI_WARNINGS to be on.

So you are better off by truncating the data yourself, for instance
with cast or convert. Or bounce thata over a variable. SQL Server does
not complain if you assign a variable with an overlong value.

Thanks. Is there an easy way to insert and truncate the data via SQL in
one go (or give something like an "It's ok to truncate hint"? I don't want
to hardcode the max length of a text column (for example) in the program
code.

See above about SET ANSI_WARNINGS. That's the hint you are looking for.
But also beware of the caveat.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[John Bokma]

Erland Sommarskog <es****@sommarskog.se> wrote:

John Bokma (jo**@castleamber.com) writes:

Erland Sommarskog <es****@sommarskog.se> wrote:

John Bokma (jo**@castleamber.com) writes:
"String or binary data would be truncated"
Can MS SQL automatically do the truncation?

Yes, if you issue the command SET ANSI_WARNINGS OFF.

However, this is not really recommendable, as there are
functionality in SQL Server that requires ANSI_WARNINGS to be on.

So you are better off by truncating the data yourself, for instance
with cast or convert. Or bounce thata over a variable. SQL Server
does not complain if you assign a variable with an overlong value.

Thanks. Is there an easy way to insert and truncate the data via SQL
in one go (or give something like an "It's ok to truncate hint"? I
don't want to hardcode the max length of a text column (for example)
in the program code.

See above about SET ANSI_WARNINGS. That's the hint you are looking
for. But also beware of the caveat.

I was somehow thinking on SET ANSI_WARNINGS OFF, query, back on, but
indeed, need to read the problems this might cause. I was hoping that
there would be an easier option (well, maybe it's easy enough :-) )

Thanks,

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

[Erland Sommarskog]

John Bokma (jo**@castleamber.com) writes:

I was somehow thinking on SET ANSI_WARNINGS OFF, query, back on, but
indeed, need to read the problems this might cause. I was hoping that
there would be an easier option (well, maybe it's easy enough :-) )

More specifically, these are the issues you can run into:

1) You cannot perform updates that would affect an indexed view or an
indexed computed column.
2) SELECT queries cannot make use of index on views and computed columns
and my run slower.
3) Cannot perform queries on linked server.
4) Adding SET ANSI_WARNINGS ON in a stored procedure causes the procedure
to be recompiled, which can degrade performance if compilation cost
is high in relation to execution time.
5) Overflows and division by zero will go unnoticed, leading to other
stange and unexpected results. This can mitigated by setting ARTITHABOT
ON. (Which has to be on for indexed views and indexed computed columns
anyway.)

My experience is that if you use stored procedure or parameterised
statments is that you rarely run into this errors. This is because,
the errors is only raised on column assignment, but not parameter or
variable assignment.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx