Additional 0 to column data

Hi

I have a column in the db with serial number data, on a export that I am doing the data has to be 10 digits long the problem is not all of them are eg

12234

122334343

1234234567

how can i get it to look like this adding an 0 to the front to make the row 10 digits

0000012234

0122334343

1234234567

Thanks

SELECT REPLICATE('0',10 - LEN(CONVERT(VARCHAR(10),Column1))) + CONVERT(VARCHAR(10),Column1)

HTH,

Babu

|||

select right('000000000' + convert(varchar(10),ColumnName),10)

example

declare @.i int
select @.i = 12345

select right('000000000' + convert(varchar(10),@.i),10)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Alternatively perform the formatting in your front end application|||

If the number is to be stored as a number, then this is the best advice. I wouldn't suggest it if this is a value that will be used many different places and never be used in a math equation. For that I would store it in a character string and prepend the zeros.

The real problem comes in all of the different places it is used (reports, data warehouse,etc.) Someone has to format it, and you don't want the user to have to use some UI function to format it. You might do the formatting on the way to the DW and to a reporting data store, but to me it begs the question of the nature of the data. If the nature of the data is a code that happens to be all numbers (but would perform just as well in the application if it was not all numbers, ie 'asd02020' would not change the application as opposed to '000022020', then store it as a character, format it when you save it, and get it over with :)

Adding to the Toolbar

Is there a way to add text to the toolbar (that contains the export choices,
refresh button, paging, etc)? I would just like to put a line that says
"You must export before you can print".
Thanks in advance,
MelissaSP1 allows some modifications to the HTML Viewer toolbar through a style
sheet. Check out
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Melissa" <a@.a.com> wrote in message
news:e3EDhsJgEHA.3192@.tk2msftngp13.phx.gbl...
> Is there a way to add text to the toolbar (that contains the export
choices,
> refresh button, paging, etc)? I would just like to put a line that says
> "You must export before you can print".
> Thanks in advance,
> Melissa
>

Adding the Date to Filename of DTS Export

I'd like to add the date to the file name of a DTS Export.
For Example:
Export092503.xls
I've tried various methods but nothing has worked as of yet.
Any ideas?
Thanks in Advance.
TechRickI export a standard file name, then rename using a BAT file. Here's the BAT file I use...

-------------------
@.echo off

c:
cd \SQL_Support_Applications\FTP Scripts\SONIC_Data

IF EXIST CUST_%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%.dat DEL CUST_%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%.dat

IF EXIST CUST_raw.dat REN CUST_raw.dat CUST_%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%.dat

EXIT
-------------------

It checks to see if the dated file exists and deletes before renaming.|||Hi TechRick!

If I get you correct, you run an data export task in a dts package.

In this case you can use an ActiveXTask in this package to get the system date and modify the properties of the export task. A similar example is shown here: http://www.sqldts.com/default.aspx?231

Hope this helps you!

Greetings,
Carstensql

Adding headers to matrix

Hi,
how can I add headers to my matrixcolumns?
I created a table inside the merged cell above, but this won't export
to excel: i get the error "Data Regions within table/matrix cells are
ignored."
thx,
FrankI'm assuming you mean the top left cell of the matrix?
Add a rectangle outside the data region, insert a textbox for each
column heading formatting, sizing and positioning them accordingly.
Cut the rectangle and paste it into the matrix cell.
--
Regards
Chris
Frank Mestdagh wrote:
> Hi,
> how can I add headers to my matrixcolumns?
> I created a table inside the merged cell above, but this won't export
> to excel: i get the error "Data Regions within table/matrix cells are
> ignored."
> thx,
> Frank|||Thanks Chris!!! I am going to try that ... it seems hokey that we cant have
the OPTION to iniclude column headings for that area to me! I will try what
you suggested.
"Chris McGuigan" wrote:
> I'm assuming you mean the top left cell of the matrix?
> Add a rectangle outside the data region, insert a textbox for each
> column heading formatting, sizing and positioning them accordingly.
> Cut the rectangle and paste it into the matrix cell.
> --
> Regards
> Chris
>
> Frank Mestdagh wrote:
> > Hi,
> > how can I add headers to my matrixcolumns?
> > I created a table inside the merged cell above, but this won't export
> > to excel: i get the error "Data Regions within table/matrix cells are
> > ignored."
> >
> > thx,
> > Frank
>

adding client server aliases

How can I import a client alias list in SQL Server 2005 Configuration Manager? The tool provides a way to export a list, but not to import a list. Adding 100 SQL Server instances via the GUI is time consuming and agonizing.

Thanks,

Justin Randall

Unfortunately, as you noticed, the functionality to import a list is missing.

If you think it would be a useful addition, please let your wishes/needs be known at:

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

|||I will make the suggestion. Thanks!