Rotate fields: view

Showing posts with label view. Show all posts

Wednesday, March 28, 2012

Row-level Security: Permissions required on base table?

I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

Yes, that is expected behavior.

Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.

If you have been GRANTed permission for a table, obviously you have permission.

If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.

But IF you have been explicited DENY(ied), that 'trumps' all.

Think of it this way.

Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).

However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.

So anytime permission is an explicit DENY, there is no route around it.

Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.

|||

Thanks for responding.

Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.

I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.

|||

I would suggest the following topics from BOL:

· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views

· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

In addition to Raul's suggestions, I offer the following insight.

Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)

SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.

|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||

As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.

I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to both you and Arnie for responding to this inquiry.

The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.

I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.

Again, thanks for your help.

Row-level Security: Permissions required on base table?

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

Yes, that is expected behavior.

Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.

If you have been GRANTed permission for a table, obviously you have permission.

If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.

But IF you have been explicited DENY(ied), that 'trumps' all.

Think of it this way.

However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.

So anytime permission is an explicit DENY, there is no route around it.

|||

Thanks for responding.

|||

I would suggest the following topics from BOL:

· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views

· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

In addition to Raul's suggestions, I offer the following insight.

Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)

As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.

I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to both you and Arnie for responding to this inquiry.

Again, thanks for your help.

sql

Friday, March 23, 2012

row to column

hello

TableT1 has

T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2

How can I write my view to report these in

MyID, MyDate1, MyNote1, MyCharge1

MyID, MyDate2,MyNote2, MyCharge2

format?

You could use the following SQL Statement to make this happen:

select myid,mydate1,mynote1,mychange1
from MyInfo
union
select myid,mydate2,mynote2,mychange2
from MyInfo
order by myid

It would return appear something like the following:

MyID MyDate MyNote MyChange

1 2006-08-07 00:00:00.000 Called Dealer For Customer Service Changed his mail cost for 15 to 20
1 2006-08-08 00:00:00.000 Faxed Information to Dealer Changed Status to Enrolled
2 2006-08-04 00:00:00.000 Enrolling of Dealer Enrolled Dealer in Oil Change Mail Piece
2 2006-08-09 00:00:00.000 Dealer Called Changed to Tune-Up Mail Piece

Let me know if this works for you.

crusso

|||

You should actually normalize your table so that it is easier to work with. You can do one of the following with your existing table structure:

select t.MyID

, case r.n when 1 then MyDate1 when 2 then MyDate2 end as MyDate

, case r.n when 1 then MyNote1 when 2 then MyNote2 end as MyNote

, case r.n when 1 then MyCharge1 when 2 then MyCharge2 end as MyCharge

from T1 as t

cross join (select 1 union all select 2) as r(n)

-- or

select t.MyID, t.MyDate1 as MyDate, t.MyNote1 as MyNote, t.MyCharge1 as MyCharge

from T1 as t

union all

select t.MyID, t.MyDate2 as MyDate, t.MyNote2 as MyNote, t.MyCharge2 as MyCharge

from T1 as t

row to column

hello
Table T1 has
T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2
How can I write my view to report these in
MyID, MyDate1, MyNote1, MyCharge1
MyID, MyDate2,MyNote2, MyCharge2
format?Try this:
How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574
(Or wait for Steve's Post...)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:FB0BC5F5-5EF1-4AA7-A3A1-BE4FA9A0010F@.microsoft.com...
> hello
> Table T1 has
> T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2
> How can I write my view to report these in
> MyID, MyDate1, MyNote1, MyCharge1
> MyID, MyDate2,MyNote2, MyCharge2
> format?
>|||Thanks you for your reply, it seems I need the exact opposite of the case
given there, how should I write my sql, any idea?
"Arnie Rowland" wrote:
> Try this:
> How to rotate a table in SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;175574
> (Or wait for Steve's Post...)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:FB0BC5F5-5EF1-4AA7-A3A1-BE4FA9A0010F@.microsoft.com...
> >
> > hello
> > Table T1 has
> > T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2
> >
> > How can I write my view to report these in
> > MyID, MyDate1, MyNote1, MyCharge1
> > MyID, MyDate2,MyNote2, MyCharge2
> > format?
> >
> >
>
>|||create view T_All
as
select MyID, MyDate1 as MyDate, MyNote1 as MyNote, MyCharge1 as
MyCharge
from T1 with(nolock)
where MyDate1 is not null
union all
select MyID, MyDate2, MyNote2, MyCharge2
from T1 with(nolock)
where MyDate2 is not null

Row Size Limitation of Report Model?

Hello,

I am trying to create a Report Model based on Data View that references a SQL View.

I get a an error when building the Report Model based on this view:

An error occurred while executing a command.
Message: Arithmetic overflow error converting expression to data type int.
Command: SELECT COUNT(*) FROM [dbo].[myViewName]
I don't know exactly how many rows are returned from this view, but I do know that it can contain quite a few rows. I tried to find out how many rows were in the view by running a SELECT COUNT() on the view, but I got the following error from SQL Server 2005 query window:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

This appears to be a SQL 2005 issue, but affects me building a Report Model. My question is, is this a SQL Server 2005 issue? Is there a way around it (other than chaning my view to decrease the amount of rows in it)?

BTW...I am running SQL 2005 Developer Edition on a Windows XP (SP2) laptop.

Thanks!!

Brian

Nevermind. It looks like there was an error in the view that I was using.

This does bring up a good question though...Are there limitations to how much data (number of rows) can be used with a Report Model?

Thanks!

Brian|||No, there is technically no limit, although certain expressions like Count(<entity>) could overflow if you have more than 2^31 (~2 billion) rows.|||what can i do if my count(*) exceeds the limit. I have this situation now already.sql

Row Size Limitation of Report Model?

Hello,

I am trying to create a Report Model based on Data View that references a SQL View.

I get a an error when building the Report Model based on this view:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

BTW...I am running SQL 2005 Developer Edition on a Windows XP (SP2) laptop.

Thanks!!

Brian

Nevermind. It looks like there was an error in the view that I was using.

This does bring up a good question though...Are there limitations to how much data (number of rows) can be used with a Report Model?

Thanks!

Row selection

Hi,

Just curious, can the crystal report show (during report view and in the printed report) different background colour for alternate rows?

For example:
1 row background colour =silver
2 row background colour = yellow
3 row background colour = silver
and so on

thanksHi,
In the details background color write the formula
if recordnumber mod 2=1 then silver
else yellow
Madhivanan|||Thank You... :)

Anyway, is there any sites that I can refer to for this kind of code for crystal report?

Row Order on View Results

When I run a view on SQL 2005 the resulting rows are not in order, even if the SQL statement defining the view includes an order by clause.

Within the Microsoft SQL Manager Studio (SQL 2005), when the view is opened, the rows are in no specific order.

Records viewed remotely via ADO likewise are not displayed in order. Neither are records viewed via ODBC.

Interestingly, when opened in modify mode within the Microsoft SQL Manager Studio (SQL 2005), the view does display the records according to the ORDER BY clause.

On the other hand, the same view on SQL 2000 produces result sets organized according to the ORDER BY clause. This is true whether the view is opened normally or in design mode.

And records viewed remotely via ADO are displayed in order, as are records viewed via ODBC.

I find this disappointing and a stumbling block in moving databases out of SQL 2000 and into SQL 2005.

The Database that I used was one pulled into a SQL 2005 64 bit server out of a back up made by a SQL 2000 server of a SQL 2000 database.

In general it's not recommended to include an ORDER BY clause in a view. A view should define a new relation of attributes derived from existing attributes in the datamodel. A query using the view should apply an order by on the data represented by the view to produce an ordered resultset.

Wednesday, March 21, 2012

Row Numbers for a View

I've been given a task that I believe is, basically, impossible, but
I'd like to see if there's a way to do it.

What my boss wants me to do is to create a view, in SQL Server 2000,
that will provide not only a row number field of some sort, but that
will produce sequential ordering for arbitrary selects and orderings.
So, if my data is a table with values from A thru D and my user does
SELECT data FROM vwTable, the result would be:

Row Data
-- --
1 A
2 B
3 C
4 D

But is they did SELECT data FROM vwTable ORDER BY data DSC, they would
get

Row Data
-- --
1 D
2 C
3 B
4 A

And if the did SELECT data FROM vwTable WHERE Data IN ('B', 'C'), they
would get

Row Data
-- --
1 B
2 C

In SQL 2005, of course, this would be fairly trivial since I could use
the ROW_NUMBER function. In 2000, though, it seems to be utterly
impossible. My boss, however, is convinced that there must be some way
to create a calculated field to do it.

I'll be cursed if I can figure out a way to do so.

Any suggestions would be appreciated.>> In 2000, though, it seems to be utterly impossible. My boss, however, is
>> convinced that there must be some way to create a calculated field to do
>> it.

Paste the following in Google search box:
"dynamically number rows site:support.microsoft.com"

--
Anith|||Where do you want to show the data?
Use Front End application to do this

Madhivanan|||Anith Sen wrote:
> >> In 2000, though, it seems to be utterly impossible. My boss, however, is
> >> convinced that there must be some way to create a calculated field to do
> >> it.
> Paste the following in Google search box:
> "dynamically number rows site:support.microsoft.com"

Thanks, however, while that is a good way to derive row numbers in a
select statement, unfortunately it isn't quite what my boss is asking
me to do. She wants a view that will produce row counts in a
calculated field regardless of the order that the user uses to select
the data.

I would prefer to require the user to generate the row numbers in their
selects, wjhich wouldd allow for the solution you offered.
Unfortunately, that isn't what I've been tasked to do.|||Madhivanan wrote:
> Where do you want to show the data?
> Use Front End application to do this

SQL Reporting Services.|||On 27 Mar 2006 16:32:09 -0800, Andrew Lias wrote:

>I've been given a task that I believe is, basically, impossible, but
>I'd like to see if there's a way to do it.
>What my boss wants me to do is to create a view, in SQL Server 2000,
>that will provide not only a row number field of some sort, but that
>will produce sequential ordering for arbitrary selects and orderings.
>So, if my data is a table with values from A thru D and my user does
>SELECT data FROM vwTable, the result would be:
>Row Data
>-- --
>1 A
>2 B
>3 C
>4 D
>But is they did SELECT data FROM vwTable ORDER BY data DSC, they would
>get
>Row Data
>-- --
>1 D
>2 C
>3 B
>4 A
>And if the did SELECT data FROM vwTable WHERE Data IN ('B', 'C'), they
>would get
>Row Data
>-- --
>1 B
>2 C
>In SQL 2005, of course, this would be fairly trivial since I could use
>the ROW_NUMBER function. In 2000, though, it seems to be utterly
>impossible. My boss, however, is convinced that there must be some way
>to create a calculated field to do it.
>I'll be cursed if I can figure out a way to do so.
>Any suggestions would be appreciated.

Hi Andrew,

The way you describe it here, it's impossible. That holds true for both
SQL Server 2005 and SQL Server 2000. Even ROW_NUMBER() won't help you.

If you need the row numbers to match the order specifiede on the select
and if you want to skip numbers for rows not included in the select,
you'll have to add row numbering logic on the SELECT statement. If you
add row numbers in the view, the numbers won't change if you exclude
some rows or choose a different order when selecting from the view.

Just to prevent misunderstanding - it is NOT impossible to get the
result sets you require. But it's only possible by extending the SELECT
with some row numbering logic. Either using ROW_NUMBER() if you're using
SQL Server 2005, or by using either a correlated subquery or a self-join
and a GROUP BY if you're using SQL Server 2000.

--
Hugo Kornelis, SQL Server MVP|||Andrew Lias (anrwlias@.gmail.com) writes:
> Thanks, however, while that is a good way to derive row numbers in a
> select statement, unfortunately it isn't quite what my boss is asking
> me to do. She wants a view that will produce row counts in a
> calculated field regardless of the order that the user uses to select
> the data.

Time to get a new boss?

What she is asking for is not possible. You would have to package the
user's SELECT statement somehow, so you can modify to add the row-number
column. As Hugo pointed out, this is the same on SQL 2005.

--
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|||>>SQL Reporting Services

Cant you make use of Recordnumber feature such as the one available in
Crystal reports?

Madhivanan|||Erland Sommarskog wrote:
> Andrew Lias (anrwlias@.gmail.com) writes:
> > Thanks, however, while that is a good way to derive row numbers in a
> > select statement, unfortunately it isn't quite what my boss is asking
> > me to do. She wants a view that will produce row counts in a
> > calculated field regardless of the order that the user uses to select
> > the data.
> Time to get a new boss?
> What she is asking for is not possible. You would have to package the
> user's SELECT statement somehow, so you can modify to add the row-number
> column. As Hugo pointed out, this is the same on SQL 2005.

That's what I thought. I just wanted to be extra sure that there
wasn't some tricky way to do this before I went back to her and said
that it simply could not be done the way that she was asking.|||if you can use a stored procedure instead of a view, you could select
the data INTO a temp table in the "correct order", alter the table to
add an identity column, and return that ordered by identity.
before someone gets excited, there isn't a GUARANTEE this will work
forever in future versions of SQL, but it probably will.|||Doug (drmiller100@.hotmail.com) writes:
> if you can use a stored procedure instead of a view, you could select
> the data INTO a temp table in the "correct order", alter the table to
> add an identity column, and return that ordered by identity.
> before someone gets excited, there isn't a GUARANTEE this will work
> forever in future versions of SQL, but it probably will.

There is no guarantee that it will work any version of SQL Server. In fact
for a result set of any size, I would not expect it to work.

What is guaranteed to work, at least in SQL 2005, is if you have a
table with an IDENTITY table, and perform an INSERT with an ORDER BY.

Note that this does not apply to SELECT INTO with the IDENTITY function
and ORDER BY. In that case, there is *no* guarantee.

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

But, at least we "solved" the problem!!!!|||Hey Andrew

Nothing is impossible, maybe I have read too fast but here is how I would do it.
Sounds like your boss just wants row numbering on your result set.
In reporting services use this expression

=RowNumber("DataSetName")

That would be like using Crystal's RecordNumber

Hope this helps

Row numbering unpredictable

Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/Assuming CustomerName is unique:
select
(select count (*)
from #tmp t1
where t1.CustomerName <= t2.CustomerName) as rownum
, *
from
#tmp t2
order by
t2.CustomerName
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/|||you could create the table first with an ID column, then insert into
it. I suspect (though have no evidence) that the select into #tmp with
an id column created then is having issues with the order by|||(cseemeuk@.googlemail.com) writes:
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
people that it's guaranteed to work. Whether that really is true, I'm not
completely convinced of, but fairly. In any case, SELECT INTO is *not*
guaranteed to work that way, so stay away from it.
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|||Thanks - works perfectly. The INTO was the problem - appears to be no
guaranteed order to the IDENTITY(bigint, 1,1)
All sorted
Cheers,
Chris Smith
http://www.cswd.co.uk/|||The order is not guaranteed when you use SELECT INTO.
See
http://support.microsoft.com/defaul...kb;en-us;273586
For a list of paging options see
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
> Hi,
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
> (in ADO :-P)
> This is SQL 2000 SP3 (pah!)
> Cheers,
> Chris Smith
> http://www.cswd.co.uk/
>|||One would think this type of thing,so common and important,
would have a kb or something written by MS.Are you aware of any
link?If none exists I would ask you to kindly request something in
'writing'.Key points of an enterprise database should not be rattling
around just in someone head! :)
Clarity,clarity and nothing but clarity.
Regards from:
www.rac4sql.net
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A28BF07D031Yazorman@.127.0.0.1...
> (cseemeuk@.googlemail.com) writes:
> Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
> BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
> people that it's guaranteed to work. Whether that really is true, I'm not
> completely convinced of, but fairly. In any case, SELECT INTO is *not*
> guaranteed to work that way, so stay away from it.
>
> --
> 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|||Steve Dassin wrote:
> One would think this type of thing,so common and important,
> would have a kb or something written by MS.Are you aware of any
> link?If none exists I would ask you to kindly request something in
> 'writing'.Key points of an enterprise database should not be rattling
> around just in someone head! :)
> Clarity,clarity and nothing but clarity.
http://support.microsoft.com/defaul...kb;en-us;273586
Do not assume that article means that all INSERTs will always cause
IDENTITY to be generated in a predetermined order. There are at least
some situations where that doesn't work - whether by design or a bug I
can't say.
Perhaps the safest course is to assume that you cannot control the
IDENTITY sequence with ORDER BY. In my view the wisest and most logical
solution is to use other methods like the ROW_NUMBER function for
example.
I can think of at least two good reasons for not using IDENTITY the way
proposed by the KB. Firstly IDENTITY is normally intended as an
arbitrary surrogate key - using the values in any "meaningful" way is a
compromise you don't need and is something it just isn't designed for.
Secondly, this supposed behaviour of an "ordered" INSERT looks contrary
to the set-based nature of an INSERT statement. Whether or not it works
today, it seems undesirable to assume that it should always work that
way in future. One would hope and expect that the engine could optimise
out any redundant sorting in INSERT...SELECT queries. That seems to be
what happens in some cases today and maybe it will happen more often in
future versions due to improvements in the optimiser. Just some things
to bear in mind.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Anyone got any ideas on how to solve that other than client side paging <
<
The basic principle of a tiered architecture is that display is done in
the front end adn NEVER in the database. Why are you s

ing violating
40 years of Software Engineering?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144803667.948395.290650@.i40g2000cwc.googlegroups.com...
<<
> The basic principle of a tiered architecture is that display is done in
> the front end adn NEVER in the database. Why are you s

ing violating
> 40 years of Software Engineering?
>
Forty years of a life sentence is enough.Time to let the innocent free.
Convicted on trumped up,unsubstantiated and false charges.In other words,
NONSENSE.
The thread:
Monday, April 10, 2006 9:48 PM
microsoft.public.sqlserver.programming
Re: Membership Timeline Spanning
contains a response that further clarifies things:
"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net

Row Not Found Err w/ zero details

SQL 2005 Transactional Repl. Receiving row not found error. In SQL2000 you
could view the error details and see the exact sp call sp_MSUpdxxx command
that was trying to execute w/ param values.
Now in 2005 - this is the most detail I get below: How do I see what row
was missing? tia Chris
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x0019780A0001C586015500000000, Command ID: 1)
Error messages:
The row was not found at the Subscriber when applying the replicated
command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
The row was not found at the Subscriber when applying the replicated
command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
Chris - you should be able to use sp_briowsereplcmds which takes an optional
parameter of @.xact_seqno_start (and @.xact_seqno_end)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||sp_browseprelcmds will give all commands to be replicated. I have many pubs
on that server along w/ other servers. Was this detail removed from repl
monitor?
"Paul Ibison" wrote:

> Chris - you should be able to use sp_briowsereplcmds which takes an optional
> parameter of @.xact_seqno_start (and @.xact_seqno_end)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Chris,
I was recommending using the optional parameters @.xact_seqno_start and
@.xact_seqno_end to filter the results.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Tuesday, March 20, 2012

Row level security - View for all?

Hi there,
I am implementing row level security on a large database (at least I think i
t is large). It is enforced by adding which company submitted the row and w
hich company they are subitting to. The security is enforced by using views
to only return the rows th
e current user is allowed to see according to there user name. What they ca
n do with what they see is determined by which role they are assigned to.
What I am wondering is if I need a view for every table in the database? I
think to be completely secure that I do. But then I think that it is redun
dent as you can't really find anything in some tables without starting from
another. i.e. to find cert
ain attributes of an object you need to fuind the object first.
Any thoughts here would be appreciated,
DenisI am implementating row level security as well and made the decision to have
a view for every table for 2 reasons - simplicity and security. No one will
have direct access to any table - only through a view or stored proc. If y
ou establish a view for eve
ry table, there will be no confusion as to wether to reference a table or vi
ew - always refer to the view.
How are you doing the filtering of data on a per user basis in your view?
"Denis Crotty" wrote:

> Hi there,
> I am implementing row level security on a large database (at least I think it is l
arge). It is enforced by adding which company submitted the row and which company t
hey are subitting to. The security is enforced by using views to only return the ro
ws
the current user is allowed to see according to there user name. What they can do with what
they see is determined by which role they are assigned to.
> What I am wondering is if I need a view for every table in the database? I think
to be completely secure that I do. But then I think that it is redundent as you ca
n't really find anything in some tables without starting from another. i.e. to find
ce
rtain attributes of an object you need to fuind the object first.
> Any thoughts here would be appreciated,
> Denis|||That was my feeling as well for using a view for every table. I just was ba
lking as there are 40+ tables.
I filter by checking SUSER_SNAME() and then using the result in a look up ta
ble for what company they are with.
Denis
"Scott Shearer" wrote:

> I am implementating row level security as well and made the decision to have a vie
w for every table for 2 reasons - simplicity and security. No one will have direct a
ccess to any table - only through a view or stored proc. If you establish a view fo
r e
very table, there will be no confusion as to wether to reference a table or view - always re
fer to the view.[vbcol=seagreen]
> How are you doing the filtering of data on a per user basis in your view?
> "Denis Crotty" wrote:
>
s the current user is allowed to see according to there user name. What they can do with wh
at they see is determined by which role they are assigned to.[vbcol=seagreen]
certain attributes of an object you need to fuind the object first.[vbcol=seagreen]

Row Length Error with View

I'm having this problem with SQL Server 2000...

Sample query in view definition:

SELECT somecol1 FROM sometable1
UNION
SELECT somecol2 FROM sometable2

I'm returning more columns in the SELECT than I've put in the sample and all the datatypes match for each column. But...

I get the row error when the view is run. If I run the first part in it's own results are returned. If I run the second on it's own I get results. I only get the error when they are UNIONed. There's no ordering or grouping.

Why would each of them run individually but not UNIONed together?

Also, if I remove ('' instead of table.column) one of the larger nvarchar columns from the first or second query, the UNIONed statement returns results just fine.

Any help would be appreciated.

Thanks,

MikeIt is quite easy to create a result set row that is larger than the maximum table row size. One simple way to do this is using character concatenation operators to produce some of your columns.

If you do that, you create a result set that can't participate in a UNION because the server can't store that result set into the intermediate table needed to create the final UNION result set.

-PatP|||post the actual code...

I think it's as simple as the number of columns in each is different|||It can't be a problem with the number of columns in the definition since the view gets created fine and in some cases, results are returned (for example, if I replace AppDB.dbo.Milestones.Description MilestoneDescription with '' MilestoneDescription). But, I do know that any one row in the AppDB.dbo.Milestones table will not exceed the row limit of 8096. And, not doing any concatenation in the select or other string manipulation.

So, once again, I can run the first part on it's own without a problem, the second on it's own without a problem but it throws the error when the view is run or when I run the union as a straight statement.

Also, I thought the row limit was only when trying to insert or update(?)...

Thanks in advance.

Mike

Here's the actual code...

CREATE view VW_PO_BUDGET as
SELECT dbo.Programs.ProgramID, dbo.Programs.ProgramName, dbo.Steps.StepID, dbo.Steps.StepName, AppDB.dbo.Agreements.CoreProjectID,
dbo.Projects.ProjectNumber, dbo.Projects.ProjectName, AppDB.dbo.ProjectExpenseBudget.ProjectVersion,
AppDB.dbo.ProjectExpenseBudget.Amount, AppDB.dbo.ProjectExpenses.ExpenseCategoryID CategoryID,
LU_CATEGORIES.Lookupvalue Category,
AppDB.dbo.ProjectExpenses.SubExpenseID SubCategoryID, LU_SUBCATEGORIES.Lookupvalue SubCategory,
AppDB.dbo.ProjectExpenses.ExpenseTypeID TypeID, LU_TYPES.Lookupvalue Type,
AppDB.dbo.ProjectExpenses.ContributorID, AppDB.dbo.Organizations.OrganizationLegalName Contributor,
AppDB.dbo.ProjectExpenses.Description ItemDescription, AppDB.dbo.Milestones.MilestoneID,
AppDB.dbo.Milestones.MilestoneNumber, AppDB.dbo.Milestones.Title, AppDB.dbo.Milestones.Description MilestoneDescription,
AppDB.dbo.Milestones.StartDate, AppDB.dbo.Milestones.EndDate, AppDB.dbo.Milestones.MandatoryMilestone,
AppDB.dbo.ProjectExpenseBudget.DateCreated, AppDB.dbo.ProjectExpenseBudget.DateUpdated, AppDB.dbo.ProjectExpenseBudget.UpdatedBy, AppDB.dbo.ProjectExpenseBudget.CreatedBy
, 'EXPENSE' EntryType, LU_CATEGORIES.LocaleID CategoryLocale, LU_SUBCATEGORIES.LocaleID SubCategoryLocale, LU_TYPES.LocaleID TypeLocale
FROM AppDB.dbo.ProjectExpenseBudget INNER JOIN
AppDB.dbo.ProjectExpenses ON AppDB.dbo.ProjectExpenseBudget.ExpenseID = AppDB.dbo.ProjectExpenses.ExpenseID AND
AppDB.dbo.ProjectExpenseBudget.ProjectID = AppDB.dbo.ProjectExpenses.ProjectID AND
AppDB.dbo.ProjectExpenseBudget.ProjectVersion = AppDB.dbo.ProjectExpenses.ProjectVersion AND
AppDB.dbo.ProjectExpenseBudget.StepID = AppDB.dbo.ProjectExpenses.StepID INNER JOIN
AppDB.dbo.Milestones ON AppDB.dbo.ProjectExpenseBudget.MilestoneID = AppDB.dbo.Milestones.MilestoneID AND
AppDB.dbo.ProjectExpenses.ProjectID = AppDB.dbo.Milestones.ProjectID AND
AppDB.dbo.ProjectExpenses.ProjectVersion = AppDB.dbo.Milestones.ProjectVersion AND
AppDB.dbo.ProjectExpenses.StepID = AppDB.dbo.Milestones.StepID INNER JOIN
AppDB.dbo.Agreements ON AppDB.dbo.ProjectExpenseBudget.ProjectID = AppDB.dbo.Agreements.ProjectID AND
AppDB.dbo.ProjectExpenseBudget.ProjectVersion = AppDB.dbo.Agreements.ProjectVersion AND
AppDB.dbo.ProjectExpenseBudget.StepID = AppDB.dbo.Agreements.StepID INNER JOIN
dbo.Programs ON AppDB.dbo.Agreements.ProgramId = dbo.Programs.ProgramID INNER JOIN
dbo.Steps ON dbo.Programs.ProgramID = dbo.Steps.ProgramID AND dbo.Steps.StepType = 'Award' AND
dbo.Programs.ProgramID = dbo.Steps.ProgramID AND dbo.Programs.ProgramID = dbo.Steps.ProgramID AND
AppDB.dbo.ProjectExpenses.StepID = dbo.Steps.StepID INNER JOIN
dbo.Projects ON AppDB.dbo.Agreements.CoreProjectId = dbo.Projects.ProjectID AND dbo.Programs.ProgramID = dbo.Projects.ProgramID
LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_CATEGORIES ON LU_CATEGORIES.lookupvalueid = AppDB.dbo.ProjectExpenses.ExpenseCategoryID
and LU_CATEGORIES.lookupname = 'ExpenseAccounts'
LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_SUBCATEGORIES ON LU_SUBCATEGORIES.lookupvalueid = AppDB.dbo.ProjectExpenses.SubExpenseID
and LU_SUBCATEGORIES.lookupname = 'SubExpenses'
LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_TYPES ON LU_TYPES.lookupvalueid = AppDB.dbo.ProjectExpenses.ExpenseTypeID
and LU_TYPES.lookupname = 'ExpenseTypes'
LEFT OUTER JOIN AppDB.dbo.Organizations ON AppDB.dbo.ProjectExpenses.ContributorID = AppDB.dbo.Organizations.StakeholderID
union
SELECT dbo.Programs.ProgramID, dbo.Programs.ProgramName, dbo.Steps.StepID, dbo.Steps.StepName, AppDB.dbo.Agreements.CoreProjectID,
dbo.Projects.ProjectNumber, dbo.Projects.ProjectName, AppDB.dbo.ProjectFundingBudget.ProjectVersion,
AppDB.dbo.ProjectFundingBudget.Amount, AppDB.dbo.FundingSources.FundingSourceCategoryID CategoryID,
LU_CATEGORIES.Lookupvalue Category,
NULL SubCategoryID, NULL SubCategory,
AppDB.dbo.FundingSources.FundingTypeID TypeID, LU_TYPES.Lookupvalue Type,
AppDB.dbo.FundingSources.ContributorID, AppDB.dbo.Organizations.OrganizationLegalName Contributor,
AppDB.dbo.FundingSources.Description ItemDescription, AppDB.dbo.Milestones.MilestoneID,
AppDB.dbo.Milestones.MilestoneNumber, AppDB.dbo.Milestones.Title, AppDB.dbo.Milestones.Description MilestoneDescription,
AppDB.dbo.Milestones.StartDate, AppDB.dbo.Milestones.EndDate, AppDB.dbo.Milestones.MandatoryMilestone,
AppDB.dbo.ProjectFundingBudget.DateCreated, AppDB.dbo.ProjectFundingBudget.DateUpdated, AppDB.dbo.ProjectFundingBudget.UpdatedBy, AppDB.dbo.ProjectFundingBudget.CreatedBy
, 'FUNDING' as EntryType, LU_CATEGORIES.LocaleID CategoryLocale, NULL SubCategoryLocale, LU_TYPES.LocaleID TypeLocale
FROM AppDB.dbo.ProjectFundingBudget INNER JOIN
AppDB.dbo.FundingSources ON AppDB.dbo.ProjectFundingBudget.FundingSourceID = AppDB.dbo.FundingSources.FundingSourceID AND
AppDB.dbo.ProjectFundingBudget.ProjectID = AppDB.dbo.FundingSources.ProjectID AND
AppDB.dbo.ProjectFundingBudget.ProjectVersion = AppDB.dbo.FundingSources.ProjectVersion AND
AppDB.dbo.ProjectFundingBudget.StepID = AppDB.dbo.FundingSources.StepID INNER JOIN
AppDB.dbo.Milestones ON AppDB.dbo.ProjectFundingBudget.MilestoneID = AppDB.dbo.Milestones.MilestoneID AND
AppDB.dbo.FundingSources.ProjectID = AppDB.dbo.Milestones.ProjectID AND
AppDB.dbo.FundingSources.ProjectVersion = AppDB.dbo.Milestones.ProjectVersion AND
AppDB.dbo.FundingSources.StepID = AppDB.dbo.Milestones.StepID INNER JOIN
AppDB.dbo.Agreements ON AppDB.dbo.ProjectFundingBudget.ProjectID = AppDB.dbo.Agreements.ProjectID AND
AppDB.dbo.ProjectFundingBudget.ProjectVersion = AppDB.dbo.Agreements.ProjectVersion AND
AppDB.dbo.ProjectFundingBudget.StepID = AppDB.dbo.Agreements.StepID INNER JOIN
dbo.Programs ON AppDB.dbo.Agreements.ProgramId = dbo.Programs.ProgramID INNER JOIN
dbo.Steps ON dbo.Programs.ProgramID = dbo.Steps.ProgramID AND dbo.Steps.StepType = 'Award' AND
dbo.Programs.ProgramID = dbo.Steps.ProgramID AND dbo.Programs.ProgramID = dbo.Steps.ProgramID AND
AppDB.dbo.FundingSources.StepID = dbo.Steps.StepID INNER JOIN
dbo.Projects ON AppDB.dbo.Agreements.CoreProjectId = dbo.Projects.ProjectID AND dbo.Programs.ProgramID = dbo.Projects.ProgramID
LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_CATEGORIES ON LU_CATEGORIES.lookupvalueid = AppDB.dbo.FundingSources.FundingSourceCategoryID
and LU_CATEGORIES.lookupname = 'Funds'
LEFT OUTER JOIN dbo.vw_cpda_po_lookups LU_TYPES ON LU_TYPES.lookupvalueid = AppDB.dbo.FundingSources.FundingTypeID
and LU_TYPES.lookupname = 'FundTypes'
LEFT OUTER JOIN AppDB.dbo.Organizations ON AppDB.dbo.FundingSources.ContributorID = AppDB.dbo.Organizations.StakeholderID|||Just to rule out one kind of problem, can you switch to a UNION ALL to see what that does?

