is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

is it possible to number the records?

I am using Sql Server 2000. I have a select query which returns rows
from a table. For exmaple let's say it returns 10 rows. I would like to
return one additional column with numbers 1 from 10 against each row.
So if my query returns 20 rows, the additional column should return me
numbers 1 to 20.
Is that something possible to do?
Thanks!!See the post in the microsoft.public.sqlserver.programming newsgroup with
today's date, subject: "add a count column"
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy" <nroy02@.gmail.com> wrote in message
news:1153508782.787452.202320@.b28g2000cwb.googlegroups.com...
> I am using Sql Server 2000. I have a select query which returns rows
> from a table. For exmaple let's say it returns 10 rows. I would like to
> return one additional column with numbers 1 from 10 against each row.
> So if my query returns 20 rows, the additional column should return me
> numbers 1 to 20.
> Is that something possible to do?
> Thanks!!
>

Is it possible to move a database from 2005 to 2000?

Hello,

I have a database in 2005 that I'd like to copy and attach in 2000. Is this
possible? if so, how do I do it? I've tried doing a number of things from
just trying to attach to the db file to trying to export and import via
access and I can't seem to get anything to work.

Any help would be greatly appreciated.

Thanks!
RickRico (me@.you.com) writes:

Quote:

Originally Posted by

I have a database in 2005 that I'd like to copy and attach in 2000. Is
this possible? if so, how do I do it? I've tried doing a number of
things from just trying to attach to the db file to trying to export and
import via access and I can't seem to get anything to work.

You cannot attach a database from SQL 2005 on SQL 2000. When the developers
completed SQL 2000, they did not what know would be in the product five
years later.

To copy the schema, right-click the database in Mgmt Studio and select
Tasks->Generate Script. There is a setting to force SQL 2000 syntax,
you need to select that one. (I seem to recall that there was a bug
with this in SQL 2000 RTM, so make sure that you have SP1.)

If memory serves, create of foreign keys is at the end. In any case,
split the script so that FKs are in a script of their own. Run only
the script without the FKs. Beware that copying the schema can only be
completed successfully, if there is on use of new features in SQL 2005 in
the database.

To copy the data run this:

SELECT 'BCP db..' + name + ' out ' + name + '.bcp -T -S server2005 -n'
FROM sys.objects
WHERE type = 'U'
AND objectproperty(object_id, 'IsMSShipped') = 0

Copy and paste result, run from a command-line window. Then change
the above to:

SELECT 'BCP db..' + name + ' in ' + name + '.bcp -T -S server2000 -n ' +
case when ident_current(name) is not null then ' -E' else '' END
FROM sys.objects
WHERE type = 'U'
AND objectproperty(object_id, 'IsMSShipped') = 0

Copy, paste and run again.

Finally rnn the script with the FK:s.

Note the BCP stuff assumes that you have everything in the dbo schema,
as is the only thing that makes sense on SQL 2000.

--
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.mspxsql

Is it possible to get the month name with only an integer representation of the number

Hi,

Is it possible to get the month name with only an integer representation of the number.

i.e January ,February..... the DATENAME only takes a date as a value.

thanks in advance

Yes, you can use a combination of the DATEADD and DATENAME functions for this. Something like this:

Code Snippet

select number,
datename(mm, dateadd(mm, number - 1, 0))
as monthName
from master.dbo.spt_values (nolock)
where name is null
and number > 0
and number < 13

/*
number monthName
--
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
*/

|||

Thank you that realy worked perfectly.

can you explain what is name =null

and what is

master.dbo.spt_values (nolock)

sorry for picking your brain I just want to understand it.

|||

Well, the MASTER.DBO.SPT_VALUES (NOLOCK) is a reference to the table that I used. This is an undocumented table and my use here is also undocumented and in general should NOT be propagated! Really, this is a case of me being lazy. You remember here earlier today? I suggested that you make a 12-entry inline table with values 1-12 for each month of the year. This is what I SHOULD be doing here. I am really doing the same thing with this particular reference only in this case my method is completely "unsafe" -- my solution is "quick and dirty" -- like REAL dirty.

