padding variables

I know this may be an elementary question but can anyone tell me how to pad a
variable.
For example
@.chvcode has value ��123�� and I want to pad it with ��00123��.
Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:

> I know this may be an elementary question but can anyone tell me how to pad a
> variable.
> For example
> @.chvcode has value ��123�� and I want to pad it with ��00123��.
>
sql

padding variables

I know this may be an elementary question but can anyone tell me how to pad
a
variable.
For example
@.chvcode has value ��123�� and I want to pad it with ��00123��.Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:

> I know this may be an elementary question but can anyone tell me how to pa
d a
> variable.
> For example
> @.chvcode has value ��123�� and I want to pad it with ��00123��.
>

padding variables

I know this may be an elementary question but can anyone tell me how to pad a
variable.
For example
@.chvcode has value â'123â' and I want to pad it with â'00123â'.Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:
> I know this may be an elementary question but can anyone tell me how to pad a
> variable.
> For example
> @.chvcode has value â'123â' and I want to pad it with â'00123â'.
>

Padding using Type 4 Drivers

Hope someone can advise me (a humble Java Programmer) of a problem I
am having.
We have a char(8) field in a MS SQL Server 7 DB, some of the data is
only 7 chars though.
We have recently upgraded to MS SQL Srever 2000.
The problem is, the JDBC queries now return space padded.
so that "1234567" comes back as "12345678 ".
They didn't do this before.
I understand there is an ODBC configuration, but as we are using
type-4 JDBC drivers, we don't go via the JDBC-ODBC bridge. We go
direct to the DB.
Does anybody know what configuration a SQL Server DB has which effects
the ANSI padding when using a type-4 JDBC driver?
I am clueless on this I can tell you!.
| From: prithpal.roda@.talk21.com (Prithpal)
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Padding using Type 4 Drivers
| Date: 16 Nov 2004 08:40:55 -0800
| Organization: http://groups.google.com
| Lines: 19
| Message-ID: <b47276f6.0411160840.6acfded0@.posting.google.com >
| NNTP-Posting-Host: 194.72.54.254
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1100623255 12885 127.0.0.1 (16 Nov 2004
16:40:55 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Tue, 16 Nov 2004 16:40:55 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!news-out.cwi
x.com!newsfeed.cwix.com!newsfeed.icl.net!proxad.ne t!216.239.36.134.MISMATCH!
postnews.google.com!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6475
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hope someone can advise me (a humble Java Programmer) of a problem I
| am having.
|
| We have a char(8) field in a MS SQL Server 7 DB, some of the data is
| only 7 chars though.
| We have recently upgraded to MS SQL Srever 2000.
|
| The problem is, the JDBC queries now return space padded.
| so that "1234567" comes back as "12345678 ".
| They didn't do this before.
|
| I understand there is an ODBC configuration, but as we are using
| type-4 JDBC drivers, we don't go via the JDBC-ODBC bridge. We go
| direct to the DB.
|
| Does anybody know what configuration a SQL Server DB has which effects
| the ANSI padding when using a type-4 JDBC driver?
|
| I am clueless on this I can tell you!.
|
Hello,
It is possible that your SQL Server 7 was using a different ANSI_PADDING
value than what you have set in SQL Server 2000.
Whenever you make a connection to SQL Server, you can specify the ANSI
settings that you require. If you do not specify these explicitly, then
your connection will receive a default set of ANSI settings based on the
configuration in SQL Server. You can use the Security Audit -> Audit Login
event in SQL Profiler to observe the ANSI settings used when your
connection is first made. Your Profiler output may look something like
this:
-- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
These default settings are documented in the SQL Server Books Online under
the topic "SET"
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_set-set_40c4.asp) under the subheading "SQL-92 Settings Statements". You
can also run DBCC USEROPTIONS from within your connection to see this
information.
Are you using the Microsoft JDBC driver or a third-party driver?.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message news:<9aT3U$AzEHA.3956@.cpmsftngxa10.phx.gbl>...
> --
> Hello,
> It is possible that your SQL Server 7 was using a different ANSI_PADDING
> value than what you have set in SQL Server 2000.
> Whenever you make a connection to SQL Server, you can specify the ANSI
> settings that you require. If you do not specify these explicitly, then
> your connection will receive a default set of ANSI settings based on the
> configuration in SQL Server. You can use the Security Audit -> Audit Login
> event in SQL Profiler to observe the ANSI settings used when your
> connection is first made. Your Profiler output may look something like
> this:
> -- network protocol: TCP/IP
> set quoted_identifier on
> set implicit_transactions off
> set cursor_close_on_commit off
> set ansi_warnings on
> set ansi_padding on
> set ansi_nulls on
> set concat_null_yields_null on
> set language us_english
> set dateformat mdy
> set datefirst 7
> These default settings are documented in the SQL Server Books Online under
> the topic "SET"
> (http://msdn.microsoft.com/library/de...-us/tsqlref/ts
> _set-set_40c4.asp) under the subheading "SQL-92 Settings Statements". You
> can also run DBCC USEROPTIONS from within your connection to see this
> information.
> Are you using the Microsoft JDBC driver or a third-party driver?.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
Thanks very much for the information.
We are indeed using a third-party driver as we are not aware of a
Microsoft Type 4 driver which will connect to sql server 7.
We have performed the tests using a weblogic driver and jtds, both
having the same results. The DBA insist that the sql server 7 and sql
server 2000 are configured the same..... but I have my doubts!.
|||I ran the suggested DBCC OPTIONS command for the box that has NO padding.
This is what I get:
C:\temp\JDBC>java -cp .;jtds-0.9.jar ConnectInfo staging
Running against staging
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
quoted_identifier SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
oes that make any sense? I get no padding, but padding is set for the connection.
|||| From: prithpal.roda@.talk21.com (Prithpal)
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Re: Padding using Type 4 Drivers
| Date: 17 Nov 2004 01:38:32 -0800
| Organization: http://groups.google.com
| Lines: 54
| Message-ID: <b47276f6.0411170138.2d960a00@.posting.google.com >
| References: <b47276f6.0411160840.6acfded0@.posting.google.com >
<9aT3U$AzEHA.3956@.cpmsftngxa10.phx.gbl>
| NNTP-Posting-Host: 194.72.54.254
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1100684312 31457 127.0.0.1 (17 Nov 2004
09:38:32 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Wed, 17 Nov 2004 09:38:32 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA02.phx.gbl!cpmsftn gxa06.phx.gbl!TK2MSFTNGP08
.phx.gbl!news-out.cwix.com!newsfeed.cwix.com!newsfeed.yul.equant .net!newsrea
d.com!newsprint.newsread.com!postnews.google.com!n ot-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6480
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message
news:<9aT3U$AzEHA.3956@.cpmsftngxa10.phx.gbl>...
| > --
| > Hello,
| >
| > It is possible that your SQL Server 7 was using a different
ANSI_PADDING
| > value than what you have set in SQL Server 2000.
| >
| > Whenever you make a connection to SQL Server, you can specify the ANSI
| > settings that you require. If you do not specify these explicitly,
then
| > your connection will receive a default set of ANSI settings based on
the
| > configuration in SQL Server. You can use the Security Audit -> Audit
Login
| > event in SQL Profiler to observe the ANSI settings used when your
| > connection is first made. Your Profiler output may look something like
| > this:
| >
| > -- network protocol: TCP/IP
| > set quoted_identifier on
| > set implicit_transactions off
| > set cursor_close_on_commit off
| > set ansi_warnings on
| > set ansi_padding on
| > set ansi_nulls on
| > set concat_null_yields_null on
| > set language us_english
| > set dateformat mdy
| > set datefirst 7
| >
| > These default settings are documented in the SQL Server Books Online
under
| > the topic "SET"
| >
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
| > _set-set_40c4.asp) under the subheading "SQL-92 Settings Statements".
You
| > can also run DBCC USEROPTIONS from within your connection to see this
| > information.
| >
| > Are you using the Microsoft JDBC driver or a third-party driver?.
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > Are you secure? For information about the Strategic Technology
Protection
| > Program and to order your FREE Security Tool Kit, please visit
| > http://www.microsoft.com/security.
|
| Thanks very much for the information.
|
| We are indeed using a third-party driver as we are not aware of a
| Microsoft Type 4 driver which will connect to sql server 7.
|
| We have performed the tests using a weblogic driver and jtds, both
| having the same results. The DBA insist that the sql server 7 and sql
| server 2000 are configured the same..... but I have my doubts!.
|
In an ealier post, you mentioned upgrading from SQL 7 to SQL 2000. Are you
seeing the same behavior with both database versions?
I did some more research for this question, and here is what I found. For
SQL Server 2000, the ANSI_PADDING setting is applied when the table is
created. If ANSI_PADDING was turned off when the table was created, then
the char(8) column would return the data with trailing spaces removed
(assuming the column allows nulls). If ANSI_PADDING was on when the table
was created, then the char(8) column would return the trailing space. This
behavior can be observed in Query Analyzer:
SET ANSI_PADDING
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
set-set_2uw7.asp
In another test, I used the Microsoft JDBC driver to query the table that
was created with ANSI_PADDING turned off. I still observe the trailing
spaces in this scenario. I'm not sure why this is the case, but the
behavior appears to be consistent with the other JDBC drivers you have
tested.
All things aside, you should consider using a variable-length data type for
your column definitions if you are concerned with trailing spaces in your
table.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

