Newbie Question about views

I have created a view by using following statement:-

ALTER VIEW vw_ctotalweeks AS
select DISTINCT allweek from hr_depts WHERE allweek > DateAdd(day,
-28, GetDate()) order by allweek desc

But I am getting follwoing error message:-

The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified.

My question is, can I use Order by class in Views if not, how can sort
dates
in view

Please helpViews are like tables - their data is not logically ordered, which is why
ORDER BY isn't valid.

The way to sort a view is in the SELECT statement when you retrieve data
from it:

SELECT allweek
FROM vw_ctotalweeks
ORDER BY allweek DESC

--
David Portas
----
Please reply only to the newsgroup
--|||It is possible to order data in a view use the following select statment:

SELECT TOP 100 PERCENT *
FROM Orders
ORDER BY CustomerID

The top 100 percent option makes the order by posible|||...so the create view statement would look something like this:

Create view v_orders as
SELECT TOP 100 PERCENT *
FROM Orders
ORDER BY CustomerID|||Some people do suggest using this "trick" to order views. I believe that
there are good reasons to avoid doing this.

This behaviour of TOP in a view is undocumented or at least,
under-documented. The ORDER BY is valid only for the purpose of defining the
TOP x PERCENT so intuitively you would not expect it to apply to the result
of a SELECT from the view. 99% of the time it *may* work but there is no
guarantee that it will always continue to work.

A view is supposed to behave like a table - without a logical order.
Sometimes you don't want the view to be sorted. Consider this example:

CREATE TABLE foo (x INTEGER PRIMARY KEY NONCLUSTERED, y INTEGER NOT NULL)

GO

CREATE VIEW foo_view
AS
SELECT TOP 100 PERCENT x,y
FROM foo
ORDER BY y

GO

SELECT x FROM foo_view

The most efficient plan for the SELECT x query is an index scan of the
nonclustered index. The optimiser therefore has a choice either to ignore
the ORDER BY and retrieve an unsorted result set or to force a sort which
gives a sub-optimal execution plan.

As always with specific engine behaviour, results could change between
different installations, service packs or versions of SQLServer, which could
break your code if it relies on an undefined feature.

In short, don't use undocumented tricks as a substitute for good design and
if you do use this feature be aware of its limitations and risks.

SELECT * FROM view ORDER BY ...

Hope this helps.

--
David Portas
----
Please reply only to the newsgroup
--

newbie q - viewing relationships in SQL Server 2000

Hi, I am trying to view the relationship data in a sql server 2000 database.
How do I go about this?
Chloe
From query analyzer you can do
sp_help tablename or
sp_helpconstraint tablename
From SQL Enterprise Manager select the database, select tables, and on the
right side you may right click on a table.
There is also an information_schema view which allows you to get at the
relationships, but I forget the name ... Search for information_schema in
books on line.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chloe" <Chloe@.discussions.microsoft.com> wrote in message
news:E15CAC20-6FC8-4E03-AFBF-4F6F0023417A@.microsoft.com...
> Hi, I am trying to view the relationship data in a sql server 2000
database.
> How do I go about this?
> --
> Chloe
|||Thanks for that. I am wondering though, is there a way to view the
relationships that are set up, similar to the way you can view it in access
for instance?
"Wayne Snyder" wrote:

> From query analyzer you can do
> sp_help tablename or
> sp_helpconstraint tablename
> From SQL Enterprise Manager select the database, select tables, and on the
> right side you may right click on a table.
> There is also an information_schema view which allows you to get at the
> relationships, but I forget the name ... Search for information_schema in
> books on line.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Chloe" <Chloe@.discussions.microsoft.com> wrote in message
> news:E15CAC20-6FC8-4E03-AFBF-4F6F0023417A@.microsoft.com...
> database.
>
>
|||In Enterprise manager you can make a diagram and when you add the tables to
it relationships will be shown graphically.
Bojidar Alexandrov
"Chloe" <Chloe@.discussions.microsoft.com> wrote in message
news:7CBA3CFD-3114-4D19-A4A1-B44B497CF656@.microsoft.com...
> Thanks for that. I am wondering though, is there a way to view the
> relationships that are set up, similar to the way you can view it in
access[vbcol=seagreen]
> for instance?
>
> "Wayne Snyder" wrote:
the[vbcol=seagreen]
in[vbcol=seagreen]
|||Thanks for that.
"Bojidar Alexandrov" wrote:

> In Enterprise manager you can make a diagram and when you add the tables to
> it relationships will be shown graphically.
> Bojidar Alexandrov
>
> "Chloe" <Chloe@.discussions.microsoft.com> wrote in message
> news:7CBA3CFD-3114-4D19-A4A1-B44B497CF656@.microsoft.com...
> access
> the
> in
>
>

newbie q - viewing relationships in SQL Server 2000

Hi, I am trying to view the relationship data in a sql server 2000 database.
How do I go about this?
--
ChloeFrom query analyzer you can do
sp_help tablename or
sp_helpconstraint tablename
From SQL Enterprise Manager select the database, select tables, and on the
right side you may right click on a table.
There is also an information_schema view which allows you to get at the
relationships, but I forget the name ... Search for information_schema in
books on line.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chloe" <Chloe@.discussions.microsoft.com> wrote in message
news:E15CAC20-6FC8-4E03-AFBF-4F6F0023417A@.microsoft.com...
> Hi, I am trying to view the relationship data in a sql server 2000
database.
> How do I go about this?
> --
> Chloe|||In Enterprise manager you can make a diagram and when you add the tables to
it relationships will be shown graphically.
Bojidar Alexandrov
"Chloe" <Chloe@.discussions.microsoft.com> wrote in message
news:7CBA3CFD-3114-4D19-A4A1-B44B497CF656@.microsoft.com...
> Thanks for that. I am wondering though, is there a way to view the
> relationships that are set up, similar to the way you can view it in
access
> for instance?
>
> "Wayne Snyder" wrote:
> > From query analyzer you can do
> > sp_help tablename or
> > sp_helpconstraint tablename
> >
> > From SQL Enterprise Manager select the database, select tables, and on
the
> > right side you may right click on a table.
> >
> > There is also an information_schema view which allows you to get at the
> > relationships, but I forget the name ... Search for information_schema
in
> > books on line.
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Chloe" <Chloe@.discussions.microsoft.com> wrote in message
> > news:E15CAC20-6FC8-4E03-AFBF-4F6F0023417A@.microsoft.com...
> > > Hi, I am trying to view the relationship data in a sql server 2000
> > database.
> > > How do I go about this?
> > > --
> > > Chloe
> >
> >
> >

newbie q - viewing relationships in SQL Server 2000

Hi, I am trying to view the relationship data in a sql server 2000 database.
How do I go about this?
--
ChloeFrom query analyzer you can do
sp_help tablename or
sp_helpconstraint tablename
From SQL Enterprise Manager select the database, select tables, and on the
right side you may right click on a table.
There is also an information_schema view which allows you to get at the
relationships, but I forget the name ... Search for information_schema in
books on line.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chloe" <Chloe@.discussions.microsoft.com> wrote in message
news:E15CAC20-6FC8-4E03-AFBF-4F6F0023417A@.microsoft.com...
> Hi, I am trying to view the relationship data in a sql server 2000
database.
> How do I go about this?
> --
> Chloe|||Thanks for that. I am wondering though, is there a way to view the
relationships that are set up, similar to the way you can view it in access
for instance?
"Wayne Snyder" wrote:

> From query analyzer you can do
> sp_help tablename or
> sp_helpconstraint tablename
> From SQL Enterprise Manager select the database, select tables, and on the
> right side you may right click on a table.
> There is also an information_schema view which allows you to get at the
> relationships, but I forget the name ... Search for information_schema in
> books on line.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Chloe" <Chloe@.discussions.microsoft.com> wrote in message
> news:E15CAC20-6FC8-4E03-AFBF-4F6F0023417A@.microsoft.com...
> database.
>
>|||In Enterprise manager you can make a diagram and when you add the tables to
it relationships will be shown graphically.
Bojidar Alexandrov
"Chloe" <Chloe@.discussions.microsoft.com> wrote in message
news:7CBA3CFD-3114-4D19-A4A1-B44B497CF656@.microsoft.com...
> Thanks for that. I am wondering though, is there a way to view the
> relationships that are set up, similar to the way you can view it in
access[vbcol=seagreen]
> for instance?
>
> "Wayne Snyder" wrote:
>
the[vbcol=seagreen]
in[vbcol=seagreen]|||Thanks for that.
"Bojidar Alexandrov" wrote:

