row numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

row numbers

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-02-13 : 05:04:28

I am trying to copy the data from one table to another using import export wizard but I am getting an error at a specific row number which is in the 40k numbers.

Is there anyway I can add the row numbers into a table when doing a select * from [table_name]?
The online resources really confused me

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-02-13 : 05:13:03

I tried the below and got the following error

Select ROW_NUMBER() OVER (ORDER BY room_id) AS Row, tb_rooms.* from tb_rooms

?? Row_number is not a recognised function name

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-13 : 05:26:06

quote:
Originally posted by godspeedba

I tried the below and got the following error

Select ROW_NUMBER() OVER (ORDER BY room_id) AS Row, ~~tb_rooms~~* from tb_rooms

?? Row_number is not a recognised function name

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-02-13 : 05:39:55

The prefix of the star will not make a difference to the statement.
The error is caused before that bit is read. Is Row_number not a function in SQL 2005?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-13 : 05:47:56

ROW_NUMBER() is a function in sqlserver 2005.

Is you database running in the wrong compatibility mode -- maybe you have the compat mode set to 8 (2000)?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-13 : 06:19:10

What is the compatiability level of your database?? It must be set to 90.

See this

http://msdn.microsoft.com/en-us/library/bb933794.aspx

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-02-13 : 06:34:32

It's set to 80. Is that wrong?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-13 : 06:39:27

Then set it 90 if you are using sql server 2005 or set it to 100 if u are using sql server 2008..

If u Don't know how to alter the compatiablility level read the link which i had posted earlier...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 09:18:25

quote:
Originally posted by godspeedba

It's set to 80. Is that wrong?

Nope its not wrong. but using 80 you cant leverage upon new features of sql 2005. so before you use them you need to make sure you change compatibility mode to 90. for this just use below

EXEC sp_dbcmptlevel 'your db name here',90

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources