Does MSDE support roles and views?

Hello,
I'm trying to figure out whether we need to go with MSDE or SQL Server. Can someone tell me if views are available in MSDE and if so, are they done the same as in SQL Server? Thanks so much.
Debbie
Yes.
Some of the differences between MSDE 2000 and SQL Server 2000 Standard
Edition are:
MSDE 2000 does not include the GUI tools.
MSDE 2000 has a workload governor that limits performance when more than a
few people connect.
MSDE 2000 databases are limited to 2GB of data per database.
MSDE 2000 does not support features primarily intended to support large
servers, such as failover clustering.
A list of the features supported by each edition of SQL Server 2000 is at:
http://msdn.microsoft.com/library/?u...asp?frame=true
You can find a lot of information about MSDE 2000 at
www.microsoft.com\sql\msde
There is a description of the workload governor starting at this location:
http://msdn.microsoft.com/library/?u...asp?frame=true
Much of the documentation in the SQL Server 2000 Books Online applies to
MSDE 2000. This topic describes which parts apply:
http://msdn.microsoft.com/library/?u...asp?frame=true
Alan Brewer [MSFT]
Lead Programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
|||On Tue, 15 Jun 2004 19:35:49 -0700, Alan Brewer [MSFT] wrote:

>MSDE 2000 has a workload governor that limits performance when more than a
>few people connect.
Hi Alan,
That's not entirely accurate. The workload governor will only limit
performance if the number of active operations gets too high (over 8, 3 of
which are for system internal use).
One connection may execute several operations simultaneously. But on the
other hand, in a typical data entry situation, one can have 50 to 100
people connected without ever hitting the workload governor's limitations,
as the connections are inactive while the data entry typist is hammering
his/her keyboard and only active for a short time when data is sent to the
server and stored.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thank you both. What I need to get at is that views in SQL Server will help us limit the access to a centralized data by remote location. This means that a location will only see their data. I'm wondering if that capability exists in MSDE 2000. Thanks
,
Debbie
"Hugo Kornelis" wrote:

> On Tue, 15 Jun 2004 19:35:49 -0700, Alan Brewer [MSFT] wrote:
>
> Hi Alan,
> That's not entirely accurate. The workload governor will only limit
> performance if the number of active operations gets too high (over 8, 3 of
> which are for system internal use).
> One connection may execute several operations simultaneously. But on the
> other hand, in a typical data entry situation, one can have 50 to 100
> people connected without ever hitting the workload governor's limitations,
> as the connections are inactive while the data entry typist is hammering
> his/her keyboard and only active for a short time when data is sent to the
> server and stored.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Wed, 16 Jun 2004 03:52:01 -0700, Debbie wrote:

>Thank you both. What I need to get at is that views in SQL Server will help us limit the access to a centralized data by remote location. This means that a location will only see their data. I'm wondering if that capability exists in MSDE 2000. Thank
s,
>Debbie
>
Hi Debbie,
Yes, you can do that, if you include a location column in the table
design. Simplified example:
CREATE VIEW ViewForLocationA
SELECT ColumnA, ColmunB, ...
FROM CompleteTable
WHERE Location = 'A'
Make sure that people at location A get rights to this view but no rights
to other views and the underlying table. This will work for both SQL
Server and MSDE.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Yes, which is why I included the link to the more in-depth description of
the governor so Debbie could get a better idea of exactly how the governor
worked.
Alan Brewer [MSFT]
Lead Programming Writer
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
|||Thank you again so much, that was a huge help!
Debbie
"Alan Brewer [MSFT]" wrote:

> Yes, which is why I included the link to the more in-depth description of
> the governor so Debbie could get a better idea of exactly how the governor
> worked.
> --
> Alan Brewer [MSFT]
> Lead Programming Writer
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
>
>

Does JOINs and VIEWs lose performance when used with more than one DataBase?

Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
Pieter
On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:

> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA
|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net. ..
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA

Does JOINs and VIEWs lose performance when used with more than one DataBase?

Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
PieterOn Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:

> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net...
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA

Does JOINs and VIEWs lose performance when used with more than one DataBase?

Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
PieterOn Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
--
Rudi Bruchez
MCDBA|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net...
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>> Hi,
>> My application consist of 2 big parts, which work together but are in
>> some
>> way seperate.
>> In case I should store the 2 parts in different databases (but on the
>> same
>> server), will I lose performance when doing a query that joins tables
>> from
>> both databases? And what about views?
>> Any help our hints would be really appreciated.
>> Thanks a lot in advance,
>> Pieter
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA

Does anyone use views?