> In Enterprise manager you can make a diagram and when you add the tables t
o
> it relationships will be shown graphically.
> Bojidar Alexandrov
>
> "Chloe" <Chloe@.discussions.microsoft.com> wrote in message
> news:7CBA3CFD-3114-4D19-A4A1-B44B497CF656@.microsoft.com...
> access
> the
> in
>
>sql

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?
Hi
Have you checked
http://support.microsoft.com/default...b;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
|||Hi
Have you checked
http://support.microsoft.com/default...b;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> Hi
> Have you checked
> http://support.microsoft.com/default...b;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> to
>
|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.[vbcol=seagreen]
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
as[vbcol=seagreen]
to[vbcol=seagreen]
operation[vbcol=seagreen]
[OLE/DB[vbcol=seagreen]
0x8004d01b].
>
|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>
sql

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?Hi
Have you checked
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||Hi
Have you checked
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> Hi
> Have you checked
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this
>> from the view designer also returns a nice result. However, if I try to
> to
>> save the view, I get the following error message:
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributedtransaction.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>>
>> Anyone know what I'm doing wrong?
>>
>|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> > Hi
> >
> > Have you checked
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
> >
> > John
> > <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> >> Hi. I've created a linked server to Oracle 8i. I want to save a view
as
> >> follows:
> >>
> >> SELECT *
> >> FROM ORACLE8I..SCOTT.EMP EMP_1
> >>
> >> From SQL Query Analyser, this returns a nice set or records. Running
> >> this
> >> from the view designer also returns a nice result. However, if I try
to
> > to
> >> save the view, I get the following error message:
> >>
> >> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
> >> could not be performed because the OLE DB provider 'MSDAORA' was unable
> >> to
> >> begin a distributedtransaction.
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB
> >> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned
0x8004d01b].
> >>
> >>
> >>
> >> Anyone know what I'm doing wrong?
> >>
> >>
> >
> >
>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this from the view designer also returns a nice result. However, if I
>> try to to save the view, I get the following error message:
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributed transaction.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>

Newbie problem: Saving a view from a linked server won't work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:
SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1
From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trac
e [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
Anyone know what I'm doing wrong?Hi
Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||Hi
Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106
John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>|||None of the stuff in that article seems to help. Same error message occurs.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
> Hi
> Have you checked
> http://support.microsoft.com/defaul...kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> to
>|||Hi
This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd
John
<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk...
as[vbcol=seagreen]
to[vbcol=seagreen]
operation[vbcol=seagreen]
[OLE/DB[vbcol=seagreen]
0x8004d01b].[vbcol=seagreen]
>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
I assume that with "view designer" you mean what's in Enterprise Manager.
I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.
Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.
But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] Th
e operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error tr
ace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?
>

Newbie problem: Saving a view from a linked server wont work

Hi. I've created a linked server to Oracle 8i. I want to save a view as
follows:

SELECT *
FROM ORACLE8I..SCOTT.EMP EMP_1

From SQL Query Analyser, this returns a nice set or records. Running this
from the view designer also returns a nice result. However, if I try to to
save the view, I get the following error message:

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider 'MSDAORA' was unable to
begin a distributed transaction.

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].

Anyone know what I'm doing wrong?Hi

Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106

John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?|||Hi

Have you checked
http://support.microsoft.com/defaul...kb;EN-US;280106

John
<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributedtransaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?|||None of the stuff in that article seems to help. Same error message occurs.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> Hi
> Have you checked
> http://support.microsoft.com/defaul...kb;EN-US;280106
> John
> <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>>
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>>
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this
>> from the view designer also returns a nice result. However, if I try to
> to
>> save the view, I get the following error message:
>>
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributedtransaction.
>>
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>>
>>
>>
>> Anyone know what I'm doing wrong?
>>
>>|||Hi

This one seems to imply MSDTC is not running:
http://tinyurl.com/4wghd

John

<arch> wrote in message news:417a85ec@.funnel.arach.net.au...
> None of the stuff in that article seems to help. Same error message
occurs.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:417a46c7$0$12657$afc38c87@.news.easynet.co.uk. ..
> > Hi
> > Have you checked
> > http://support.microsoft.com/defaul...kb;EN-US;280106
> > John
> > <arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> >> Hi. I've created a linked server to Oracle 8i. I want to save a view
as
> >> follows:
> >>
> >> SELECT *
> >> FROM ORACLE8I..SCOTT.EMP EMP_1
> >>
> >> From SQL Query Analyser, this returns a nice set or records. Running
> >> this
> >> from the view designer also returns a nice result. However, if I try
to
> > to
> >> save the view, I get the following error message:
> >>
> >> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The
operation
> >> could not be performed because the OLE DB provider 'MSDAORA' was unable
> >> to
> >> begin a distributedtransaction.
> >>
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB
> >> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned
0x8004d01b].
> >>
> >>
> >>
> >> Anyone know what I'm doing wrong?
> >>
> >>|||(arch) writes:
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running
> this from the view designer also returns a nice result. However, if I
> try to to save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].

I assume that with "view designer" you mean what's in Enterprise Manager.

I used the Profiler, to see what Enterprise Manager passes to SQL Server,
and I found that it starts a transaction before it creates a view, no matter
if the view refers to local tables only or remote tables as well.

Apparently you have not set things so you can run distributed transactions
against your Oracle box. I have no expierence with Oracle servers, so I
cannot help there. But checking that MSDTC is running on the local SQL
Server machine as John suggested is a simple thing.

But if you don't need distrubuted transactions against your Oracle server,
there is a very simple workaround: create the view from Query Analyzer
instead.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks John and Erland. That seems to have solved it. DTC is certainly
running. Simply avoiding the use of the View Designer in Enterprise Manager
seems to prevent the error from occurring. Damn, I wish I'd thought of
that!

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns958C2C52D211Yazorman@.127.0.0.1...
> (arch) writes:
>> Hi. I've created a linked server to Oracle 8i. I want to save a view as
>> follows:
>>
>> SELECT *
>> FROM ORACLE8I..SCOTT.EMP EMP_1
>>
>> From SQL Query Analyser, this returns a nice set or records. Running
>> this from the view designer also returns a nice result. However, if I
>> try to to save the view, I get the following error message:
>>
>> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
>> could not be performed because the OLE DB provider 'MSDAORA' was unable
>> to
>> begin a distributed transaction.
>>
>> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
>> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
> I assume that with "view designer" you mean what's in Enterprise Manager.
> I used the Profiler, to see what Enterprise Manager passes to SQL Server,
> and I found that it starts a transaction before it creates a view, no
> matter
> if the view refers to local tables only or remote tables as well.
> Apparently you have not set things so you can run distributed transactions
> against your Oracle box. I have no expierence with Oracle servers, so I
> cannot help there. But checking that MSDTC is running on the local SQL
> Server machine as John suggested is a simple thing.
> But if you don't need distrubuted transactions against your Oracle server,
> there is a very simple workaround: create the view from Query Analyzer
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Linked server connections only allow select insert update and delete... and
( unless you do tricks) you may not change the DDL on the Linked server...

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

<arch> wrote in message news:417a42df$1@.funnel.arach.net.au...
> Hi. I've created a linked server to Oracle 8i. I want to save a view as
> follows:
> SELECT *
> FROM ORACLE8I..SCOTT.EMP EMP_1
> From SQL Query Analyser, this returns a nice set or records. Running this
> from the view designer also returns a nice result. However, if I try to
to
> save the view, I get the following error message:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider 'MSDAORA' was unable to
> begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB
> Provider 'MSDAORA' ITransactionJoiJoin Transaction returned 0x8004d01b].
>
> Anyone know what I'm doing wrong?

Newbie Can't See tables of DB in Enterprise Manager

Hello,
I'm trying to view the tables of a db on a SQL7 server, but cannot. I used
to be able to... The view says "Tables 289 Items" but then also says "There
are no items to show in this view" where the tables are normally listed.
Any ideas why?
Thanks,
IvanHi,
From Query Analyzer , execute the below code to get the table details.
Use <dbname>
go
sp_tables
Thanks
Hari
MCDBA
"Ivan Starr" <ivan@.ivanstarr.com> wrote in message
news:e5I$D1OPEHA.2920@.tk2msftngp13.phx.gbl...
> Hello,
> I'm trying to view the tables of a db on a SQL7 server, but cannot. I
used
> to be able to... The view says "Tables 289 Items" but then also says
"There
> are no items to show in this view" where the tables are normally listed.
> Any ideas why?
> Thanks,
> Ivan
>

newbie - user permissions

I have added a new user to a database without any explicit permissions, but when I view their effective permissions inside the Microsoft SQL Server Management Studio, they have a whole host of permissions. How can this be? Is it a bug in SQL Server? Or could it be that the public role has all these permissions?

If new users are inheriting these permissions from the public role, how do I view the public role permissions?

Thanks.

To view the public role permissions use the following query

select class_desc,major_id,minor_id,permission_name,state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('public')

For more information see the documentation on the catalog view sys.database_permissions at http://msdn2.microsoft.com/en-us/library/ms188367.aspx

|||

Hi,

did you create a Windows User account or a SQL Server user account ? If you created a SQl Server user Account, then the rights probably belong to the public role. You can view the permissions by naviogating to the Database --> Security --> Database Roles --> public . If you added a Windows user this might also apply as well as the user can be in a Windows group which was added and has the appropiate permissions. All permissions are then inherited from the group the user is in. grant permissions are additive while deny′s are restrictive which means that once denied you cannot do the command this eas applied on.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

This is what I did (all using the Microsoft SQL Server Management Studio):

- Firstly I created a new login (Security -> Logins -> New Login) selecting Windows authentication. Whilst still in the New Login dialog, I selected the User Mappings page and mapped the user to the desired database (leaving membership to the public database role only).

- I then opened the properties for the database that I mapped the new login to, selected the new user and clicked 'Effective Permissions'. The list displayed many more permissions than just "connect".

I have added this user to another SQL Server on the network in exactly the same way and everything worked fine (i.e. the permissions listed were just "Connect").

Any ideas? Thanks again.

|||

As Jens mentioned, the user is probably a member of a group that is granted permissions in this database. You can look at the permissions directly granted to the user in the database by executing:

select class_desc, major_id, minor_id, permission_name, state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('your_user_name')

If this only shows connect, then the other permissions are inherited from a group. You can look more closely at the database_permissions catalog to see which group is granted the extra set of permissions.

Thanks
Laurentiu

|||

Using the query you provided I have determined that the user is only directly granted connect.

Do you know of a query I can run that will tell me the source of the other permissions the user has?

|||

You can check to see what groups/roles you have in the database by querying sys.database_principals. You can then query sys.database_permissions for each group/role to which your user belongs to, to see what permissions these are granted. Or you could work the other way around and check to see who is the grantee of the permissions that you see in Management Studio. To see what Windows groups your user belongs to, you can check the sys.login_token catalog. To see what roles he belongs to, you can query sys.database_role_members.

For example, here are some queries that you may find useful:

-- Find groups to which current user belongs to in current database
--
select lt.name from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid and dbp.type = 'G'

-- Find roles to which current user belongs to in current database
--
select rls.name from sys.database_role_members dbrm, sys.database_principals rls
where dbrm.role_principal_id = rls.principal_id and dbrm.member_principal_id = user_id()

-- Find permissions granted to current user directly or that are inherited
-- from groups or roles
--
select permission_name from sys.database_permissions
where grantee_principal_id in
(
select user_id()
union
select dbp.principal_id from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid
union
select dbrm.role_principal_id from sys.database_role_members dbrm
where dbrm.member_principal_id = user_id()
)

If you need more help, please include the output of these queries with your next reply.

Thanks
Laurentiu

|||

Hi Laurentiu:

Could you please show us on how to get the login name, the username and the role to which the login belongs (db_ddladmin, db_datareader etc) in all the databases?. This query when run from master should show the output as:

Loginname usename databasename Rolename

This should show the output listed by each db.

Also is there a way to script the roles to which the user belongs. I did not see that option in SSMS. In 2000 the option was the checkboxes script users and script database roles.

Please let us know.

Thank you

AK

|||

This query will get the login, it's user name, and database role. Run this in each database, and you have your answer. Although it's not a complete picture if you use Windows Authentication. If you have a windows group as a login, members of that group won't get mapped to a database user until needed and will not show up in this query. But they still have access.

