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
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Wednesday, March 28, 2012
PADDING of blanks, how to turn off?
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'),'MyC
olumn', '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'),'IssueFunctionalAr
ea','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'),'MyC
olumn', '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'),'IssueFunctionalAr
ea','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 solut
ion
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
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 solu
tion
>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'),'IssueF
unctionalArea','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
_9msy.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_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.
_9msy.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_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:
then[vbcol=seagreen]
> Why would a NULL be returned for an nvarchar column for
> SELECT COLUMNPROPERTY(
>
OBJECT_ID('IssueTracker_Issues'),'IssueF
unctionalArea','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
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'),'MyC
olumn', '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'),'IssueFunctionalAr
ea','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'),'MyC
olumn', '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'),'IssueFunctionalAr
ea','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 solut
ion
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
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 solu
tion
>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'),'IssueF
unctionalArea','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
_9msy.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_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.
_9msy.asp" target="_blank">http://msdn.microsoft.com/library/d...r />
_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:
then[vbcol=seagreen]
> Why would a NULL be returned for an nvarchar column for
> SELECT COLUMNPROPERTY(
>
OBJECT_ID('IssueTracker_Issues'),'IssueF
unctionalArea','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
PADDING of blanks, how to turn off?
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
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: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'),'IssueFunctio nalArea','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'),'IssueFunctio nalArea','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
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'),'IssueFunctionalA rea','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/de...a-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/de...a-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:[vbcol=seagreen]
then
> Why would a NULL be returned for an nvarchar column for
> SELECT COLUMNPROPERTY(
>
OBJECT_ID('IssueTracker_Issues'),'IssueFunctionalA rea','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
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
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: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'),'IssueFunctio nalArea','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'),'IssueFunctio nalArea','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
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'),'IssueFunctionalA rea','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/de...a-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/de...a-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:[vbcol=seagreen]
then
> Why would a NULL be returned for an nvarchar column for
> SELECT COLUMNPROPERTY(
>
OBJECT_ID('IssueTracker_Issues'),'IssueFunctionalA rea','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
PADDING of blanks, how to turn off?
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'),'MyC
olumn', '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'),'IssueFunctionalAr
ea','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'),'MyC
olumn', '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'),'IssueFunctionalAr
ea','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
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'),'IssueF
unctionalArea','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/d...r />
_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/d...r />
_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:
then
> Why would a NULL be returned for an nvarchar column for
> SELECT COLUMNPROPERTY(
>
OBJECT_ID('IssueTracker_Issues'),'IssueF
unctionalArea','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 inputsql
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'),'MyC
olumn', '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'),'IssueFunctionalAr
ea','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'),'MyC
olumn', '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'),'IssueFunctionalAr
ea','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
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'),'IssueF
unctionalArea','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/d...r />
_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/d...r />
_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:
then
> Why would a NULL be returned for an nvarchar column for
> SELECT COLUMNPROPERTY(
>
OBJECT_ID('IssueTracker_Issues'),'IssueF
unctionalArea','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 inputsql
Padding an auto-incremented Int column to ten zeros
Good day gentleman,
Is it possible to pad columns of Int data type with zeros?
Is it possible to pad columns of Int data type with zeros?
I currently have a primary key column of data type Int which is auto-incremented by 1. My problem is I need to pad the auto-incremented ID's with ten zeros so, 0000000009 instead of 9.
I can pad the ID's in the c# dataaccess layer, but i need to generate/store them padded in the database to allow direct reporting.
I am using Enterprise Manager and i can see a Formula field when the table is in design view, can i use this? i am just guessing here.
Any comments or pointers would be a big help.What you are discussing is a display issue. An int is, by definition, isolated from any particular display formatting. It is just a number. You could add an additional column (nvarchar) and have a trigger on inserts that populates that nvarchar() column with a string value with 0 padding.
Wednesday, March 21, 2012
Package fails "Cannot use a CONTAINS or FREETEXT predicate"
I have a database (SQL Server 2000 Enterprise Ed.) with a single table
(Products), which has 3 columns set for Full-Text-Indexing. This
database is working fine. All queries are done through Stored Procs,
and works like a charm.
I tried to copy the database to another database (SQL Server 2000
Personal Ed.) which is on my PC. I have created a DTS package on the
Originating server. This DTS package used to work fine for a long
time, until sometime back few weeks ago I introduced FTI, and now it
does not work.
I get this error when executing DTS package:
"[ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT
predicate on table 'Products' because it is not full-text indexed."
This table is full text on the originating and also on the destination
database. (I created one on the destination, thought perhaps that's why
it wouldn't work)
Does anyone know how to fix this? Thanks.
Is your DTS package dropping the table each time? It should merely delete
the data.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<bikmann@.gmail.com> wrote in message
news:1108690034.091208.194630@.g14g2000cwa.googlegr oups.com...
> I have a database (SQL Server 2000 Enterprise Ed.) with a single table
> (Products), which has 3 columns set for Full-Text-Indexing. This
> database is working fine. All queries are done through Stored Procs,
> and works like a charm.
> I tried to copy the database to another database (SQL Server 2000
> Personal Ed.) which is on my PC. I have created a DTS package on the
> Originating server. This DTS package used to work fine for a long
> time, until sometime back few weeks ago I introduced FTI, and now it
> does not work.
> I get this error when executing DTS package:
> "[ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT
> predicate on table 'Products' because it is not full-text indexed."
> This table is full text on the originating and also on the destination
> database. (I created one on the destination, thought perhaps that's why
> it wouldn't work)
> Does anyone know how to fix this? Thanks.
>
|||DTS package is No dropping the table, and I do remember a while back
seeing data in the Products table. But I will check this again to make
sure and get back.
BTW, I couldn't get to nwsu.com's SQL Server replication book, perhaps
it is blocked on the corporate proxy.
|||It is also available on Amazon.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<bikmann@.gmail.com> wrote in message
news:1108749013.221494.201010@.g14g2000cwa.googlegr oups.com...
> DTS package is No dropping the table, and I do remember a while back
> seeing data in the Products table. But I will check this again to make
> sure and get back.
> BTW, I couldn't get to nwsu.com's SQL Server replication book, perhaps
> it is blocked on the corporate proxy.
>
|||Alright, I checked. The DTS is NOT dropping the table in the
destination nor originating. And it is also properly copying the
contents of the Products table into the destination database. But yet
it package will still fail with that message.
|||How are you transferring the data?
Are you using the Transform Data task or the Transfer Objects task?
If the latter then what options do you have set?
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
<bikmann@.gmail.com> wrote in message news:1108690034.091208.194630@.g14g2000cwa.googlegr oups.com...
>I have a database (SQL Server 2000 Enterprise Ed.) with a single table
> (Products), which has 3 columns set for Full-Text-Indexing. This
> database is working fine. All queries are done through Stored Procs,
> and works like a charm.
> I tried to copy the database to another database (SQL Server 2000
> Personal Ed.) which is on my PC. I have created a DTS package on the
> Originating server. This DTS package used to work fine for a long
> time, until sometime back few weeks ago I introduced FTI, and now it
> does not work.
> I get this error when executing DTS package:
> "[ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT
> predicate on table 'Products' because it is not full-text indexed."
> This table is full text on the originating and also on the destination
> database. (I created one on the destination, thought perhaps that's why
> it wouldn't work)
> Does anyone know how to fix this? Thanks.
>
|||Allan,
I am using "Copy SQL Server Objects Task" to copy the database, with
all the defaults settings.
|||Personally If I was copying a database I would use BACKUP/RESTORE. I am
not a great fan of the Copy Objects task.
Allan
"Bik" <bikmann@.gmail.com> wrote in message news:bikmann@.gmail.com:
> Allan,
> I am using "Copy SQL Server Objects Task" to copy the database, with
> all the defaults settings.
|||DTS allows unattended copy to another running SQL Server. I have used
BACKUP/RESORE, but i wanted to use DTS so that I can put it on
schedule. Currently I have 3 other DBs being copied via DTS, this is
the only one that causes problem., yet it would still copy the table
contents.
|||You can set up a job to do the BACKUP and restore also unattended.
Allan
"Bik" <bikmann@.gmail.com> wrote in message news:bikmann@.gmail.com:
> DTS allows unattended copy to another running SQL Server. I have used
> BACKUP/RESORE, but i wanted to use DTS so that I can put it on
> schedule. Currently I have 3 other DBs being copied via DTS, this is
> the only one that causes problem., yet it would still copy the table
> contents.
(Products), which has 3 columns set for Full-Text-Indexing. This
database is working fine. All queries are done through Stored Procs,
and works like a charm.
I tried to copy the database to another database (SQL Server 2000
Personal Ed.) which is on my PC. I have created a DTS package on the
Originating server. This DTS package used to work fine for a long
time, until sometime back few weeks ago I introduced FTI, and now it
does not work.
I get this error when executing DTS package:
"[ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT
predicate on table 'Products' because it is not full-text indexed."
This table is full text on the originating and also on the destination
database. (I created one on the destination, thought perhaps that's why
it wouldn't work)
Does anyone know how to fix this? Thanks.
Is your DTS package dropping the table each time? It should merely delete
the data.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<bikmann@.gmail.com> wrote in message
news:1108690034.091208.194630@.g14g2000cwa.googlegr oups.com...
> I have a database (SQL Server 2000 Enterprise Ed.) with a single table
> (Products), which has 3 columns set for Full-Text-Indexing. This
> database is working fine. All queries are done through Stored Procs,
> and works like a charm.
> I tried to copy the database to another database (SQL Server 2000
> Personal Ed.) which is on my PC. I have created a DTS package on the
> Originating server. This DTS package used to work fine for a long
> time, until sometime back few weeks ago I introduced FTI, and now it
> does not work.
> I get this error when executing DTS package:
> "[ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT
> predicate on table 'Products' because it is not full-text indexed."
> This table is full text on the originating and also on the destination
> database. (I created one on the destination, thought perhaps that's why
> it wouldn't work)
> Does anyone know how to fix this? Thanks.
>
|||DTS package is No dropping the table, and I do remember a while back
seeing data in the Products table. But I will check this again to make
sure and get back.
BTW, I couldn't get to nwsu.com's SQL Server replication book, perhaps
it is blocked on the corporate proxy.
|||It is also available on Amazon.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<bikmann@.gmail.com> wrote in message
news:1108749013.221494.201010@.g14g2000cwa.googlegr oups.com...
> DTS package is No dropping the table, and I do remember a while back
> seeing data in the Products table. But I will check this again to make
> sure and get back.
> BTW, I couldn't get to nwsu.com's SQL Server replication book, perhaps
> it is blocked on the corporate proxy.
>
|||Alright, I checked. The DTS is NOT dropping the table in the
destination nor originating. And it is also properly copying the
contents of the Products table into the destination database. But yet
it package will still fail with that message.
|||How are you transferring the data?
Are you using the Transform Data task or the Transfer Objects task?
If the latter then what options do you have set?
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
<bikmann@.gmail.com> wrote in message news:1108690034.091208.194630@.g14g2000cwa.googlegr oups.com...
>I have a database (SQL Server 2000 Enterprise Ed.) with a single table
> (Products), which has 3 columns set for Full-Text-Indexing. This
> database is working fine. All queries are done through Stored Procs,
> and works like a charm.
> I tried to copy the database to another database (SQL Server 2000
> Personal Ed.) which is on my PC. I have created a DTS package on the
> Originating server. This DTS package used to work fine for a long
> time, until sometime back few weeks ago I introduced FTI, and now it
> does not work.
> I get this error when executing DTS package:
> "[ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT
> predicate on table 'Products' because it is not full-text indexed."
> This table is full text on the originating and also on the destination
> database. (I created one on the destination, thought perhaps that's why
> it wouldn't work)
> Does anyone know how to fix this? Thanks.
>
|||Allan,
I am using "Copy SQL Server Objects Task" to copy the database, with
all the defaults settings.
|||Personally If I was copying a database I would use BACKUP/RESTORE. I am
not a great fan of the Copy Objects task.
Allan
"Bik" <bikmann@.gmail.com> wrote in message news:bikmann@.gmail.com:
> Allan,
> I am using "Copy SQL Server Objects Task" to copy the database, with
> all the defaults settings.
|||DTS allows unattended copy to another running SQL Server. I have used
BACKUP/RESORE, but i wanted to use DTS so that I can put it on
schedule. Currently I have 3 other DBs being copied via DTS, this is
the only one that causes problem., yet it would still copy the table
contents.
|||You can set up a job to do the BACKUP and restore also unattended.
Allan
"Bik" <bikmann@.gmail.com> wrote in message news:bikmann@.gmail.com:
> DTS allows unattended copy to another running SQL Server. I have used
> BACKUP/RESORE, but i wanted to use DTS so that I can put it on
> schedule. Currently I have 3 other DBs being copied via DTS, this is
> the only one that causes problem., yet it would still copy the table
> contents.
Monday, March 12, 2012
p2p replication error too many arguments specified
Currently, we have peer-to-peer replication on two servers (server1 and
server2).
I added a few columns using sp_repladdcolumn stored procedure. Rows inserted
at Server1
are replicated to Server2 without problems, and rows inserted Server2 can
not be replicated to Server1
because of the error below. Any help is appreciated. thanks, D
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x0000008600000EE7000400000000, Command ID: 1)
Error messages:
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Paul,
First time at this. Not sure how to quiesce the system. Does
this mean change db to single user?
I've used sp_repladdcolumn to add two columns to the publisher's article.
Both tables at subscriber and publisher do reflect the new columns.
However, stored procedures at the publisher do not receive the changes
while stored procedures at subscriber do.
When a new record is inserted into the table at publisher, the
new record does get distributed. When a new record is inserted
at the subscriber, the record got an error when being distributed.
Thanks, D
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OwyqlhJwHHA.4612@.TK2MSFTNGP04.phx.gbl...
> 1.. Quiesce the system.
> 2.. Execute the data definition language (DDL) statements to modify the
> schema of published tables. For more information about supported schema
> changes, see Making Schema Changes on Publication Databases.
> 3.. Before resuming activity on published tables, quiesce the system
> again. This ensures that schema changes have been received by all nodes
> before any new data changes are replicated
> The general advice is to do things as above - is that how you made the
> schema change?
> I'm also interested in what has actually happened - do the tables have an
> equal number of columns now? Can you also compare the text of the 2
> sp_MSins_dboCustomers procs (pub and subs) and see if they are different.
> If so, can you tell us which one is correct.
> Cheers,
> Paul Ibison
>
server2).
I added a few columns using sp_repladdcolumn stored procedure. Rows inserted
at Server1
are replicated to Server2 without problems, and rows inserted Server2 can
not be replicated to Server1
because of the error below. Any help is appreciated. thanks, D
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x0000008600000EE7000400000000, Command ID: 1)
Error messages:
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Paul,
First time at this. Not sure how to quiesce the system. Does
this mean change db to single user?
I've used sp_repladdcolumn to add two columns to the publisher's article.
Both tables at subscriber and publisher do reflect the new columns.
However, stored procedures at the publisher do not receive the changes
while stored procedures at subscriber do.
When a new record is inserted into the table at publisher, the
new record does get distributed. When a new record is inserted
at the subscriber, the record got an error when being distributed.
Thanks, D
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OwyqlhJwHHA.4612@.TK2MSFTNGP04.phx.gbl...
> 1.. Quiesce the system.
> 2.. Execute the data definition language (DDL) statements to modify the
> schema of published tables. For more information about supported schema
> changes, see Making Schema Changes on Publication Databases.
> 3.. Before resuming activity on published tables, quiesce the system
> again. This ensures that schema changes have been received by all nodes
> before any new data changes are replicated
> The general advice is to do things as above - is that how you made the
> schema change?
> I'm also interested in what has actually happened - do the tables have an
> equal number of columns now? Can you also compare the text of the 2
> sp_MSins_dboCustomers procs (pub and subs) and see if they are different.
> If so, can you tell us which one is correct.
> Cheers,
> Paul Ibison
>
Subscribe to:
Posts (Atom)