[SQL2000] permissions to use view based on tables from many databases

Hi
I have two databases: Customers and Operations. In Customers database I have
made a view based on a few tables from both Customers and Operations (left
join - customers without any operations). In the same database (Customers) I
have created a stored procedure based on the view. Finally I'd like to give
to some users permission only to exec the stored procedure.
Have I to add the users to Customers? If yes, please describe me how to
limit the users privileges only to execution the stored procedure (no rights
to open tables or view from Customers).
Regards,
Grzegorz
Ps. I had sent the post on microsoft.public.sqlserver.security, but I had no
answer.By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.
Andrew J. Kelly SQL MVP
"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dteimk$1b7$1@.inews.gazeta.pl...
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and Operations
> (left join - customers without any operations). In the same database
> (Customers) I have created a stored procedure based on the view. Finally
> I'd like to give to some users permission only to exec the stored
> procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I had
> no answer.|||Well, it works fine in situations when all tables are in the same database,
but it doesn't work when tables are in two databases. If user have no rights
to read source table from other database SQL Server shows error:
"SELECT permission denied on object 'CustomersData', database 'Customers',
owner 'dbo'."
Grzegorz
Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> By default when you add a user they do not have any permissions to do
> anything. So just make sure you don't add them to any of the server or
> database roles. Then simply GRANT them execute permission on that sp.
> --
> Andrew J. Kelly SQL MVP|||Are the objects owned by the same owner in both db's? If so you may have to
specify rights on the other tables. Is Cross database Ownership chaining
turned on?
http://support.microsoft.com/?kbid=810474
Andrew J. Kelly SQL MVP
"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dtf589$5h3$1@.inews.gazeta.pl...
> Well, it works fine in situations when all tables are in the same
> database, but it doesn't work when tables are in two databases. If user
> have no rights to read source table from other database SQL Server shows
> error:
> "SELECT permission denied on object 'CustomersData', database 'Customers',
> owner 'dbo'."
> Grzegorz
>
> Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
> wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Thanks, it works.
Grzegorz
Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:O07NN2uNGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Are the objects owned by the same owner in both db's? If so you may have
> to specify rights on the other tables. Is Cross database Ownership
> chaining turned on?
> http://support.microsoft.com/?kbid=810474
> --
> Andrew J. Kelly SQL MVP
>
> "Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
> news:dtf589$5h3$1@.inews.gazeta.pl...
>|||It is a in SQL that permission is check on the first called element i.e.
in case of stored procedure the there's a check made if the user has
the right to execute a procedure. Thus it's possiblie to allow a user to
execure a procedure and e.g. display a subset of data without allowing
to read tables content. Up to sp3 it was valid for the whole server.
From sp3 you've got to set cross-databse chaining explicitly.
Grzegorz Danowski wrote:
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and
> Operations (left join - customers without any operations). In the same
> database (Customers) I have created a stored procedure based on the
> view. Finally I'd like to give to some users permission only to exec the
> stored procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I
> had no answer.

[SQL2000] permissions to use view based on tables from many databases

Hi

I have two databases: Customers and Operations. In Customers database I have
made a view based on a few tables from both Customers and Operations (left
join - customers without any operations). In the same database (Customers) I
have created a stored procedure based on the view. Finally I'd like to give
to some users permission only to exec the stored procedure.

Have I to add the users to Customers? If yes, please describe me how to
limit the users privileges only to execution the stored procedure (no rights
to open tables or view from Customers).

Regards,
Grzegorz

Ps. I had sent the post on microsoft.public.sqlserver.security, but I had no
answer.By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.

--
Andrew J. Kelly SQL MVP

"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dteimk$1b7$1@.inews.gazeta.pl...
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and Operations
> (left join - customers without any operations). In the same database
> (Customers) I have created a stored procedure based on the view. Finally
> I'd like to give to some users permission only to exec the stored
> procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I had
> no answer.|||Well, it works fine in situations when all tables are in the same database,
but it doesn't work when tables are in two databases. If user have no rights
to read source table from other database SQL Server shows error:
"SELECT permission denied on object 'CustomersData', database 'Customers',
owner 'dbo'."

Grzegorz

Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> By default when you add a user they do not have any permissions to do
> anything. So just make sure you don't add them to any of the server or
> database roles. Then simply GRANT them execute permission on that sp.
> --
> Andrew J. Kelly SQL MVP|||Are the objects owned by the same owner in both db's? If so you may have to
specify rights on the other tables. Is Cross database Ownership chaining
turned on?

http://support.microsoft.com/?kbid=810474

--
Andrew J. Kelly SQL MVP

"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dtf589$5h3$1@.inews.gazeta.pl...
> Well, it works fine in situations when all tables are in the same
> database, but it doesn't work when tables are in two databases. If user
> have no rights to read source table from other database SQL Server shows
> error:
> "SELECT permission denied on object 'CustomersData', database 'Customers',
> owner 'dbo'."
> Grzegorz
>
> Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
> wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
>> By default when you add a user they do not have any permissions to do
>> anything. So just make sure you don't add them to any of the server or
>> database roles. Then simply GRANT them execute permission on that sp.
>>
>> --
>> Andrew J. Kelly SQL MVP|||Thanks, it works.
Grzegorz

Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:O07NN2uNGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Are the objects owned by the same owner in both db's? If so you may have
> to specify rights on the other tables. Is Cross database Ownership
> chaining turned on?
> http://support.microsoft.com/?kbid=810474
> --
> Andrew J. Kelly SQL MVP
>
> "Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
> news:dtf589$5h3$1@.inews.gazeta.pl...
>> Well, it works fine in situations when all tables are in the same
>> database, but it doesn't work when tables are in two databases. If user
>> have no rights to read source table from other database SQL Server shows
>> error:
>> "SELECT permission denied on object 'CustomersData', database
>> 'Customers', owner 'dbo'."
>>
>> Grzegorz
>>
>>
>> Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
>> wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
>>> By default when you add a user they do not have any permissions to do
>>> anything. So just make sure you don't add them to any of the server or
>>> database roles. Then simply GRANT them execute permission on that sp.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>|||It is a in SQL that permission is check on the first called element i.e.
in case of stored procedure the there's a check made if the user has
the right to execute a procedure. Thus it's possiblie to allow a user to
execure a procedure and e.g. display a subset of data without allowing
to read tables content. Up to sp3 it was valid for the whole server.
From sp3 you've got to set cross-databse chaining explicitly.

Grzegorz Danowski wrote:
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and
> Operations (left join - customers without any operations). In the same
> database (Customers) I have created a stored procedure based on the
> view. Finally I'd like to give to some users permission only to exec the
> stored procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I
> had no answer.

[RFI] Sql Server 2k- Error Handling strategy

I am currently trying to put together a document that would outline the
Error handling strategy for a SQL Server 2k based project.
The solution is based on some DTS (mainly ActiveX scripts) but mostly
Stored procedures are doing the bulk of the work.
As I am new to this technology so I thought I would ask someone on the
list that has experience in this area.
Therefore, I am looking for some material/tips/documentation such as
best Practices, Do's & Don't's, issues to consider etc.
or anything that you might think would be useful.Uzy
http://www.sommarskog.se/error-handling-II.html
"Uzy" <usmanlatif77@.gmail.com> wrote in message
news:1131619531.055286.192370@.z14g2000cwz.googlegroups.com...
>I am currently trying to put together a document that would outline the
> Error handling strategy for a SQL Server 2k based project.
> The solution is based on some DTS (mainly ActiveX scripts) but mostly
> Stored procedures are doing the bulk of the work.
> As I am new to this technology so I thought I would ask someone on the
> list that has experience in this area.
> Therefore, I am looking for some material/tips/documentation such as
> best Practices, Do's & Don't's, issues to consider etc.
> or anything that you might think would be useful.
>|||Thanks Uri
Uzy wrote:
> I am currently trying to put together a document that would outline the
> Error handling strategy for a SQL Server 2k based project.
> The solution is based on some DTS (mainly ActiveX scripts) but mostly
> Stored procedures are doing the bulk of the work.
> As I am new to this technology so I thought I would ask someone on the
> list that has experience in this area.
> Therefore, I am looking for some material/tips/documentation such as
> best Practices, Do's & Don't's, issues to consider etc.
> or anything that you might think would be useful.

[RFI] Sql Server 2k- Error Handling strategy

I am currently trying to put together a document that would outline the
Error handling strategy for a SQL Server 2k based project.
The solution is based on some DTS (mainly ActiveX scripts) but mostly
Stored procedures are doing the bulk of the work.
As I am new to this technology so I thought I would ask someone on the
list that has experience in this area.
Therefore, I am looking for some material/tips/documentation such as
best Practices, Do's & Don't's, issues to consider etc.
or anything that you might think would be useful.Uzy
http://www.sommarskog.se/error-handling-II.html
"Uzy" <usmanlatif77@.gmail.com> wrote in message
news:1131619531.055286.192370@.z14g2000cwz.googlegroups.com...
>I am currently trying to put together a document that would outline the
> Error handling strategy for a SQL Server 2k based project.
> The solution is based on some DTS (mainly ActiveX scripts) but mostly
> Stored procedures are doing the bulk of the work.
> As I am new to this technology so I thought I would ask someone on the
> list that has experience in this area.
> Therefore, I am looking for some material/tips/documentation such as
> best Practices, Do's & Don't's, issues to consider etc.
> or anything that you might think would be useful.
>

[RFI] Sql Server 2k- Error Handling strategy

I am currently trying to put together a document that would outline the
Error handling strategy for a SQL Server 2k based project.
The solution is based on some DTS (mainly ActiveX scripts) but mostly
Stored procedures are doing the bulk of the work.
As I am new to this technology so I thought I would ask someone on the
list that has experience in this area.
Therefore, I am looking for some material/tips/documentation such as
best Practices, Do's & Don't's, issues to consider etc.
or anything that you might think would be useful.
Uzy
http://www.sommarskog.se/error-handling-II.html
"Uzy" <usmanlatif77@.gmail.com> wrote in message
news:1131619531.055286.192370@.z14g2000cwz.googlegr oups.com...
>I am currently trying to put together a document that would outline the
> Error handling strategy for a SQL Server 2k based project.
> The solution is based on some DTS (mainly ActiveX scripts) but mostly
> Stored procedures are doing the bulk of the work.
> As I am new to this technology so I thought I would ask someone on the
> list that has experience in this area.
> Therefore, I am looking for some material/tips/documentation such as
> best Practices, Do's & Don't's, issues to consider etc.
> or anything that you might think would be useful.
>

Getting Unique entire row records based on one unique column..

Hi,

I like to get all records(all columns) for any employee who has more than 2 unique SuggestedTraining..As simple as possible and execution (performance optimized) speed is as fast as possible.

Data:

Table structure below

Assessment Table

AssessmentID EmployeeID CourseID SuggestedTraining Date ....more columns
1 4 1 'Training 1' <date>
2 2 2 'Training 2' <date>
3 1 4 'Training 1' <date>
4 2 1 'Training 2' <date>
5 4 2 'Training 1' <date>
6 3 3 '' <date>
7 2 5 'Training 3' <date>
8 5 7 'Training 2' <date>
9 7 4 'Training 2' <date>
10 5 3 'Training 1' <date>
11 5 6 'Training 3' <date>

Desired Result:

AssessmentID EmployeeID CourseID SuggestedTraining Date
4 2 1 'Training 2' <date>
7 2 5 'Training 3' <date>
8 5 7 'Training 2' <date>
10 5 3 'Training 1' <date>
11 5 6 'Training 3' <date>

Try the example below.

Chris

Code Snippet

DECLARE @.AssessmentTable TABLE

(

AssessmentID INT,

EmployeeID INT,

CourseID INT,

SuggestedTraining VARCHAR(20)

)

INSERT INTO @.AssessmentTable(AssessmentID, EmployeeID, CourseID, SuggestedTraining)

SELECT 1, 4, 1, 'Training 1' UNION ALL

SELECT 2, 2, 2, 'Training 2' UNION ALL

SELECT 3, 1, 4, 'Training 1' UNION ALL

SELECT 4, 2, 1, 'Training 2' UNION ALL

SELECT 5, 4, 2, 'Training 1' UNION ALL

SELECT 6, 3, 3, '' UNION ALL

SELECT 7, 2, 5, 'Training 3' UNION ALL

SELECT 8, 5, 7, 'Training 2' UNION ALL

SELECT 9, 7, 4, 'Training 2' UNION ALL

SELECT 10, 5, 3, 'Training 1' UNION ALL

SELECT 11, 5, 6, 'Training 3'

SELECT at.AssessmentID,

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

FROM @.AssessmentTable at

INNER JOIN (SELECT atg.EmployeeID

FROM @.AssessmentTable atg

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

|||

Hi Chris,

I cannot do the insertion because simply I do not know the data at runtime,

Code Snippet

INSERT INTO @.AssessmentTable(AssessmentID, EmployeeID, CourseID, SuggestedTraining)

SELECT 1, 4, 1, 'Training 1' UNION ALL

SELECT 2, 2, 2, 'Training 2' UNION ALL

SELECT 3, 1, 4, 'Training 1' UNION ALL

SELECT 4, 2, 1, 'Training 2' UNION ALL

SELECT 5, 4, 2, 'Training 1' UNION ALL

SELECT 6, 3, 3, '' UNION ALL

SELECT 7, 2, 5, 'Training 3' UNION ALL

SELECT 8, 5, 7, 'Training 2' UNION ALL

SELECT 9, 7, 4, 'Training 2' UNION ALL

SELECT 10, 5, 3, 'Training 1' UNION ALL

SELECT 11, 5, 6, 'Training 3'

The desired result is to get records (all columns) of table based on Employees having more than 2 Suggested Training, empty Suggested Training and Duplicated Training are excluded...

|||

It is just sample data Chris offered to demonstrate his suggested solution.

Run all of the code as Chris posted to verify if the solution works for you.

According to your specifications, the Date is immaterial for the solution. If that is NOT correct, then refine your desired outcome so we can refine the suggested solutions.

|||

Hi,

My question how do I replace this part of script to be adaptable to actual data.

Code Snippet

SELECT 1, 4, 1, 'Training 1' UNION ALL

SELECT 2, 2, 2, 'Training 2' UNION ALL

SELECT 3, 1, 4, 'Training 1' UNION ALL

SELECT 4, 2, 1, 'Training 2' UNION ALL

SELECT 5, 4, 2, 'Training 1' UNION ALL

SELECT 6, 3, 3, '' UNION ALL

SELECT 7, 2, 5, 'Training 3' UNION ALL

SELECT 8, 5, 7, 'Training 2' UNION ALL

SELECT 9, 7, 4, 'Training 2' UNION ALL

SELECT 10, 5, 3, 'Training 1' UNION ALL

SELECT 11, 5, 6, 'Training 3'

Try to replace entire code it with this,

Code Snippet

DECLARE @.AssessmentTable TABLE

(

AssessmentID INT,

EmployeeID INT,

CatalogueCourseID INT,

SuggestedTraining nVARCHAR(500),

CompetencyModelID int

)

Insert Into @.AssessmentTable(AssessmentID, EmployeeID, CatalogueCourseID, SuggestedTraining, CompetencyModelID)

Select AssessmentID, EmployeeID, CatalogueCourseID, SuggestedTraining, CompetencyModelID From Assessment

Where IsActive =1 AND SuggestedTraining <> ''

SELECT at.AssessmentID,at.EmployeeID,at.CatalogueCourseID,at.SuggestedTraining FROM @.AssessmentTable at

INNER JOIN (SELECT atg.EmployeeID FROM @.AssessmentTable atg GROUP BY atg.EmployeeID HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

Actual Result:

AssessmentID EmployeeID CatalogueCourseID SuggestedTraining
9 9 8 Training Course 2
30 9 9 Training Course 3
42 9 9 Training Course 3 << Duplicate
43 9 7 Training Course 1
47 9 7 Training Course 1 << Duplicate
54 9 5 Training
48 12 7 Training Course 1
40 12 7 Training Course 1 << Duplicate
46 12 9 Training Course 3
..... More data below....

Still Desired Result is not achieved...

|||

As Arnie pointed out in a response to your previous post, you only need to use the following:

Code Snippet

SELECT at.AssessmentID,

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

FROM @.AssessmentTable at

INNER JOIN (SELECT atg.EmployeeID

FROM @.AssessmentTable atg

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

The CREATE TABLE / INSERT statements were included in my original post solely to show that the concept works. All you need to do is take the above sample and replace '@.AssessmentTable' with your actual table name.

Chris

|||

Hi Chris,

I tried this code slightly modified it to exclude empty SuggestedTraining, and still there are duplicate Suggested Training in the results. How can I remove duplicated SuggestedTraining in the result set. Thanks.

Code Snippet

SELECT at.AssessmentID, at.EmployeeID, at.CatalogueCourseID, at.SuggestedTraining FROM Assessment at

INNER JOIN (SELECT atg.EmployeeID FROM Assessment atg Where SuggestedTraining <> ''

GROUP BY atg.EmployeeID HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2) t

ON t.EmployeeID = at.EmployeeID

|||

For the duplicates, which AssessmentID would you want to return - the maximum or the minimum? [therein lies a clue to the solution... ]

Chris

|||

Well,

Maximum is the choice, right now date is not considered, how would the sql statement would be if both maximum and date (latest) is considered?

|||

Try the code below.

Chris

Code Snippet

SELECT MAX(at.AssessmentID),

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

--, MAX(at.DateColumn)

FROM @.AssessmentTable at

INNER JOIN (

SELECT atg.EmployeeID

FROM @.AssessmentTable atg

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2

) t ON t.EmployeeID = at.EmployeeID

GROUP BY at.EmployeeID,

at.CourseID,

at.SuggestedTraining

|||

I run the sql script you provided and here is the result:

AssessmentID EmployeeID CourseID SuggestedTraining

26 4 8 Training Course 1
8 4 8 Training Course 2
58 8 NULL <<-- Should Not be included
6 8 16 Training Course 1 <<-- Should Not be included
57 9 NULL <<-- Should Not be included
54 9 5 Training
47 9 7 Training Course 1
9 9 8 Training Course 2
42 9 9 Training Course 3
55 12 NULL <<-- Should Not be included
1 12 3 Training Course 2
48 12 7 Training Course 1
46 12 9 Training Course 3
22 13 NULL <<-- Should Not be included
13 13 NULL TEST TRAIN
41 13 7 Training Course 1
31 13 8 Training Course 2
45 13 9 Training Course 3
16 26 NULL <<-- Should Not be included
18 26 7 Training Course 1 <<-- Should Not be included
53 82 7 Training Course 1
38 82 16 Test Training Course
28 150 7 Training Course 1
27 150 9 Training Course 3
33 179 3 Training Course 2
37 179 16 Test Training Course
36 180 NULL <<-- Should Not be included
35 180 8 Training Course 2 <<-- Should Not be included

I filter the empty Suggested Training using this modified sql script:

Code Snippet

SELECT MAX(at.AssessmentID), at.EmployeeID, at.CatalogueCourseID, at.SuggestedTraining FROM Assessment at

INNER JOIN (SELECT atg.EmployeeID FROM Assessment atg GROUP BY atg.EmployeeID HAVING COUNT

(DISTINCT atg.SuggestedTraining) >= 2) t ON t.EmployeeID = at.EmployeeID Where at.SuggestedTraining <> ''

GROUP BY at.EmployeeID, at.CatalogueCourseID,at.SuggestedTraining

And still did not get desired result...

AssessmentID EmployeeID CourseID SuggestedTraining

26 4 8 Training Course 1
8 4 8 Training Course 2
6 8 16 Training Course 1 <<-- Should Not be included
54 9 5 Training
47 9 7 Training Course 1
9 9 8 Training Course 2
42 9 9 Training Course 3
1 12 3 Training Course 2
48 12 7 Training Course 1
46 12 9 Training Course 3
13 13 NULL TEST TRAIN
41 13 7 Training Course 1
31 13 8 Training Course 2
45 13 9 Training Course 3
18 26 7 Training Course 1 <<-- Should Not be included
53 82 7 Training Course 1
38 82 16 Test Training Course
28 150 7 Training Course 1
27 150 9 Training Course 3
33 179 3 Training Course 2
37 179 16 Test Training Course
35 180 8 Training Course 2 <<-- Should Not be included

Here is Whole Data for Assessment table:

AssessmentID EmployeeID CourseID SuggestedTraining

1 12 3 Training Course 2
3 59 7 Training Course 1
6 8 16 Training Course 1
7 10 3
8 4 8 Training Course 2
9 9 8 Training Course 2
10 132 7 Training Course 1
11 25 NULL Training Course 1
12 12 7 Training Course 1
13 13 NULL TEST TRAIN
14 54 7 Training Course 1
15 28 NULL
16 26 NULL
17 29 NULL
18 26 7 Training Course 1
19 6 NULL
20 84 7 Training Course 1
21 139 7 Training Course 1
22 13 NULL
23 82 7 Training Course 1
24 127 7 Training Course 1
25 64 7 Training Course 1
26 4 8 Training Course 1
27 150 9 Training Course 3
28 150 7 Training Course 1
29 12 7 Training Course 1
30 9 9 Training Course 3
31 13 8 Training Course 2
32 177 NULL
33 179 3 Training Course 2
34 180 8 Training Course 2
35 180 8 Training Course 2
36 180 NULL
37 179 16 Test Training Course
38 82 16 Test Training Course
39 9 7 Training Course 1
40 12 7 Training Course 1
41 13 7 Training Course 1
42 9 9 Training Course 3
43 9 7 Training Course 1
44 13 9 Training Course 3
45 13 9 Training Course 3
46 12 9 Training Course 3
47 9 7 Training Course 1
48 12 7 Training Course 1
49 87 7 Training Course 1
50 9 NULL
51 72 7 Training Course 1
52 83 9 Training Course 1
53 82 7 Training Course 1
54 9 5 Training
55 12 NULL
56 7 NULL
57 9 NULL
58 8 NULL

|||

I've re-arranged the filters in the query to filter out the rows that you don't need - see below. This now returns the results as specified in your previous post.

Chris

Code Snippet

DECLARE @.AssessmentTable TABLE

(

AssessmentID INT,

EmployeeID INT,

CourseID INT,

SuggestedTraining VARCHAR(20)

)

INSERT INTO @.AssessmentTable (AssessmentID, EmployeeID, CourseID, SuggestedTraining)

SELECT 1, 12, 3, 'Training Course 2' UNION ALL

SELECT 3, 59, 7, 'Training Course 1' UNION ALL

SELECT 6, 8, 16, 'Training Course 1' UNION ALL

SELECT 7, 10, 3, '' UNION ALL

SELECT 8, 4, 8, 'Training Course 2' UNION ALL

SELECT 9, 9, 8, 'Training Course 2' UNION ALL

SELECT 10, 132, 7, 'Training Course 1' UNION ALL

SELECT 11, 25, NULL, 'Training Course 1' UNION ALL

SELECT 12, 12, 7, 'Training Course 1' UNION ALL

SELECT 13, 13, NULL, 'TEST TRAIN' UNION ALL

SELECT 14, 54, 7, 'Training Course 1' UNION ALL

SELECT 15, 28, NULL, '' UNION ALL

SELECT 16, 26, NULL, '' UNION ALL

SELECT 17, 29, NULL, '' UNION ALL

SELECT 18, 26, 7, 'Training Course 1' UNION ALL

SELECT 19, 6, NULL, '' UNION ALL

SELECT 20, 84, 7, 'Training Course 1' UNION ALL

SELECT 21, 139, 7, 'Training Course 1' UNION ALL

SELECT 22, 13, NULL, '' UNION ALL

SELECT 23, 82, 7, 'Training Course 1' UNION ALL

SELECT 24, 127, 7, 'Training Course 1' UNION ALL

SELECT 25, 64, 7, 'Training Course 1' UNION ALL

SELECT 26, 4, 8, 'Training Course 1' UNION ALL

SELECT 27, 150, 9, 'Training Course 3' UNION ALL

SELECT 28, 150, 7, 'Training Course 1' UNION ALL

SELECT 29, 12, 7, 'Training Course 1' UNION ALL

SELECT 30, 9, 9, 'Training Course 3' UNION ALL

SELECT 31, 13, 8, 'Training Course 2' UNION ALL

SELECT 32, 177, NULL, '' UNION ALL

SELECT 33, 179, 3, 'Training Course 2' UNION ALL

SELECT 34, 180, 8, 'Training Course 2' UNION ALL

SELECT 35, 180, 8, 'Training Course 2' UNION ALL

SELECT 36, 180, NULL, '' UNION ALL

SELECT 37, 179, 16, 'Test Training Course' UNION ALL

SELECT 38, 82, 16, 'Test Training Course' UNION ALL

SELECT 39, 9, 7, 'Training Course 1' UNION ALL

SELECT 40, 12, 7, 'Training Course 1' UNION ALL

SELECT 41, 13, 7, 'Training Course 1' UNION ALL

SELECT 42, 9, 9, 'Training Course 3' UNION ALL

SELECT 43, 9, 7, 'Training Course 1' UNION ALL

SELECT 44, 13, 9, 'Training Course 3' UNION ALL

SELECT 45, 13, 9, 'Training Course 3' UNION ALL

SELECT 46, 12, 9, 'Training Course 3' UNION ALL

SELECT 47, 9, 7, 'Training Course 1' UNION ALL

SELECT 48, 12, 7, 'Training Course 1' UNION ALL

SELECT 49, 87, 7, 'Training Course 1' UNION ALL

SELECT 50, 9, NULL, '' UNION ALL

SELECT 51, 72, 7, 'Training Course 1' UNION ALL

SELECT 52, 83, 9, 'Training Course 1' UNION ALL

SELECT 53, 82, 7, 'Training Course 1' UNION ALL

SELECT 54, 9, 5, 'Training' UNION ALL

SELECT 55, 12, NULL, '' UNION ALL

SELECT 56, 7, NULL, '' UNION ALL

SELECT 57, 9, NULL, '' UNION ALL

SELECT 58, 8, NULL, ''

SELECT MAX(at.AssessmentID) AS AssessmentID,

at.EmployeeID,

at.CourseID,

at.SuggestedTraining

--, MAX(at.DateColumn)

FROM @.AssessmentTable at

INNER JOIN (

SELECT atg.EmployeeID

FROM @.AssessmentTable atg

WHERE atg.SuggestedTraining <> ''

GROUP BY atg.EmployeeID

HAVING COUNT(DISTINCT atg.SuggestedTraining) >= 2

) t ON t.EmployeeID = at.EmployeeID

WHERE at.SuggestedTraining <> ''

GROUP BY at.EmployeeID,

at.CourseID,

at.SuggestedTraining

ORDER BY 1

|||Thanks Chris, right now, based on data I have, this problem is resolved, when another problem arises...I post it here...I have marked your last reply as answer...

[Feature Request] Jump to Report (Choice of report to the user)

I am trying to build a report with a "jump to report" (with all the
applicable parameters) effect in a table.
But based on user's choice - I somehow want to let them choose which
report they can jump on.
How may I present them the choices?
One idea I had was to create a dummy report that stores all the
parameters, and then provide different report options...You might want to use jump to url instead. Here is an idea: You have a
report that shows the values you want to have the user select from. Just
show it in a table. Have one of the fields (could be an extra field that
says, Detail ... or something like that. Make it blue and underlined. Then
set the advanced properties and use the jump to URL setting the appropriate
parameters. Depending on how complicated it is you might want to create code
behind that assembles the URL string for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Harsh" <harsh.vb@.gmail.com> wrote in message
news:1e70ecdd.0411130826.3d172549@.posting.google.com...
>I am trying to build a report with a "jump to report" (with all the
> applicable parameters) effect in a table.
> But based on user's choice - I somehow want to let them choose which
> report they can jump on.
> How may I present them the choices?
>
> One idea I had was to create a dummy report that stores all the
> parameters, and then provide different report options...|||In Jump to dialog box, where it asked the name of the report to jump to, do
an
IIF(Fields!xxx.Value = "A", "AReport", IIF(Fields!xxx.Value = "B",
"BReport", "")
"Harsh" wrote:
> I am trying to build a report with a "jump to report" (with all the
> applicable parameters) effect in a table.
> But based on user's choice - I somehow want to let them choose which
> report they can jump on.
> How may I present them the choices?
>
> One idea I had was to create a dummy report that stores all the
> parameters, and then provide different report options...
>

[bug?] Hidden parameters

Hello,
I have troubles with hidden parameters. What am I doing:
1) define a report parameter based on a dataset's field (a dataset is
"Stored proc." type, the procedure returns the only string value -- if it is
important)
2) Build & deploy a solution, run the report from Report manager -- it's ok
3) run the report from Report manager, click on "Properties" tab, choose
"parameters":
Has Default - leave checked on
Default Value - (Query Based)
Null - (none)
Prompt User - leave checked on
Prompt String - I cleared this textbox
4) run the report again:
Reporting Services Error
Default value or value provided for the report parameter 'MyParam' is not a
valid value. (rsInvalidReportParameter) Get Online Help
Refresh button on RM or the IE's same button don't change this error message
5) Properties -> Parameters -> Prompt String: type a former prompt string
6) run the report: it works ok, however the aforementioned parameter acts
like it doesn't have Default Value anymore.
What have I do to hide param properly?
(MS RS sp1)
Microsoft SQL Server Reporting Services Version 8.00.878.00
Thanks,
Lucy.Does it have list of available values?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have troubles with hidden parameters. What am I doing:
> 1) define a report parameter based on a dataset's field (a dataset is
> "Stored proc." type, the procedure returns the only string value -- if it
> is
> important)
> 2) Build & deploy a solution, run the report from Report manager -- it's
> ok
> 3) run the report from Report manager, click on "Properties" tab, choose
> "parameters":
> Has Default - leave checked on
> Default Value - (Query Based)
> Null - (none)
> Prompt User - leave checked on
> Prompt String - I cleared this textbox
> 4) run the report again:
> Reporting Services Error
> Default value or value provided for the report parameter 'MyParam' is not
> a
> valid value. (rsInvalidReportParameter) Get Online Help
> Refresh button on RM or the IE's same button don't change this error
> message
> 5) Properties -> Parameters -> Prompt String: type a former prompt string
> 6) run the report: it works ok, however the aforementioned parameter acts
> like it doesn't have Default Value anymore.
> What have I do to hide param properly?
> (MS RS sp1)
> Microsoft SQL Server Reporting Services Version 8.00.878.00
> Thanks,
> Lucy.
>|||Yes. From query. Same to "default".
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Does it have list of available values?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I have troubles with hidden parameters. What am I doing:
> >
> > 1) define a report parameter based on a dataset's field (a dataset is
> > "Stored proc." type, the procedure returns the only string value -- if
it
> > is
> > important)
> > 2) Build & deploy a solution, run the report from Report manager -- it's
> > ok
> > 3) run the report from Report manager, click on "Properties" tab, choose
> > "parameters":
> > Has Default - leave checked on
> > Default Value - (Query Based)
> > Null - (none)
> > Prompt User - leave checked on
> > Prompt String - I cleared this textbox
> > 4) run the report again:
> >
> > Reporting Services Error
> > Default value or value provided for the report parameter 'MyParam' is
not
> > a
> > valid value. (rsInvalidReportParameter) Get Online Help
> >
> > Refresh button on RM or the IE's same button don't change this error
> > message
> >
> > 5) Properties -> Parameters -> Prompt String: type a former prompt
string
> > 6) run the report: it works ok, however the aforementioned parameter
acts
> > like it doesn't have Default Value anymore.
> >
> > What have I do to hide param properly?
> >
> > (MS RS sp1)
> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >
> > Thanks,
> > Lucy.
> >
> >
>|||To summarize, error rsInvalidReportParameter happened because same stored
procedure was used for available values and default value, and this stored
procedure returned unique result for every call.
Report Server was executing this stored procedure twice (1st time for
available values and 2nd time for default value) and was unable to match
default value with available value.
Solution is not to set available values.
Also because you just wanted to have read-only parameter, it is enough to
leave prompt blank, and Report Desinger would set parameter properties
during Deploy procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Yes. From query. Same to "default".
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
>> Does it have list of available values?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Lusy Crown" <evesq@.uk2.net> wrote in message
>> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
>> > Hello,
>> >
>> > I have troubles with hidden parameters. What am I doing:
>> >
>> > 1) define a report parameter based on a dataset's field (a dataset is
>> > "Stored proc." type, the procedure returns the only string value -- if
> it
>> > is
>> > important)
>> > 2) Build & deploy a solution, run the report from Report manager --
>> > it's
>> > ok
>> > 3) run the report from Report manager, click on "Properties" tab,
>> > choose
>> > "parameters":
>> > Has Default - leave checked on
>> > Default Value - (Query Based)
>> > Null - (none)
>> > Prompt User - leave checked on
>> > Prompt String - I cleared this textbox
>> > 4) run the report again:
>> >
>> > Reporting Services Error
>> > Default value or value provided for the report parameter 'MyParam' is
> not
>> > a
>> > valid value. (rsInvalidReportParameter) Get Online Help
>> >
>> > Refresh button on RM or the IE's same button don't change this error
>> > message
>> >
>> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> string
>> > 6) run the report: it works ok, however the aforementioned parameter
> acts
>> > like it doesn't have Default Value anymore.
>> >
>> > What have I do to hide param properly?
>> >
>> > (MS RS sp1)
>> > Microsoft SQL Server Reporting Services Version 8.00.878.00
>> >
>> > Thanks,
>> > Lucy.
>> >
>> >
>>
>|||How do you not set available values...in report designer report params
dialog, my parameter has a name, 'tick', has the non-queried available values
radio button clicked and the list to the right is completely blank, as I want
it to be. This parameter is unique everytime a report is executed and cannot
be selected from a list of existing values...but I'm still getting the
following error when I call the web service render method...
+ System.SystemException {"The value provided for the report parameter
'tick' is not valid for its type. --> The value provided for the report
parameter 'tick' is not valid for its type. --> The value provided for the
report parameter 'tick' is not valid for its type."} System.SystemException
"Lev Semenets [MSFT]" wrote:
> To summarize, error rsInvalidReportParameter happened because same stored
> procedure was used for available values and default value, and this stored
> procedure returned unique result for every call.
> Report Server was executing this stored procedure twice (1st time for
> available values and 2nd time for default value) and was unable to match
> default value with available value.
> Solution is not to set available values.
> Also because you just wanted to have read-only parameter, it is enough to
> leave prompt blank, and Report Desinger would set parameter properties
> during Deploy procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Yes. From query. Same to "default".
> >
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> >> Does it have list of available values?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Lusy Crown" <evesq@.uk2.net> wrote in message
> >> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> >> > Hello,
> >> >
> >> > I have troubles with hidden parameters. What am I doing:
> >> >
> >> > 1) define a report parameter based on a dataset's field (a dataset is
> >> > "Stored proc." type, the procedure returns the only string value -- if
> > it
> >> > is
> >> > important)
> >> > 2) Build & deploy a solution, run the report from Report manager --
> >> > it's
> >> > ok
> >> > 3) run the report from Report manager, click on "Properties" tab,
> >> > choose
> >> > "parameters":
> >> > Has Default - leave checked on
> >> > Default Value - (Query Based)
> >> > Null - (none)
> >> > Prompt User - leave checked on
> >> > Prompt String - I cleared this textbox
> >> > 4) run the report again:
> >> >
> >> > Reporting Services Error
> >> > Default value or value provided for the report parameter 'MyParam' is
> > not
> >> > a
> >> > valid value. (rsInvalidReportParameter) Get Online Help
> >> >
> >> > Refresh button on RM or the IE's same button don't change this error
> >> > message
> >> >
> >> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> > string
> >> > 6) run the report: it works ok, however the aforementioned parameter
> > acts
> >> > like it doesn't have Default Value anymore.
> >> >
> >> > What have I do to hide param properly?
> >> >
> >> > (MS RS sp1)
> >> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >> >
> >> > Thanks,
> >> > Lucy.
> >> >
> >> >
> >>
> >>
> >
> >
>
>