Couple of days back I saw a question on MSDN forums where a user is not getting any warnings when using isql (commandline utility) and he is getting warnings when using isqlw (query analyzer GUI) for the same query. I’m just writing this article because many of them face this problem when they do batch processing data’s through isql.
Example:
Consider you are having a table with column char(3) and if you insert a value which has 4 characters then sql client utility should throw a error. When you connect SQL Server using query analyzer, then Ansi_warnings are on by default and you will receive this error & the transaction will not happen. However if you connect SQL server using isql command line utility then you won’t get this error, instead it will truncate the last character and insert it into the table. Lets check it out!
TABLE Schema
Below is the table schema which I’m using for this example. I’m creating this table on Pubs database.
CREATE TABLE Ansi_test (Test Char(3))
INSERT Ansi_test Values('SQLServer')
Connecting through isqlw (Query Analyzer):
When you insert values through isqlw which is having 4 character you will be thrown with the error shown below
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
This happens because by default ANSI_Defaults values are ON in query analyzer hence ANSI_Warnings are also ON by default which is responsible to capture and return this error. You can check connection properties through DBCC Useroptions command, check the image below where ansi values are set to on.
Connecting through isql (command line utility):
When you insert values through isql which is having 4 character then isql will not throw this error instead it will truncate the last character and insert the value to the table without error as shown below
This happens because by default ANSI_Defaults values are OFF in isql hence ANSI_Warnings are also OFF by default which is responsible to capture and return this error. You can check connection properties through DBCC Useroptions command, check the image below where ansi values are set to off.
How can I get this error in isql
It’s very simple, you need to On ANSI_Warnings parameter or ANSI_Defaults values. Once this done it will throw the error. You can enable the parameter using the command
SET ANSI_WARNINGS ON
or
SET ANSI_DEFAULTS ON
If you now insert the value then it will throw the error as shown below
You can also use osql command line utility in which ANSI_DEFAULTS values are ON by default which will help you to capture these errors. It’s better to use osql command line utility instead of isql since osql connects through ODBC. For more on osql utility refer the link “OSQL Utility“.