padding property RS 2005

Anyone notice that the padding property doesn't seem to work as nice as RS 2000? When I select more than one cell or row, and try to set the padding to 1,1,1,1, by typing the values in the property field (as opposed to expanding it and entering it in each side), it sometimes it gives me the following error:

'RptPaddingConverter' is unable to convert 'Microsoft.ReportDesigner.Drawing.ReportPadding' to 'System.ComponentModel.Design.Serialization.InstanceDescriptor'.

Other times it works. This has always worked in RS 2000. It is more of a time-consuming annoyance than anything because then I have to go into each cell individually and change the padding, one by one.

This issue was fixed in SQL 2005 SP1|||Great. We were going to install it this week anyway. Thanks.|||Hello,

I can't confirm that the problem is solved. I work with SQL Server 2005 Standard Edition with Service Pack 1 installed. When selecting more than one cell in a table and modifying any of the padding-properties I get the error message (freely adapted from the german error message): "invalid property value". The details: RptPaddingConverter cannot convert Microsoft.ReportDesigner.Drawing.ReportPadding to System.ComponentModel.Design.Serialization.InstanceDescriptor.

regards,
Gerald

|||Please check version of Report Designer (in Help -> About)|||Microsoft Visual Studio 2005
Version 8.0.507277.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
SQL Server Reporting Services
Designer for Microsoft SQL Server Reporting Services
Version 9.00.2047.00