The "NAME IS NULL" is just a method of making sure that I am narrowing the list of numbers that will be returned such that no number will appear more than once -- again, "quck and dirty". All I was trying to do was to quickly give you numbers 1-12 (in this case from a very dirty source) so that I could show you how to use the DATEADD and DATENAME functions to correspond to the numeric values for each month (GRRRR this KEYBOARD!!!). The (NOLOCK) portion is designating the "NOLOCK OPTIMIZER HINT" so that I do not incur any locks on the SPT_VALUES table.

I really should have explained this rather than leave you hanging to wander about it. Please forgive me for this infraction.

Kent

Please, someone add commentary to this.

|||

I thought about it a bit over night and realized that you might want to give a look to this article about the virtues of having a "table of numbers":

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

Since twice we have discussed queries that utilizes numbers 1-12, it might be appropriate to give this a look.

|||

Kent Nice work. I closed the thread.

Is it possiable conversion

Hi

i heared that Oracle can convert from number to word thru query i
remember thru (jsp format) .Is it possiable in sqlserver

ThanksHi

Although I have not done this myslf, you may want to look at either:
Creating an RTF document
Creating a txt document and a word macro to format and save it
Using a COM object (called from sp_AO* procedures)
Using XML and a word template that picks it up.
Sucking data from SQL Server into word

John

"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0311140440.4738e50d@.posting.google.c om...
> Hi
> i heared that Oracle can convert from number to word thru query i
> remember thru (jsp format) .Is it possiable in sqlserver
>
>
> Thanks

Is backing up an MDF safe?

Heard a frightening thing: apparently a number of our customers do
backups by just copying the MDF and LDF and backing those up...on a
live database. My hunch is that that could well result in a backup
that's not usable. But I'm told "oh, people have been doing this for
years!"
Is this a sane way to do 'backups' at all? I know that it could be a
reasonable way if a server is shut down and/or the files are detached.
But live?
Any links to information discussing this would be appreciated--if its
dangerous, I'd like to be able to point some of my people to it and
point out the dangers.
Thanks!
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
No, that is definitely frightening, and I'd love to see what happens when
you ask them to demonstrate the whole process. (a) you can't copy an MDF
file if it is active, and (b) if the MDF is not detached properly, there is
little chance that you will be able to attach it.
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hi Mike,
One of my basic question is, how are they able to copy .mdf and .ldf files,
when the database is active? It gives sharing violation error, as the files
are used by the database.
Normal practice is to take full backup during off-peak hours if the db is
not huge. And if they want to copy data files and log files, they should
detach the files from sql server instance by applying sp_detach_db. Copy it
to a different folder and then take the file backup.
If they have been doing it for years, I guess they are either taking SQL
server offline or detaching the datafile and logfile, before taking the file
backup.
To confirm, ask them if they are able to restore the database from the
backup they have taken.
Thanks
Yogish
|||"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eaObDxO%23EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
"And how many practice restores have you done?"

> Is this a sane way to do 'backups' at all? I know that it could be a
> reasonable way if a server is shut down and/or the files are detached.
> But live?
Some backup agents will backup SQL Server "live". If they're using one of
those they're probably ok.
But otherwise I'd worry.

> Any links to information discussing this would be appreciated--if its
> dangerous, I'd like to be able to point some of my people to it and
> point out the dangers.
> Thanks!
|||The problem with detaching a database is that it becomes unavailable. This
is unacceptable in an environment where you want 24x7 availability. The
best option (in my opinion) is to backup to disk using the Transact-SQL
BACKUP command. It is easy to restore a database backed up using this
method via the RESTORE statement. Whatever method people use to backup
their data they should know how to restore/recover the data. It is
important to test the restore process. Test it on a separate server and see
if you can indeed restore the database.
Keith
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:C210C859-E155-44F1-83DA-6A62440594B8@.microsoft.com...
> Hi Mike,
> One of my basic question is, how are they able to copy .mdf and .ldf
files,
> when the database is active? It gives sharing violation error, as the
files
> are used by the database.
> Normal practice is to take full backup during off-peak hours if the db is
> not huge. And if they want to copy data files and log files, they should
> detach the files from sql server instance by applying sp_detach_db. Copy
it
> to a different folder and then take the file backup.
> If they have been doing it for years, I guess they are either taking SQL
> server offline or detaching the datafile and logfile, before taking the
file
> backup.
> To confirm, ask them if they are able to restore the database from the
> backup they have taken.
> --
> Thanks
> Yogish

