Formatting numbers in SQL

Hi
I need to format the output of a numeric value from SQL-server with a
chosen thousand-separator and decimal-separator.
The Access equivalent would be something like:
Select format(myNumber, "#.###,##") as myFormattedNumber from myTable;
I have searched the net for a solution, and found something about
FORMAT_STRING, but that doesn't seem to be usable in SQL-Server...
Can anyone help me with this?
Regards,
Johnny Nordtvedt, NorwayIn an N-Tier environment it's better to leave presentational features
out of the database and do them in your client/middle tier. Some users
might prefer to configure a different numeric format so it's probably
not something you ought to fix in a SELECT statement.
TSQL does have a CONVERT function, which provides some limited
formatting capability but only if you convert your numerics to a
string. Also, AFAIK the CONVERT function only supports comma as a
thousands separator by default. In reality you should find it much
easier to do the formatting in the client control or form that displays
the data.
David Portas
SQL Server MVP
--|||My problem is that these values is displayed in a Word-document, and the
program that collects these values and inserts them into word, is not
available for formatting, and will not ever be available. So basically, I
am stuck with formatting this in SQL-server.
Regards,
Johnny Nordtvedt, Oslo, Norway|||Is it possible to make a user defined function to do this? And how would I
go by to do this?
Still hope that someone can help me with this. I am certain someone has
done this before?
Regards,
Johnny Nordtvedt, Oslo, Norway|||DECLARE @.numeric NUMERIC(10,2)
SET @.numeric = 123456.78
SELECT
REPLACE(REPLACE(REPLACE(
CONVERT(VARCHAR(10),CAST(@.numeric AS MONEY),1)
,',','~') ,'.',',') ,'~','.')
David Portas
SQL Server MVP
--|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
There are formatting functions on Merge fields in Word. Read the Word
Help file (in English version):
Field Types and Switches
Field Reference
Switches
How to: Highlight merge field, hit Alt-9. Add switches at end of
field:
/# will give numeric format.
/#.#,## I believe will format the number 99999.00 as 99.999,00
Also, read the Word Help articles on Merge Fields.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlWjTIechKqOuFEgEQLidACfWKC4/ST6Pc1f6yJC3xaIoOO4dC8AoKLM
k5KfIvuctrzjBfvZduWw4W0t
=lpsE
--END PGP SIGNATURE--
Johnny, Norway wrote:
> My problem is that these values is displayed in a Word-document, and the
> program that collects these values and inserts them into word, is not
> available for formatting, and will not ever be available. So basically, I
> am stuck with formatting this in SQL-server.

Formatting Issue

Hi

I am using Reporting Services 2005. I have deployed my reports on mutiple servers. It works fine. But on a specific server, when I deployed my reports, its formatting disturbed for a specific font. Wherever I have used Font:

Name: "Arial Narrow"

Size: 8pt

Style: Normal

Weight: Bold

Decoration: None

its size changed and disturbed the whole formatting. It is something related to server settings, but I dont know where to change the formatting to solve the issue. I have tried to change the system display resolution, but it didn't worked. I cant re install server as it is client's machine. It has "Microsoft Windows 2003 Server" installed on it. Please help me to solve the problem.

Looking for a quick response.

I have tried to install Arial Narrow Font on the server. It is installed successfully but the problem is still there. Can anyone help me out to solve the problem.

Urgent

|||

try to check ur issue in this file

ReportingServices.css its available in styles folder under reports folder in IIS or check out styles folder in reportserver folder in IIS

|||

Did you find a solution to this problem?

I'm getting the exact same problem, in the exact same scenario: Deploying to a new Windows 2003 server (of which I don't have complete control) with my Arial Narrow 8pt fonts (normal and bold) being rendered incorrectly. One point: initially the server did not have Arial Narrow on it. I added the font and this improved the situation (arial narrow is being used) but it isn't being rendered like arial narrow should be. Lot's of spacing and padding around each letter, which themselves not looking like arial narrow. If I cut and paste the PDF content into another document (word or outlook) the text is pasted as arial narrow perfectly (not looking anything like the text appeared in the problematic PDF).

|||I am having similiar issues. I have complete access to the pc's displaying the reports and the servers running the reports. I am looking to use a font such as Trade Gothic (our company font) and I am trying to get it to render into the PDF. I have installed the font on all machines and servers involved in the report generation and no success to date. I can get it to show up as HTML output, because that is reliant on the machine to support the font, however when I export to a PDF it falls to the default Sans-Seriff font. Any ideas?|||You have to reboot the server after installing a new font.
|||After initially trying to just install the missing font (which did not work), installing the font and then rebooting the server did the trick! Thanks.
sql

Formatting Issue

Hi

I am using Reporting Services 2005. I have deployed my reports on mutiple servers. It works fine. But on a specific server, when I deployed my reports, its formatting disturbed for a specific font. Wherever I have used Font:

Name: "Arial Narrow"

Size: 8pt

Style: Normal

Weight: Bold

Decoration: None

its size changed and disturbed the whole formatting. It is something related to server settings, but I dont know where to change the formatting to solve the issue. I have tried to change the system display resolution, but it didn't worked. I cant re install server as it is client's machine. It has "Microsoft Windows 2003 Server" installed on it. Please help me to solve the problem.

Looking for a quick response.

I have tried to install Arial Narrow Font on the server. It is installed successfully but the problem is still there. Can anyone help me out to solve the problem.

Urgent

|||

try to check ur issue in this file

ReportingServices.css its available in styles folder under reports folder in IIS or check out styles folder in reportserver folder in IIS

|||

Did you find a solution to this problem?

I'm getting the exact same problem, in the exact same scenario: Deploying to a new Windows 2003 server (of which I don't have complete control) with my Arial Narrow 8pt fonts (normal and bold) being rendered incorrectly. One point: initially the server did not have Arial Narrow on it. I added the font and this improved the situation (arial narrow is being used) but it isn't being rendered like arial narrow should be. Lot's of spacing and padding around each letter, which themselves not looking like arial narrow. If I cut and paste the PDF content into another document (word or outlook) the text is pasted as arial narrow perfectly (not looking anything like the text appeared in the problematic PDF).

|||I am having similiar issues. I have complete access to the pc's displaying the reports and the servers running the reports. I am looking to use a font such as Trade Gothic (our company font) and I am trying to get it to render into the PDF. I have installed the font on all machines and servers involved in the report generation and no success to date. I can get it to show up as HTML output, because that is reliant on the machine to support the font, however when I export to a PDF it falls to the default Sans-Seriff font. Any ideas?|||You have to reboot the server after installing a new font.|||After initially trying to just install the missing font (which did not work), installing the font and then rebooting the server did the trick! Thanks.sql

Formatting database content

Hi
I have set up a SQL database to contain alist FAQ's for our company and
then plan to pull this info off using a web page.

So far I have entered the data but I am unable to control how it is
displayed inside SQL ie I cannot enter new blank lines I have tried
using lots of spaces but this does not work when I use the website to
display the info.

Is there a way of formatting and editing the data in the sql database
as I am unable to do this, if I try to edit the data in the database I
have to copy it to notepad delete what is in the database, edit the
text in notepad then paste it back in.

There must be a better way.

Please help

alamb200Sounds as if you need to convert your carraige return / line feed into the
HTML <BR> tag which will then give you the correct formatting on the screen.

If you want to do it in the database then use REPLACE( <col>, char(13) +
char(10), '<BR>' ) - check, it might be 10, 13 :)

Tony

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<alamb200@.hotmail.com> wrote in message
news:1147771818.200249.269650@.j55g2000cwa.googlegr oups.com...
> Hi
> I have set up a SQL database to contain alist FAQ's for our company and
> then plan to pull this info off using a web page.
> So far I have entered the data but I am unable to control how it is
> displayed inside SQL ie I cannot enter new blank lines I have tried
> using lots of spaces but this does not work when I use the website to
> display the info.
> Is there a way of formatting and editing the data in the sql database
> as I am unable to do this, if I try to edit the data in the database I
> have to copy it to notepad delete what is in the database, edit the
> text in notepad then paste it back in.
> There must be a better way.
> Please help
> alamb200|||I have just had a look at the parameter tag but as I am new to SQL I am
not sure how I would put this place.

