Help with simple query involving 3 tables

Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:

create table [User_Tbl](
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[Password] varchar(100),
)
go

create table [Printer_Tbl(
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[PaperCost] int
)
go

create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
go

I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @.MinDate - @.MaxDate)

Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--- ------ ----- ------ --
HR 5 7 12 .84
Finance 10 15 25 1.75

So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.

One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!SELECT U.name, sum(J.NumberPrintedPages), sum(J.NumberPrintedPages * P.PaperCost)
FROM Jobs J INNER JOIN User_Tbl U ON J.User_ID = U.ID
INNER JOIN Printer_Tbl P ON J.Printer_ID = P.ID
GROUP BY U.Name

This example shows, how to join your tables, and how to return two of your fields. Since a user may have used 0 to n printers in his jobs, there isn't a clear indication of your fields #PagesPrinter1 and #PagesPrinter2.|||He wants a crosstab query.

Nicomachus, look up CROSSTAB in Books Online and you will see an excelent example of how to accomplish this using CASE statements. Unfortunately, it requires considerable programming to make your crosstab queries dynamic as the number of columns (printers) changes.
Supposedly this feature will be built into the next version of SQL Server, but in any case when you make your output dynamic you are going to have a hard time building reports around it, because the output format will not be consistent.

You are really best served by outputting your data in a standard normalized format and then letting your reporting application (Crystal, Access, whatever...) handle formatting as a crosstab.

help with select query

I need help with a select query.I have a VisualBasic application using Access.

ACCESS.300611111 = LabelsPrinted.FirstBoxNbr
this is what I will be searching a screen by,but at the moment this is completely ignored and it get a list of all the results

SELECT CustomerDetails.CustomerCode, CustomerDetails.CustomerName, LabelsPrinted.PrintedWhen, LabelsPrinted.FirstBoxNbr, LabelsPrinted.DispatchTime FROM CustomerDetails INNER JOIN LabelsPrinted ON CustomerDetails.CustomerID = LabelsPrinted.CustomerID WHERE (((LabelsPrinted.PrintedWhen) Is Not Null) AND ((LabelsPrinted.DispatchTime) Is Null))

How could I change this query,if possible, to include that field LablesPrinted.FirstBoxNbr LIKE (%1%) but I only this to be able to look at this much ACCESS.3006 from the field and do a successful search, obivously that code can change and be something like (ORSTEW.3006)

By the way Access only stores data, Comms.ini is where we keep all the queries.

I hope this makes sense.
ThanksInstead of using LablesPrinted.FirstBoxNbr LIKE (%1%), you should use LablesPrinted.FirstBoxNbr = ("*1*").

Paulo

Help with Security!

I have a web application for which I am required to authenticate users at the database level (no generic or application type logins permitted). I am not permitted to use Active Directory because we do not have AD installed. We chose to use standard SQL accounts. I have two groups of users:

1. Normal users
2. Super Users (can do everything a normal user can do, plus can add/delete/modify user accounts)

When a Super User is created, they are added to three fixed roles Security Administrator (Server Role) and db_accessadmin and db_securityadmin (Database Roles).

A normal user is assigned to some custom roles that we created, but is not assigned to any fixed roles (database or server) other than the default Public role.

The problem comes when a Super User attempt to add another Super user. The process fails because the Super user does not have sufficient privileges to run sp_addrolemember. The following two statements fail because of permissions:

sp_addrolemember 'db_securityadmin', N'mySuperUser'
sp_addrolemember 'db_accessadmin', N'mySuperUser'

Additional research indicates that I am required to be a member of the SysAdmin fixed role of the db_Owner role in order to have access to sp_addrolemember.

Does anyone have any suggestions for a workaround? This is pretty frustrating. I am unwilling to let my Super Users have sysadmin or db_owner rights. These grant far more access than is needed. I just want my super users to be able to add and administer normal user accounts and other Super User accounts.

Thanks,

Hugh ScottI think you are using SQL2K, if so how about using Application roles? I have not done this before but it might be worth checking out. Look up Application Roles in BOL.

My theory is that you could setup an application role with dbo authority. When you need to create a superuser you would make an additional connection to the db using an application role, create the super user and then drop the connection.|||Ding!

You are correct. I should have stated that we were using SQL 2K. I like your idea and I will give it a shot.

Thanks!

Hugh

Originally posted by Paul Young
I think you are using SQL2K, if so how about using Application roles? I have not done this before but it might be worth checking out. Look up Application Roles in BOL.

My theory is that you could setup an application role with dbo authority. When you need to create a superuser you would make an additional connection to the db using an application role, create the super user and then drop the connection.sql

Help with security model for RS implementation needed

We're running Reporting Services (wSP1) on a Win 2003 server box. Presently
(for development), SQL Server, the web application and RS all run on the
same box. I've configured an app pool in IIS under which Reports,
ReportServer and the web application run. I'm also collecting credentials
via forms auth which I pass as the credentials to RS during web service
calls. We are using URL access to access rendered reports.
RS Windows Service is configured to run as NT AUTH\Network Service.
All datasources are set up using trusted security.
What I'd like to be able to do to ensure that we use connection pooling is
not impersonate the credentials passed in but instead connect to the OLAP
database as a single domain account.
Is this possible and if so, what security configuration changes should I
make to make this happen?
Thanks in advance.
-TimPlease disregard my original post. The absurd amounts of caffeine I've been
consuming lately have caused temporary memory loss. :)
-Tim
"Tim Ellison" <TimEllison@.direcway.com> wrote in message
news:Oajl$kstEHA.1400@.TK2MSFTNGP11.phx.gbl...
> We're running Reporting Services (wSP1) on a Win 2003 server box.
Presently
> (for development), SQL Server, the web application and RS all run on the
> same box. I've configured an app pool in IIS under which Reports,
> ReportServer and the web application run. I'm also collecting credentials
> via forms auth which I pass as the credentials to RS during web service
> calls. We are using URL access to access rendered reports.
> RS Windows Service is configured to run as NT AUTH\Network Service.
> All datasources are set up using trusted security.
> What I'd like to be able to do to ensure that we use connection pooling is
> not impersonate the credentials passed in but instead connect to the OLAP
> database as a single domain account.
> Is this possible and if so, what security configuration changes should I
> make to make this happen?
> Thanks in advance.
> -Tim
>

Help with multiple counts..

I have a application table that I have turned into a cube. It includes attributes of a organizaiton name, month, and year. This all works fine.

I would like to add another table to the cube that counts a visits. It also has attributes of organization name, month and year.

I am confused where I make the associations so they can operate in the same cube.

Do I join the tables in the datasource and just add the measure in the cube? Do I add a new dimention in the cube?

Any help in this area would be great. I am basically looking for a cube like so..

Month Year

Organization Name ApplicationCount (From the application table)

Visit Count (From the visit table)

Thanks in advance,

Mardo

If it has a similar structure, just a different measure, then you could add this table as a separate measure group with a measure in it that maps to the Visit Count. If these two table are completely identical you might be able to join them with a view or a named query in the DSV so that Visit Count is just added as an extra column to the existing cube, this might be a more efficient approach.

help with locking

Hi,
I have an application that calls a strored procedure that return a record
built from several tables joined.
This data is then present to the user in application where they can then
edit and save.
The problem I have is that the as the user changes different bits of data,
it will be updating the table that holds that specific data. If the user
changes all the data, it would update several SQL tables.
I want to be able to put all this into a transaction, so that if their is a
problem, I can just rollback the transaction, and all the data put in
numerous different tables will be rolled back.
The problem is that I want to lock a couple of the records in a few of the
tables at the point the stored procedure does the select.
An example would be that if one user is working in record_id 92, then nobody
else can load that single row at the same time.
Whilst a user is editing a record in the application, with that record
locked, i want all the other users to be able to select records from the
table.
What I am seeing is if I force a lock, such as an xLock, then the entire
table is locked, selects are suspended until i either commit or rollback my
transaction. (the select with the xlock clause is executed with a sql
transaction)
Hope that makes sense, and somebody is able to help.
Using a timestamp, and comparing before updating is not really an option as
there are many table underneath that would all have to be timestamp checked,
and that would seem a poor way of doing this.
ThanksCan you specify ROWLOCK instead of XLOCK in your SELECT statement?
I am not sure whether this is what you want.
Leo Leong
"Aussie Rules" wrote:

> Hi,
> I have an application that calls a strored procedure that return a record
> built from several tables joined.
> This data is then present to the user in application where they can then
> edit and save.
> The problem I have is that the as the user changes different bits of data,
> it will be updating the table that holds that specific data. If the user
> changes all the data, it would update several SQL tables.
> I want to be able to put all this into a transaction, so that if their is
a
> problem, I can just rollback the transaction, and all the data put in
> numerous different tables will be rolled back.
> The problem is that I want to lock a couple of the records in a few of the
> tables at the point the stored procedure does the select.
> An example would be that if one user is working in record_id 92, then nobo
dy
> else can load that single row at the same time.
> Whilst a user is editing a record in the application, with that record
> locked, i want all the other users to be able to select records from the
> table.
> What I am seeing is if I force a lock, such as an xLock, then the entire
> table is locked, selects are suspended until i either commit or rollback m
y
> transaction. (the select with the xlock clause is executed with a sql
> transaction)
> Hope that makes sense, and somebody is able to help.
> Using a timestamp, and comparing before updating is not really an option a
s
> there are many table underneath that would all have to be timestamp checke
d,
> and that would seem a poor way of doing this.
>
> Thanks
>
>|||On Fri, 3 Jun 2005 06:37:13 +0100, Aussie Rules wrote:
(snip)
>Whilst a user is editing a record in the application, with that record
>locked, i want all the other users to be able to select records from the
>table.
>What I am seeing is if I force a lock, such as an xLock, then the entire
>table is locked, selects are suspended until i either commit or rollback my
>transaction. (the select with the xlock clause is executed with a sql
>transaction)
>Hope that makes sense, and somebody is able to help.
Hi Aussie,
How did you conclude that the entire table is locked? Default behaviour
for SQL Server is to use row-level locks, unless such a large proportion
of the rows in the table gets locked that promoting to a table-level
lock makes more sense. For single-row updates or few-row updates, that
should not happen.
However, you should be aware that a locked row won't be skipped if it
has to be read. And some queries have to read more rows than they'll
eventually return.
Take this query for example:
SELECT COUNT(*)
FROM Personnel
WHERE Sex = 'M'
If the sex column is indexed and the optimizer decides to use that index
to evaluate this query, then this query will return data quickly, even
if ALL rows of female personnel are currently locked. However, if there
is no index on sex (or if the optimizer chooses a plan that bypasses
this index), then a lock on even one of the female employees' rows will
effectively block this query - the database has to read the row in order
to check the WHERE condition, so it will wait until the lock is
released, then fetch the row, check the sex column and discard it from
the results.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 3 Jun 2005 06:37:13 +0100, "Aussie Rules"
<someone@.somewhere.com> wrote:
>What I am seeing is if I force a lock, such as an xLock, then the entire
>table is locked, selects are suspended until i either commit or rollback my
>transaction. (the select with the xlock clause is executed with a sql
>transaction)
Try an updlock, locks just the row(s).
But watch out for what Hugo says.

>Using a timestamp, and comparing before updating is not really an option as
>there are many table underneath that would all have to be timestamp checked
,
>and that would seem a poor way of doing this.
This should not really be a problem in most data models. Your app
should be structured enough that tables are always accessed in a
particular order. If they all do the appropriate (pessimistic)
locking, then they will see each others' locks at the parent table
levels, and in general all will be well.
I presume you are doing rich/smart or two-tier client/server
development, it's pretty much impossible to do true pessimistic
locking for stateless (!) web apps.
I have built entire apps against SQLServer using pessimistic locking
as you describe, it can be done, though it's terribly unpopular these
days, and BOL even recommends against it!
Josh