describe OK- select not

Hello,
I am connecting to SQLserver via an application running on linux.
Under the hood it does a describe which brings back the field types
for any table OK.
a select always fails (no data returned) however. I am troubleshooting
this remotely and have asked the MSSQL dba to run a select with the
same user id/password that I use on the client - this succeeds.
There is a firewall between client and db, but I guess this is OK cos
the describe succeeds.
Anything I can ask the dba to do or check to help diagnose the
problem? Does it follow that if userid is good for selects on MS box
it will be good for remote queries?
TIA
TonTry connecting to the DB remotely via Query Analyzer. I know you are on
Linux , so can you see if you can connect from a Windows, with the
username/password and run the SELECT . Does it return results?
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"ton de w" <ton_de_winter@.yahoo.co.uk> wrote in message
news:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
> Hello,
> I am connecting to SQLserver via an application running on linux.
> Under the hood it does a describe which brings back the field types
> for any table OK.
> a select always fails (no data returned) however. I am troubleshooting
> this remotely and have asked the MSSQL dba to run a select with the
> same user id/password that I use on the client - this succeeds.
> There is a firewall between client and db, but I guess this is OK cos
> the describe succeeds.
> Anything I can ask the dba to do or check to help diagnose the
> problem? Does it follow that if userid is good for selects on MS box
> it will be good for remote queries?
> TIA
> Ton
>|||On 21 Sep, 11:23, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Try connecting to the DB remotely via Query Analyzer. I know you are on
> Linux , so can you see if you can connect from a Windows, with the
> username/password and run the SELECT . Does it return results?
> --
> Jack Vamvas
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com/SQL
> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in messagenews:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
>
> > Hello,
> > I am connecting to SQLserver via an application running on linux.
> > Under the hood it does a describe which brings back the field types
> > for any table OK.
> > a select always fails (no data returned) however. I am troubleshooting
> > this remotely and have asked the MSSQL dba to run a select with the
> > same user id/password that I use on the client - this succeeds.
> > There is a firewall between client and db, but I guess this is OK cos
> > the describe succeeds.
> > Anything I can ask the dba to do or check to help diagnose the
> > problem? Does it follow that if userid is good for selects on MS box
> > it will be good for remote queries?
> > TIA
> > Ton- Hide quoted text -
> - Show quoted text -
Thanks for that Query Analyzer works well with userid/password
So does linux commandline tool tsql which allows me to pull back the
data with a "select * from tablename" - it just needs the !P port
username and password to logon.
This works fine.
However the application I am dealing with does not bring the data back
<sigh>
I am not sure how tsql gets the data without a databasename ie i would
have expected "select * from tablename" to fail and a select * from
databasename.tablename to succeed - perhaps cos of my Oracle
experience.
The application that is failing needs the database name before it will
log on OK. but "select * from tablename" generates a syntax error near
FROM - and so does a "select * from databasename.tablename"|||Ton,
sql server's specification of table in a select statement can have up to
four parts. In addition to the name itself, you can have / may need three
qualifiers:
select column_list from server_name.database_name.schema_name.table_name.
It is recommended if you are in the database where the table is located, you
use the schema_name (also know as owner) to qualify the table. If you are
not in the database where the table is, you need to add the database name
too, and then (better) specify the schema as well, which can be omitted if
it is dbo (such as mydb..mytalbe). If you are accessing a liked server, you
then also need to specify the server name.
In your case, if your default database is where the table is located, when
you establish the connection, you can directly run a select * from
tablename. But select * from databasename.tablename will never work unless
the databasename is the same as the schema name by accident, in which case
sql server considers you were doing a select * from schemaname.tablename.
hth
Quentin
"ton de w" <ton_de_winter@.yahoo.co.uk> wrote in message
news:1190390145.227481.285540@.g4g2000hsf.googlegroups.com...
> On 21 Sep, 11:23, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
>> Try connecting to the DB remotely via Query Analyzer. I know you are on
>> Linux , so can you see if you can connect from a Windows, with the
>> username/password and run the SELECT . Does it return results?
>> --
>> Jack Vamvas
>> ___________________________________
>> Need an IT job? http://www.ITjobfeed.com/SQL
>> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in
>> messagenews:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
>>
>> > Hello,
>> > I am connecting to SQLserver via an application running on linux.
>> > Under the hood it does a describe which brings back the field types
>> > for any table OK.
>> > a select always fails (no data returned) however. I am troubleshooting
>> > this remotely and have asked the MSSQL dba to run a select with the
>> > same user id/password that I use on the client - this succeeds.
>> > There is a firewall between client and db, but I guess this is OK cos
>> > the describe succeeds.
>> > Anything I can ask the dba to do or check to help diagnose the
>> > problem? Does it follow that if userid is good for selects on MS box
>> > it will be good for remote queries?
>> > TIA
>> > Ton- Hide quoted text -
>> - Show quoted text -
> Thanks for that Query Analyzer works well with userid/password
> So does linux commandline tool tsql which allows me to pull back the
> data with a "select * from tablename" - it just needs the !P port
> username and password to logon.
> This works fine.
> However the application I am dealing with does not bring the data back
> <sigh>
> I am not sure how tsql gets the data without a databasename ie i would
> have expected "select * from tablename" to fail and a select * from
> databasename.tablename to succeed - perhaps cos of my Oracle
> experience.
> The application that is failing needs the database name before it will
> log on OK. but "select * from tablename" generates a syntax error near
> FROM - and so does a "select * from databasename.tablename"
>
>|||On 21 Sep, 17:20, "Quentin Ran" <remove_qr...@.yahoo.com> wrote:
> Ton,
> sql server's specification of table in a select statement can have up to
> four parts. In addition to the name itself, you can have / may need three
> qualifiers:
> select column_list from server_name.database_name.schema_name.table_name.
> It is recommended if you are in the database where the table is located, you
> use the schema_name (also know as owner) to qualify the table. If you are
> not in the database where the table is, you need to add the database name
> too, and then (better) specify the schema as well, which can be omitted if
> it is dbo (such as mydb..mytalbe). If you are accessing a liked server, you
> then also need to specify the server name.
> In your case, if your default database is where the table is located, when
> you establish the connection, you can directly run a select * from
> tablename. But select * from databasename.tablename will never work unless
> the databasename is the same as the schema name by accident, in which case
> sql server considers you were doing a select * from schemaname.tablename.
> hth
> Quentin
> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in messagenews:1190390145.227481.285540@.g4g2000hsf.googlegroups.com...
>
> > On 21 Sep, 11:23, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> >> Try connecting to the DB remotely via Query Analyzer. I know you are on
> >> Linux , so can you see if you can connect from a Windows, with the
> >> username/password and run the SELECT . Does it return results?
> >> --
> >> Jack Vamvas
> >> ___________________________________
> >> Need an IT job? http://www.ITjobfeed.com/SQL
> >> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in
> >> messagenews:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
> >> > Hello,
> >> > I am connecting to SQLserver via an application running on linux.
> >> > Under the hood it does a describe which brings back the field types
> >> > for any table OK.
> >> > a select always fails (no data returned) however. I am troubleshooting
> >> > this remotely and have asked the MSSQL dba to run a select with the
> >> > same user id/password that I use on the client - this succeeds.
> >> > There is a firewall between client and db, but I guess this is OK cos
> >> > the describe succeeds.
> >> > Anything I can ask the dba to do or check to help diagnose the
> >> > problem? Does it follow that if userid is good for selects on MS box
> >> > it will be good for remote queries?
> >> > TIA
> >> > Ton- Hide quoted text -
> >> - Show quoted text -
> > Thanks for that Query Analyzer works well with userid/password
> > So does linux commandline tool tsql which allows me to pull back the
> > data with a "select * from tablename" - it just needs the !P port
> > username and password to logon.
> > This works fine.
> > However the application I am dealing with does not bring the data back
> > <sigh>
> > I am not sure how tsql gets the data without a databasename ie i would
> > have expected "select * from tablename" to fail and a select * from
> > databasename.tablename to succeed - perhaps cos of my Oracle
> > experience.
> > The application that is failing needs the database name before it will
> > log on OK. but "select * from tablename" generates a syntax error near
> > FROM - and so does a "select * from databasename.tablename"- Hide quoted text -
> - Show quoted text -
Gosh just what I need to know - thanks very much!
I have an idea I am going to meet a few more problems along the way
tho.
Can I ask the MSSQL dba to capture incoming queries to help me with
troubleshooting BTW?|||yes, get them to run a Profiler trace - asking to then to filter just on
your DatabaseId and Database name.
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"ton de w" <ton_de_winter@.yahoo.co.uk> wrote in message
news:1190581978.259876.259490@.d55g2000hsg.googlegroups.com...
> On 21 Sep, 17:20, "Quentin Ran" <remove_qr...@.yahoo.com> wrote:
>> Ton,
>> sql server's specification of table in a select statement can have up to
>> four parts. In addition to the name itself, you can have / may need
>> three
>> qualifiers:
>> select column_list from server_name.database_name.schema_name.table_name.
>> It is recommended if you are in the database where the table is located,
>> you
>> use the schema_name (also know as owner) to qualify the table. If you
>> are
>> not in the database where the table is, you need to add the database name
>> too, and then (better) specify the schema as well, which can be omitted
>> if
>> it is dbo (such as mydb..mytalbe). If you are accessing a liked server,
>> you
>> then also need to specify the server name.
>> In your case, if your default database is where the table is located,
>> when
>> you establish the connection, you can directly run a select * from
>> tablename. But select * from databasename.tablename will never work
>> unless
>> the databasename is the same as the schema name by accident, in which
>> case
>> sql server considers you were doing a select * from schemaname.tablename.
>> hth
>> Quentin
>> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in
>> messagenews:1190390145.227481.285540@.g4g2000hsf.googlegroups.com...
>>
>> > On 21 Sep, 11:23, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
>> >> Try connecting to the DB remotely via Query Analyzer. I know you are
>> >> on
>> >> Linux , so can you see if you can connect from a Windows, with the
>> >> username/password and run the SELECT . Does it return results?
>> >> --
>> >> Jack Vamvas
>> >> ___________________________________
>> >> Need an IT job? http://www.ITjobfeed.com/SQL
>> >> "ton de w" <ton_de_win...@.yahoo.co.uk> wrote in
>> >> messagenews:1190367783.673561.160950@.22g2000hsm.googlegroups.com...
>> >> > Hello,
>> >> > I am connecting to SQLserver via an application running on linux.
>> >> > Under the hood it does a describe which brings back the field types
>> >> > for any table OK.
>> >> > a select always fails (no data returned) however. I am
>> >> > troubleshooting
>> >> > this remotely and have asked the MSSQL dba to run a select with the
>> >> > same user id/password that I use on the client - this succeeds.
>> >> > There is a firewall between client and db, but I guess this is OK
>> >> > cos
>> >> > the describe succeeds.
>> >> > Anything I can ask the dba to do or check to help diagnose the
>> >> > problem? Does it follow that if userid is good for selects on MS box
>> >> > it will be good for remote queries?
>> >> > TIA
>> >> > Ton- Hide quoted text -
>> >> - Show quoted text -
>> > Thanks for that Query Analyzer works well with userid/password
>> > So does linux commandline tool tsql which allows me to pull back the
>> > data with a "select * from tablename" - it just needs the !P port
>> > username and password to logon.
>> > This works fine.
>> > However the application I am dealing with does not bring the data back
>> > <sigh>
>> > I am not sure how tsql gets the data without a databasename ie i would
>> > have expected "select * from tablename" to fail and a select * from
>> > databasename.tablename to succeed - perhaps cos of my Oracle
>> > experience.
>> > The application that is failing needs the database name before it will
>> > log on OK. but "select * from tablename" generates a syntax error near
>> > FROM - and so does a "select * from databasename.tablename"- Hide
>> > quoted text -
>> - Show quoted text -
> Gosh just what I need to know - thanks very much!
> I have an idea I am going to meet a few more problems along the way
> tho.
> Can I ask the MSSQL dba to capture incoming queries to help me with
> troubleshooting BTW?
>
>

Derived Table Problem

I've created this:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID

ORDER BY
c.ProjectID

and I get this:

ProjectID Registrants Submissions
--- ---- ----
adv_104699 99
adv_1047185 185
adv_110566 66
boh_107134 34

Instead, I want this:

ProjectID Registrants Submissions
--- ---- ----
adv_104699 14
adv_1047185 82
adv_110566 17
boh_107134 12

The "ProjectID" and "Submissions" columns are produced when I run the
derived table (dt, above) as a standalone query. By the same token,
the "Project ID" and "Registrants" columns are produced when I run the
"outer" query, above.

Am I on the right track here?

TIA,

-- Bill[posted and mailed, please reply in news]

Bill (w.white@.snet.net) writes:
> I've created this:
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID

COUNT(dt.Hits) returns the number of rows where this column is not null.
I would guess that you want SUM(dt.Hits) here instead.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9462F13D69581Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Bill (w.white@.snet.net) writes:
> > I've created this:
> > SELECT
> > c.ProjectID,
> > Count(c.ID) as 'Registrants',
> > Count(dt.Hits) as 'Submissions'
> > FROM
> > CME_TBL c
> > JOIN
> > (SELECT ProjectID, Count(*) as Hits FROM CME_TBL
> > WHERE evalDate Is Not NULL OR testDate Is Not NULL
> > GROUP BY ProjectID
> > ) dt
> > ON c.ProjectID = dt.ProjectID
> COUNT(dt.Hits) returns the number of rows where this column is not null.
> I would guess that you want SUM(dt.Hits) here instead.

Using SUM(dt.Hits) yields:

ProjectID Registrants Submissions
--- -- --
adv_104699 1881
adv_1047185 2960
adv_110566 462
boh_107134 952
boh_112238 608
boh_113637 444
brw_106544 1012

which I suspect is closer to my desired result, since the value in the
Submissions column = (Registrants * Submissions) for that ProjectID;
so the proper Submissions value is "in there somewhere". My need is
for the correct Submissions value to appear within the Submissions
column:

ProjectID Registrants Submissions
--- -- --
adv_104699 19
adv_1047185 16
adv_110566 7
boh_107134 28
boh_112238 16
boh_113637 12
brw_106544 23

Happy New Year!

-- Bill|||Bill (w.white@.snet.net) writes:
> Using SUM(dt.Hits) yields:
> ProjectID Registrants Submissions
> --- -- --
> adv_1046 99 1881
> adv_1047 185 2960
> adv_1105 66 462
> boh_1071 34 952
> boh_1122 38 608
> boh_1136 37 444
> brw_1065 44 1012
> which I suspect is closer to my desired result, since the value in the
> Submissions column = (Registrants * Submissions) for that ProjectID;
> so the proper Submissions value is "in there somewhere". My need is
> for the correct Submissions value to appear within the Submissions
> column:
> ProjectID Registrants Submissions
> --- -- --
> adv_1046 99 19
> adv_1047 185 16
> adv_1105 66 7
> boh_1071 34 28
> boh_1122 38 16
> boh_1136 37 12
> brw_1065 44 23

Indeed it seems that diving the Submissions column with the Registratns
column gives the result you are asking for. That is:

SUM(dt.Hits) / COUNT(c.ID)

Moral: when you ask a question like this, it is always a good idea to
provide:

o CREATE TABLE statements of the involved tables.
o INSERT statements with sample data.
o The desired output given the sample.

With this infomation, anyone who takes a stab with your problem can post a
tested solution. Without this information, the answer you get is more or
less guesswork.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9463F1E595160Yazorman@.127.0.0.1>...
> Bill (w.white@.snet.net) writes:
> > Using SUM(dt.Hits) yields:
> > ProjectID Registrants Submissions
> > --- -- --
> > adv_1046 99 1881
> > adv_1047 185 2960
> > adv_1105 66 462
> > boh_1071 34 952
> > boh_1122 38 608
> > boh_1136 37 444
> > brw_1065 44 1012
> > which I suspect is closer to my desired result, since the value in the
> > Submissions column = (Registrants * Submissions) for that ProjectID;
> > so the proper Submissions value is "in there somewhere". My need is
> > for the correct Submissions value to appear within the Submissions
> > column:
> > ProjectID Registrants Submissions
> > --- -- --
> > adv_1046 99 19
> > adv_1047 185 16
> > adv_1105 66 7
> > boh_1071 34 28
> > boh_1122 38 16
> > boh_1136 37 12
> > brw_1065 44 23
> Indeed it seems that diving the Submissions column with the Registratns
> column gives the result you are asking for. That is:
> SUM(dt.Hits) / COUNT(c.ID)
> Moral: when you ask a question like this, it is always a good idea to
> provide:
> o CREATE TABLE statements of the involved tables.
> o INSERT statements with sample data.
> o The desired output given the sample.
> With this infomation, anyone who takes a stab with your problem can post a
> tested solution. Without this information, the answer you get is more or
> less guesswork.

Alrighty, then! Here we go:

CREATE TABLE CME_TBL_dev
(
ID int IDENTITY (1, 1) NOT NULL,
ProjectID varchar (50) NULL,
registrationDate datetime NULL DEFAULT (getdate()),
lastName varchar (60) NULL,
testDate datetime NULL,
evalDate datetime NULL
)

--------------

INSERT INTO CME_TBL_dev
(
ProjectID,
lastName,
testDate,
evalDate
)

SELECT 'pmw_1129', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'pmw_1129', 'wilkins', NULL, NULL
UNION ALL
SELECT 'pmw_1129', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'pmw_1129', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'pmw_1129', 'fife', NULL, NULL
UNION ALL
SELECT 'pmw_1129', 'fonebone', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'brw_1065', 'wilkins', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'brw_1065', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'brw_1065', 'fife', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'fonebone', NULL, NULL
UNION ALL
SELECT 'any_8930', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'any_8930', 'wilkins', NULL, NULL
UNION ALL
SELECT 'any_8930', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'any_8930', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'any_8930', 'fife', NULL, NULL
UNION ALL
SELECT 'any_8930', 'fonebone', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'hir_1093', 'wilkins', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'hir_1093', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'hir_1093', 'fife', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'fonebone', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'yth_9804', 'wilkins', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'yth_9804', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'yth_9804', 'fife', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'fonebone', NULL, NULL

---------------
-- This is the query I'm hoping I can get to yield
-- the desired results (see below).

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL_dev c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID

ORDER BY
c.ProjectID

--------
-- The following two queries are for utility purposes.

SELECT
c.ProjectID, Count(c.ID) as 'Registrants'
FROM
CME_TBL_dev c
GROUP BY
c.ProjectID
ORDER BY
c.ProjectID

--------

SELECT
c.ProjectID, Count(c.ID) as 'Submissions'
FROM
CME_TBL_dev c
WHERE
c.evalDate Is Not NULL OR
c.testDate Is Not NULL
GROUP BY
c.ProjectID
ORDER BY
c.ProjectID

------------

What I seek is this:

ProjectID Registrants Submissions
--- ---- ----
any_89306 3
brw_10656 3
hir_10936 3
pmw_11296 3
yth_98046 3

But what I get instead is this (per the derived table query above):

ProjectID Registrants Submissions
--- ---- ----
any_89306 6
brw_10656 6
hir_10936 6
pmw_11296 6
yth_98046 6

Solving this would have major positive impact on many aspects of my
reporting efforts.

Thanks in advance!

-- Bill|||Bill (w.white@.snet.net) writes:
> -- This is the query I'm hoping I can get to yield
> -- the desired results (see below).
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL_dev c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID
> GROUP BY c.ProjectID
> ORDER BY c.ProjectID
>...
> What I seek is this:
> ProjectID Registrants Submissions
> --- ---- ----
> any_8930 6 3
> brw_1065 6 3
> hir_1093 6 3
> pmw_1129 6 3
> yth_9804 6 3

It does indeed seem that my suggest to take sum divided by count
gives the desired result:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
SUM(dt.Hits) / Count(dt.Hits) as 'Submissions'
FROM
CME_TBL_dev c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID
GROUP BY c.ProjectID
ORDER BY c.ProjectID

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9464EE208FB4EYazorman@.127.0.0.1>...
> Bill (w.white@.snet.net) writes:
> > -- This is the query I'm hoping I can get to yield
> > -- the desired results (see below).
> > SELECT
> > c.ProjectID,
> > Count(c.ID) as 'Registrants',
> > Count(dt.Hits) as 'Submissions'
> > FROM
> > CME_TBL_dev c
> > JOIN
> > (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> > WHERE evalDate Is Not NULL OR testDate Is Not NULL
> > GROUP BY ProjectID
> > ) dt
> > ON c.ProjectID = dt.ProjectID
> > GROUP BY c.ProjectID
> > ORDER BY c.ProjectID
> >...
> > What I seek is this:
> > ProjectID Registrants Submissions
> > --- ---- ----
> > any_8930 6 3
> > brw_1065 6 3
> > hir_1093 6 3
> > pmw_1129 6 3
> > yth_9804 6 3
> It does indeed seem that my suggest to take sum divided by count
> gives the desired result:
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> SUM(dt.Hits) / Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL_dev c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID
> GROUP BY c.ProjectID
> ORDER BY c.ProjectID

Solved it! I took a different approach. I think the crux of my
difficulty lay in "overprocessing" dt.Hits:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
dt.Hits as 'Submissions'
FROM
CME_TBL_dev c,
(
SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt

WHERE
c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID, dt.Hits

ORDER BY
c.ProjectID

Yields:

ProjectID Registrants Submissions
--- ---- ----
any_8930 6 3
brw_1065 6 3
hir_1093 6 3
pmw_1129 6 3
yth_9804 6 3

Thanks for priming my mental pump!

-- Bill

derived table

Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.Your syntax looks fine.
Is it not doing what you expect it to?
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses whe
re
> program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
to
> do this in a query?
> Please help. Thanks.
>
>
>|||Please post the intended results.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
Hi,
what's the syntax for a derived table?
select count(*) from (select hostname, program_name from sysprocesses where
program_name='mydb' group by hostname, program_name) as DB1?
I want to return how many rows after group by hostname, program_name. How to
do this in a query?
Please help. Thanks.|||I got error:
No column was specified for column 3 of 'DB1'?
what's that mean? Thanks.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
> Your syntax looks fine.
> Is it not doing what you expect it to?
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>|||Is 'mydb' the name of the database you want information about?
program_name refers to the application that is accessing the database.
If so:
The Database is in the field dbid.
Try:
select count(*) from (select hostname, program_name from sysprocesses where
dbid=db_id('mydb') group by hostname, program_name) as DB1
"js" <js@.someone@.hotmail.com> wrote in message
news:uuCWWRjFGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> what's the syntax for a derived table?
> select count(*) from (select hostname, program_name from sysprocesses
> where program_name='mydb' group by hostname, program_name) as DB1?
> I want to return how many rows after group by hostname, program_name. How
> to do this in a query?
> Please help. Thanks.
>
>|||You must have posted a different query than what you are trying to run.
That error is telling you that all columns specified in a derived table need
a name, so that they can be referenced by the outer query.
If you had a query like:
select count(*) from (select hostname, program_name, count(1) from
sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
I'd expect the error. Because the third column does not have a referencable
name for the outer query. It would need to be re-written like:
select count(*) from (select hostname, program_name, count(1) as rowCount
from sysprocesses
where
program_name='mydb' group by hostname, program_name) as DB1
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"js" wrote:

> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>|||your post is missing the aggregate for DB1, btw
however, this is the problem: all columsn in in a derived table must
have a name, e.g. [using max(spid) for example]
select count(*) from (select hostname, program_name, max(spid) as spid
from sysprocesses where program_name='mydb' group by hostname,
program_name) as DB1
or
select count(*) from (select hostname, program_name, max(spid)from
sysprocesses where program_name='mydb' group by hostname, program_name)
as DB1 (hostname, program_name, spid)
js wrote:
> I got error:
> No column was specified for column 3 of 'DB1'?
> what's that mean? Thanks.
>
> "Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
> news:43B55608-9A4B-4467-B3D2-286F566712D3@.microsoft.com...
>
>
>|||Thanks Trey.
I got it. need to assign name: count(*) as counters first
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:u3fb5gjFGHA.984@.tk2msftngp13.phx.gbl...
> your post is missing the aggregate for DB1, btw
> however, this is the problem: all columsn in in a derived table must have
> a name, e.g. [using max(spid) for example]
> select count(*) from (select hostname, program_name, max(spid) as spid
> from sysprocesses where program_name='mydb' group by hostname,
> program_name) as DB1
> or
> select count(*) from (select hostname, program_name, max(spid)from
> sysprocesses where program_name='mydb' group by hostname, program_name) as
> DB1 (hostname, program_name, spid)
>
> js wrote:|||Thanks Ryan.
"Ryan Powers" <RyanPowers@.discussions.microsoft.com> wrote in message
news:6026DBD4-29D4-426C-BF58-DCAB853CB8E0@.microsoft.com...
> You must have posted a different query than what you are trying to run.
> That error is telling you that all columns specified in a derived table
> need
> a name, so that they can be referenced by the outer query.
> If you had a query like:
> select count(*) from (select hostname, program_name, count(1) from
> sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> I'd expect the error. Because the third column does not have a
> referencable
> name for the outer query. It would need to be re-written like:
> select count(*) from (select hostname, program_name, count(1) as rowCount
> from sysprocesses
> where
> program_name='mydb' group by hostname, program_name) as DB1
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "js" wrote:
>sql

Derived Table

I am trying to use a derived Table and this will not work for me. Obviously,
I am NOT going to SELECT *, but for demonstration purposes, here it is.
SELECT *
FROM (SELECT Filename,
RACE =
CASE WHEN BRaceAA = 'X' THEN '1,' ELSE '
' END +
CASE WHEN BRaceA = 'X' THEN '2,' ELSE ''
END +
CASE WHEN BRaceB = 'X' THEN '3,' ELSE ''
END +
CASE WHEN BRaceNH = 'X' THEN '4,' ELSE '
' END +
CASE WHEN BRaceW = 'X' THEN '5,' ELSE ''
END
FROM HMDAFromPointIMP
WHERE BRaceAA IS NOT NULL
OR BRaceA IS NOT NULL
OR BRaceB IS NOT NULL
OR BRaceNH IS NOT NULL
OR BRaceW IS NOT NULL)
It is giving me Incorrect Syntax on the last ")"You need to give the derived table an alias, like I demonstrated in an
earlier thread about this same issue:
FROM
(
SELECT /* blah blah */
) x
--^ alias
Please stop starting new threads!
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:34AAF6C5-E9FF-4D7C-A26C-09DDF7223265@.microsoft.com...
>I am trying to use a derived Table and this will not work for me.
>Obviously,
> I am NOT going to SELECT *, but for demonstration purposes, here it is.
> SELECT *
> FROM (SELECT Filename,
> RACE =
> CASE WHEN BRaceAA = 'X' THEN '1,' ELSE '' END +
> CASE WHEN BRaceA = 'X' THEN '2,' ELSE '' END +
> CASE WHEN BRaceB = 'X' THEN '3,' ELSE '' END +
> CASE WHEN BRaceNH = 'X' THEN '4,' ELSE '' END +
> CASE WHEN BRaceW = 'X' THEN '5,' ELSE '' END
> FROM HMDAFromPointIMP
> WHERE BRaceAA IS NOT NULL
> OR BRaceA IS NOT NULL
> OR BRaceB IS NOT NULL
> OR BRaceNH IS NOT NULL
> OR BRaceW IS NOT NULL)
> It is giving me Incorrect Syntax on the last ")"|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OVFSQP2nFHA.420@.TK2MSFTNGP09.phx.gbl...
> Please stop starting new threads!
As a member of the B,NH,W race, I take exception to your suggestion that
new threads not be created! We are a proud race of thread-starters.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||In addition to the syntax error that my other fellow posters have mentioned,
you might want to consider (if you can) changing your table structures.
Having columns like this is messy (as your query demonstrates)
You might want to do something like:
HMDAFromPointIMP
===============
HMDAFromPointIMP_key --pk
<other columns>
HMDAFromPointIMPRace
===================
HMDAFromPointIMP_key --pk
Race_key --pk
Race
====
Race_key --pk
RaceCode --AA, B,NH, etc
Description --
Then adding a new race is easier, and building queries is also much easier.
Just an idea if you are building a new system :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:34AAF6C5-E9FF-4D7C-A26C-09DDF7223265@.microsoft.com...
>I am trying to use a derived Table and this will not work for me.
>Obviously,
> I am NOT going to SELECT *, but for demonstration purposes, here it is.
> SELECT *
> FROM (SELECT Filename,
> RACE =
> CASE WHEN BRaceAA = 'X' THEN '1,' ELSE '' END +
> CASE WHEN BRaceA = 'X' THEN '2,' ELSE '' END +
> CASE WHEN BRaceB = 'X' THEN '3,' ELSE '' END +
> CASE WHEN BRaceNH = 'X' THEN '4,' ELSE '' END +
> CASE WHEN BRaceW = 'X' THEN '5,' ELSE '' END
> FROM HMDAFromPointIMP
> WHERE BRaceAA IS NOT NULL
> OR BRaceA IS NOT NULL
> OR BRaceB IS NOT NULL
> OR BRaceNH IS NOT NULL
> OR BRaceW IS NOT NULL)
> It is giving me Incorrect Syntax on the last ")"|||...and I thought Tiger Woods was supposed to be white, Asian and black. Thi
s
is the fourth socially disturbing thread today. Plus there's approximately 3
0
different races in the world.
This could make such a nice relational model...
ML

Derived Fields

Guys, this is what I want to do. How can I do this ?

Select employid,
Gross=( Select sum (uprtrxam) from Fleet..upr30300 where pyrlrtyp=1),
DedofWages=( Select sum (uprtrxam) from Fleet..upr30300 where
pyrltyp=2),
Gross-DedofWages
from FLEET..UPR00100

The problem is the third field (Gross-DedofWages). It says Invalid
column. Any ideas ?

Thanks, GirishIt doesn't look like your query would give the result you expected
anyway - the subqueries aren't correlated. Try this:

SELECT U.employid, T.gross, T.dedofwages,
T.gross - T.dedofwages
FROM Fleet..upr00100 AS U
LEFT JOIN
(SELECT employid,
CASE WHEN pyrlrtyp=1 THEN uprtrxam END AS gross,
CASE WHEN pyrlrtyp=2 THEN uprtrxam END AS dedofwages
FROM Fleet..upr30300
WHERE pyrlrtyp BETWEEN 1 AND 2
GROUP BY employid) AS T
ON U.empoyid = T.employeid

The rule is that columns in the SELECT list must exist in the base
tables or derived tables. Aliases aren't allowed.

--
David Portas
SQL Server MVP
--|||Oops. Correction:

SELECT U.employid, T.gross, T.dedofwages,
T.gross - T.dedofwages
FROM upr00100 AS U
LEFT JOIN
(SELECT employid,
SUM(CASE WHEN pyrlrtyp=1 THEN uprtrxam END) AS gross,
SUM(CASE WHEN pyrlrtyp=2 THEN uprtrxam END) AS dedofwages
FROM upr30300
WHERE pyrlrtyp BETWEEN 1 AND 2
GROUP BY employid) AS T
ON U.employid = T.employid

--
David Portas
SQL Server MVP
--|||Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things, but the code has to produce the same
results.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.

As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

Think about what a confused mess this statement is in the SQL model.

SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;

That is why such nonsense is illegal syntax.sql

Deploying problem with reports

Hi,

I have two aquestions.

1. How do I select data ovewrite option in SSRS? When I deploy a report i got a following error message.

Warning : Cannot deploy data source mydatasource to the server because it already exists and OverwriteDataSources is not specified.

2. When I try to snapshot a report on report manager, I get a error message.

"Credentials used to run this report are not stored

Then I went to Management Studio and selected the data source and open the report data source property box. I gave a login name and a password under credential stored securely on the server. Can this login name and password be random ones or do they come from smwhere else? Even After I did this still i get the error.

Thanks

Click project -> Properties in your report project. There is an option for OverwriteDataSources. Personally, I use a shared datasource though. Is your datasource shared?

|||

OK thanks In this case no..my data source is not shared........Is there any disadvantage if i select Overwrite option?

what if the data source is shared? still can I used this option?

I'm supposed to create few reports and execute in daily and monthly basis. So which option should I use?

|||If you use a shared datasource then you don't need the overwrite option. If the data source is not shared and you are constantly redeploying it then you want to use the overwrite option.|||OK I understood..Thanks lot

I found the anwer for second question too. If I make snapshots, I can not select impersation option.

Thanks lot

Dependant Dropdown Filters displaying <Select a Value>

I have a few reports that we have filters where our client can select from dropdown menus. There are four menus and each one is dependant on the previous one. The problem is that initially all of the dropdowns are defaulted to <All>, but once you have gone and selected a value in the dropdowns then you go back and change any of the dropdowns, the dropdowns that are then dependant on the dropdown changed revert to <Select a Value> instead of going back to the default assigned of <All>. This is happening to all of the dropdowns dependant on the one changed no matter how many are changed initially or left alone.

Is there any way to revert them back to their assigned default <All> or is this a bug in SQL Server Reporting Services?

Is there anyone who can help me out with this issue?

Thank you for any help you may have.

Dependant Dropdown Filters displaying <Select a Value>

I have a few reports that we have filters where our client can select from dropdown menus. There are four menus and each one is dependant on the previous one. The problem is that initially all of the dropdowns are defaulted to <All>, but once you have gone and selected a value in the dropdowns then you go back and change any of the dropdowns, the dropdowns that are then dependant on the dropdown changed revert to <Select a Value> instead of going back to the default assigned of <All>. This is happening to all of the dropdowns dependant on the one changed no matter how many are changed initially or left alone.

Is there any way to revert them back to their assigned default <All> or is this a bug in SQL Server Reporting Services?

Is there anyone who can help me out with this issue?

Thank you for any help you may have.

Deny select to "everyone" except object owner

Hello,
I want to create a table that has denies all select, update, delete
access for all users except the owner of the table. This isn't really
hard to do, however what I'm trying to accomplish is that access is
also denied from the SQL Server Manager, meaning the dbo must also be
denied.
Is this possible? I've tried to deny select access for the object on
the dbo, however this doesn't do anything...
Thanks in advance for any answers.
Best regards,Not possible - you cannot deny any permissions to the owner of the
database (ie. the dbo user within the database). And, as an extension
to that, you cannot deny any permissions in any database to members of
the sysadmin server role as they're implicitly owners of every database.
You can, however, create a login on the server, make it the owner of the
database (ie. the login will map to the dbo user within the database)
and then disable that login so nobody can use it. That would
effectively deny access to anything within the database to the dbo user
since that user would not even be able to connect to the SQL instance.
*mike hodgson*
http://sqlnerd.blogspot.com
Peter wrote:

>Hello,
>I want to create a table that has denies all select, update, delete
>access for all users except the owner of the table. This isn't really
>hard to do, however what I'm trying to accomplish is that access is
>also denied from the SQL Server Manager, meaning the dbo must also be
>denied.
>Is this possible? I've tried to deny select access for the object on
>the dbo, however this doesn't do anything...
>Thanks in advance for any answers.
>
>Best regards,
>
>

DENY SELECT on SCHEMA issue

Hi,
I want to deny select on many views/table so that my excel users won't
access them.
1. USING DENY SELECT ON VIEWS/TABLE
deny select on sys.all_columns to loana
deny select on sys.all_objects to loana
...etc...
-> This works fine but takes ages for going throgh all the views/tables
2. USING DENY SELECT ON SCHEMA
deny select on SCHEMA::sys to loana
-> This would do what I want in a unique statement but it does not
work. What I have wrong with the deny select on SCHEMA statement?
Note:
I use SQL server 2005 that I want to connect from Excel 2002/2003 Pivot
TableI just posted the similar question in the sqlserver.security newsgroup...
Having the same problem here. We want to suppress INFORMATION_SCHEMA and
sys Schemas without having to issue Deny statements on each object within.
Bill Blakey
<christophe.leroquais@.gmail.com> wrote in message
news:1150295767.920118.51280@.f6g2000cwb.googlegroups.com...
> Hi,
>
> I want to deny select on many views/table so that my excel users won't
> access them.
> 1. USING DENY SELECT ON VIEWS/TABLE
> deny select on sys.all_columns to loana
> deny select on sys.all_objects to loana
> ...etc...
> -> This works fine but takes ages for going throgh all the views/tables
>
> 2. USING DENY SELECT ON SCHEMA
> deny select on SCHEMA::sys to loana
> -> This would do what I want in a unique statement but it does not
> work. What I have wrong with the deny select on SCHEMA statement?
>
> Note:
> I use SQL server 2005 that I want to connect from Excel 2002/2003 Pivot
> Table
>

DENY permission

Hi ,
i have set up to db roles "Admin" which have been
granted select , delete , insert , update
and "test" which has been granted select and DENY update
i have set up a user "user1" account which is part of
the "Admin" and "test" db role.
In this case will "user1" be denied the ability to
UPDATE ?
AND if i also set up user1 as a system administrator
fixed server role besides the 2 db role above , will the
DENY update has any effect on user1 ?
thks & rdgs
Hi,
Answer to your queries:-
In this case will "user1" be denied the ability to UPDATE ?
Ans: - Yes, User will not be able to select and update.
if i also set up user1 as a system administrator fixed server role besides
the 2 db role above , will the
DENY update has any effect on user1 ?
Ans: - Sysadmin role will override all the roles or Deny permissions. The
user with SYADMIN role can do any functionality in the SQL server
as well as all databases.
Thanks
Hari
MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:0d4a01c484cd$c77bfbd0$a301280a@.phx.gbl...
> Hi ,
> i have set up to db roles "Admin" which have been
> granted select , delete , insert , update
> and "test" which has been granted select and DENY update
> i have set up a user "user1" account which is part of
> the "Admin" and "test" db role.
> In this case will "user1" be denied the ability to
> UPDATE ?
> AND if i also set up user1 as a system administrator
> fixed server role besides the 2 db role above , will the
> DENY update has any effect on user1 ?
> thks & rdgs
|||IN addition permissions are cumulative across all of the roles, etc which
apply to a user, THEN subtract out all of the DENY permissions... ( Deny
overrides a grant.)
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
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:0d4a01c484cd$c77bfbd0$a301280a@.phx.gbl...
> Hi ,
> i have set up to db roles "Admin" which have been
> granted select , delete , insert , update
> and "test" which has been granted select and DENY update
> i have set up a user "user1" account which is part of
> the "Admin" and "test" db role.
> In this case will "user1" be denied the ability to
> UPDATE ?
> AND if i also set up user1 as a system administrator
> fixed server role besides the 2 db role above , will the
> DENY update has any effect on user1 ?
> thks & rdgs

DENY permission

Hi ,
i have set up to db roles "Admin" which have been
granted select , delete , insert , update
and "test" which has been granted select and DENY update
i have set up a user "user1" account which is part of
the "Admin" and "test" db role.
In this case will "user1" be denied the ability to
UPDATE ?
AND if i also set up user1 as a system administrator
fixed server role besides the 2 db role above , will the
DENY update has any effect on user1 ?
thks & rdgsHi,
Answer to your queries:-
In this case will "user1" be denied the ability to UPDATE ?
Ans: - Yes, User will not be able to select and update.
if i also set up user1 as a system administrator fixed server role besides
the 2 db role above , will the
DENY update has any effect on user1 ?
Ans: - Sysadmin role will override all the roles or Deny permissions. The
user with SYADMIN role can do any functionality in the SQL server
as well as all databases.
Thanks
Hari
MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:0d4a01c484cd$c77bfbd0$a301280a@.phx.gbl...
> Hi ,
> i have set up to db roles "Admin" which have been
> granted select , delete , insert , update
> and "test" which has been granted select and DENY update
> i have set up a user "user1" account which is part of
> the "Admin" and "test" db role.
> In this case will "user1" be denied the ability to
> UPDATE ?
> AND if i also set up user1 as a system administrator
> fixed server role besides the 2 db role above , will the
> DENY update has any effect on user1 ?
> thks & rdgs|||IN addition permissions are cumulative across all of the roles, etc which
apply to a user, THEN subtract out all of the DENY permissions... ( Deny
overrides a grant.)
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
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:0d4a01c484cd$c77bfbd0$a301280a@.phx.gbl...
> Hi ,
> i have set up to db roles "Admin" which have been
> granted select , delete , insert , update
> and "test" which has been granted select and DENY update
> i have set up a user "user1" account which is part of
> the "Admin" and "test" db role.
> In this case will "user1" be denied the ability to
> UPDATE ?
> AND if i also set up user1 as a system administrator
> fixed server role besides the 2 db role above , will the
> DENY update has any effect on user1 ?
> thks & rdgs

DENY permission

Hi ,
i have set up to db roles "Admin" which have been
granted select , delete , insert , update
and "test" which has been granted select and DENY update
i have set up a user "user1" account which is part of
the "Admin" and "test" db role.
In this case will "user1" be denied the ability to
UPDATE ?
AND if i also set up user1 as a system administrator
fixed server role besides the 2 db role above , will the
DENY update has any effect on user1 ?
thks & rdgsHi,
Answer to your queries:-
In this case will "user1" be denied the ability to UPDATE ?
Ans: - Yes, User will not be able to select and update.
if i also set up user1 as a system administrator fixed server role besides
the 2 db role above , will the
DENY update has any effect on user1 ?
Ans: - Sysadmin role will override all the roles or Deny permissions. The
user with SYADMIN role can do any functionality in the SQL server
as well as all databases.
Thanks
Hari
MCDBA
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:0d4a01c484cd$c77bfbd0$a301280a@.phx.gbl...
> Hi ,
> i have set up to db roles "Admin" which have been
> granted select , delete , insert , update
> and "test" which has been granted select and DENY update
> i have set up a user "user1" account which is part of
> the "Admin" and "test" db role.
> In this case will "user1" be denied the ability to
> UPDATE ?
> AND if i also set up user1 as a system administrator
> fixed server role besides the 2 db role above , will the
> DENY update has any effect on user1 ?
> thks & rdgs|||IN addition permissions are cumulative across all of the roles, etc which
apply to a user, THEN subtract out all of the DENY permissions... ( Deny
overrides a grant.)
--
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
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:0d4a01c484cd$c77bfbd0$a301280a@.phx.gbl...
> Hi ,
> i have set up to db roles "Admin" which have been
> granted select , delete , insert , update
> and "test" which has been granted select and DENY update
> i have set up a user "user1" account which is part of
> the "Admin" and "test" db role.
> In this case will "user1" be denied the ability to
> UPDATE ?
> AND if i also set up user1 as a system administrator
> fixed server role besides the 2 db role above , will the
> DENY update has any effect on user1 ?
> thks & rdgs