select sl.name [loginname], dbm.name [username], db_name() [databasename], dbr.name [rolename]
from sys.server_principals sl inner join sys.database_principals dbm
on (sl.sid = dbm.sid)
inner join sys.database_role_members dbrm
on (dbm.principal_id = dbrm.member_principal_id)
inner join sys.database_principals dbr
on (dbrm.role_principal_id = dbr.principal_id)

-Jack

|||Thanks a lot Jack. I can modify it on my end according to my needs. Thanks again.

newbie - user permissions

I have added a new user to a database without any explicit permissions, but when I view their effective permissions inside the Microsoft SQL Server Management Studio, they have a whole host of permissions. How can this be? Is it a bug in SQL Server? Or could it be that the public role has all these permissions?

If new users are inheriting these permissions from the public role, how do I view the public role permissions?

Thanks.

To view the public role permissions use the following query

select class_desc,major_id,minor_id,permission_name,state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('public')

For more information see the documentation on the catalog view sys.database_permissions at http://msdn2.microsoft.com/en-us/library/ms188367.aspx

|||

Hi,

did you create a Windows User account or a SQL Server user account ? If you created a SQl Server user Account, then the rights probably belong to the public role. You can view the permissions by naviogating to the Database --> Security --> Database Roles --> public . If you added a Windows user this might also apply as well as the user can be in a Windows group which was added and has the appropiate permissions. All permissions are then inherited from the group the user is in. grant permissions are additive while deny′s are restrictive which means that once denied you cannot do the command this eas applied on.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

This is what I did (all using the Microsoft SQL Server Management Studio):

- Firstly I created a new login (Security -> Logins -> New Login) selecting Windows authentication. Whilst still in the New Login dialog, I selected the User Mappings page and mapped the user to the desired database (leaving membership to the public database role only).

- I then opened the properties for the database that I mapped the new login to, selected the new user and clicked 'Effective Permissions'. The list displayed many more permissions than just "connect".

I have added this user to another SQL Server on the network in exactly the same way and everything worked fine (i.e. the permissions listed were just "Connect").

Any ideas? Thanks again.

|||

As Jens mentioned, the user is probably a member of a group that is granted permissions in this database. You can look at the permissions directly granted to the user in the database by executing:

select class_desc, major_id, minor_id, permission_name, state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('your_user_name')

If this only shows connect, then the other permissions are inherited from a group. You can look more closely at the database_permissions catalog to see which group is granted the extra set of permissions.

Thanks
Laurentiu

|||

Using the query you provided I have determined that the user is only directly granted connect.

Do you know of a query I can run that will tell me the source of the other permissions the user has?

|||

You can check to see what groups/roles you have in the database by querying sys.database_principals. You can then query sys.database_permissions for each group/role to which your user belongs to, to see what permissions these are granted. Or you could work the other way around and check to see who is the grantee of the permissions that you see in Management Studio. To see what Windows groups your user belongs to, you can check the sys.login_token catalog. To see what roles he belongs to, you can query sys.database_role_members.

For example, here are some queries that you may find useful:

-- Find groups to which current user belongs to in current database
--
select lt.name from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid and dbp.type = 'G'

-- Find roles to which current user belongs to in current database
--
select rls.name from sys.database_role_members dbrm, sys.database_principals rls
where dbrm.role_principal_id = rls.principal_id and dbrm.member_principal_id = user_id()

-- Find permissions granted to current user directly or that are inherited
-- from groups or roles
--
select permission_name from sys.database_permissions
where grantee_principal_id in
(
select user_id()
union
select dbp.principal_id from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid
union
select dbrm.role_principal_id from sys.database_role_members dbrm
where dbrm.member_principal_id = user_id()
)

If you need more help, please include the output of these queries with your next reply.

Thanks
Laurentiu

|||

Hi Laurentiu:

Could you please show us on how to get the login name, the username and the role to which the login belongs (db_ddladmin, db_datareader etc) in all the databases?. This query when run from master should show the output as:

Loginname usename databasename Rolename

This should show the output listed by each db.

Also is there a way to script the roles to which the user belongs. I did not see that option in SSMS. In 2000 the option was the checkboxes script users and script database roles.

Please let us know.

Thank you

AK

|||

This query will get the login, it's user name, and database role. Run this in each database, and you have your answer. Although it's not a complete picture if you use Windows Authentication. If you have a windows group as a login, members of that group won't get mapped to a database user until needed and will not show up in this query. But they still have access.