Good Morning,
Does anyone have any use for views? I know how to build them, but i cant
find any use for them.
They are supposed to protected the data, protect how? does the users have
access to all the data if i dont use views? should i allways access views
insted of the tables to protected the data?
Thanks,
Bruno Nhttp://www.aspfaq.com/2537
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Bruno N" <nylren@.hotmail.com> wrote in message
news:uKqcCWpPFHA.248@.TK2MSFTNGP15.phx.gbl...
> Good Morning,
> Does anyone have any use for views? I know how to build them, but i cant
> find any use for them.
> They are supposed to protected the data, protect how? does the users have
> access to all the data if i dont use views? should i allways access views
> insted of the tables to protected the data?
> Thanks,
> Bruno N
>|||Thank you ! :)
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:uKqcCWpPFHA.248@.TK2MSFTNGP15.phx.gbl...
> Good Morning,
> Does anyone have any use for views? I know how to build them, but i cant
> find any use for them.
> They are supposed to protected the data, protect how? does the users have
> access to all the data if i dont use views? should i allways access views
> insted of the tables to protected the data?
> Thanks,
> Bruno N
>|||To add to Aaron's response, another reason to use views and procs instead of
direct access to the underlying tables is that it provides a well-defined
application interface. As long as you don't change the interface, you can
freely modify table schema without changing application code.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bruno N" <nylren@.hotmail.com> wrote in message
news:uKqcCWpPFHA.248@.TK2MSFTNGP15.phx.gbl...
> Good Morning,
> Does anyone have any use for views? I know how to build them, but i cant
> find any use for them.
> They are supposed to protected the data, protect how? does the users have
> access to all the data if i dont use views? should i allways access views
> insted of the tables to protected the data?
> Thanks,
> Bruno N
>|||Of course you should use them and not just because of security but for bette
r
performance too. This is a long story so I’ll give you some ref links to h
elp
you (my fingers hurts me already...this way typing day :))
http://www.microsoft.com/resources/... />
c3661.mspx
http://www.sqlteam.com/item.asp?ItemID=1015
http://www.sql-server-performance.com/indexed_views.asp
I couldn't find any article that exclusively focuses only on views and
security.
But I'll try to give you and idea with this example:
You have table Orders (OrderID int, CustomerID int,) which contains all
orders of your company.
You want to allow to group of users "A" to select, update and delete only
orders of Customer "C1" (CustomerID=1) and to group "B" only orders of
Customer "C2" (CustomerID=2) .
And, of course, at the same time you may deny all these privileges directly
on table Orders for both groups (A and B).
One of possible solutions (and recommended one) is two create 2 separate
Views: One which will display only orders of Customer "C1" (where
CustomerID=1) and other one which will display only orders of Customer "C2"
(where CustomerID=2) and on both views grant select, update, delete to its
respective groups (A->C1, B->C-2).
Hope this help.
Regards,
Marko Simic
"Bruno N" wrote:

> Good Morning,
> Does anyone have any use for views? I know how to build them, but i cant
> find any use for them.
> They are supposed to protected the data, protect how? does the users have
> access to all the data if i dont use views? should i allways access views
> insted of the tables to protected the data?
> Thanks,
> Bruno N
>
>|||Thank you all that helped me out!
Im gonna read them right now!
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:uKqcCWpPFHA.248@.TK2MSFTNGP15.phx.gbl...
> Good Morning,
> Does anyone have any use for views? I know how to build them, but i cant
> find any use for them.
> They are supposed to protected the data, protect how? does the users have
> access to all the data if i dont use views? should i allways access views
> insted of the tables to protected the data?
> Thanks,
> Bruno N
>

does a view refresh itself when i reference it? (was "Question On Views")

guys, ive never worked with Views before so forgive me.
i know how to create one, and that it creates a virtual table in memory, but i've got one small question.

if i create a view:

CREATE view dbo.myView
as
select Distinct FirstName,LastName from SomeTable

When ever i reference that view, such as
Select FirstName,LastName
from myView
where LastName like 'Jo%'

does that View Refresh itself??

in other words does it run each time i Reference it? or is it static from when i created it.

Wouldnt it be easier just to use a #TempTable or some other Table thats used to hold a few values?

thanks for any help
rikDont worry man ,view is not static .It always fetch records from base table.It is uptodate unless u r using index on views|||You know - I've always thought that the definition of a view as something like a virtual table as very confusing. When I studied relational databases the chapter on views made an assumption that the reader would be very confused at what differentiated a view from a table and spent line after line emphasising the difference. "No " thought I "I can very well differentiate a view from a table - what the flip is the distinction between a view and a query?"

The way I tend to think of views (indexed views excluded) is that a view is best thought of as a persistent, optimised, stored query that can usually be treated in the same way that you would treat a table as far as viewing data is concerned. It is, however, just that - a view of the data. As such, it does not store any data itself. Every time you call it it views the data afresh.

HTH|||thank you guys so much. that's exactly the answer i was looking for.
thanks again
rik