padding property RS 2005

Anyone notice that the padding property doesn't seem to work as nice as RS 2000? When I select more than one cell or row, and try to set the padding to 1,1,1,1, by typing the values in the property field (as opposed to expanding it and entering it in each side), it sometimes it gives me the following error:

'RptPaddingConverter' is unable to convert 'Microsoft.ReportDesigner.Drawing.ReportPadding' to 'System.ComponentModel.Design.Serialization.InstanceDescriptor'.

Other times it works. This has always worked in RS 2000. It is more of a time-consuming annoyance than anything because then I have to go into each cell individually and change the padding, one by one.

This issue was fixed in SQL 2005 SP1|||Great. We were going to install it this week anyway. Thanks.|||Hello,

I can't confirm that the problem is solved. I work with SQL Server 2005 Standard Edition with Service Pack 1 installed. When selecting more than one cell in a table and modifying any of the padding-properties I get the error message (freely adapted from the german error message): "invalid property value". The details: RptPaddingConverter cannot convert Microsoft.ReportDesigner.Drawing.ReportPadding to System.ComponentModel.Design.Serialization.InstanceDescriptor.

regards,
Gerald

|||Please check version of Report Designer (in Help -> About)|||Microsoft Visual Studio 2005
Version 8.0.507277.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
SQL Server Reporting Services
Designer for Microsoft SQL Server Reporting Services
Version 9.00.2047.00sql

Padding partial table name with @variable value

Is there a way to this in SQL
declare @.partial_table_name varchar(128)
set @.partial_table_name = 'mytable'
select * from customer
into tbl_cust_@.partial_table_name
Above i am inserting the values from customer to table
tbl_cust__@.partial_table_name. I want to pass the @.partial_table_name value
to the table so that table name becomes tbl_cust_mytable. This is so that I
can dynamically insert into a table based opon conditions in my store proc.
Any help would be appreciated.
ThanksEXEC('SELECT * INTO tbl_cust_'+@.partial_table_name+' from customer');
I'd be very careful about where @.partial_table_name gets created. SQL
injection can yield to inadvertent statements being executed, such as:
SELECT * INTO tbl_cust_foo FROM (SELECT a=1); UPDATE Employees SET Salary =
Salary * 10 WHERE FullName='Malicious Employee'; SELECT * from customer;
Please give the following article a thorough read:
http://www.sommarskog.se/dynamic_sql.html
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:CB37F1E5-5C85-46B6-B443-6F504E1CD69D@.microsoft.com...
> Is there a way to this in SQL
> declare @.partial_table_name varchar(128)
> set @.partial_table_name = 'mytable'
> select * from customer
> into tbl_cust_@.partial_table_name
> Above i am inserting the values from customer to table
> tbl_cust__@.partial_table_name. I want to pass the @.partial_table_name
> value
> to the table so that table name becomes tbl_cust_mytable. This is so that
> I
> can dynamically insert into a table based opon conditions in my store
> proc.
> Any help would be appreciated.
> Thanks|||Thanks Aaron,
That was very helpful.Its doing what I wanted to do. I would be careful as
where the variable is created.
Appreciate your quick response.
cheers,
b_dba
"Aaron Bertrand [SQL Server MVP]" wrote:

> EXEC('SELECT * INTO tbl_cust_'+@.partial_table_name+' from customer');
> I'd be very careful about where @.partial_table_name gets created. SQL
> injection can yield to inadvertent statements being executed, such as:
> SELECT * INTO tbl_cust_foo FROM (SELECT a=1); UPDATE Employees SET Salary
=
> Salary * 10 WHERE FullName='Malicious Employee'; SELECT * from customer;
> Please give the following article a thorough read:
> http://www.sommarskog.se/dynamic_sql.html
>
>
>
> "b_dba" <b_dba@.discussions.microsoft.com> wrote in message
> news:CB37F1E5-5C85-46B6-B443-6F504E1CD69D@.microsoft.com...
>
>

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'),'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

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

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

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

Padding Issue

We are creating hierarchical report showing line of sight for given Organization. The report data is tree structure. Each node is a row in the report. Each row in the report is nested by using padding expression. Padding expression is based on Node Level. When we print report or create PDF padding is removed and all the rows appear aligned. It looks like reporting Services treat padding as white space. We tried several options like using gif images and rectangle but failed.

The report appears fine when we export to Web Archive, the working sample and sample with issue are as attached.

This issue occurs both in report viewer control and report services url.

Does "Padding expression" mean that you insert whitespace based on the node level?

There is another way of doing this: Every textbox has a Padding.Left property. Assuming that the node level is available as a numeric field value, you could set the Padding.Left property dynamically: =CStr(4 * Fields!NodeLevel.Value) & " pt"

-- Robert

|||

Thanks for the reply

I have tried both padding.left and padding.right. When report is rendered padding appears fine but on print or export to pdf, padding is removed.

|||

Which version of Reporting Services are you using? Did you install the latest service packs for that version?

-- Robert

Padding identity column values with zeros

Hi Guys

Is there an easy way to pad identity column values with zeros to the left (0001, 0010,0100, etc) or I will just have to format them before displaying

George

This works for me a lot of times.

