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 SQL DBA and BI magazines to order

Hi...I would like to order some magazines about SQL DBA and BI. would
anybody suggest some good options?
Thanks.SQL Magazine would be my first choice. http://www.sqlmag.com
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<yvette.ye@.gmail.com> wrote in message
news:48ba86d8-e399-40d0-bfa6-f1fa41e404a1@.y21g2000hsf.googlegroups.com...
> Hi...I would like to order some magazines about SQL DBA and BI. would
> anybody suggest some good options?
> Thanks.|||On Apr 22, 6:27=A0pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> SQLMagazinewould be my first choice. =A0http://www.sqlmag.com
> --
> Andrew J. Kelly =A0 =A0SQL MVP
> Solid Quality Mentors
> <yvette...@.gmail.com> wrote in message
> news:48ba86d8-e399-40d0-bfa6-f1fa41e404a1@.y21g2000hsf.googlegroups.com...
>
> > Hi...I would like to order some magazines about SQL DBA and BI. would
> > anybody suggest some good options?
> > Thanks.- Hide quoted text -
> - Show quoted text -
Any other options, other than SQLMag? Is there any magazine for BI
(Business Intelligence)?|||SQL Mag does have BI content as well but I don't know of any BI specific
magazines related to SQL Server off hand.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
<yvette.ye@.gmail.com> wrote in message
news:dfa0ad5f-a16f-4eae-88d6-3197c8933efa@.x35g2000hsb.googlegroups.com...
On Apr 22, 6:27 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> SQLMagazinewould be my first choice. http://www.sqlmag.com
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> <yvette...@.gmail.com> wrote in message
> news:48ba86d8-e399-40d0-bfa6-f1fa41e404a1@.y21g2000hsf.googlegroups.com...
>
> > Hi...I would like to order some magazines about SQL DBA and BI. would
> > anybody suggest some good options?
> > Thanks.- Hide quoted text -
> - Show quoted text -
Any other options, other than SQLMag? Is there any magazine for BI
(Business Intelligence)?

benefits to use collations BIN vs windows

Hi
which are the real benefits to use collations BIN vs for example
Latin1_General_Ci_AS.
Or only it is for the order of the data, this always utilizing types of data
unicode.
ThankOn Tue, 9 Jan 2007 09:28:01 -0800, sqlextreme wrote:

>Hi
>which are the real benefits to use collations BIN vs for example
>Latin1_General_Ci_AS.
>Or only it is for the order of the data, this always utilizing types of dat
a
>unicode.
Hi sqlextreme,
The most important consideration should be how you generally want to
compare data. What do the users expect? Do they expect to find
"Washington" when entering "washington" as search string? Should
"Dsseldorf" be returned when searching "Dusseldorf"? Would they be
surprised to find "aaron" after "Zelazny" if the data is supposed to be
ordered?
The only advantage of using binary collation that I know of is that it's
(slightly) faster.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Behavior of OPTION

we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
now there is a complaint that these procs are running quite slow. while
trying to figure out the problem for the sluggishness
of the procedure, i noticed that commenting this line produced different
plan while uncommenting this line produced quite a
different one. Also, the usage of indexes varied drastically between these
plans (commented v/s uncommented OPTION clause)
can anybody throw some light on why using or NOT using OPTION would change
the execution plan?
thx in advanceHi
I prefer to remove hints when upgrading significant versions as things like
changes to the Query Optimizer may mean you are not using specific features
and as you have found they may be slower.
It is also a good idea to benchmark the system before and after the upgrade
to make sure that it is performing better!
It also usually a good time to review the code for general "good practices"
such as declaring temporary tables a the start of a procedure, reducing the
use of unnecessary temporary tables, owner prefixes for tables and stored
procedures, correct error and transaction handling etc...
Make sure that indexes and statistics are in place and rebuilt.
You can then look at the code and the query plans for the slower procedures.
John
"paraa" wrote:

> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>|||parasda
Firstly , BOL has a pretty good explanation about all options that you can
use
Actually ,FORCE ORDER option specifies that the join order indicated by the
query syntax is preserved during query optimization. In fact , query
optimizer is free to decide in what order (join) ( believe me, it is smart
enough) to execute the query im more efficient way ,so by using this option
you limit the optimizer to create an efficient execution plan
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:12F9AE7A-C7F4-4228-88C1-C1C5E35FA47B@.microsoft.com...
> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>

BEGINNER'S QUESTION: SORTING IN ALPHABETICAL ORDER

Hi

I am trying to convert an Access back end into a SQL Server back end and cannot work out how to convert a query that sorts addresses that start with numbers into alphabetical order.

In Access I defined a function that converted the full address to one without the initial numbers eg:
66 Acacia Gardens to Acacia Gardens

I then used this function as an expression in the field column of the query and set it to sort by this field. This put the rows into alphabetical order. (A second function was used to return just the number which was used as the second sort).

I am converting the Access query into a stored procedure. I have successfully converted the Access number removal function into a separate stored procedure and I was intending to execute it from within the main stored procedure but I cannot work out how to do this. I don't think you can insert an Execute statement into a field as you can with a function in Access.

Code for working number removal stored procedure:

ALTER PROCEDURE spRemoveInitialNumbers
@.JobName varchar(20), @.FirstNumbersRemoved varchar(20) OUTPUT
As
DECLARE @.Chopped varchar(20)
DECLARE @.CharacterCount tinyint
DECLARE @.CharacterCode tinyint
SET @.Chopped = @.JobName
IF Substring(@.Chopped,1,5) = 'Unit '
Begin
SET @.Chopped = Right(@.Chopped, Len(@.Chopped)-5)
End
ELSE
SET @.CharacterCount = 1
WHILE @.CharacterCount<=Len(@.Chopped)
BEGIN
SET @.CharacterCode=Ascii(Substring(@.Chopped,@.CharacterCount,1))
If @.CharacterCode>64 and @.CharacterCode<91
Begin
SET @.Chopped=Right(@.Chopped, Len(@.Chopped) - @.CharacterCount + 1)
Break
End
Else
Set @.CharacterCount = @.CharacterCount + 1
End
SET @.FirstNumbersRemoved=@.Chopped

Code for non-working main stored procedure:

SELECT dbo.tblJobs.JobID, dbo.tblJobs.JobNumber, dbo.tblJobs.Surveyor, dbo.tblClients.ClientName, dbo.tblJobs.JobName, dbo.tblJobs.Description,
dbo.tblJobs.Status, dbo.tblJobs.UrgencyCode, dbo.tblJobs.EstimatedFeesOutstanding, dbo.tblJobs.[Live/Dead],
[Intended to Execute SpRemoveInitialNumbers here] AS Expr1,
SUM(dbo.tblFeeAccounts.TotalLessVATandExpenses - dbo.tblFeeAccounts.SubContactors) AS TotalNet, dbo.tblJobs.InstructionDate
FROM dbo.tblJobs INNER JOIN
dbo.tblClients ON dbo.tblJobs.ClientID = dbo.tblClients.ClientID LEFT OUTER JOIN
dbo.tblFeeAccounts ON dbo.tblJobs.JobID = dbo.tblFeeAccounts.JobID
GROUP BY dbo.tblJobs.JobNumber, dbo.tblJobs.Surveyor, dbo.tblClients.ClientName, dbo.tblJobs.JobName, dbo.tblJobs.Description, dbo.tblJobs.Status,
dbo.tblJobs.UrgencyCode, dbo.tblJobs.EstimatedFeesOutstanding, dbo.tblJobs.[Live/Dead], dbo.tblJobs.InstructionDate, dbo.tblJobs.JobID
HAVING (dbo.tblJobs.[Live/Dead] = N'D')
ORDER BY dbo.tblClients.ClientName, dbo.tblJobs.JobName

I would like to undertake the sort on the server, not client side hence my attempt to use stored procedures. I'm not sure that I am going about this the right way. Can anyone help me?

Many thanks.

Whitebeam

Please put the logic of SpRemoveInitialNumbers SP into a TSQL scalar user-defined function. You can then call it from the SELECT list. Alternatively, you can also embed the required substring / charindex / patindex expression in the SELECT list itself for better performance. ( I am assuming you use such functions to remove the initial numbers).

beginner in SQL Server 2000

Hi,
i'm a beginner in sql server 2000 and i have a question.
i want to store word, excel, pdf etc documents in order to search them
for specific content. i don't want to get as result lines of the
documents but the names of them.What data type i must use? can i search
and for Metadata information and how? i will really appreciate any
answer.
Thanks in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
use the image datatype.
Have a look at
http://www.indexserverfaq.com/blobs.htm for more information on how to do
this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Bill,
Yes, you can store word, excel, pdf etc documents in order to search them
for specific content (as in Full-text) the contents of the MS Word files
stored in an SQL Table's FT-enable IMAGE column. As you're are using SQL
Sever 2000, you will need to add a "file extension" column to your table and
populate it with the file extension value, for example "doc" or ".doc" for
MS Word documents. Note, this "file extension" column must be defined as
sysname, char(3) or varchar(4) in order for the MSSearch service to
correctly identify the file type to be indexed.
Additionally, below is a SQL script example of using TextCopy.exe (ships
with SQL Server 2000) to import MS Word documents into SQL Sever table and
IMAGE column and then using SQLFTS CONTAINS or FREETEXT to search the
contents of that MS word document:
use pubs
go
if exists (select * from sysobjects where id = object_id('FTSTable'))
drop table FTSTable
go
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol char(3) NULL, -- can be either sysname or char(3)
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Insert data... (Note: Initalizing IMAGE column with 0xFFFFFFFF for use
with TextCopy.exe)
INSERT FTSTable values('Test TEXT Data for row 1', 0xFFFFFFFF, 'doc', NULL)
go
-- Select data
SELECT * from FTSTable
go
declare @.query varchar(200)
-- Insert MS_Word_document.doc into Row 5 !!
-- NOTE: Ensure the correct path for textcopy.exe!!
set @.query = 'D:\MSSQL80\MSSQL$SQL80\Binn\textcopy /s '+@.@.servername+' /u sa
/p<password> /d pubs /t FTSTable /c ImageCol /f
D:\SQLFiles\Shiloh\<MS_Word>.doc /i /k 5000 /w "where KeyCol=1"'
print @.query
exec master..xp_cmdshell @.query
go
-- Select data
SELECT * from FTSTable
go
-- FTI
use pubs
go
exec sp_fulltext_database 'enable' -- only do this once!
go
-- Drop FTI, if necessary...
-- exec sp_fulltext_table 'FTSTable','drop'
-- exec sp_fulltext_Catalog 'FTSCatalog','drop'
exec sp_fulltext_catalog 'FTSCatalog','create'
exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX'
exec sp_fulltext_column 'FTSTable','ImageCol','add', 0x0409, 'ExtCol'
exec sp_fulltext_column 'FTSTable','TextCol','add'
exec sp_fulltext_table 'FTSTable', 'activate'
go
-- Start FT Indexing...
exec sp_fulltext_catalog 'FTSCatalog','start_full'
go
-- Wait for FT Indexing to complete and check NT/Win2K Application log for
success/errors..
-- Search for search_word_here in MS_Word
select KeyCol, ImageCol from FTSTable where
contains(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Search for search_word_here in MS_Word file...
select KeyCol, ImageCol from FTSTable where
freetext(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Remove FT Indexes & Catalog & table..
exec sp_fulltext_table 'FTSTable','drop'
exec sp_fulltext_Catalog 'FTSCatalog','drop'
drop table FTSTable
Note, that to FT Search the Metadata info contained in the documents, such
as author, title, you wll need to programmaticlly extract this information
and store it in a textual column and then FT Index these columns in addition
to the document column.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!