'ROW_NUMBER' is not a recognized function name.

Hi experts,
recently i download the SQLExpress 2005 version to do some testing,
when i try to run a new feature which all ROW_NUMBER, but SQL give me
error
'ROW_NUMBER' is not a recognized function name.
i wondering after install the 05, we need to add-on some package to
support this kind of feature,. which is not default '
* the sample is from
http://msdn.microsoft.com/sql/learn...tsqlenhance.asp
pls give advise, many thanksJust the routine check. I hope you are not connecting to a SQL Server 2000
instance from the SQL Srvr Management Studio express edition.|||Check your database compatibility level
exec sp_dbcmptlevel '<Name of db>'
HTH
Kalen Delaney, SQL Server MVP
"XJ" <ianyian@.hotmail.com> wrote in message
news:1148698630.294392.99660@.g10g2000cwb.googlegroups.com...
> Hi experts,
> recently i download the SQLExpress 2005 version to do some testing,
> when i try to run a new feature which all ROW_NUMBER, but SQL give me
> error
> 'ROW_NUMBER' is not a recognized function name.
> i wondering after install the 05, we need to add-on some package to
> support this kind of feature,. which is not default '
> * the sample is from
> http://msdn.microsoft.com/sql/learn...tsqlenhance.asp
> pls give advise, many thanks
>|||Hi Kalen,
i catch u, which give me 80, yes i agree that i have sql 2000
before, but i already uninstall it, rght now under the service name
call XJ\SQLEXPRESS.
and i have use "Microsoft SQL Server Management Studio Express",
but even i try to create a new DB, still give me 80,. pls give some
idea how can i create a DB which have 2005 feature under "studio
express" or any other way ,. .. many thanks|||Hi Kalen , Omnibuzz
Bcos the instance make me some confuse ,. finally i can
connect ! ya ,. many thanks !!!!

Row level security

Hi,
is there a way by which we can provide rowlevel security in sql server2000.
If the above feature is not available can anyone tell me any Work around solutions
thanks
VyasDOn't think there's any built-in support for this.

Use a view?

Row Level Locking

Hello,
I have been trying to figure out how to user Row Level locking feature in
SQL Server 2005. Here is the scenario.
1.Open a new query window in SQL Server Management sudio.
2.Execute a Begin transaction and an Update statement that updates a row in
a table. Do not commit the transaction yet.
3. Open another query window
4. Execute a Begin transaction and an Update statement that updates a
different row in the same table. Do not commit the transaction yet.
The secod session does not complete execution and waits because the table is
locked. I verified that by checking the locks in the database. I was
expecting just the first row to be locked and not the entire table. Once I
commit the first transaction, the second session completes execution of the
update statement.
How do I make it to lock only the row and not the table. I did try using
ROWLOCK hint as part of te UPDATE statement. It did not help. I had turned
off the Page Level locks for the index in the table.
VMake sure you have an index on the column you use in the WHERE clause. Other
wise, SQL Server has to
look at each row to see whether the row satisfies the WHERE condition (and t
he other connection has
one row with excusive lock).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"V" <V@.discussions.microsoft.com> wrote in message
news:1631B36D-FC9E-4B3B-A1ED-7586261C74B1@.microsoft.com...
> Hello,
> I have been trying to figure out how to user Row Level locking feature in
> SQL Server 2005. Here is the scenario.
> 1.Open a new query window in SQL Server Management sudio.
> 2.Execute a Begin transaction and an Update statement that updates a row
in
> a table. Do not commit the transaction yet.
> 3. Open another query window
> 4. Execute a Begin transaction and an Update statement that updates a
> different row in the same table. Do not commit the transaction yet.
> The secod session does not complete execution and waits because the table
is
> locked. I verified that by checking the locks in the database. I was
> expecting just the first row to be locked and not the entire table. Once I
> commit the first transaction, the second session completes execution of th
e
> update statement.
> How do I make it to lock only the row and not the table. I did try using
> ROWLOCK hint as part of te UPDATE statement. It did not help. I had turned
> off the Page Level locks for the index in the table.
> V
>|||Thanks, That helped. After creating an index on the column used in where
clause, it worked.
"Tibor Karaszi" wrote:

> Make sure you have an index on the column you use in the WHERE clause. Oth
erwise, SQL Server has to
> look at each row to see whether the row satisfies the WHERE condition (and
the other connection has
> one row with excusive lock).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "V" <V@.discussions.microsoft.com> wrote in message
> news:1631B36D-FC9E-4B3B-A1ED-7586261C74B1@.microsoft.com...
>

Row based security

Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:

> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>

Row based security

Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:

> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>