Is backing up an MDF safe?

Heard a frightening thing: apparently a number of our customers do
backups by just copying the MDF and LDF and backing those up...on a
live database. My hunch is that that could well result in a backup
that's not usable. But I'm told "oh, people have been doing this for
years!"
Is this a sane way to do 'backups' at all? I know that it could be a
reasonable way if a server is shut down and/or the files are detached.
But live?
Any links to information discussing this would be appreciated--if its
dangerous, I'd like to be able to point some of my people to it and
point out the dangers.
Thanks!> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
No, that is definitely frightening, and I'd love to see what happens when
you ask them to demonstrate the whole process. (a) you can't copy an MDF
file if it is active, and (b) if the MDF is not detached properly, there is
little chance that you will be able to attach it.
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi Mike,
One of my basic question is, how are they able to copy .mdf and .ldf files,
when the database is active? It gives sharing violation error, as the files
are used by the database.
Normal practice is to take full backup during off-peak hours if the db is
not huge. And if they want to copy data files and log files, they should
detach the files from sql server instance by applying sp_detach_db. Copy it
to a different folder and then take the file backup.
If they have been doing it for years, I guess they are either taking SQL
server offline or detaching the datafile and logfile, before taking the file
backup.
To confirm, ask them if they are able to restore the database from the
backup they have taken.
Thanks
Yogish|||"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eaObDxO%23EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
"And how many practice restores have you done?"

> Is this a sane way to do 'backups' at all? I know that it could be a
> reasonable way if a server is shut down and/or the files are detached.
> But live?
Some backup agents will backup SQL Server "live". If they're using one of
those they're probably ok.
But otherwise I'd worry.

> Any links to information discussing this would be appreciated--if its
> dangerous, I'd like to be able to point some of my people to it and
> point out the dangers.
> Thanks!|||The problem with detaching a database is that it becomes unavailable. This
is unacceptable in an environment where you want 24x7 availability. The
best option (in my opinion) is to backup to disk using the Transact-SQL
BACKUP command. It is easy to restore a database backed up using this
method via the RESTORE statement. Whatever method people use to backup
their data they should know how to restore/recover the data. It is
important to test the restore process. Test it on a separate server and see
if you can indeed restore the database.
Keith
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:C210C859-E155-44F1-83DA-6A62440594B8@.microsoft.com...
> Hi Mike,
> One of my basic question is, how are they able to copy .mdf and .ldf
files,
> when the database is active? It gives sharing violation error, as the
files
> are used by the database.
> Normal practice is to take full backup during off-peak hours if the db is
> not huge. And if they want to copy data files and log files, they should
> detach the files from sql server instance by applying sp_detach_db. Copy
it
> to a different folder and then take the file backup.
> If they have been doing it for years, I guess they are either taking SQL
> server offline or detaching the datafile and logfile, before taking the
file
> backup.
> To confirm, ask them if they are able to restore the database from the
> backup they have taken.
> --
> Thanks
> Yogish

Is backing up an MDF safe?