REPLICATE('0', 4-len(cast(id as char(4))) + cast(id as char(4))

|||

You could use an expression like:

right(replicate('0', 8) + cast($identity as varchar), 8)

-- or

right(replicate('0', 8) + cast(identitycol as varchar), 8)

You could either use a computed column or a view with the expression. Alternatively, you could simply do these type of operations on the client side.

Padding fields with zeros.

I am trying to create a field in SQL query builder that takes the input number 12345 and formats it 0000012345. In MSAccess there is a great Format() function which allowed this to be done easily, but I cant find an equivalent in SQLServer. Can anyone provide me with a new function and possibly a sample? Thanks in Advance,

Michael.

try this

SELECT cast(replace(str(12345 ,10),' ','0') AS char(10))

OR write Stored Procedure as you need ( i think)

CREATE FUNCTION fnPadLeft

( @.PadChar char(1), @.PadToLen int, @.BaseString varchar(100) )

RETURNS varchar(1000)

AS

BEGIN

DECLARE @.Padded varchar(1000)

DECLARE @.BaseLen int

SET @.BaseLen = LEN(@.BaseString)

IF @.BaseLen >= @.PadToLen

BEGIN

SET @.Padded = @.BaseString

END

ELSE

BEGIN

SET @.Padded = REPLICATE(@.PadChar, @.PadToLen - @.BaseLen) + @.BaseString

END

RETURN @.Padded

END

Now try this

SELECT dbo.fnPadLeft('0', 10, 12345) ==> 0000012345

Parameters:

First = Padding character

Second = Total Length

Third = Your Column

Best of Luck

Regards,

Thanks.

Gurpreet S .Gill

|||

You can also try this

select Replicate('0',10-Len(Column1))+ Convert(Varchar,Column1) from Table1

I am assuming Column1 in the table is INT. If it is varchar, no need to for conversion.

|||

SELECT RIGHT('0000000000' + CONVERT(VARCHAR(10),col1) , 10) FROM table1

|||

Thanks all! I will give it a shot...

Michael

Padding blank spaces when exporting to PDF?

I know that some people have already asked this question, but answers were not usefull to me.

I want to export the content of my textbox to PDF and can't find the way to save it's formating on pdf.

I'm creating a report for invoice. I have one TextBox at the end of report for displaying terms of payment.
There can be several paragraphs in this text. This text is stored in one column, which is nvarchar, in database.

Is there any way to keep the blank spaces that I use at the beginning of paragraph? Is there any non-printable character that
won't be ignored when trying to insert a new paragrapf in PDF? I can't use tab and blank spaces-these are ignored. And I don't
know how many paragraphs there will be-this changes.Pozdrav!|||

I'm not sure but can't you do something like this:

Write custom code that evaluates the string. The custom code will iterate through the string and will evaluate each character. If it is a " " (space), then add another value to a counter. Then you know how many spaces there are in a string.

|||

Yes, but I can't find the way to put those blank spaces at the beginning of each paragraph.
They are ignored. It is not that I don't know how many blank spaces there will be, I simply
don't know how to make them appear on my PDF.

Padding and Writing to a fixed format flat file!

Hi,

I am trying to write to a fixed format flat file using Flat File Destination Data Flow Component. I have all required information gathered from more than one sources. But when I tried to format the columns to a big string that will make up one line in the flat file, I could not figure out how to do that. Couple of issues that I am facing are:

How to padd different columns? For example, One interger column has could be 1 to 10 character long in my case. When I convert to string, dont know how to padd the remaining characters i.e. if the value of integer is '1234', it should be written to file as '1234 ' . Which transformation is best in this case, if available? How to convert T-SQL datetime to a specific date and time format to write in the flate file? I have to write these date formats depending upon one of the parameters passed. Also, I dont want to put a delimiter at the end of each column, just the new line characters at the end of each record. Some of the columns has some unwanted characters (like new line characters) how to find them and remove them from the string. Can we directly write columns to a specific position in the flat file? e.g. col 1 a position 1 and col2 starts at postion 20 etc.

Your co-operation will be appreciated.

Thanks,

Paraclete

Take a look at a ragged-right or fixed width flat file connection manager. WHen you create the connection manager, you can choose ragged right or fixed width instead of delimited. This should solve a number of your issues.sql

Padding an auto-incremented Int column to ten zeros

Good day gentleman,
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.