For example if my prefix charecter was / and my suffix charecter was \
for the sake of argument nad i wanted a new line after the full stop
in the following text how would I enter the information.

If your printer is no longer printing your logo's or overlays it may
have lost the images that have been "Flashed" on to it, to reflash your
printer follow the following instructions. Log on to SB Client and go
into the Company Files section. Next goto Runtime then Maintenance and
System Manager Menu.

Also I seem to be unable to edit text already in the database and have
to delete it and reenter it every time, is there an easier way of doing
that.

Thanks

Anthony

Tony Rogerson wrote:
> Sounds as if you need to convert your carraige return / line feed into the
> HTML <BR> tag which will then give you the correct formatting on the screen.
> If you want to do it in the database then use REPLACE( <col>, char(13) +
> char(10), '<BR>' ) - check, it might be 10, 13 :)
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> <alamb200@.hotmail.com> wrote in message
> news:1147771818.200249.269650@.j55g2000cwa.googlegr oups.com...
> > Hi
> > I have set up a SQL database to contain alist FAQ's for our company and
> > then plan to pull this info off using a web page.
> > So far I have entered the data but I am unable to control how it is
> > displayed inside SQL ie I cannot enter new blank lines I have tried
> > using lots of spaces but this does not work when I use the website to
> > display the info.
> > Is there a way of formatting and editing the data in the sql database
> > as I am unable to do this, if I try to edit the data in the database I
> > have to copy it to notepad delete what is in the database, edit the
> > text in notepad then paste it back in.
> > There must be a better way.
> > Please help
> > alamb200|||On 16 May 2006 04:13:58 -0700, alamb200@.hotmail.com wrote:

>I have just had a look at the parameter tag but as I am new to SQL I am
>not sure how I would put this place.
>For example if my prefix charecter was / and my suffix charecter was \
>for the sake of argument nad i wanted a new line after the full stop
>in the following text how would I enter the information.

Hi Anthony,

wiith the right front-end, there's no need to use parameters. For
instance, copy and paste the following into Query Analyzer and execute;
you'll see that the output has exactly the lline breaks yoou asked for:

CREATE TABLE test (KeyColumn int NOT NULL PRIMARY KEY,
DataColumn varchar(1000) NOT NULL)
INSERT INTO test (KeyColumn, DataColumn)
VALUES (1, 'If your printer is no longer printing your logo''s or
overlays it may have lost the images that have been "Flashed" on to it,
to reflash your printer follow the following instructions.
Log on to SB Client and go into the Company Files section.
Next goto Runtime then Maintenance and System Manager Menu.')
go
SELECT * FROM test
go
DROP TABLE test
go

(Note: beware of extra line breaks inserted by Usenet software. In the
intended code, there are line breaks after the periods, but no other
line breaks in the VALUES clause.)

>Also I seem to be unable to edit text already in the database and have
>to delete it and reenter it every time, is there an easier way of doing
>that.

That, too, is a problem with your frontend.

May I ask you what tool you are using as your frontend?

--
Hugo Kornelis, SQL Server MVP|||Hi

I am using a web page as my front end using ASP to pull the info from
the SQL database so I am in the position of either having multiple
columns with small bits of info in my database so I can format them how
I want

The other option is to have some commands built into the text in the
database entry which will be read by the system and control the
formatting when it is read.

Is this possible?

When I am trying to edit the info in the database all I am doing is
right clicking on the table in Enterprise manager and display all
columns and working on it from there.

Is this the right way to do this?

Anthony

Hugo Kornelis wrote:
> On 16 May 2006 04:13:58 -0700, alamb200@.hotmail.com wrote:
> >I have just had a look at the parameter tag but as I am new to SQL I am
> >not sure how I would put this place.
> >For example if my prefix charecter was / and my suffix charecter was \
> >for the sake of argument nad i wanted a new line after the full stop
> >in the following text how would I enter the information.
> Hi Anthony,
> wiith the right front-end, there's no need to use parameters. For
> instance, copy and paste the following into Query Analyzer and execute;
> you'll see that the output has exactly the lline breaks yoou asked for:
> CREATE TABLE test (KeyColumn int NOT NULL PRIMARY KEY,
> DataColumn varchar(1000) NOT NULL)
> INSERT INTO test (KeyColumn, DataColumn)
> VALUES (1, 'If your printer is no longer printing your logo''s or
> overlays it may have lost the images that have been "Flashed" on to it,
> to reflash your printer follow the following instructions.
> Log on to SB Client and go into the Company Files section.
> Next goto Runtime then Maintenance and System Manager Menu.')
> go
> SELECT * FROM test
> go
> DROP TABLE test
> go
> (Note: beware of extra line breaks inserted by Usenet software. In the
> intended code, there are line breaks after the periods, but no other
> line breaks in the VALUES clause.)
> >Also I seem to be unable to edit text already in the database and have
> >to delete it and reenter it every time, is there an easier way of doing
> >that.
> That, too, is a problem with your frontend.
> May I ask you what tool you are using as your frontend?
> --
> Hugo Kornelis, SQL Server MVP|||On 17 May 2006 02:01:41 -0700, alamb200@.hotmail.com wrote:

>Hi
>I am using a web page as my front end using ASP to pull the info from
>the SQL database so I am in the position of either having multiple
>columns with small bits of info in my database so I can format them how
>I want
>The other option is to have some commands built into the text in the
>database entry which will be read by the system and control the
>formatting when it is read.
>Is this possible?

Hi Anthony,

I'm not an ASP guy, so I don't know. Are you saying that linebreaks in a
long character data column are not displayed properly by ASP? Have you
consiedered asking for alternatives in one of the ASP groups?

>When I am trying to edit the info in the database all I am doing is
>right clicking on the table in Enterprise manager and display all
>columns and working on it from there.
>Is this the right way to do this?

No, definitely not. Data entry through Enterprise Manager is broken in
far more ways than I care to remember. Some of these errors are listed
by Aaron Bertrand at http://www.aspfaq.com/show.asp?id=2455.

--
Hugo Kornelis, SQL Server MVP|||Hi

Go to http://212.50.191.220/techfaq and enter restart in the search box
in the bottom center this pops up a new page of info pulled from my SQL
database. As you can see it can be difficult to read so I would like to
add some line breaks and if possible some other formatting.

When I pull the info in it comes as one big lump of text so ASP cannot
format it what I beleive from one of my earlier replies is that I can
embed some commands into the text held in the entry to be pulled out of
the database that ASP can read and turn into html formatting when it is
displayed on the page.

Is this correct and if so how do I add them.

Anthony
Hugo Kornelis wrote:
> On 17 May 2006 02:01:41 -0700, alamb200@.hotmail.com wrote:
> >Hi
> >I am using a web page as my front end using ASP to pull the info from
> >the SQL database so I am in the position of either having multiple
> >columns with small bits of info in my database so I can format them how
> >I want
> >The other option is to have some commands built into the text in the
> >database entry which will be read by the system and control the
> >formatting when it is read.
> >Is this possible?
> Hi Anthony,
> I'm not an ASP guy, so I don't know. Are you saying that linebreaks in a
> long character data column are not displayed properly by ASP? Have you
> consiedered asking for alternatives in one of the ASP groups?
> >When I am trying to edit the info in the database all I am doing is
> >right clicking on the table in Enterprise manager and display all
> >columns and working on it from there.
> >Is this the right way to do this?
> No, definitely not. Data entry through Enterprise Manager is broken in
> far more ways than I care to remember. Some of these errors are listed
> by Aaron Bertrand at http://www.aspfaq.com/show.asp?id=2455.
> --
> Hugo Kornelis, SQL Server MVP|||On 18 May 2006 06:53:21 -0700, alamb200@.hotmail.com wrote:

>Hi
>Go to http://212.50.191.220/techfaq

"The page cannot be found"

>When I pull the info in it comes as one big lump of text so ASP cannot
>format it

I can only repeat my previous suggestion: if ASP has trouble retaining
embedded CR/LF combinations from a varchar column, then the best place
to seek help is an ASP group.

--
Hugo Kornelis, SQL Server MVP|||(alamb200@.hotmail.com) writes:
> When I pull the info in it comes as one big lump of text so ASP cannot
> format it what I beleive from one of my earlier replies is that I can
> embed some commands into the text held in the entry to be pulled out of
> the database that ASP can read and turn into html formatting when it is
> displayed on the page.

Neither do I know much ASP, but I would assume that if you get some
text from the database, and spit it out between regular <P> tags,
that any line breaks will be blissfully ignored, as that is how HTML
works.

You could use the <PRE> tag to maintain the line-breaks, but the text
would be presented in a monospaced font, so it may look a bit ugly.

You could run this:

SELECT replace(col, char(13) + char(10), <BR />)

to change line breaks to <BR /> tags. The <BR /> introduces a line break
in the text.

You could also store the text in HTML format.

But as Hugo says, asking in an ASP group is probably a good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

FORMAT_STRING "Currency" returns different currencies

hi!

i'm just getting to know SSAS. i created a standard measure and a calculated measure. the standard measure returns my values in euros, whereas the calculated measure displays them in swiss francs. the analysis server language (which one can set in sql server management studio) is German (Germany). my operating system language is German (Switzerland) though. seems as if calculated measures would read their standard value out of the system language, instead of the SSAS language - is that true? and how can it be fixed?

thank you,

Nico

Please check the following blog - http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx it should help you to set up the formatting of currencies the way you want it.

Format string

Hii
can somebody please tell me what format string i should use to bring 3
digits after decimal in any textbox.
thanx in advanceformat(Field!Number.Value,"0.000")
"Techotsav" wrote:
> Hii
> can somebody please tell me what format string i should use to bring 3
> digits after decimal in any textbox.
> thanx in advance
>|||many many thanks (sorry didnt get ur label), i placed =format("0.000")
in formatstring textbox n bang, it works!

fORMAT eXCEPTION

Hi
I am continuously getting the error:

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

Line 57: {
Line 58: Connection.Open() ;
Line 59: ThreadResult = ThreadCommand.ExecuteNonQuery() ;
Line 60: }
Line 61:

in the following part of the code :

protected void PostClick(object sender,EventArgs e)
{
SqlConnection Connection ;
SqlCommand ThreadCommand ;
SqlCommand GetThreadCommand ;
SqlCommand MessageCommand ;
SqlDataReader ThreadReader ;
string InsertThread,InsertMessage ;
string GetThread,Message ;
long ThreadID ;
int ThreadResult ;
Connection = new SqlConnection("server=HT; Integrated Security= SSPI; Database=Forums");
Feedback.Text = "" ;
InsertThread = "Insert Into Threads(ThreadName,TopicId) VALUES(@.ThreadName,@.TopicId)" ;
ThreadCommand = new SqlCommand(InsertThread, Connection) ;
ThreadCommand.Parameters.Add(new SqlParameter("@.ThreadName", SqlDbType.VarChar,50)) ;
ThreadCommand.Parameters.Add(new SqlParameter("@.TopicId", System.Data.SqlDbType.Int));
ThreadCommand.Parameters["@.ThreadName"].Value = ThreadText.Text ;
ThreadCommand.Parameters["@.TopicId"].Value = Request.QueryString["TopicId"] ;
try
{
Connection.Open() ;
ThreadResult = ThreadCommand.ExecuteNonQuery() ;
}
catch (System.Data.SqlClient.SqlException excp)
{
if (excp.Errors[0].Number == -105121349)
{
Feedback.Text = "<font color=red>*** There is already a thread with the name"+ThreadText.Text+". Please choose a different name and click Post.</font><br><br>" ;
}
else
{
Feedback.Text = "<font color=red>*** "+excp.Errors[0].Message +"<br><br>" ;
}
}

I would be very grateful for any suggestions.I expect the problem is here:

ThreadCommand.Parameters["@.TopicId"].Value = Request.QueryString["TopicId"] ;

Perhaps Request.QueryString["TopicId"] is null, or not a value that can be converted into a number? Debug the application and check this value, or alternately, do a Response.Write("TopicID: " + Request.QueryString["TopicId"] );|||Hi
Yes Mr.Douglas you are right. but the problem lies in this statement only in this page. Actually i think its in the following line in the other page :

<a href="http://links.10026.com/?link=threads.aspx?TopicId=<%=Request.QueryString["TopicId"]%>&TopicName= <%=Request.QueryString["TopicName"]%>"
Actually on printing TopicId, i noticed its '1' (with comma) instead of being just 1 due to the courtesy of above line. Can you please tell me how can i remove the comma thats being appended. Thanks a lot|||I presume when you say comma, you really mean the single quotes ' and ' and if so, change to this:

=<%=String.Trim(Request.QueryString["TopicId"],"'".ToCharArray()%
The best bet would be to determine why, on the previous page, it is putting the single quotes in.|||Yes you are right , i can use the trim method but Mr. Douglas why is the following statement attaching a comma.

<%=Request.QueryString["TopicId"]%
in

<a href="http://links.10026.com/?link=threads.aspx?TopicId=
<%=Request.QueryString["TopicId"]%>
&TopicName=<%=Request.QueryString["TopicName"]%>"
Thanks a lot and Mr. Douglas i would like you advise on a designing tool . Which one do u use since yesterday i was trying to do things with Html Table but thats a HeadAche and i can't even arrange the data the way i like.|||I have no idea what the value of Request.QueryString["TopicID"] is, but my guess is that somehow, it has the apostrophes.

I tend to use a mix of tables and Cascading Style sheets, but honestly, my page design skills are less than perfect.

Format display of current row

Hi!

I'm wondering is there any simple way to achieve the following
function call in SQL Server. The sentence to translate is (Oracle
syntax):

to_char(rownum, '000')

rownum: number of the current row

to_char: formats a number (the 1st param) according to the format
defined in the 2nd param. In this case, the '000' preprends 2 or more
zeros until forming a 3-digit number.

I'm using it in something like:

SELECT id_table, string_column || TO_CHAR(ROWNUM,'000') FROM table

Thanx a bunch,
CroNo such thing as a "row number" in SQL Server. Explain what you want to do
so that we can suggest some alternatives.

The Standard SQL alternative to TO_CHAR is the CAST function (CAST(x AS
VARCHAR) or CAST(x AS CHAR)) and that is supported by SQL Server.

--
David Portas
SQL Server MVP
--|||replace(str(123, 3,0),' ', '0')

where 123 is your number, 3 is the lenght(number of zeros to padd) and
0 is the number of decimals places to show

yuck, but it works|||I usually use the following when trying to zero-pad numbers:

SELECT RIGHT('000' + CAST(my_num AS VARCHAR), 3)

-Tom.|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<-oWdnSaXw8NBEfXfRVn-iA@.giganews.com>...
> No such thing as a "row number" in SQL Server. Explain what you want to do
> so that we can suggest some alternatives.
> The Standard SQL alternative to TO_CHAR is the CAST function (CAST(x AS
> VARCHAR) or CAST(x AS CHAR)) and that is supported by SQL Server.

The idea behind is creating a stored procedure that retrieves a
numbered list of elements. This list could be displayed in the web, so
it needs to have that "rownum":

SELECT rn, el
FROM (
SELECT <<rownum>> AS rn, element AS el
FROM table
) AS tmp

I've seen some approaches using rank=count(*), but they look ugly.
Could I use something like a temporal sequence in SQL Server?

Thanx again,
Cro|||Why not create the table with a unique column for the display order of
the list elements? If the column is integer, you willnot have to pad a
string with zeroes -- that kind of display is supposed to be done in
the front end, not the database.|||Don Croata (el.croata@.gmail.com) writes:
> The idea behind is creating a stored procedure that retrieves a
> numbered list of elements. This list could be displayed in the web, so
> it needs to have that "rownum":
> SELECT rn, el
> FROM (
> SELECT <<rownum>> AS rn, element AS el
> FROM table
> ) AS tmp
> I've seen some approaches using rank=count(*), but they look ugly.
> Could I use something like a temporal sequence in SQL Server?

There is a row_number() function in SQL 2005, currently in beta.
Beware that this is not a row_number of Oracle fame, but one
related to the actual result set. The syntax is also a little more
complicated. If memory serves, this is derived from the SQL-99 standard.

For SQL-2000 the best bet is probably to say:

CREATE TABLE #t (row_number int IDENTITY,
col1 ...)
INSERT #t (col1, )
SELECT col1, ...
FROM ...
ORDER BY

Note that you cannot use SELECT INTO for this, as it's not guaranteed
that the identity value will follow the ORDER BY clause in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp