Association and Dependency Network

Hello,

How do I get n and only items predicted by a specific item(s) either directly or indirectly as shown in the dependency network diagram?

For instance, the predict function won’t work for me - because running this query on AdvantureWorks:

SELECT PREDICT([Association].[Products], 5)

From [Association]

NATURAL PREDICTION JOIN

(SELECT (SELECT 'Touring Tire Tube' AS [Model]) AS [Products]) AS t

Returns Sport-100 as a second result, although it is not predicted by any mean by Touring Tire Tube as shown in the dependency network diagram.

My query should have returned just one row - Touring Tire.

See this tip and trick

Returning Associative Predictions Based Solely on Rules

Note that the Dep Net only shows pairwise rules so this technique may still return more results if there are rules that don't show in the Dep Net. You can avoid this by setting the maximum itemset length to 2.

sql

Assigning a lower priority to some users in SQL Server.

I have a production database used by a web site, and at the same time a group of read-only users who can query the database directly “without the web site”

When one of the users runs a complex query, it slows down the server, and affects the web site.

Is it possible to change the SQL User account or SQL User Group’s priority to low?

You know, the same like in the Task Manager and Windows, I can change a process to low, so it will not affect the important processes, can I do this in SQL Server, and is there any workaround.

in my humble opinion

its the query that needs to be changed

or if your using 2005 you can implement HA feature

such as database snapshot, mirroring etc.

|||Besides the guessing, does anyone have a real solution? Are there sql execution priorities available in SQL Server? Or they are just in the deal databases, like Oracle?|||

if you are so convince that thats the best query you can write and there is no

room for improvement then you can schedule your heavy process to run

on offpeak times.

if that heavy process cross the line of tolerable performance your only option

is to kill that process.

the solution to your problem are

1. send readonly report users to a database snapshot if you are using 2k5

2. schedule the process to run on offpeak times

3. use page caching, fragment caching and most importanctly database caching in asp.net or on your website so you dont rely much on your database

By the way, what does this complex query do? if you would not mind.

how complex is it?

Assign name to the DB users.

I'm developing a vb 2005 application and I’m creating the users directly to the database. I want to assign them names.

I want to do something like this:

CREATE TABLE admin.db_users (

id INT CONSTRAINT db_user_pk PRIMARY KEY,

[name] VARCHAR(50) CONSTRAINT db_user_name_nn NOT NULL,

authentication VARCHAR(25) CONSTRAINT db_user_authentication_nn NOT NULL,

CONSTRAINT db_user_fk FOREIGN KEY(id)

REFERENCES sys.database_principals (principal_id)

ON UPDATE CASCADE

ON DELETE CASCADE

);

GO

This is the error that i'm getting:

Msg 1767, Level 16, State 0, Line 1

Foreign key 'db_user_fk' references invalid table 'sys.database_principals'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

How do I solve this problem or how can I do something similar.

You cannot create foreign key references to views. A different way to get functionality close to what you need would be to use insert/update trigger on the db_users table where the trigger action verifies the existance of the user in sys.database_principals. Then define a DDL trigger on the database for the DDL_USER_EVENTS (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb2a7bd0-2347-488c-bb75-734098050c7c.htm) to catch DROP/ALTER USER events and do the appropriate delete/cascade action.|||

Hi,

I’m new in these if you or some body could to help me I will appreciated. How can I create that trigger?

This is the script of the table that I want to use if this is possible:

CREATE TABLE admin.db_users (

idINT CONSTRAINT user_pk PRIMARY KEY,

[name]VARCHAR(50) CONSTRAINT user_name_nn NOT NULL,

authenticationVARCHAR(25) CONSTRAINT user_authentication_nn NOT NULL,

activeBIT

);

|||

Here's a sample that will give you a sense of what can be done and get you started. You will need to modify/build on the sample to achieve what you need. Please check the "CREATE TRIGGER" topic in Books Online for more detailed information on using triggers.

-- DML Trigger to verify users against sys.database_principals
--
create trigger check_users on db_users
for insert, update
as
-- Collect inserted users that don't exist in sys.database_principals
declare @.invalid_users table([name] sysname)
insert into @.invalid_users
select convert(sysname, name) from inserted except select [name] from sys.database_principals

-- If invalid users are found, rollback transaction
if exists (select [name] from @.invalid_users)
begin
print 'Operation was aborted because following users are invalid'
select [name] as invalid_users from @.invalid_users
if @.@.trancount > 0 rollback tran
end
go

-- DDL Trigger to catch create/drop/alter of new database principals
--
create trigger user_ddl_trig
on database
for ddl_user_events
as
declare @.user_name sysname,
@.event_type sysname

select @.event_type = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','sysname')
select @.user_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

-- Do your cascased action
if @.event_type = 'DROP_USER'
begin
print 'dropping user ' + @.user_name
delete from db_users where [name] = @.user_name
-- Or check against db_users and rollback this transaction, etc
end
--else if @.event_type = 'ALTER_USER'...
go

|||Thanks a lot.