Bob,

This is what Microsoft's SQL documentation has to say about
ANSI_PADDING and ANSI_NULLS:

*****
ANSI_NULLS

When set to ON, all comparisons to a null value evaluate to NULL
(unknown). When set to OFF, comparisons of non-Unicode values to a null
value evaluate to TRUE if both values are NULL. By default, the
ANSI_NULLS database option is OFF.

Connection-level settings (set using the SET statement) override the
default database setting for ANSI_NULLS. By default, ODBC and OLE DB
clients issue a connection-level SET statement setting ANSI_NULLS to ON
for the session when connecting to SQL Server. For more information, see
SET ANSI_NULLS.

SET ANSI_NULLS also must be set to ON when you create or manipulate
indexes on computed columns or indexed views.

The status of this option can be determined by examining the
IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.

ANSI_PADDING

When set to ON, trailing blanks in character values inserted into
varchar columns and trailing zeros in binary values inserted into
varbinary columns are not trimmed. Values are not padded to the length
of the column. When set to OFF, the trailing blanks (for varchar) and
zeros (for varbinary) are trimmed. This setting affects only the
definition of new columns.

Char(n) and binary(n) columns that allow nulls are padded to the length
of the column when SET ANSI_PADDING is set to ON, but trailing blanks
and zeros are trimmed when SET ANSI_PADDING is OFF. Char(n) and
binary(n) columns that do not allow nulls are always padded to the
length of the column.

Important  It is recommended that ANSI_PADDING always be set to ON. SET
ANSI_PADDING must be ON when creating or manipulating indexes on
computed columns or indexed views.

The status of this option can be determined by examining the
IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function.
*****

So, you're getting these warnings when you *add* the varchar field
(before you even touch the existing text field), right?  How are you
adding the field?  (Query Analyzer, Enterprise Manager, ODBC/OLE?)  I
would check with the vendor about limitations they may have regarding
custom fields.  They may have good reasons for setting this field as
text in the first place, and for setting ANSI_PADDING off and ANSI_NULLS
off.

HTH,

Art Frank
Manager of Information Systems
OHSU Foundation
[log in to unmask]
(503) 220-8320


>>> Bob McGregor <[log in to unmask]> 8/31/2004 8:27:42 AM
>>>
We have a database in SQL Server 2000 and I need to change a field from
type TEXT to type VarChar with enterprise manager (I think).  Anyway
what I planned to do was to add a new varchar field and then update it
with the contents of the TEXT field, delete the TEXT field and rename
the varchar field to the TEXT field name.  Hopefully that works...

My problem is when I go to add the varchar field I get these messages:

warning: One or more existing columns have ANSI_PADDING 'off' and will
be re-created with ANSI_PADDING 'on'
warning: The table was created with ANSI_NULLS 'off' and will be
re-created with ANSI_NULLS 'on'

Is there a way in enterprise mananger I can set these items before I
add the field to the table? I can't seem to locate a command window in
enterprise manager to set the items.

The database is vendor supplied with the ability to add custom fields
which we did only specified TEXT instead of varchar and want to change
it. I'm concerned if I change the settings above the software will not
like it.

Any help would be appreciated.

bob

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *