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 turn. Show all posts
Showing posts with label turn. 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
Friday, March 23, 2012
PACKAGE START / PACKAGEEND In SSIS
This is a repeat listing - third time - of this problem.
Here's the deal:
If I turn on logging on an SSIS package in Development Studio, when the package executes it will log all the events I choose to the sysdtslog90 table in the MSDB database - INCLUDING the PACKAGESTART and PACKAGEEND events.
When I create my own custom logging, however, those two events ARE NOT being logged, even though I explicitly state in my script I want those two logged. Everything else in the script (OnWarning, OnPreExecute, OnPostExecute, etc.) is being logged.
In my reading, it states that the PACKAGESTART and PACKAGEEND events are defaults and are always logged and cannot be excluded.
If this is the case, can someone explain why they aren't getting logged?
I've seen other people have run across the same issue...This is largely due to technical issues within SSIS. In their rush to release SSIS with SQL Server 2005, Microsoft was unable to get the product fully assed. As a result, SSIS is half-assed at best, and portions of it are barely one quarter assed. You can add this glitch to a collection of SSIS inadequacies, including the ability to import XML but not the ability to export XML, and the ability to add headers to output files but not footers.|||I heard tale that SP2 supposedly clears this up? Is that what you've heard, or can I pretty much just hang it up?
Thanks!|||I haven't heard anything about SP2. Sure would be nice if they someday finished the application they rolled out.|||This is largely due to technical issues within SSIS. In their rush to release SSIS with SQL Server 2005, Microsoft was unable to get the product fully assed. As a result, SSIS is half-assed at best, and portions of it are barely one quarter assed. You can add this glitch to a collection of SSIS inadequacies, including the ability to import XML but not the ability to export XML, and the ability to add headers to output files but not footers.I didn't know that. Gives me a nice "after the fact" sense of smugness that I have so far been able to avoid using the pesky thing entirely.
I heard tale that SP2 supposedly clears this up? Is that what you've heard, or can I pretty much just hang it up?
Got any links? I would like to keep up even if I still come to the conclusion that it is more hassle than it is worth.|||Here ya go:
http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en|||To any and all interested parties:
I eventually got a response from the Mothsership herself...see below:
I believe the reason the package in the linked thread was working after SP2 is that it was using an Execute Package task, while you are using a script task. The problems you're seeing don't seem to have anything to do with your custom logger - I was able to reproduce the issue using a simple package with a SQL Logger configured.
The PackageStart and PackageEnd events are special, in that they are always fired, regardless of filtering. However, it looks like executing a package through the script task stops the event from actually being propogated up. I'm unable to determine the cause right now, but I will log an internal bug for further investigation.
As a workaround, could you instead fire a custom event from inside the script task, right before you execute the child package?
This response came directly from someone at Microsoft...
The squeaky wheel does indeed get the grease! I guess we can keep our eyes hopefully peeled for a HotFix maybe...or at least getting this addressed in a future Service Pack.|||...I'm unable to determine the cause right now, but I will log an internal bug for further investigation. One step closer to ass-completeness.|||Man...you crack me up seriously!!!!
Kudos, Blindman ;)
Here's the deal:
If I turn on logging on an SSIS package in Development Studio, when the package executes it will log all the events I choose to the sysdtslog90 table in the MSDB database - INCLUDING the PACKAGESTART and PACKAGEEND events.
When I create my own custom logging, however, those two events ARE NOT being logged, even though I explicitly state in my script I want those two logged. Everything else in the script (OnWarning, OnPreExecute, OnPostExecute, etc.) is being logged.
In my reading, it states that the PACKAGESTART and PACKAGEEND events are defaults and are always logged and cannot be excluded.
If this is the case, can someone explain why they aren't getting logged?
I've seen other people have run across the same issue...This is largely due to technical issues within SSIS. In their rush to release SSIS with SQL Server 2005, Microsoft was unable to get the product fully assed. As a result, SSIS is half-assed at best, and portions of it are barely one quarter assed. You can add this glitch to a collection of SSIS inadequacies, including the ability to import XML but not the ability to export XML, and the ability to add headers to output files but not footers.|||I heard tale that SP2 supposedly clears this up? Is that what you've heard, or can I pretty much just hang it up?
Thanks!|||I haven't heard anything about SP2. Sure would be nice if they someday finished the application they rolled out.|||This is largely due to technical issues within SSIS. In their rush to release SSIS with SQL Server 2005, Microsoft was unable to get the product fully assed. As a result, SSIS is half-assed at best, and portions of it are barely one quarter assed. You can add this glitch to a collection of SSIS inadequacies, including the ability to import XML but not the ability to export XML, and the ability to add headers to output files but not footers.I didn't know that. Gives me a nice "after the fact" sense of smugness that I have so far been able to avoid using the pesky thing entirely.
I heard tale that SP2 supposedly clears this up? Is that what you've heard, or can I pretty much just hang it up?
Got any links? I would like to keep up even if I still come to the conclusion that it is more hassle than it is worth.|||Here ya go:
http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en|||To any and all interested parties:
I eventually got a response from the Mothsership herself...see below:
I believe the reason the package in the linked thread was working after SP2 is that it was using an Execute Package task, while you are using a script task. The problems you're seeing don't seem to have anything to do with your custom logger - I was able to reproduce the issue using a simple package with a SQL Logger configured.
The PackageStart and PackageEnd events are special, in that they are always fired, regardless of filtering. However, it looks like executing a package through the script task stops the event from actually being propogated up. I'm unable to determine the cause right now, but I will log an internal bug for further investigation.
As a workaround, could you instead fire a custom event from inside the script task, right before you execute the child package?
This response came directly from someone at Microsoft...
The squeaky wheel does indeed get the grease! I guess we can keep our eyes hopefully peeled for a HotFix maybe...or at least getting this addressed in a future Service Pack.|||...I'm unable to determine the cause right now, but I will log an internal bug for further investigation. One step closer to ass-completeness.|||Man...you crack me up seriously!!!!
Kudos, Blindman ;)
Subscribe to:
Posts (Atom)