Re: New to SQL server

Dude you're fucking whacked...
we have a TRUMP CARD: it is called triggers.

ANYTHING THAT YOU CAN DO IN JET IS BABY TALK COMPARED TO WHAT WE CAN DO
IN SQL.

seriously I have no clue what your difficulty is:

This is the simple way to do it:

USE [SQL2005TEST]
GO
/****** Object: Table [dbo].[MYTABLE] Script Date: 12/06/2006
12:59:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MYTABLE](
[RECORDID] [int] NOT NULL,
[NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PARENTID] [int] NOT NULL,
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
(
[RECORDID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MYTABLE] WITH CHECK ADD CONSTRAINT
[FK_MYTABLE_MYTABLE] FOREIGN KEY([PARENTID])
REFERENCES [dbo].[MYTABLE] ([RECORDID])
GO
ALTER TABLE [dbo].[MYTABLE] CHECK CONSTRAINT [FK_MYTABLE_MYTABLE]



And if you insist on having cascade deletes / updates.. then you can
use a TRIGGER.

Access doesn't support TRIGGERS; and it's not 1/10th the database that
SQL Server is.


Also; with MDB you can 'create a relationship' against a query.

BUT IT DOESNT _DO_ ANYTHING right?
you can't enforce it; and you sure can't cascase update / delete this.

This is one feature that I wish would WORK in Access _AND_ in SQL
Server.
But i'm not going to let you get away with saying that there is
anything that you can do in JET that I can't do in SQL Server

I've been building parent-child relationships-- probably 2 or 3 a
year-- for the past 5 years and it's never given me a DROP of a
problem.

WIth Analysis Services; it's probably the single most powerful FEATURE
in SQL Server.

and for the record-- all DRI used to be triggers; so no; I dont' think
that it's that difficult to mix and match triggers and DRI.

Maybe if you stopped usign MDB you could fully learn how to use SQL
Server

CREATE TABLE Employees (
employee_ID INTEGER NOT NULL PRIMARY KEY
)

PS - maybe your problem with SQL Server is that it's not handicapped to
'only run one sql statement at a time'

with SQL Server you can have IF EXISTS () and stuff like that..
with Accees you can't do jack *** and you have to mix VBA code and SQL
to do a simple task like 'create table X if it doesn't already exist'

with Accees you can't do jack *** and you have to mix VBA code and SQL
to do a simple task like 'create table X if it doesn't already exist'

-Aaron



onedaywhen wrote:
dbahooker@xxxxxxxxxxx wrote:

DRI and CHECK constraints?

SQL SERVER WORKS LIKE A CHARM, PLEASE EXPLAIN YOUR SO CALLED BUGS

I didn't call them bugs; rather, they are omissions by design.

Both Cascade referential actions in DRI and table-level CHECK
constraints are both features of _full_ SQL-92 and Microsoft only
claims _entry level_ SQL-92 compliance for SQL Server (and not even
that for Access/Jet). Still, I think it's a poor show that these
features have been in Access/Jet for so long yet still missing from SQL
Server.

Didn't I already provide examples? Oh yeah, you didn't like
self-referencing FKs and inline constraints. OK, try this:

CREATE TABLE Employees (
employee_ID INTEGER NOT NULL
)
;
ALTER TABLE Employees ADD
CONSTRAINT pk__Employees
PRIMARY KEY (employee_ID)
;
CREATE TABLE OrgChart
employee_ID INTEGER NOT NULL,
manager_employee_ID INTEGER NOT NULL
)
;
ALTER TABLE OrgChart ADD
CONSTRAINT pk__OrgChart
PRIMARY KEY (employee_ID)
;
ALTER TABLE OrgChart ADD
CONSTRAINT fk__OrgChart__employee_ID
FOREIGN KEY (employee_ID)
REFERENCES Employees (employee_ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
;
ALTER TABLE OrgChart ADD
CONSTRAINT fk__OrgChart__manager_employee_ID
FOREIGN KEY (manager_employee_ID)
REFERENCES Employees (employee_ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
;

The above is SQL-92 code so it can used in Jet (e.g. ADO or, if you
prefer the GUI, in 'ANSI-92 Query Mode').

It works fine in Jet. For SQL Server the last statement generates and
error, 'Introducing FOREIGN KEY constraint
'fk__OrgChart__manager_employee_ID' on table 'OrgChart' may cause
cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'

You see, Jet has been programmed to resolve most cascade paths whereas
SQL Server is programmed to count the number of paths and choke when it
gets to 2; the fact the paths can be resolved easily is of no
consequence because SQL Server simply does not have the ability to do
it. I figure they were too busy trying to get CLR into the product
rather than get TSQL up to standard ;-)

Of course, you could write a trigger to perform the cascade action for
the second one but you have to resolve the paths yourself, do error
handling, transaction management, etc. Don't you think it a bit of a
weird table design that one cascade would be done with a FK and other
done with a trigger? Or would you write them both as triggers? All your
FKs as triggers? And you thought it was only Access/Jet where you had
to write your own tools to do basic operations <vbg>!

As for CHECK constraints:

ALTER TABLE OrgChart ADD
CONSTRAINT manager_for_maximum_three_employees
CHECK (NOT EXISTS (
SELECT T2.manager_employee_ID, COUNT(*)
FROM OrgChart AS T2
GROUP BY T2.manager_employee_ID
HAVING COUNT(*) > 3))
;

Works fine in Jet but in SQL Server it generates an error, 'Subqueries
are not allowed in this context. Only scalar expressions are allowed.'
Table-level CHECK constraints must be implemented in SQL Server with a
trigger. Don't get me wrong: it's great that SQL Server has a trigger
language to be able to plug these gaps; my point is, DRI and CHECK
constraints are the basic fundamentals and I have better things to do
with my time that to code around Microsoft's omissions.

Jamie.

--

.

Loading