Heard a frightening thing: apparently a number of our customers do
backups by just copying the MDF and LDF and backing those up...on a
live database. My hunch is that that could well result in a backup
that's not usable. But I'm told "oh, people have been doing this for
years!"
Is this a sane way to do 'backups' at all? I know that it could be a
reasonable way if a server is shut down and/or the files are detached.
But live?
Any links to information discussing this would be appreciated--if its
dangerous, I'd like to be able to point some of my people to it and
point out the dangers.
Thanks!> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
No, that is definitely frightening, and I'd love to see what happens when
you ask them to demonstrate the whole process. (a) you can't copy an MDF
file if it is active, and (b) if the MDF is not detached properly, there is
little chance that you will be able to attach it.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi Mike,
One of my basic question is, how are they able to copy .mdf and .ldf files,
when the database is active? It gives sharing violation error, as the files
are used by the database.
Normal practice is to take full backup during off-peak hours if the db is
not huge. And if they want to copy data files and log files, they should
detach the files from sql server instance by applying sp_detach_db. Copy it
to a different folder and then take the file backup.
If they have been doing it for years, I guess they are either taking SQL
server offline or detaching the datafile and logfile, before taking the file
backup.
To confirm, ask them if they are able to restore the database from the
backup they have taken.
--
Thanks
Yogish|||"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eaObDxO%23EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
"And how many practice restores have you done?"
> Is this a sane way to do 'backups' at all? I know that it could be a
> reasonable way if a server is shut down and/or the files are detached.
> But live?
Some backup agents will backup SQL Server "live". If they're using one of
those they're probably ok.
But otherwise I'd worry.
> Any links to information discussing this would be appreciated--if its
> dangerous, I'd like to be able to point some of my people to it and
> point out the dangers.
> Thanks!|||The problem with detaching a database is that it becomes unavailable. This
is unacceptable in an environment where you want 24x7 availability. The
best option (in my opinion) is to backup to disk using the Transact-SQL
BACKUP command. It is easy to restore a database backed up using this
method via the RESTORE statement. Whatever method people use to backup
their data they should know how to restore/recover the data. It is
important to test the restore process. Test it on a separate server and see
if you can indeed restore the database.
--
Keith
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:C210C859-E155-44F1-83DA-6A62440594B8@.microsoft.com...
> Hi Mike,
> One of my basic question is, how are they able to copy .mdf and .ldf
files,
> when the database is active? It gives sharing violation error, as the
files
> are used by the database.
> Normal practice is to take full backup during off-peak hours if the db is
> not huge. And if they want to copy data files and log files, they should
> detach the files from sql server instance by applying sp_detach_db. Copy
it
> to a different folder and then take the file backup.
> If they have been doing it for years, I guess they are either taking SQL
> server offline or detaching the datafile and logfile, before taking the
file
> backup.
> To confirm, ask them if they are able to restore the database from the
> backup they have taken.
> --
> Thanks
> Yogish

Is Autonumber Possible

I have an SQL database that was an Access database. In a
few of the tables, in the Access database, I used an auto
number. When I converted it to SQL I noticed that there
is not an auto number data type. I was wondering if there
is an equivalent to Auto number in SQL or if there was any
way of replicating that data type.
Thanks,
Aaron ShoverCheck out Identity in Books Online.
"Aaron Shover" <ashover@.state.pa.us> wrote in message
news:079201c35a8e$35f79620$a401280a@.phx.gbl...
> I have an SQL database that was an Access database. In a
> few of the tables, in the Access database, I used an auto
> number. When I converted it to SQL I noticed that there
> is not an auto number data type. I was wondering if there
> is an equivalent to Auto number in SQL or if there was any
> way of replicating that data type.
> Thanks,
> Aaron Shover|||Aaron,
Refer 'IDENTITY' property in BooksOnLine.A sample is
CREATE TABLE products
(
pid int IDENTITY(1,1),
pname varchar (35),
)
GO
INSERT products VALUES('book')
GO
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Aaron Shover" <ashover@.state.pa.us> wrote in message
news:079201c35a8e$35f79620$a401280a@.phx.gbl...
> I have an SQL database that was an Access database. In a
> few of the tables, in the Access database, I used an auto
> number. When I converted it to SQL I noticed that there
> is not an auto number data type. I was wondering if there
> is an equivalent to Auto number in SQL or if there was any
> way of replicating that data type.
> Thanks,
> Aaron Shover|||In SQL Server, it is called an Identity column.
Depending on how you are making your tables, you either
set IDENTITY to YES once you have set the column data
type to integer, or from script you define the table
something like
CREATE TABLE {tablename}
(
tablenameID int identity(1,1) primary key clustered,
.
.
.
)
Either way will give an auto-incremented table. Note
that you are able to set the starting value, and the
incremental jump amount independently. In that example
it starts at 1 and increments by 1 (pretty common).
Paladin
>--Original Message--
>I have an SQL database that was an Access database. In
a
>few of the tables, in the Access database, I used an
auto
>number. When I converted it to SQL I noticed that there
>is not an auto number data type. I was wondering if
there
>is an equivalent to Auto number in SQL or if there was
any
>way of replicating that data type.
>Thanks,
>Aaron Shover
>.
>