Best strategy? How to filter Report results

Hi,

I’m using SQL Server 2000.

I need some help on what strategy to adopt in order to filter some existing reports that I have written. I’m basically a web developer (ASP.NET) with some ? T-SQL skills (therefore I need advice on which is the best strategy please).

The business system I’m working on has 2 levels of access: Administrators (there can be several) and Users (20,000+).

Users can login and do stuff (do a task).

Administrators can login and give tasks to the Users to do. Administrators can also run reports to see the status of the Users relating to a particular task. A report on task X will show a list of all Users who have been given that same task (example of a task: “attend to a conference”) and next to each User will be the status (e.g. “Completed” or “Not yet completed”).

That is the existing system (above). Now I need to add a new type of person who can login and view the same reports as the administrator. This type of person will be called a “Manager”. The difference is that the Manager will only be able to view information on a defined set of Users. There might be a few hundred “Managers”.

So my “Wizard web page” code that allows an Administrator to create a Manager account will allow the Administrator to select (from a list of Users) which Users this Manager is allowed to see.

This means I’m going to have a new TABLE to relate the Manager to the User. I’ll call this the tblReporterUser table.

This table will have a ReporterID and UserID (read strategy #1 for reason this is not called “ManagerID”).

Here are the strategies I can think of. Which is best or is there one even better?

Strategy #1

As well as filling the tblReporterUser with Manager to User relationships, also fill it will Administrator to User relationships.

This means that when I call the Stored Procedure to select the rows of my report I need to pass in the ReporterID (which will actually be a ManagerID if it’s a Manager running the report, or an AdministratorID if it is an Administrator).

In order to filter Users out of the report I would then use this ReporterID to JOIN against the tblReporterUser TABLE.

Disadvantages: Each time Users are added to the system I have to make sure all administrators get permission to view the new User (add a row to tblReporterUser TABLE for each administrator with the new UserID).

Also it might make reporting for administrators slower?

Strategy #2

Explained with some pseudo-code (I don’t like this because of the “repeated code” as its easy to get into maintenance problems):

If (RoleIsAdministrator) THEN

-- role is Administrator

Existing report code

ELSE

-- role is Manager

Existing report code

JOIN on the tblReporterUser using the ManagerID as the ReporterID

ENDIF

Strategy #3

Is there a way to do a “conditional” JOIN ON the tblReporterUser if the “role” is Manager (but don’t do the JOIN if the role is Administrator).

If there is a way then this sounds like the best solution.

I have no idea how to do a conditional join – could someone give me an example please?

Massive thanks in advance to anyone replying with any amount of help.

Cheers,

Just back from 5 days camping. It rained! :D But in Australia that is a good thing!!!!

I guess this is a *BUMP* for this thread. If anyone has any ideas about "conditional JOINs" please help.

Many thanks,

Chiz

sql

Best Practices for Insert/Update/Delete

for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...

My 2 cents... SPs are a great way to interface with a database. For me, I will never access a DB in any other way. In fact, I would say it is good practice to secure the database so that only the defined stored procedures can be Executed against the DB. No direct table reads or writes. This will ensure that no one (other than an errant DBA) can do anything other than what is inteded by the interface provided through the stored procedures. It is essentially just another layer in the application model. Also, it provides a level of reusability, and design hiding... generally considered good things. An application will essentially only need to make "function calls" on the database, rather than some nasty select statement that, oh, by the way, I split this table out into two seperate ones, so now you have to go rewrite all your queries in every application that was ever written that uses my database because I didn't just write stored procedures in the first place, which, looking back would have been smart because then I would only have to fix things in two places.

// of course, you probably could create a view with the same name as the old table to fix it, but, just making a point

|||

I think you should use Stored Procedures whenever possible. But for making sure that the value in a column is unique you should always use unique constraints etc. For insert/update/delete always use SPs.

sql

Best Practices (Forms/Letters)

I have a rather large number of already existing forms and letters (in paper
format) that I need to recreate in Reporting Services. After reading a few
posts with regards to formatting etc I have noted that several recommend not
using Text Boxes/Labels but rather tables.
As I'm very new to RS could somebody please advise as to the best possible
way to go about creating these.
Often there is a few lines of text then something that needs to be populated
from the database then another few lines of text etc.
Any help on this would be greatly appreciated.On Sun, 3 Apr 2005 18:03:02 -0700, "Nat Johnson"
<NatJohnson@.discussions.microsoft.com> wrote:
>I have a rather large number of already existing forms and letters (in paper
>format) that I need to recreate in Reporting Services. After reading a few
>posts with regards to formatting etc I have noted that several recommend not
>using Text Boxes/Labels but rather tables.
>As I'm very new to RS could somebody please advise as to the best possible
>way to go about creating these.
>Often there is a few lines of text then something that needs to be populated
>from the database then another few lines of text etc.
>Any help on this would be greatly appreciated.
>
Nat,
Can you explain more about what you are trying to do (ignoring what
the appropriate software might or might not be).
On the face of it, I suspect that Reporting Services may not be the
optimal solution. But I would prefer to better understand your
objective. Forms, for example, in my terminology are used to collect
data from users. I assume that is not part of your aim?
Andrew Watt
MVP - InfoPath|||The company I work for are creating an application which includes an SQL
database. I have to use RS for the reports that are required. Most of the
reports are the traditional style (eg lineflow looking) but others are
letters or forms that the system will automatically print out if a certain
condition is met.
The forms that will be printed will have parts that will be populated from
the database. The web team for the project are creating the online forms. I
have to make my forms similar to theirs for printing out as once the parts
are populated from db they need to be printed so a user can take them to a
site for hand completion.
Most of the letters that need to be printed out need to get data from the
db. And will be automatically printed by the system when a condition is met.
The normal reports that need to be created are not the problem. My question
really relates to the forms and letters and the formatting of these.
Let me know if this helps you anymore or not.
cheers
Nat
"Andrew Watt [MVP - InfoPath]" wrote:
> On Sun, 3 Apr 2005 18:03:02 -0700, "Nat Johnson"
> <NatJohnson@.discussions.microsoft.com> wrote:
> >I have a rather large number of already existing forms and letters (in paper
> >format) that I need to recreate in Reporting Services. After reading a few
> >posts with regards to formatting etc I have noted that several recommend not
> >using Text Boxes/Labels but rather tables.
> >
> >As I'm very new to RS could somebody please advise as to the best possible
> >way to go about creating these.
> >
> >Often there is a few lines of text then something that needs to be populated
> >from the database then another few lines of text etc.
> >
> >Any help on this would be greatly appreciated.
> >
> Nat,
> Can you explain more about what you are trying to do (ignoring what
> the appropriate software might or might not be).
> On the face of it, I suspect that Reporting Services may not be the
> optimal solution. But I would prefer to better understand your
> objective. Forms, for example, in my terminology are used to collect
> data from users. I assume that is not part of your aim?
> Andrew Watt
> MVP - InfoPath
>|||You should be able to render to PDF and print the PDF documents.
If the documents are to be completed by hand that ought to work for
you.
Almost certainly there will be some tweaking involved to get the
appearance that everybody is happy with.
Andrew Watt
MVP - InfoPath
On Mon, 4 Apr 2005 13:05:03 -0700, "Nat Johnson"
<NatJohnson@.discussions.microsoft.com> wrote:
>The company I work for are creating an application which includes an SQL
>database. I have to use RS for the reports that are required. Most of the
>reports are the traditional style (eg lineflow looking) but others are
>letters or forms that the system will automatically print out if a certain
>condition is met.
>The forms that will be printed will have parts that will be populated from
>the database. The web team for the project are creating the online forms. I
>have to make my forms similar to theirs for printing out as once the parts
>are populated from db they need to be printed so a user can take them to a
>site for hand completion.
>Most of the letters that need to be printed out need to get data from the
>db. And will be automatically printed by the system when a condition is met.
>The normal reports that need to be created are not the problem. My question
>really relates to the forms and letters and the formatting of these.
>Let me know if this helps you anymore or not.
>cheers
>Nat
>"Andrew Watt [MVP - InfoPath]" wrote:
>> On Sun, 3 Apr 2005 18:03:02 -0700, "Nat Johnson"
>> <NatJohnson@.discussions.microsoft.com> wrote:
>> >I have a rather large number of already existing forms and letters (in paper
>> >format) that I need to recreate in Reporting Services. After reading a few
>> >posts with regards to formatting etc I have noted that several recommend not
>> >using Text Boxes/Labels but rather tables.
>> >
>> >As I'm very new to RS could somebody please advise as to the best possible
>> >way to go about creating these.
>> >
>> >Often there is a few lines of text then something that needs to be populated
>> >from the database then another few lines of text etc.
>> >
>> >Any help on this would be greatly appreciated.
>> >
>> Nat,
>> Can you explain more about what you are trying to do (ignoring what
>> the appropriate software might or might not be).
>> On the face of it, I suspect that Reporting Services may not be the
>> optimal solution. But I would prefer to better understand your
>> objective. Forms, for example, in my terminology are used to collect
>> data from users. I assume that is not part of your aim?
>> Andrew Watt
>> MVP - InfoPath

Best Method to Document Database?

I have SQL Server 2005 Developer edition. What is the best way to create a
data dictionary for an existing database? Does reporting services or other
tools provide canned reports that include all objects including keys,
constraints etc? Do I need to write my own queries to generate the meta
data or do I need to use another vendor's custom app?
txOn Wed, 22 Feb 2006 12:03:34 -0800, Simon Shutter wrote:

> I have SQL Server 2005 Developer edition. What is the best way to create
a
> data dictionary for an existing database? Does reporting services or othe
r
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
Personally, I'd use another vendors too... ApexSQL Doc is what I'm
currently evaluating|||Simon Shutter wrote:
> I have SQL Server 2005 Developer edition. What is the best way to create
a
> data dictionary for an existing database? Does reporting services or othe
r
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
You might want to try SchemaToDoc at http://www.schematodoc.com. It's
output is a Word document that includes information about your tables
(primary keys), fields (type, size, defaults, nullable), indexes, check
constraints, foreign key constraints, stored procedures, and views. It
also lets you annotate your tables and fields, and include those
annotations in the Word output.

Best Method to Document Database?

I have SQL Server 2005 Developer edition. What is the best way to create a
data dictionary for an existing database? Does reporting services or other
tools provide canned reports that include all objects including keys,
constraints etc? Do I need to write my own queries to generate the meta
data or do I need to use another vendor's custom app?
txOn Wed, 22 Feb 2006 12:03:34 -0800, Simon Shutter wrote:
> I have SQL Server 2005 Developer edition. What is the best way to create a
> data dictionary for an existing database? Does reporting services or other
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
Personally, I'd use another vendors too... ApexSQL Doc is what I'm
currently evaluating|||Simon Shutter wrote:
> I have SQL Server 2005 Developer edition. What is the best way to create a
> data dictionary for an existing database? Does reporting services or other
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
You might want to try SchemaToDoc at http://www.schematodoc.com. It's
output is a Word document that includes information about your tables
(primary keys), fields (type, size, defaults, nullable), indexes, check
constraints, foreign key constraints, stored procedures, and views. It
also lets you annotate your tables and fields, and include those
annotations in the Word output.

Best Method to Document Database?

I have SQL Server 2005 Developer edition. What is the best way to create a
data dictionary for an existing database? Does reporting services or other
tools provide canned reports that include all objects including keys,
constraints etc? Do I need to write my own queries to generate the meta
data or do I need to use another vendor's custom app?
tx
On Wed, 22 Feb 2006 12:03:34 -0800, Simon Shutter wrote:

> I have SQL Server 2005 Developer edition. What is the best way to create a
> data dictionary for an existing database? Does reporting services or other
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
Personally, I'd use another vendors too... ApexSQL Doc is what I'm
currently evaluating
|||Simon Shutter wrote:
> I have SQL Server 2005 Developer edition. What is the best way to create a
> data dictionary for an existing database? Does reporting services or other
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
You might want to try SchemaToDoc at http://www.schematodoc.com. It's
output is a Word document that includes information about your tables
(primary keys), fields (type, size, defaults, nullable), indexes, check
constraints, foreign key constraints, stored procedures, and views. It
also lets you annotate your tables and fields, and include those
annotations in the Word output.

Best design for a service that will monitor db

We have an existing database that is constantly receiving updated
information on the status and attributes of specific objects within the
application in batches. As these records come in, there is portions of the
table that they populate that are intentionally left empty, because the data
for these fields is retrieved from a seperate Java application through a
published web service (on same network). We are constructing a .Net service
which will handle the retrieval of records from the Java app and push the
new data into the relevant fields.
We are currently designing the .NET service to check the database on a
predefined interval, to see if any new records have appeared that need to be
looked up in the Java application. However, it would be preferable (at
least for testing) if this interaction could be designed so that the Sql
Server 2000 database could notify the .Net service that a new batch of
records has arrived (push instead of pull). Does anyone have any knowledge
if there is a means by which this can be accomplished?
Thanks.Hmmm ... There surely is a notificaiton service in SQL Server but as far as
I've read the documentation it maynot be suitable for this problem ...
But I think you can write a trigger that can call a DTS package or a Jobs
framework to do this notification ... I think this can also be one solution
...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"nfalconer" <navid@.gci.net> wrote in message
news:vm4o27o29cu596@.corp.supernews.com...
> We have an existing database that is constantly receiving updated
> information on the status and attributes of specific objects within the
> application in batches. As these records come in, there is portions of
the
> table that they populate that are intentionally left empty, because the
data
> for these fields is retrieved from a seperate Java application through a
> published web service (on same network). We are constructing a .Net
service
> which will handle the retrieval of records from the Java app and push the
> new data into the relevant fields.
> We are currently designing the .NET service to check the database on a
> predefined interval, to see if any new records have appeared that need to
be
> looked up in the Java application. However, it would be preferable (at
> least for testing) if this interaction could be designed so that the Sql
> Server 2000 database could notify the .Net service that a new batch of
> records has arrived (push instead of pull). Does anyone have any
knowledge
> if there is a means by which this can be accomplished?
> Thanks.
>

Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
it
inside
>
>.
>

Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
--
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
>> Hi,
>> This could be a basic question. But still I want to get
it
>> clarified.
>> Normally if an ALTER or UPDATE command been tried with a
>> non existing column, SQL Server throws error. To avoid
>> this, the practice is to place the alter statements
inside
>> an IF EXISTS() block. Only if the column exists/not
>> exists, the alter would get executed.
>> I tried using the same technique for an UPDATE. I still
>> get the error inspite I have an exists() check.
>> In the below given example, The statement1 (ALTER) does
>> not throw any error while the statement2(Update)
>> throws error.
>> Could somebody please explain the reason behind this?
>> Regards,
>> J.P. Job
>>
>> Eg:.
>> USE PUBS
>> go
>> --Statement1
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> ALTER TABLE AUTHORS DROP COLUMN DUMMY
>> go
>> --Statement2
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> Update AUTHORS
>> SET DUMMY = 'TEST'
>> go
>
>.
>