Here's a potential stumper.
I have 20 columns in a SQL table: I am accessing the data through a view. I
am acessing this data through SP's via ASP.NET.
Two of the columns are identical, "nvarhar 50"
One of these columns automatically pads extra blanks when the data is
written, the other does not do the padding.
The column that is doing the padding was created as a "char 50" after the
other column was created as a nvarchar. I then changed the 'char 50' to an
'nvarhar 50' in Enterprise Manager.
I read up un ANSI PADDDING option, but nothing I know of would have changed
the padding setting of the database between these 2 changes (except one was
colum was possibly created under SQl Server and the 'broken one' under
MSDE...). Anyway. my documentation says that nvarhar's are always padded
(but mine aren't for one column).
So my questions:
1) Did the conversion from char to nvarchar do something screwy with the
padding behavior of this column?
2) Is there any way to fix this problem in a live database? From what I
read, youy can't change the padding nature after a field is already created.
Mark McFarlaneHi
Look at the UsesAnsiTrim setting for COLUMNPROPERTY
COLUMNPROPERTY(OBJECT_ID('MyTable'),'MyColumn', 'UsesAnsiTrim')
More information in books online.
John
"mark mcfarlane" <markamcfarlane@.hotmail.com> wrote in message
news:uvJDsGuXFHA.2080@.TK2MSFTNGP15.phx.gbl...
> Here's a potential stumper.
> I have 20 columns in a SQL table: I am accessing the data through a view.
> I am acessing this data through SP's via ASP.NET.
> Two of the columns are identical, "nvarhar 50"
> One of these columns automatically pads extra blanks when the data is
> written, the other does not do the padding.
> The column that is doing the padding was created as a "char 50" after the
> other column was created as a nvarchar. I then changed the 'char 50' to
> an 'nvarhar 50' in Enterprise Manager.
> I read up un ANSI PADDDING option, but nothing I know of would have
> changed the padding setting of the database between these 2 changes
> (except one was colum was possibly created under SQl Server and the
> 'broken one' under MSDE...). Anyway. my documentation says that nvarhar's
> are always padded (but mine aren't for one column).
> So my questions:
> 1) Did the conversion from char to nvarchar do something screwy with the
> padding behavior of this column?
> 2) Is there any way to fix this problem in a live database? From what I
> read, youy can't change the padding nature after a field is already
> created.
> Mark McFarlane
>|||For some reason I can't get a query to return this property. The return
value is a NULL. Maybe this is because I am using MSDE?
USE gad_projects
SELECT COLUMNPROPERTY(
OBJECT_ID('dbo.IssueTracker_Issues'),'IssueFunctionalArea','UsesAnsiTrim')
Also, this will tell me if the column was created with null padding off, but
it wont tell me how to fix it :)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3F0RquXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi
> Look at the UsesAnsiTrim setting for COLUMNPROPERTY
> COLUMNPROPERTY(OBJECT_ID('MyTable'),'MyColumn', 'UsesAnsiTrim')
> More information in books online.
> John
>|||"mark mcfarlane" <markamcfarlane@.hotmail.com> wrote in message
news:e1pgrcwXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> For some reason I can't get a query to return this property. The return
> value is a NULL. Maybe this is because I am using MSDE?
> USE gad_projects
> SELECT COLUMNPROPERTY(
> OBJECT_ID('dbo.IssueTracker_Issues'),'IssueFunctionalArea','UsesAnsiTrim')
> Also, this will tell me if the column was created with null padding off,
> but it wont tell me how to fix it :)
By default varchar and nvarchar do not pad values with spaces; conversely,
char and nchar columns are padded to length. So it would be no surprise to
find that all of your existing data is still padded after the conversion,
UPDATE mytable SET paddedcol = LTRIM(paddedcol) would fix it nicely.
If you're certain that newly inserted values are still being padded, that
would be a little surprising [to me] but none-the-less, the simple solution
is to create a new nvarchar column, copy/LTRIM the data in the existing
column to it, then drop the old col and rename the new one. Or maybe
simpler, dump it to a new table, drop, rename, end of problem.
Forest obscured by trees syndrome, maybe?
-Mark
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:e3F0RquXFHA.3032@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> Look at the UsesAnsiTrim setting for COLUMNPROPERTY
>> COLUMNPROPERTY(OBJECT_ID('MyTable'),'MyColumn', 'UsesAnsiTrim')
>> More information in books online.
>> John
>>
>|||Hi
A NULL value is returned for nvarchar.
Rather than changing the datatype of the existing column, you can
create a new column update it with the existing columns data and then
drop the exiting column.
John|||> By default varchar and nvarchar do not pad values with spaces; conversely,
> char and nchar columns are padded to length. So it would be no surprise
> to find that all of your existing data is still padded after the
> conversion, UPDATE mytable SET paddedcol = LTRIM(paddedcol) would fix it
> nicely.
>If you're certain that newly inserted values are still being padded, that
>would be a little surprising [to me] but none-the-less, the simple solution
>is to create a new nvarchar column, copy/LTRIM the data in the existing
>column to it, then drop the old col and rename the new one. Or maybe
>simpler, dump it to a new table, drop, rename, end of problem.
>
Thanks Mark,
that's what i'll do, make a new column, move the data, and delete the old
column.
Newly insserted values are still being padded, which is as I expected after
reading the documentation. Once a column is set to pad, there is apparently
no way to change that, so since they were created as chars (which pad by
defult), the padding setting stuck when I later changed them to nvarchars.|||>
> A NULL value is returned for nvarchar.
> Rather than changing the datatype of the existing column, you can
> create a new column update it with the existing columns data and then
> drop the exiting column.
Why would a NULL be returned for an nvarchar column for
SELECT COLUMNPROPERTY(
OBJECT_ID('IssueTracker_Issues'),'IssueFunctionalArea','UsesAnsiTrim')?
Books Online says I should get a true or false and only a NULL if there was
an invalid input.
UsesAnsiTrim ANSI padding setting was ON when the table was initially
created. 1= TRUE
0= FALSE
NULL = Invalid input|||Hi
I guess you could argue that it should always return 1 as ANSI_PADDING
is always on for nchar/nvarchar
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_na-nop_9msy.asp
or that it is NULL as UsesAnsiTrim reports the ANSI_PADDING value when
the column was created and as this is ignored it would not make sense
to return a value.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_na-nop_9msy.asp
The second option seems to have been chosen, but if you disagree then
you could send in a request to SQLWish@.microsoft.com.
John
mark mcfarlane wrote:
> >
> > A NULL value is returned for nvarchar.
> >
> > Rather than changing the datatype of the existing column, you can
> > create a new column update it with the existing columns data and
then
> > drop the exiting column.
> Why would a NULL be returned for an nvarchar column for
> SELECT COLUMNPROPERTY(
>
OBJECT_ID('IssueTracker_Issues'),'IssueFunctionalArea','UsesAnsiTrim')?
> Books Online says I should get a true or false and only a NULL if
there was
> an invalid input.
> UsesAnsiTrim ANSI padding setting was ON when the table was
initially
> created. 1= TRUE
> 0= FALSE
> NULL = Invalid input