select sl.name [loginname], dbm.name [username], db_name() [databasename], dbr.name [rolename]
from sys.server_principals sl inner join sys.database_principals dbm
on (sl.sid = dbm.sid)
inner join sys.database_role_members dbrm
on (dbm.principal_id = dbrm.member_principal_id)
inner join sys.database_principals dbr
on (dbrm.role_principal_id = dbr.principal_id)

-Jack

|||Thanks a lot Jack. I can modify it on my end according to my needs. Thanks again.

newbie - user permissions

I have added a new user to a database without any explicit permissions, but when I view their effective permissions inside the Microsoft SQL Server Management Studio, they have a whole host of permissions. How can this be? Is it a bug in SQL Server? Or could it be that the public role has all these permissions?

If new users are inheriting these permissions from the public role, how do I view the public role permissions?

Thanks.

To view the public role permissions use the following query

select class_desc,major_id,minor_id,permission_name,state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('public')

For more information see the documentation on the catalog view sys.database_permissions at http://msdn2.microsoft.com/en-us/library/ms188367.aspx

|||

Hi,

did you create a Windows User account or a SQL Server user account ? If you created a SQl Server user Account, then the rights probably belong to the public role. You can view the permissions by naviogating to the Database --> Security --> Database Roles --> public . If you added a Windows user this might also apply as well as the user can be in a Windows group which was added and has the appropiate permissions. All permissions are then inherited from the group the user is in. grant permissions are additive while deny′s are restrictive which means that once denied you cannot do the command this eas applied on.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

This is what I did (all using the Microsoft SQL Server Management Studio):

- Firstly I created a new login (Security -> Logins -> New Login) selecting Windows authentication. Whilst still in the New Login dialog, I selected the User Mappings page and mapped the user to the desired database (leaving membership to the public database role only).

- I then opened the properties for the database that I mapped the new login to, selected the new user and clicked 'Effective Permissions'. The list displayed many more permissions than just "connect".

I have added this user to another SQL Server on the network in exactly the same way and everything worked fine (i.e. the permissions listed were just "Connect").

Any ideas? Thanks again.

|||

As Jens mentioned, the user is probably a member of a group that is granted permissions in this database. You can look at the permissions directly granted to the user in the database by executing:

select class_desc, major_id, minor_id, permission_name, state_desc from sys.database_permissions where grantee_principal_id=database_principal_id('your_user_name')

If this only shows connect, then the other permissions are inherited from a group. You can look more closely at the database_permissions catalog to see which group is granted the extra set of permissions.

Thanks
Laurentiu

|||

Using the query you provided I have determined that the user is only directly granted connect.

Do you know of a query I can run that will tell me the source of the other permissions the user has?

|||

You can check to see what groups/roles you have in the database by querying sys.database_principals. You can then query sys.database_permissions for each group/role to which your user belongs to, to see what permissions these are granted. Or you could work the other way around and check to see who is the grantee of the permissions that you see in Management Studio. To see what Windows groups your user belongs to, you can check the sys.login_token catalog. To see what roles he belongs to, you can query sys.database_role_members.

For example, here are some queries that you may find useful:

-- Find groups to which current user belongs to in current database
--
select lt.name from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid and dbp.type = 'G'

-- Find roles to which current user belongs to in current database
--
select rls.name from sys.database_role_members dbrm, sys.database_principals rls
where dbrm.role_principal_id = rls.principal_id and dbrm.member_principal_id = user_id()

-- Find permissions granted to current user directly or that are inherited
-- from groups or roles
--
select permission_name from sys.database_permissions
where grantee_principal_id in
(
select user_id()
union
select dbp.principal_id from sys.login_token lt, sys.database_principals dbp
where lt.sid = dbp.sid
union
select dbrm.role_principal_id from sys.database_role_members dbrm
where dbrm.member_principal_id = user_id()
)

If you need more help, please include the output of these queries with your next reply.

Thanks
Laurentiu

|||

Hi Laurentiu:

Could you please show us on how to get the login name, the username and the role to which the login belongs (db_ddladmin, db_datareader etc) in all the databases?. This query when run from master should show the output as:

Loginname usename databasename Rolename

This should show the output listed by each db.

Also is there a way to script the roles to which the user belongs. I did not see that option in SSMS. In 2000 the option was the checkboxes script users and script database roles.

Please let us know.

Thank you

AK

|||

This query will get the login, it's user name, and database role. Run this in each database, and you have your answer. Although it's not a complete picture if you use Windows Authentication. If you have a windows group as a login, members of that group won't get mapped to a database user until needed and will not show up in this query. But they still have access.

select sl.name [loginname], dbm.name [username], db_name() [databasename], dbr.name [rolename]
from sys.server_principals sl inner join sys.database_principals dbm
on (sl.sid = dbm.sid)
inner join sys.database_role_members dbrm
on (dbm.principal_id = dbrm.member_principal_id)
inner join sys.database_principals dbr
on (dbrm.role_principal_id = dbr.principal_id)

-Jack

|||Thanks a lot Jack. I can modify it on my end according to my needs. Thanks again.

newbie - Most Recent Records from multiple tables

Hi,

I'm trying to create a view or TSQL statement to return in one recordset...

a) the most recent record of a PK in table1 [foodRecipes]

b) the most recent record (if exists) of FK from table 1 with the PK from table2

Goal: Each recipe can have many versions, and each version can have many historical attempts at making cookies...

example:

table 1: foodRecipes (PK = foodGroup + recipeName + recipeDateModified)

foodGroup [nvarchar (50)]

recipeName [nvarchar (50]

recipeDateModified [datetime]

cupsOfSugar [float]

sampleData:

cookies, peanutButter, 3/3/2007, 1.5

cookies, peanutButter, 3/4/2007, 2.0

cookies, sugar, 3/3/2007, 5.0

table 2: foodRecipeHistory (PK = foodGroup + recipeName + recipeDateModified + historyDateModified)

foodGroup [nvarchar (50)] ...FK from table1

recipeName [nvarchar (50] ...FK from table1

recipeDateModified [datetime] ...FK from table1

historyDateModified [datetime]

cupsOfSugarHistory [float]

sampleData:

cookies, peanutButter, 3/3/2007, 3/3/2007 10:15:00 AM, 1.5

cookies, peanutButter, 3/4/2007, 3/4/2007 10:20:00 AM, 2.0

cookies, peanutButter, 3/4/2007, 3/4/2007 10:21:00 AM, 2.2

What I want: the view or TSQL should provide the most recent unique recipes data + the most recent history (if exists, otherwise NULL)

SELECT * FROM myRecipies

sample Resultset:

foodGroup, recipeName, recipeDateModified, cupsOfSugar, historyDateModified, cupsOfSugarHistory

cookies, peanutButter, 3/4/2007, 2.0, 2.2

cookies, sugar, 3/3/2007, 5.0, <NULL>

What I've got now:

1. TSQL that gives me back the most recent recipes (No History yet)

SELECT foodGroup, recipeName, recipeDateModified, cupsOfSugar, CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate
FROM dbo.foodRecipes oher
WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN
(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))
FROM dbo.foodRecipes
WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

...and this works great, I get back each unique recipe from table #1, the most recent...

anyone good at this?

thanks in advance,

bsierad

You must have a sweet tooth if your only ingredient is CupsOfSugar.... ;)

Anyway, try the query below to see if this is what you're after.

Chris

SELECT foodGroup,

recipeName,

recipeDateModified,

cupsOfSugar,

CONVERT(nvarchar(30), recipeDateModified, 9) AS strModifiedDate,

(SELECT TOP 1 frh.cupsOfSugarHistory

FROM dbo.foodRecipeHistory frh

WHERE frh.foodGroup = oher.foodGroup

AND frh.recipeName = oher.recipeName

AND frh.recipeDateModified = oher.recipeDateModified

ORDER BY frh.historyDateModified DESC) AS cupsOfSugarHistory

FROM dbo.foodRecipes oher

WHERE (CONVERT(nvarchar(30), recipeDateModified, 9) IN

(SELECT MAX(CONVERT(nvarchar(30), recipeDateModified, 9))

FROM dbo.foodRecipes

WHERE foodGroup= oher.foodGroupAND recipeName = oher.recipeName))

|||

Thanks!

Works great...and I can soak this in and apply it in other areas...

My real fields don't taste this good...machineGasFlow sounds pretty boring...

Can't thank you enough,

bsierad