Documenting a SQL Server 2000 Database

I am looking for an easy way to document the specifications of my tables, views, etc. In MS Access, one could use the "Documenter" utility to print out field names, data types, field sizes, and comments. Does SQL Server 2000 have a similar utility?

If not...
1. Why not? This would seem to be very useful.
2. What is the recommended way to document database specifications?

My ultimate goal is to develop an administration manual for our database.

Please note: I looked at the database designer diagram utility which is great for visually representing the relationships between tables. However, I am looking for a utility that will print out the field names, data types, field sizes and comments related to the fields.

Any help would be appreciated.

Thanks :)I've used the INFORMATION_SCHEMA views, such as COLUMNS to get at this type of information.

This thread might help:view post 353693

Also, to get at the comments, you will likely need to use the system function fn_listextendedproperty.

Terri|||The other thing I forgot to mention was the system stored proceduresp_help.

Terri|||tmorton,

Your suggestions are perfect! Both the sp_help and fn_listextendedproperty provide the information I am looking for.

Follow-up question...

If I want to obtain the column information from both of these procedures for a particular table, how would I structure the query such that there would be a single result set? For example, a result set that would have column name, data type, and length from sp_help, and description from fn_listextendedproperty?

I apologize if my question seems rudimentary. I have limited experience with combining stored procedures and functions.

Thanks for your help :)|||I finally figured out how to obtain what I need.

Below you will see the stored procedure that I now use to obtain the following information: 1. Table name, 2. Column name, 3. Column position, 4. Allowance of nulls, 5. Maximum length, and 6. Field description. I used INFORMATION_SCHEMA and fn_listextendedproperty. The stored procedure requires that the table name be specified (i.e., @.TableName parameter).

Here it goes...

CREATE PROCEDURE A_GetTableSpecs_Desc
(
@.TableNamevarchar(100)
)
AS
SELECT
TABLE_NAME AS TableName,
COLUMN_NAME AS ColumnName,
ORDINAL_POSITION AS Position,
IS_NULLABLE AS AllowNulls,
CHARACTER_MAXIMUM_LENGTH AS MaxLength,
value AS FieldDescription
FROM INFORMATION_SCHEMA.COLUMNS AS MyColumns
LEFT JOIN
(
SELECT objname, name, value
FROM ::fn_listextendedproperty
(
NULL,
'user',
'dbo',
'table',
@.TableName,
'column',
default
)
) AS MyExtendedProperty
ON MyColumns.COLUMN_NAME = MyExtendedProperty.objname
WHERE MyColumns.TABLE_NAME = @.TableName
ORDER BY Position
RETURN
GO

Any better suggestions would be much appreciated. Thanks :)

Document map icon disappears!

Has anyone experienced this? One of our users cannot see the Document Map
icon on any report he views.
Clearly this is an issue with his browser or O/S or how his system is
configured, as everyone else can see the icon (and the map!).
Any pointers to what might be causing this greatly appreciated.
ThanksIt would appear to be the browser version. Upgraded to v6 of IE and problem
solved! :)
"Hyper" wrote:
> Has anyone experienced this? One of our users cannot see the Document Map
> icon on any report he views.
> Clearly this is an issue with his browser or O/S or how his system is
> configured, as everyone else can see the icon (and the map!).
> Any pointers to what might be causing this greatly appreciated.
> Thanks

Do you replicate views, stored procedures, and user functions?

Greetings,

We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.

Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?

Thanks for any ideas that you share.

BCB

There shouldnt be any issues replicating views , sprocs , udfs.

|||

It works great appart from when you want to run the snapshot. It will take much longer to run the snapshot for a couple of 100 sp's than for a couple of hundered tables.

Other than that it works great and with 2005 will replicate your changes nicely.

We keep them in a separate publication because of the snapshot issue and to make things simpler for the main publication.

Martin

Do you replicate views, stored procedures, and user functions?

Greetings,

We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.

Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?

Thanks for any ideas that you share.

BCB

There shouldnt be any issues replicating views , sprocs , udfs.

|||

It works great appart from when you want to run the snapshot. It will take much longer to run the snapshot for a couple of 100 sp's than for a couple of hundered tables.

Other than that it works great and with 2005 will replicate your changes nicely.

We keep them in a separate publication because of the snapshot issue and to make things simpler for the main publication.

Martin

Do you replicate views, stored procedures, and user functions?

Greetings,

We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.

Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?

Thanks for any ideas that you share.

BCB

There shouldnt be any issues replicating views , sprocs , udfs.

|||

It works great appart from when you want to run the snapshot. It will take much longer to run the snapshot for a couple of 100 sp's than for a couple of hundered tables.

Other than that it works great and with 2005 will replicate your changes nicely.

We keep them in a separate publication because of the snapshot issue and to make things simpler for the main publication.

Martin