-PatP|||UNION ALL seemed to do if I run the query without an ORDER BY. Any way to get around that? Still not understanding why this error is coming from a SELECT. Didn't think there was that limitation when querying. Does that mean that any SELECT that I put together must have a row length of less than 8096? That's pretty limiting if that is the case.

Thanks,

Mike|||This gets a little complicated to explain in terms of what the code is actually doing, but the short answer comes from the Relational Algebra that ought to be the cornerstone of any relational database... A view ought to express what should be shown (which rows should appear in the result set), but not how it should be shown (sequencing, formatting, etc.). Until a view is materialized into a result set, an order is logically irrelevant.

The fine folks at Sybase allowed views to specify an order, and Microsoft has carried on that functionality at the syntactic level even though some of the "inner workings" of the engine don't support it very well. From a logical perspective, they shouldn't allow you to specify an order for a view, but since they do permit it, they really ought to do it 100% (or not at all).

-PatP

Friday, March 9, 2012

row column repetition help

I've made a view from a (complex) select statement of 4 tables in my database.

lest say I get results like these...
colname1 colname2 colname3 colname4
----------------
patient1 med1 usage1 diagnum
patient1 med2 usage2 diagnum
patient1 med3 usage3 diagnum

I would like to cortrect my query so that the result is the following...
colname1 colname2 colname3 colname4
----------------
patient1 med1 usage1 diagnum
med2 usage2
med3 usage3

since the patient number is the same and the diag num is the same
i want ot be able to avoid the repetition...

this might not help but heres is my original query

CREATE OR REPLACE VIEW PRESCRIPTIONS AS
SELECT DISTINCT d.NoAssMaladie, p.prenompatient || ' ' || UPPER(p.nompatient) AS NomPatient, l.nomedicament AS NoMedic,m.libmedicament AS Libelle, l.quantite || ',' || l.prises || ',' || l.duree AS "Desc. d.usage", l.nodiagnostic AS Diag
FROM lignes_prescriptions l, patients p, diagnostics d, medicaments m
WHERE l.nodiagnostic = d.nodiagnostic AND
d.noassmaladie = p.noassmaladie AND
m.nomedicament = l.nomedicament AND
d.RESULTATDIAGNOSTIC = 'P' AND
d.NoAssMaladie = 'SANL 6005 1218'

where my patient is 'SANL 6005 1218'

And secondly I would like to know how to make a view that will ask for a value that I can apply to my where statement.

lets say I wanted to ask for the patient number 'SANL 6005 1218' instead of putting it into my query directly.Technically speaking the view you created contains no duplicates, where a duplicate is defined as the row projected from the select statement. If you select the primary key then the rows are already distinct. I don't think you can return 4 columns with different row depths as the dbms would not know what to place in the 'Empty' cells.|||You should check your schema and ensure the FD's are correct in your view for example the view you created leeds one to believe that possibly column1, column2, column3 are the key with column4 being dependent on this key.|||Hi,
First you cannot give parameters to a VIEW as they are just structures stored with no data.

When you query the view you should include the parameter in the where clause.

As for your requirement, you should use SQL REPORTING utility to get the report in that format.

use the following commands

BREAK ON PATIENT_ID ON DIAGRAMID

SELECT PATIENT_ID, DIAGRAMID ,....
FROM <<VIEW NAME>> ORDER BY PATIENT_ID, DIAGRAMID

If needed spool the result into a text file.
then use CLEAR BREAKS command to clear the break settings.

Regars
Shelva

Row and Cell Segurity

I am trying to implement row-security in SQL 2005 but i make a query to make a view

CREATE VIEW vwVisibleLabels
AS

SELECT ID, Label.ToString()
FROM tblUniqueLabel WITH (NOLOCK)
WHERE
ID IN --Classification
(SELECT ID FROM tblUniqueLabelMarking WITH (NOLOCK)
WHERE CategoryID = 1 AND IS_MEMBER(MarkingRoleName) = 1)
AND --Compartments
1 = ALL(SELECT IS_MEMBER(MarkingRoleName) FROM tblUniqueLabelMarking
WHERE CategoryID = 2 AND UniqueLabelID = tblUniqueLabel.ID)
GO

And the error is

Msg 208, Level 16, State 1, Procedure vwVisibleLabels, Line 4
Invalid object name 'tblUniqueLabel'.

The tblUniquelabel does exist, what is going on?
Please someone help me!!!!

PS. I am following the white papper on Implementing Row and Cell Level Security from the microsoft site.

Could you please post a script that reproes this problem? I don't see anything wrong with the view definition unless you have a typo somewhere or wrong case (matters in case-sensitive collation).|||

I think the problem is in the ToString(). I have the colum label with the data type nchar(20) do you think that could be the problem?

|||It is hard to tell anything conclusive without seeing a repro of the problem. So please post back with sample schema/script.|||

But what do you mean by script scheam? Is it the code that generates the database? Here is it?

Thanks

Ps. I made a view only ti list the tbUniquelabel and it was fine so the problem isn't the table not existing or being badly written.

|||Not the entire database script but just the SQL statements that can repro the problem. It needn't be the actual schema also.|||

When you run the code

SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

No error but no right result because, i think that IS_MEMBER(MarkingRoleName) = 1 always is 0. so every time it gives me a empty Table with the label and id colum

So i need to convert Label to string so it can be compared in the IS_MEMBER(MarkingRoleName) = 1

But if i run

SELECT ID, Label.ToString() AS Expr1
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

It gives me the folowing messege:

Cannot find the colum label (which is impossible because the first code runs), or the user-defined function or aggregate "Label.ToString", or the name is ambiguous.

When i run

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel' the is result is a table with one row and one column with the heading expr 1 and the cell with dbo written on on

Thanks

|||Is the label column a CLR UDT? If so, does it have a ToString() method? If it is a SQL data type then use CAST to convert the value to string.|||

Looking back to the code it does not seem like you need to convert anything. It was just what i thought the toString() procedure did.

I put nchar(20 ) on every column is that the mistake?

How do i chandge it to the CLR type and what is it?

Thanks again.

|||

Why don't you use CAST or CONVERT?

Stjepan

|||dude, you probably have tblUniqueLabel sitting in a different schema, or your default schema isn't dbo.

reference tblUniqueLabel using the form SchemaName.tblUniqueLabel

Also, you don't have to do .ToString() - there's no such function in sql server. just reference label as is.

Row and Cell Security

I am trying to implement row-security in SQL 2005 but i make a query to make a view

CREATE VIEW vwVisibleLabels
AS

And the error is

Msg 208, Level 16, State 1, Procedure vwVisibleLabels, Line 4
Invalid object name 'tblUniqueLabel'.

The tblUniquelabel does exist, what is going on?
Please someone help me!!!!

PS. I am following the white papper on Implementing Row and Cell Level Security from the microsoft site.

You may need to prefix the table name by its schema name.

Thanks
Laurentiu|||I think is the ToSrting that is giving the error do you know what this meens?

Thanks|||I think the problem is in the ToString(). I have the colum label with the data type nchar(20) do you think that could be the problem?|||Have you looked into prefixing the table name with the schema name? The message that you obtained indicates that the table name lookup failed. The SELECT didn't even get to process the "ID, Label.ToString()" part, so even if there would be errors in it, they're not the ones generating the message that you received.

Thanks
Laurentiu|||

But what do you mean by scheam? Is it the code that generates the database? were is it?

Thanks

Ps. I made a view only ti list the tbUniquelabel and it was fine so the problem isn't the table not existing or being badly written.

|||

A schema is a new concept that helps managing the contents of a database. See the "User-Schema Separation" topic in Books Online.

To find the schema name for this table, you can try the following query:

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel'

This will list all schemas in which you can find objects named tblUniqueLabel.

But now that you mentioned that you could create another view on the table, I took a closer look at the create statement and it may be incorrect. Try executing the following:

SELECT ID, Label.ToString()
FROM tblUniqueLabel tUL WITH (NOLOCK)
WHERE
ID IN --Classification
(SELECT ID FROM tblUniqueLabelMarking WITH (NOLOCK)
WHERE CategoryID = 1 AND IS_MEMBER(MarkingRoleName) = 1)
AND --Compartments
1 = ALL(SELECT IS_MEMBER(MarkingRoleName) FROM tblUniqueLabelMarking
WHERE CategoryID = 2 AND UniqueLabelID = tUL.ID)
GO

I didn't notice that you had a second reference to tblUniqueLabel in the inner query. That may be the one generating the error. Let us know if this works. If this doesn't work, please also post the create table statements that you used to create the tblUniqueLabel and tblUniqueLabelMarking tables.

Thanks

Laurentiu

|||

When you run the code

No error but no right result because, i think that IS_MEMBER(MarkingRoleName) = 1 always is 0. so every time it gives me a empty Table with the label and id colum

So i need to convert Label to string so it can be compared in the IS_MEMBER(MarkingRoleName) = 1

But if i run

It gives me the folowing messege:

Cannot find the colum label (which is impossible because the first code runs), or the user-defined function or aggregate "Label.ToString", or the name is ambiguous.

When i run

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel' the is result is a table with one row and one column with the heading expr 1 and the cell with dbo written on on

Thanks

|||

You've changed the query, so you're getting different errors now. Note that the original error, as I have mentioned in my previous message, was not related to the schema, but to the way the inner clause of the query was written. You don't need to be explicit about the dbo schema, this is one of the schemas searched by default.

What is the type of the Label column? I've looked over the article but it doesn't specify this. In T-SQL, to convert from a type to another, you would need to use the CONVERT function. The ToString method indicates Label is a CLR user defined type. If you've just defined it as a SQL builtin type, then this won't work.

Thanks
Laurentiu

|||

Looking back to the code it does not seem like you need to convert anything. It was just what i thought the toString() procedure did.

I put nchar(20 ) on every column is that the mistake?

How do i chandge it to the CLR type and what is it?

Thanks again.

|||

I don't know how the CLR type is defined and the paper does not seem to describe it. I spoke with one of the authors and they are working to release some additional material that will allow implementing the solution described in the white paper. I don't yet have a date for when this will happen, but I am trying to find one. It will most likely happen next year though. I'll post to this thread when I will have more information.

Thanks
Laurentiu

|||

Thank you anyway.

But i do have another problem with the database menber describe in the white paper, it says in the paper that we have to add as menber of the role the child role. But when you go to add a menber a the role you can not add a role. How is it possible to do so?

|||

Please open a new thread on this issue and provide more details on what commands you are trying and what error messages you receive.

Thanks
Laurentiu

|||

Please feel free to explore the free eval of Data Nomad ( http://www.technicalmedia.com ). This product will automatically generate views for row level security in SQL Server. It works with any version including SQL Server 2005 Express.

You can look at the schemas, views synonyms and stored procedures that are created to learn more about how to build your own (or of course you can use the product too $100 developer - no runtime costs :))

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

“Using Technical Media’s Nomad product has saved us months of development” said Darcy Vaughan, a founder and Director of PetroWEB, Inc.

PetroWEB, Inc has obtained an exclusive Data Nomad? license for the upstream oil and gas industry. For information on utilizing Data Nomad? technology in this industry, please contact Darcy Vaughan at 303.308.9100.

Row and Cell Security

I am trying to implement row-security in SQL 2005 but i make a query to make a view

CREATE VIEW vwVisibleLabels
AS

And the error is

Msg 208, Level 16, State 1, Procedure vwVisibleLabels, Line 4
Invalid object name 'tblUniqueLabel'.

The tblUniquelabel does exist, what is going on?
Please someone help me!!!!

PS. I am following the white papper on Implementing Row and Cell Level Security from the microsoft site.

You may need to prefix the table name by its schema name.

Thanks
Laurentiu|||I think is the ToSrting that is giving the error do you know what this meens?

Thanks
Laurentiu|||

But what do you mean by scheam? Is it the code that generates the database? were is it?

Thanks

Ps. I made a view only ti list the tbUniquelabel and it was fine so the problem isn't the table not existing or being badly written.

|||

A schema is a new concept that helps managing the contents of a database. See the "User-Schema Separation" topic in Books Online.

To find the schema name for this table, you can try the following query:

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel'

This will list all schemas in which you can find objects named tblUniqueLabel.

But now that you mentioned that you could create another view on the table, I took a closer look at the create statement and it may be incorrect. Try executing the following:

Thanks

Laurentiu

|||

When you run the code

No error but no right result because, i think that IS_MEMBER(MarkingRoleName) = 1 always is 0. so every time it gives me a empty Table with the label and id colum

So i need to convert Label to string so it can be compared in the IS_MEMBER(MarkingRoleName) = 1

But if i run

It gives me the folowing messege:

Cannot find the colum label (which is impossible because the first code runs), or the user-defined function or aggregate "Label.ToString", or the name is ambiguous.

When i run

select schema_name(schema_id) from sys.objects where name = 'tblUniqueLabel' the is result is a table with one row and one column with the heading expr 1 and the cell with dbo written on on

Thanks

|||

Thanks
Laurentiu

|||

Looking back to the code it does not seem like you need to convert anything. It was just what i thought the toString() procedure did.

I put nchar(20 ) on every column is that the mistake?

How do i chandge it to the CLR type and what is it?

Thanks again.

|||

Thanks
Laurentiu

|||

Thank you anyway.

|||

Please open a new thread on this issue and provide more details on what commands you are trying and what error messages you receive.

Thanks
Laurentiu

|||

Row-Level Security for Microsoft SQL Server 2005

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.

“Using Technical Media’s Nomad product has saved us months of development” said Darcy Vaughan, a founder and Director of PetroWEB, Inc.

Tuesday, February 21, 2012

rotates between Active and Passive node

Hi, all.
Do anyone rotate the active and passive nodes in your cluster? From hardware
wear and tear point of view, it sounds reasonable.
Please share your thoughts.
Thanks!
Hi
I would not be worried about wear and tear...the hard drives are the ones
getting it, and they are shared.
From a confidence point of view, yes. You know the failover works and that
there are no problems with the node.
Regards
Mike
"Raymond Fang" wrote:

> Hi, all.
> Do anyone rotate the active and passive nodes in your cluster? From hardware
> wear and tear point of view, it sounds reasonable.
> Please share your thoughts.
> Thanks!
>
>

Wednesday, March 28, 2012

Row-level Security: Permissions required on base table?

Row-level Security: Permissions required on base table?

Friday, March 23, 2012

row to column

row to column

Row Size Limitation of Report Model?

Row Size Limitation of Report Model?

Row selection

Row Order on View Results

Wednesday, March 21, 2012

Row Numbers for a View

Row numbering unpredictable

Row Not Found Err w/ zero details

Tuesday, March 20, 2012

Row level security - View for all?

Row Length Error with View

Friday, March 9, 2012

row column repetition help

Row and Cell Segurity

Row and Cell Security

Row and Cell Security

Tuesday, February 21, 2012

rotates between Active and Passive node

Rotate fields

Blog Archive

About Me