Help writing query - Database Discussion Boards

This helps allot thank you very much. Can i ask you one last thing have any links that show how i can retrieve the data as xml? thank you very much

Just take a look at BOL or google, is not difficult.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Triggers are hell slow don't use it use C2 audit

Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...

softdev_sup wrote:
Triggers are hell

True, if badly written, but we use them regularly for auditing on tables with upwards of 20 million transactions a month withu problems.

softdev_sup wrote:
use C2 audit

Never used it, does it acually record before and after images, user etc? Also I would be worried about it bringing the system down - from MSDN

Important:
C2 audit mode saves a large amount of event information to the log file, which can grow quickly. If the data directory in which logs are being saved runs out of space, SQL Server will shut itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag (which bypasses auditing), or free up additional disk space for the audit log.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

well i tried one software ApexSQLAudit for the auditing sql and it uses triggers and the system performance after using it was so bad almost dead while beofre using this ApexSQLAudit the system was efficient but after this it was very slow and c2 and sql profiler trace did the job

i am confused now because that system is running now more then 2 years and is not crashed but by using the triggers we had to remove ApexSQLAudit within one week because of slowness

Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...

So you used a generic logging system and it slowed your system. No big suprise there. As I said, we use triggers for auditing and have never had performance issues, but we write them ourselves so they are optimised for our system, with the audit tables properly located and indexed etc.

You may have had a bad experience with triggers, but do not assume they are bad, its just a case of using them properly - I have no experience of ApexSQLAudit, but I wouldn't expect some generic product to be as performant as one written specifically for your database by someone who knows what they are doing.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

In Synopsis

Go to Sql Server Management Studio
object Explorer
Select Server Right Click on Server goto Properties

then Navigate To Securities -----> Enable C2 audit trailing

thats all the only disadvantage of this is that it consumes more space on HDD but it is the requirement and now days with the size of HDD it does not matter

Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...

Hello,

I need to write a query.
I have a table with many operators (can take them using a query) and for each of them i need to calculate it's success rate (count fail units query/count all units query). I can do the calculation of the success rate for every operator,
but how can i write a query that will loop through all the operators and show the success rate of each operator (in one table).

The operators and the units (count for fail and all the units) are located in one table in the DB.

The results will be as follows:
there will be an operator columns and success rate column. In the operator column it will write the operators from the table and in the second column it will write the success rate of each operator.

How can i do it?

I use the following query for the success rate (of one operator):
select (convert(float,(select count(distinct jobid)
from v_equipattrrep where ([assigndate] > '01/01/2009' or jobcompletiontime>'01/01/2009') and
[operator] like 'mgroiser' and
equipname like '%fib%' and equipname not like '%test%' and
iscabinet = 0 and utilcatdesc = 'Debug' and labid = '37' and jobresult='success')))/(select count(distinct jobid)
from v_equipattrrep where ([assigndate] > '01/01/2009' or jobcompletiontime>'01/01/2009') and
[operator] like 'mgroiser1' and
equipname like '%fib%' and equipname not like '%test%' and
iscabinet = 0 and utilcatdesc = 'Debug' and labid = '37' and jobresult is not null) *100

I tried to write as follows:
select (convert(float,sum(case when jobresult='success' then 1 else 0 end))/ count(distinct jobid)) * 100
from v_equipattrrep where ([assigndate] > '01/01/2009' or jobcompletiontime>'01/01/2009') and
equipname like '%fib%' and equipname not like '%test%' and
iscabinet = 0 and utilcatdesc = 'Debug' and labid = '37' and jobresult is not null
group by [operator]

But it doesn't work well because i muse use a distinct in the sum(case...) too.

Thats one, no two ugly queries. You have a couple of options, you can move this into a cursor and insert the operator information into a table variable. I hate cursors and would not recommend this.

I would move the operator information out of the where clause into the select areas and join the 2 result sets by operator. This will allow you to use group by operator to get the results without using a cursor.

Research GROUP BY and INNER JOIN

You are probably going to have to muck with the calc as well.

Never underestimate the power of human stupidity
RAH

static query executes fine but there is problem when I make it dynamic.please ignore logic of query,only consider syntax

    set @sSQL = 'select s.first_name from sfm_students s
               left outer join
               (select seq_id from sfm_classes)c
               on c.seq_id = s.sfm_classes_seq_id'
print @sSQL
    exec @sSQL

when executing above procedure
For Example

exec GetInfo

following error produce

Msg 203, Level 16, State 2, Procedure GetInfo, Line 15
The name 'select s.first_name from sfm_students s
                   left outer join 
                   (select seq_id from sfm_classes)c 
                   on c.seq_id = s.sfm_classes_seq_id' is not a valid identifier.

modified on Friday, August 28, 2009 5:15 AM

try
exec (getinfo)

Never underestimate the power of human stupidity
RAH

Hi
I have a senario in which I need to move the data from one database to another depending upon some conditions so that it will reduce some load on one database.For example we have A and B database and lets say lots of new data comes into database A daily.Now i want to put a logic so that some of infrequently used data in database A can be moved to database B on a daily basis.Once data is moved from A database to B databse data will be deleted from A thus reducing load on database A.so daily this process goes on and data from A will be moved to B.So B contains all the data that is moved from A daily and no data of previous day is overwritten on B.Will replication be helpful in this senario ?

What you are talking about is ARCHIVING, and replication can be useful here. You need to identify the database you are using, the strategy will be different for the type (SQL Server/Oracle/ MySQL do not relate to Access).

Personally we use an SSIS (SQL Server) package to move the data from the active DB to the archive DB in the low processing period (2am in our case) and do any other maintenance required.

You might also look into creating an OLAP cube as you archive destination.

Never underestimate the power of human stupidity
RAH

Check this article out ...

This might be what you are looking to accomplish.

http://www.databasejournal.com/features/mssql/article.php/3638236/Data-partitioning-in-SQL-Server-2005---Part-I.htm[^]

Hi all,

Is there something like ## in sqlserver related to scope of temprory tables.??
which decides the scope of the temp tables??
thanks

dipak

Yes, a single # creates a temp table only available to the connection it was created on, a ## prefix makes temp table available to all connections.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Have a look at this
http://www.sqlteam.com/article/temporary-tables

http://www.sqlservercentral.com/articles/Basic+Querying/temptablesinsqlserver/1279/

Coding C#

ExciteTemplate

I am running into a problem and need another set of eyes.
I am in the middle of building a database to support logins to my website (username & password)
There will be different roles with different persmissions.
Certain roles will have corresponding content in other tables.

For example I have a buyer table, I also have a buyer role.

Then I have a memberrep table that contains a record for anyone who can login to the system.
One table for all logins - no mater the role.

I need to connect a single buyer to a single login in the memberrep table.
I will have the same issue with the sellers and the salesreps. Each will have a one to one relation to a record in the memberep table.

Am I better off linking directly to the table without the 3rd table?

Or can I simply add the 3 or 4 columns into the memberrep table like buyerID, sellerID, salesrepID, ect.
And then only populate the ID of the login that relates to the external table?
I am at a loss here.. Is there another way to make this work?

Here is a screen shot of what I have already.
Any issues jump right out?

http://i654.photobucket.com/albums/uu269/squeakita/dbs/gpMemberstables.jpg[^]

Any suggestions?

Hi,

I'm not a DB expert, but your drawing looks way too complex. I see a lot of duplicate field names, which to me is typical for a poor design.

For starters, I would have a PERSON table, holding a person ID, and all info regarding that person, independent of his role(s); so fname, lname, phone, email, etc.

Then, I would have a PERMISSIONS table, holding an role ID, a role name, and all the permissions; not sure whether I would choose many permission columns, or use several rows with only one permission field (and when in doubt several rows/1 column often is the right choice).

And finally I would have a ROLES table, holding a person ID and a role ID. If a person has multiple roles, just give him multiple rows.

As a result, in order to determine whether person X has permission Y, you would search person X and permission Y in a join of all three tables. If you find one or more rows that match, the permission is granted.

Hope this helps.

Luc Pattyn

Badger | [badger,badger,badger,badger...]

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

Thanks for the reply. I am confusing myself - believe me.

I have a working user/role table setup that is here - the memberrep table is your person table. I've used this table design for many websites.

[^]

Independent of the login tables -(and this is where I get confused explaining what has to happen)
I have several other 'data' tables.

A buyer , who will be a buyer and have a login.
I want to relate the buyer to his user login.

I want to do the same with a seller (another similar but different table)

and yet a 3rd, salesrep.

In order for my buyer to login to the admin and see his data in the buyer table, I need to relate his buyer record to his login record.

I am considering adding the memberRepID to the buyer table - and then I will have the relation. But duplicate data (fname,lname,email,etc)

It's like it's calling me to create seperate login tables for each external 'content' type,

The buyer, seller and salesrep tables are not tables I am using to define the role.
does that make sense?

Kimmmmy wrote:
does that make sense?

I couldn't tell, you've lost me early on.

I suggest you start from the hard data, that to me is PERSONS. And I really would insist on a very neutral name for that table, it has nothing to do with functions, roles, permissions, which are all weakly defined and volatile (they can evolve in all kinds of ways), non-unique, and overlapping.

IMO in general, more tables is wrong except for normalization (i.e. avoiding duplicate entries in fields). Think of it like this: using more tables will result in needing more code, and does it really improve the quality or the power of your system?

Here is a functional question: does a person with two roles also use two logins? if yes (I hope not), the login belongs somewhat to the role; if not, the login belongs to the person, and may or may not be one or a few fields in the PERSONS table; I would be inclined to keep it apart, just personID and login data.

I wish an expert kicked in here. I would learn too.

Smile | :)

Luc Pattyn

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

Yes, a person with two roles would have one login. Although in this site, I do not see that happening. (famous last words) Smile | :)

I appreciate your help. I need to do some more research. Thank you for your time.

Didn't look at the graphic but this is a simple issue, you need to answer 1 question. Can a user have more that 1 role.

Yes
UserTable - all the details of the user as per Luc's suggestion
RoleTable - a list of all the roles available
LinkTable - a many to many link table with userid and roleid

When the user logs in they select the role to be used, you have a chicken/egg issue here - how do I filter the possible roles bfore the user has logged in, you don't you validate the role after the user and return for a role selection is an incorrect one is requested.

No
UserTable - with the additional field of RoleID
RoleTable - a list of all the roles available

Validate the user only, role is automatic.

Seems we fully agree, your description is much better though.
You had the advantage of not having seen the diagram!

Smile | :)

Luc Pattyn

Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.

So for once I can thank my net nazi for not allowing image sharing sites Big Grin | :-D