check replication conflicts programmatically

Hi,
I would like to know if we can check merge replication conflicts
programmatically.
which tables do we need to check for replication conflicts?
By default some conflict_* tables are created. will these contain the
info?
Rgds,
amit
mitsql,
yes the conflict_ tables contain the necessary information. In detail
they contain the column values of the losing replication partner plus
the reason for the conflict and the guid. If you want to be notified
when a conflict occurs you can create an insert trigger on the conflict
tables which either sends a mail or runs some sript/store procedure.
Markus
sql

check replication conflicts programmatically

Hi,
I would like to know if we can check merge replication conflicts
programmatically.
which tables do we need to check for replication conflicts?
By default some conflict_* tables are created. will these contain the
info?
Rgds,
amitmitsql,
yes the conflict_ tables contain the necessary information. In detail
they contain the column values of the losing replication partner plus
the reason for the conflict and the guid. If you want to be notified
when a conflict occurs you can create an insert trigger on the conflict
tables which either sends a mail or runs some sript/store procedure.
Markus

check replication conflicts programmatically

Hi,
I would like to know if we can check merge replication conflicts
programmatically.
which tables do we need to check for replication conflicts?
By default some conflict_* tables are created. will these contain the
info?
Rgds,
amitmitsql,
yes the conflict_ tables contain the necessary information. In detail
they contain the column values of the losing replication partner plus
the reason for the conflict and the guid. If you want to be notified
when a conflict occurs you can create an insert trigger on the conflict
tables which either sends a mail or runs some sript/store procedure.
Markus

Check on the tables about last modification!

Hi,
Is there any possibilty to have a check on the tables about the last
modification(ie.,who has changed the data in the table using a query or hard
core change in the table value)?
If not, what are the other possibilities (any third party tool).
Kindly help.
Thanks,
ShyamHi Shyam,
Unfortunately that is not possible. But there are methods by which you can
track it...
(a) Having a trigger on the table. Remember its a costly operation
(b) Having the trace on
(c) If the modification is allowed only from the application, then probably
you can have a datetime field (getdate()) in the table for insert/update
statements.
HTH
--
Thanks
Yogish|||you can use a log explorer (http://www.kccltd.com/tools/log.asp) to see what
has happened after the event, which may be too late or you could introduce
database change management so you know what is changing in the database
before it actually changes (http://www.dbghost.com)
"Shyam" wrote:

> Hi,
> Is there any possibilty to have a check on the tables about the last
> modification(ie.,who has changed the data in the table using a query or ha
rd
> core change in the table value)?
> If not, what are the other possibilities (any third party tool).
> Kindly help.
> Thanks,
> Shyamsql

Check on the tables about last modification!

Hi,
Is there any possibilty to have a check on the tables about the last
modification(ie.,who has changed the data in the table using a query or hard
core change in the table value)?
If not, what are the other possibilities (any third party tool).
Kindly help.
Thanks,
Shyam
Hi Shyam,
Unfortunately that is not possible. But there are methods by which you can
track it...
(a) Having a trigger on the table. Remember its a costly operation
(b) Having the trace on
(c) If the modification is allowed only from the application, then probably
you can have a datetime field (getdate()) in the table for insert/update
statements.
HTH
Thanks
Yogish
|||you can use a log explorer (http://www.kccltd.com/tools/log.asp) to see what
has happened after the event, which may be too late or you could introduce
database change management so you know what is changing in the database
before it actually changes (http://www.dbghost.com)
"Shyam" wrote:

> Hi,
> Is there any possibilty to have a check on the tables about the last
> modification(ie.,who has changed the data in the table using a query or hard
> core change in the table value)?
> If not, what are the other possibilities (any third party tool).
> Kindly help.
> Thanks,
> Shyam

Check on the tables about last modification!

Hi,
Is there any possibilty to have a check on the tables about the last
modification(ie.,who has changed the data in the table using a query or hard
core change in the table value)?
If not, what are the other possibilities (any third party tool).
Kindly help.
Thanks,
ShyamHi Shyam,
Unfortunately that is not possible. But there are methods by which you can
track it...
(a) Having a trigger on the table. Remember its a costly operation
(b) Having the trace on
(c) If the modification is allowed only from the application, then probably
you can have a datetime field (getdate()) in the table for insert/update
statements.
HTH
--
Thanks
Yogish|||you can use a log explorer (http://www.kccltd.com/tools/log.asp) to see what
has happened after the event, which may be too late or you could introduce
database change management so you know what is changing in the database
before it actually changes (http://www.dbghost.com)
"Shyam" wrote:
> Hi,
> Is there any possibilty to have a check on the tables about the last
> modification(ie.,who has changed the data in the table using a query or hard
> core change in the table value)?
> If not, what are the other possibilities (any third party tool).
> Kindly help.
> Thanks,
> Shyam

check merge replication conflicts

Hi,
I would like to know if we can check merge replication conflicts
programmatically.
which tables do we need to check for replication conflicts?
By default some conflict_* tables are created. will these contain the
info?
Rgds,
amit
Yes - these custom-named tables and MSmerge_delete_conflicts will contain
the info you require.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanks a lot.

Check if temporary table exists

Hello.

How can I check if a temporary table exists in the current context?

With normal tables I'd do a

EXISTS ( SELECT name FROM sysobjects

WHERE name='myTableName' AND type='U')

However, I can't do that with a temporary table. I'd have to go look at the sysobjects table in the tempdb database.

The problem is that for temporary tables, a suffix is added to the name to make it unique for each scope. I can't change the WHERE clause to name LIKE 'myTableName%' because this would return true if a temporary table with the same name exists in a different scope.

Any ideas?

Carlos

You can try the following

IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
ID int IDENTITY(1,1),
SomeValue varchar(100)
)
GO

|||IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL
Print 'Yes'
Else
Print 'No'sql

Check if record has dependencies

I have a database where one table 'Project' has a one to many relationship
with several other tables. Other than doing multiple Select queries, is
there a simple quick way of testing to see if there are any dependencies in
the tables connect with the constraints
Cheerstry this:
sp_help <table>
--
current location: alicante (es)
"Newbie" wrote:

> I have a database where one table 'Project' has a one to many relationship
> with several other tables. Other than doing multiple Select queries, is
> there a simple quick way of testing to see if there are any dependencies i
n
> the tables connect with the constraints
> Cheers
>
>|||What ?,
were you replying to someone else by mistake, this makes absolutely no sense
to me whatsoever !
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:2BF857E7-DDE1-4AFF-AA13-965C3573162B@.microsoft.com...
> try this:
> sp_help <table>
> --
> current location: alicante (es)
>
> "Newbie" wrote:
>|||Basically you want to do a outer left join with any related tables. Let's
assume that the Project table has related tables Tasks and Notes. The
following will count the number of projects that have at least one task or
note:
select
count(distinct Project.ProjectID) as CountAssignedProjects
from Project
left join Tasks on Tasks.ProjectID = Project.ProjectID
left join Notes on Notes.ProjectID = Project.ProjectID
where
Tasks.ProjectID is not null or
Notes.ProjectID is not null
"Newbie" <me@.me.com> wrote in message
news:%23yfFEoBSGHA.4792@.TK2MSFTNGP14.phx.gbl...
>I have a database where one table 'Project' has a one to many relationship
>with several other tables. Other than doing multiple Select queries, is
>there a simple quick way of testing to see if there are any dependencies in
>the tables connect with the constraints
> Cheers
>|||I'm sorry I was wrong. I though that you are looking for the current
references for a table and using sp_help such request is returned...
--
current location: alicante (es)
"Newbie" wrote:

> What ?,
> were you replying to someone else by mistake, this makes absolutely no sen
se
> to me whatsoever !
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:2BF857E7-DDE1-4AFF-AA13-965C3573162B@.microsoft.com...
>
>

Check if a field exists before ADD a new column.

Hi All.
We have some scripts adding columns to tables. Is there a simple way to
check if the column are there already in the table before running the ADD
command. Just like you get on the table when scripting it?
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CalDates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Thanx all
ghHello,
You can use something like this:
IF NOT EXISTS (SELECT * FROM syscolumns
WHERE id=OBJECT_ID('TableName') AND name='ColumnName')
[...]
Razvan|||if col_length('tb','col') is null
print('col does not exist in tb')
-oj
"Geir Holme" <geir@.multicase.no> wrote in message
news:uA2GTzcPGHA.1532@.TK2MSFTNGP12.phx.gbl...
> Hi All.
> We have some scripts adding columns to tables. Is there a simple way to
> check if the column are there already in the table before running the ADD
> command. Just like you get on the table when scripting it?
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[CalDates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>
> Thanx all
> gh
>

Check for Temp Table

How do you check if a temp table exists?
I usually do the following for tables and Views:
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME =
'EarningsDeductions')
DROP Table EarningsDeductions
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME =
'EarningsWithRank')
DROP VIEW EarningsWithRank
But I can't seem to find out how to check for a temp Table.
I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
was there and whether there was a type code there, but there wasn't.
Thanks,
Tomtry this:
http://www.devx.com/tips/Tip/13938
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||or this :
if object_id('tempdb..#temp') is not null
print 'exists'
else
print 'not exists'
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||"Yosh" <yoshi@.nospam.com> wrote in message
news:%23%23mEoyxvFHA.708@.TK2MSFTNGP10.phx.gbl...
> or this :
> if object_id('tempdb..#temp') is not null
> print 'exists'
> else
> print 'not exists'
That would do what I wanted.
Thanks,
Tom
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
NAME
NAME
it
>

CHECK contraint

Hi,
Hi, this is the situation.. I have somewhat simplified the tables here that
are insignificant to the structure.
There are organizations, who can be assigned different codes and users (by
an administrator).
These users can access all codes that are assigned to the same organization
that the user himself belongs to.
However, these users (parents) can create sub-users, and assign them a
SUBSET of all the codes that the parent user itself has access to.
So the administrator creates an organization "O" and and assignes user "A".
And assignes codes 1,2 and 3 to this organization. So user A has access to
codes 1, 2 and 3.
Then user A may create one or more users, such as user B, and assign it a
subset of the codes it has access to, such as 2 and 3.
While this type of nesting could go on, in reality we only have 3 types of
users, Admin, A-type users (created by Admin), and B-type users (created by
A-type users).
This is what I came up with:
-- organizations
CREATE TABLE organizations (
`organizationid` INT UNSIGNED PRIMARY KEY
`name` VARCHAR(100)
)
-- each organization can be assigned multiple codes
CREATE codes (
`codeid` INT UNSIGNED PRIMARY KEY,
`organizationid` INT UNSIGNED FOREIGN KEY organizations (`organizationid)
)
-- admins, A-type users and B-type users
CREATE TABLE users (
`userid` INT UNSIGNED PRIMARY KEY,
`userparentid` INT UNSIGNED FOREIGN KEY users (`userid`),
`organizationid` INT UNSIGNED FOREIGN KEY organizations (`organizationid`)
`name` VARCHAR(50)
)
-- map B-users to subset of codes that its parent has access to
CREATE users_codes (
`userid` INT UNSIGNED NOT NULL FOREIGN KEY users (`userid`),
`codeid` INT UNSIGNED NOT NULL FOREIGN KEY codes (`codeid`)
)
This is how I differentiate between Admins, A-type users and B-type users.
Admins don't have a parent id (NULL) and no organization id (NULL).
A-type users have Admins as parent id or NULL, and non-null
organizationid's.
B-type users have parentid's pointing to A-type users and non-null
organization id's.
There's some duplication because B-type id's can get the organizationid from
its parent (A-type) user too.
codes are assigned to organizations, and to B-type users only, not to A-type
users or Admins.
Somehow I am not comfortable with this model. It seems like I am doing
something wrong and there's a better solution.
Anyway, if I do use this model, I need some CHECK constraints on
users_codes, that makes sure that B-type users (with parent-id) can only be
assigned codeids that their parents (A-type users) have access to.
How do I put this in some kind of CHECK constraint?
Please advise on the structure / model, as well as the CHECK constraint(s)
required.
LisaHow about a standard nested set model with nodes that hold the various
codes
CREATE TABLE Tree
(node_id INTEGER NOT NULL
REFERENCES Nodes(node_id),
lft INTEGER NOT NULL UNIQUE
CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt));
CREATE TABLE Nodes
(node_id INTEGER NOT NULL PRIMARY KEY,
code_1 INTEGER DEFAULT 0 NOT NULL
CHECK (code_1 IN (0,1)),
code_2 INTEGER DEFAULT 0 NOT NULL
CHECK (code_2 IN (0,1)),
code_3 INTEGER DEFAULT 0 NOT NULL
CHECK (code_3 IN (0,1)),
code_4 INTEGER DEFAULT 0 NOT NULL
CHECK (code_4 IN (0,1)),
code_5 INTEGER DEFAULT 0 NOT NULL
CHECK (code_5 IN (0,1))
);
The rule seems to be that you can only inherit codes from a superior.
You could write this with a CREATE ASSERTION in SQL-92; SQL Server
needs a stored procedure that checks for allowed codes before doing an
insertion of a subordinate.|||Interesting concept, but I am having some difficulty grasping how this fits
with my situation. Is the treestructure just for codes only? Users point to
a certain node? An organization (and subusers) could be assigned up to 65536
codes. So it has to be a set, not fields in a table (like the 5 code fields
in your example).
I think my model is more like what I need, but what makes me feel
uncomfortabe is that 3 types of users, Admins, A-type and B-type users, are
all in the same users table. The type of user can only be recognized and
distinguished indirectely by whether organizationid and/or userparentid is
NULL or not.
And A-type users are not linked directely to codes, but are linked to
organizations, and codes are also linked to organizations and via the
organization, A-type users are INDIRECTELY linked to these codes.
But B-type users require a subset of codes, thus they have to be linked
DIRECTELY to the codes table:
( A-type users )===>( Organizations )<===( Codes )<===( B-type users )
Anyway, let me ask you another question... can check constrains refer to
other tables?
CREATE TABLE A (
val INT
)
CREATE TABLE B (
val INT CHECK( val > A(val))
)
something like this?
Lisa
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141180347.823645.141760@.t39g2000cwt.googlegroups.com...
> How about a standard nested set model with nodes that hold the various
> codes
> CREATE TABLE Tree
> (node_id INTEGER NOT NULL
> REFERENCES Nodes(node_id),
> lft INTEGER NOT NULL UNIQUE
> CHECK (lft > 0),
> rgt INTEGER NOT NULL UNIQUE,
> CHECK (lft < rgt));
>
> CREATE TABLE Nodes
> (node_id INTEGER NOT NULL PRIMARY KEY,
> code_1 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_1 IN (0,1)),
> code_2 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_2 IN (0,1)),
> code_3 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_3 IN (0,1)),
> code_4 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_4 IN (0,1)),
> code_5 INTEGER DEFAULT 0 NOT NULL
> CHECK (code_5 IN (0,1))
> );
> The rule seems to be that you can only inherit codes from a superior.
> You could write this with a CREATE ASSERTION in SQL-92; SQL Server
> needs a stored procedure that checks for allowed codes before doing an
> insertion of a subordinate.
>

Check constraints on Tables within UDFs - cannot drop constraint l

This is probably obscure usage of the SQL Server feature-set, but any help
appreciated.
I attempted to include a CHECK constraint in the table-definition for the
RETURN table value of a UDF. Like this:
create function dbo.MyFunction ()
returns @.r table
( MyColumn int not null,
check (MyColumn in (1,2,3))
)
as
... ... ...
(Greatly simplified of course.)
I succeeded in having it create the constraint, as long as I (a) did not
name it, and (b) did it as a table constraint rather than inline with the
column definition. [These are also odd behaviors to me.]
However, when I later attempt to ALTER FUNCTION to apply a new version, I
get an error that it cannot alter the function because it is being reference
d
by another object, then gives the obviously system-generated name of the
CHECK constraint it created, apparently, under the hood.
It seems the only way to get rid of it now is to DROP the function (which I
do not like for other reasons, preferring "ALTER" until SQL Server gets an
Oracle-esque "create or replace" syntax going).
But outside of that, there seems to be no way to get rid of it. I can't
alter-function-drop-constraint, like one could with a table. And I can't jus
t
drop the constraint by itself.
Thoughts? Suggestions? Future feature request maybe?
It would be nice if table-valued functions were more closely aligned with
tables in functionality.
Eric M. Wilson
www.datazulu.comHi
Your finding seem to be correct! It does seem to be an obscure requirement
and I can not think of a reason why you would want to do this. The most
obvious way to get around it is to work with a table variable within the
function that has the constraint and remove it from the function.
If you have any requests for additional/changed functionality you can email
them too SQLWish@.microsoft.com
John
"Eric Wilson" wrote:

> This is probably obscure usage of the SQL Server feature-set, but any help
> appreciated.
> I attempted to include a CHECK constraint in the table-definition for the
> RETURN table value of a UDF. Like this:
> create function dbo.MyFunction ()
> returns @.r table
> ( MyColumn int not null,
> check (MyColumn in (1,2,3))
> )
> as
> ... ... ...
> (Greatly simplified of course.)
> I succeeded in having it create the constraint, as long as I (a) did not
> name it, and (b) did it as a table constraint rather than inline with the
> column definition. [These are also odd behaviors to me.]
> However, when I later attempt to ALTER FUNCTION to apply a new version, I
> get an error that it cannot alter the function because it is being referen
ced
> by another object, then gives the obviously system-generated name of the
> CHECK constraint it created, apparently, under the hood.
> It seems the only way to get rid of it now is to DROP the function (which
I
> do not like for other reasons, preferring "ALTER" until SQL Server gets an
> Oracle-esque "create or replace" syntax going).
> But outside of that, there seems to be no way to get rid of it. I can't
> alter-function-drop-constraint, like one could with a table. And I can't j
ust
> drop the constraint by itself.
> Thoughts? Suggestions? Future feature request maybe?
> It would be nice if table-valued functions were more closely aligned with
> tables in functionality.
> --
> Eric M. Wilson
> www.datazulu.com

Check Constraint UDF: Update vs. Insert

I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:

> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
>
>
>
>
>
>
>
>
> - Show quoted text -|||rich wrote:
> Alejandro, thanks for the reply - I was trying to find some resource
> to confirm or deny the workings so I appreciate it.
> I will share the true source of my problem in case you, or others,
> would like to weigh in on the best method.
> Basically, all of our data tables have a delete_flag bit field.
> At the same time, I am trying to disallow duplicate combination of 3
> fields. (sounds like a primary key, huh?)
> Each table has an Identity field as the primary key.
> I can't create a unique index on the three field combo because I allow
> logical deletes using the delete_flag field.
> I tried including the delete_flag in a four field index, but then you
> can only delete each unique combination once.
> I need unlimited logically deleted rows where my three fields are not
> necessarily unique,
> but only one non-deleted one row for each unique combination.
> make sense?
The solution(s) to the problem "unique constraint with multiple NULLs"
also applies to your case, only in your case the delete flag acts as a
NULL.
So for example, something like the script below could be a solution:
ALTER TABLE YourTable
ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
ALTER TABLE YourTable
ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
HTH,
Gert-Jan|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
>
>
>
>
>
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:

> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne|||Well, for one thing, using a constraint will avoid unnecessary page
splits, because it will prevent duplicate rows. A trigger will roll back
duplicate rows.
Also, I think that in this case, writing a trigger is more error prone
than defining the extra column and unique constraint.
Of course, the unique index will use slightly more space. But then
again, you also benefit from the fact that there now is a highly
selective index on the natural key.
So in this case, IMO the constraint wins from the trigger hands down...
Gert-Jan
rich wrote:[vbcol=seagreen]
> Gert-Jan, thanks for the tip!
> I get it and it makes sense.
> I also just tried a trigger (based on the above "test1" table) and it
> works too.
> Not sure what I'd rather do - add a new trigger to a bunch of tables
> or a column and constraint.
> here's the trigger in case anyone is interested:
> CREATE TRIGGER dbo.tr_Test
> ON [dbo].[test1]
> FOR INSERT, UPDATE
> AS
> declare @.cnt int
> BEGIN
> select @.cnt=count(*) from test1 where test in (select test from
> inserted)
> if @.cnt>1 rollback tran --it's always 1 because the newly inserted
or
> updated row is included in the test1 table
> END
> Thanks for everyone's help!
> On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.

Check Constraint UDF: Update vs. Insert

I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!
rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:

> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>
|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
>
>
>
>
> - Show quoted text -
|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
>
>
>
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -
|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:

> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne
|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.

Check Constraint UDF: Update vs. Insert

I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:
> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
> > I am using a check constraint on one of my tables. It appears to me
> > that the constraint processes an insert AFTER the row is inserted on
> > an insert, but BEFORE the data is updated on an update. here's my
> > example:
> > Table:
> > CREATE TABLE [test1] (
> > [test] [int] NOT NULL )
> > Function:
> > CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> > RETURNS INT
> > AS
> > BEGIN
> > declare @.cnt int
> > select @.cnt = count(*) from test1 where test=@.test
> > return @.cnt
> > END
> > here's my constraint added to the table test1:
> > alter table test1 add constraint ck_test1 check
> > (dbo.fn_RI_test([test])=1)
> > Now, if I insert new rows:
> > insert into test1 values (1) - works fine
> > insert into test1 values (2) - works fine
> > insert into test1 values (1) - error:
> > Server: Msg 547, Level 16, State 1, Line 1
> > INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> > The conflict occurred in database 'JHRA_Test', table 'test1', column
> > 'test'.
> > The statement has been terminated.
> > BUT, if I perform an update:
> > update test1 set test=1 where test=2
> > IT WORKS and now there are 2 rows with test=1
> > Am I crazy? Is there any way for me to know in the function whether
> > this is an update or an insert? Any other options? BTW, I don't need
> > to make a single column unique in this manner, my needs are much more
> > complex. this is just an illustration which demonstrates my issue.
> > Thanks!- Hide quoted text -
> - Show quoted text -|||rich wrote:
> Alejandro, thanks for the reply - I was trying to find some resource
> to confirm or deny the workings so I appreciate it.
> I will share the true source of my problem in case you, or others,
> would like to weigh in on the best method.
> Basically, all of our data tables have a delete_flag bit field.
> At the same time, I am trying to disallow duplicate combination of 3
> fields. (sounds like a primary key, huh?)
> Each table has an Identity field as the primary key.
> I can't create a unique index on the three field combo because I allow
> logical deletes using the delete_flag field.
> I tried including the delete_flag in a four field index, but then you
> can only delete each unique combination once.
> I need unlimited logically deleted rows where my three fields are not
> necessarily unique,
> but only one non-deleted one row for each unique combination.
> make sense?
The solution(s) to the problem "unique constraint with multiple NULLs"
also applies to your case, only in your case the delete flag acts as a
NULL.
So for example, something like the script below could be a solution:
ALTER TABLE YourTable
ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
ALTER TABLE YourTable
ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
HTH,
Gert-Jan|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
> > Alejandro, thanks for the reply - I was trying to find some resource
> > to confirm or deny the workings so I appreciate it.
> > I will share the true source of my problem in case you, or others,
> > would like to weigh in on the best method.
> > Basically, all of our data tables have a delete_flag bit field.
> > At the same time, I am trying to disallow duplicate combination of 3
> > fields. (sounds like a primary key, huh?)
> > Each table has an Identity field as the primary key.
> > I can't create a unique index on the three field combo because I allow
> > logical deletes using the delete_flag field.
> > I tried including the delete_flag in a four field index, but then you
> > can only delete each unique combination once.
> > I need unlimited logically deleted rows where my three fields are not
> > necessarily unique,
> > but only one non-deleted one row for each unique combination.
> > make sense?
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne|||Well, for one thing, using a constraint will avoid unnecessary page
splits, because it will prevent duplicate rows. A trigger will roll back
duplicate rows.
Also, I think that in this case, writing a trigger is more error prone
than defining the extra column and unique constraint.
Of course, the unique index will use slightly more space. But then
again, you also benefit from the fact that there now is a highly
selective index on the natural key.
So in this case, IMO the constraint wins from the trigger hands down...
Gert-Jan
rich wrote:
> Gert-Jan, thanks for the tip!
> I get it and it makes sense.
> I also just tried a trigger (based on the above "test1" table) and it
> works too.
> Not sure what I'd rather do - add a new trigger to a bunch of tables
> or a column and constraint.
> here's the trigger in case anyone is interested:
> CREATE TRIGGER dbo.tr_Test
> ON [dbo].[test1]
> FOR INSERT, UPDATE
> AS
> declare @.cnt int
> BEGIN
> select @.cnt=count(*) from test1 where test in (select test from
> inserted)
> if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> updated row is included in the test1 table
> END
> Thanks for everyone's help!
> On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > rich wrote:
> >
> > > Alejandro, thanks for the reply - I was trying to find some resource
> > > to confirm or deny the workings so I appreciate it.
> >
> > > I will share the true source of my problem in case you, or others,
> > > would like to weigh in on the best method.
> >
> > > Basically, all of our data tables have a delete_flag bit field.
> > > At the same time, I am trying to disallow duplicate combination of 3
> > > fields. (sounds like a primary key, huh?)
> > > Each table has an Identity field as the primary key.
> > > I can't create a unique index on the three field combo because I allow
> > > logical deletes using the delete_flag field.
> > > I tried including the delete_flag in a four field index, but then you
> > > can only delete each unique combination once.
> >
> > > I need unlimited logically deleted rows where my three fields are not
> > > necessarily unique,
> > > but only one non-deleted one row for each unique combination.
> >
> > > make sense?
> >
> > The solution(s) to the problem "unique constraint with multiple NULLs"
> > also applies to your case, only in your case the delete flag acts as a
> > NULL.
> >
> > So for example, something like the script below could be a solution:
> >
> > ALTER TABLE YourTable
> > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> >
> > ALTER TABLE YourTable
> > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> >
> > HTH,
> > Gert-Jan- Hide quoted text -
> >
> > - Show quoted text -|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
> > Gert-Jan, thanks for the tip!
> > I get it and it makes sense.
> > I also just tried a trigger (based on the above "test1" table) and it
> > works too.
> > Not sure what I'd rather do - add a new trigger to a bunch of tables
> > or a column and constraint.
> > here's the trigger in case anyone is interested:
> > CREATE TRIGGER dbo.tr_Test
> > ON [dbo].[test1]
> > FOR INSERT, UPDATE
> > AS
> > declare @.cnt int
> > BEGIN
> > select @.cnt=count(*) from test1 where test in (select test from
> > inserted)
> > if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> > updated row is included in the test1 table
> > END
> > Thanks for everyone's help!
> > On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > > rich wrote:
> > > > Alejandro, thanks for the reply - I was trying to find some resource
> > > > to confirm or deny the workings so I appreciate it.
> > > > I will share the true source of my problem in case you, or others,
> > > > would like to weigh in on the best method.
> > > > Basically, all of our data tables have a delete_flag bit field.
> > > > At the same time, I am trying to disallow duplicate combination of 3
> > > > fields. (sounds like a primary key, huh?)
> > > > Each table has an Identity field as the primary key.
> > > > I can't create a unique index on the three field combo because I allow
> > > > logical deletes using the delete_flag field.
> > > > I tried including the delete_flag in a four field index, but then you
> > > > can only delete each unique combination once.
> > > > I need unlimited logically deleted rows where my three fields are not
> > > > necessarily unique,
> > > > but only one non-deleted one row for each unique combination.
> > > > make sense?
> > > The solution(s) to the problem "unique constraint with multiple NULLs"
> > > also applies to your case, only in your case the delete flag acts as a
> > > NULL.
> > > So for example, something like the script below could be a solution:
> > > ALTER TABLE YourTable
> > > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> > > ALTER TABLE YourTable
> > > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> > > HTH,
> > > Gert-Jan- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.

Check constraint for identity range

Hi,
When I delete subscriptions and publications and disable the replication,
the check constraint for identity range stays in subscriber for tables. Then
I have to manually drop or disable them. Is it an expected behavior or
there's something wrong in my work? How can I have them removed
automatically?
Thanks in advance,
Leila
I reported this problem to Microsoft already. From what I understand this
request was unanticipated by the design team. They expected that when you
replicate to a subscriber, the publisher subscriber relationship is forever.
They did not anticipate customers breaking replication and then using the
subscriber as a standalone database.
I did post a script here sometime ago to remove these constraints.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Leila" <Leilas@.hotpop.com> wrote in message
news:ecnVFp18EHA.808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> When I delete subscriptions and publications and disable the replication,
> the check constraint for identity range stays in subscriber for tables.
Then
> I have to manually drop or disable them. Is it an expected behavior or
> there's something wrong in my work? How can I have them removed
> automatically?
> Thanks in advance,
> Leila
>
|||Thanks Hilary,
Could you please post a link to your script again.
Thanks again,
Leila
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uXYke518EHA.1392@.tk2msftngp13.phx.gbl...
>I reported this problem to Microsoft already. From what I understand this
> request was unanticipated by the design team. They expected that when you
> replicate to a subscriber, the publisher subscriber relationship is
> forever.
> They did not anticipate customers breaking replication and then using the
> subscriber as a standalone database.
> I did post a script here sometime ago to remove these constraints.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:ecnVFp18EHA.808@.TK2MSFTNGP10.phx.gbl...
> Then
>

Check Constraint

Here is what I am trying to accomplish, and I would like to know if it is
possible through the use of check constraints.
Two tables Table_A and Table_B. When I insert new record in Table_A I would
like to check for the existence of a record, through the use of a check
constraint, in Table_B. If the record exists then insert the record,
otherwise the insert fails.
If this is possible, how would I set up the expression? I have tried the
EXISTS statement with no success.
Thanks,On Wed, 26 Apr 2006 15:01:02 -0700, Robert wrote:

>Here is what I am trying to accomplish, and I would like to know if it is
>possible through the use of check constraints.
>Two tables Table_A and Table_B. When I insert new record in Table_A I would
>like to check for the existence of a record, through the use of a check
>constraint, in Table_B. If the record exists then insert the record,
>otherwise the insert fails.
>If this is possible, how would I set up the expression? I have tried the
>EXISTS statement with no success.
>Thanks,
Hi Robert,
Rather than using a CHECK constraint, use a FOREIGN KEY (aka REFERENCES)
constraint for this.
Hugo Kornelis, SQL Server MVP|||Robert (Robert@.discussions.microsoft.com) writes:
> Here is what I am trying to accomplish, and I would like to know if it is
> possible through the use of check constraints.
> Two tables Table_A and Table_B. When I insert new record in Table_A I
> would like to check for the existence of a record, through the use of a
> check constraint, in Table_B. If the record exists then insert the
> record, otherwise the insert fails.
> If this is possible, how would I set up the expression? I have tried the
> EXISTS statement with no success.
You can do this, if you put the check in a user-defined function.
But don't do this! I tried it once. An update of all rows in a table with
24000 rows went from one second to 30 seconds.
Inter-table checks are best done in a trigger.
Unless, we are talking foreign-key checks, in which case you should
use a FOREIGN KEY constraint:
ALTER TABLE Orders ADD CONSTRAINT
FOREIGN KEY fk_Orders_Customers (CustomerID)
REFERENCES Customers(CustomerID)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Check box insert question

I have 3 tables - Data, Facility and FacilityKey

The FacilityKey table will hold the Data ID and Facility ID based on a series of check boxes on a form.

My question is what is the best way to do the insert into the FacilityKey table from the form? If 5 facilities are checked I don't want to do 5 separate calls to the database for inserts, but I'm a bit confused on what the best method would be.

Thank you!I can't really think of any other way. You're going to need to call 5 INSERT statements, each inserting a single row. You can use the one connection to do this. To be honest, I don't think SQL Server is going to struggle with this.

Cheers
Ken

Check all SPs from a script

It's known that the SQL Server doesn't check the SPs and views when we
modify the tables and if we try to use these old SPs after we applied any
modifications like column names to the tables we'll get very serious
problems.
Is it possible to check all SPs and View from one script, like open each
one, check if it's correct (maybe try to save?) and if it's a problem then
save the name of this wrong SP to some table or just provide a list with
these names?
Dmitri.Oh, thats a good one, the only thing that come in mind would be to figure
out all Procedures (in all sysobjects) and to identify for every single
procedure the parameters in syscolumns. Then you have to create a
SQLStatement with the paramters and some dummy values within an automatic
Rollback transaction and query after execution for the @.@.error to identify
wheter it was executed valid or not.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Just D." <no@.spam.please> schrieb im Newsbeitrag
news:zwthe.10252$D91.4971@.fed1read01...
> It's known that the SQL Server doesn't check the SPs and views when we
> modify the tables and if we try to use these old SPs after we applied any
> modifications like column names to the tables we'll get very serious
> problems.
> Is it possible to check all SPs and View from one script, like open each
> one, check if it's correct (maybe try to save?) and if it's a problem then
> save the name of this wrong SP to some table or just provide a list with
> these names?
> Dmitri.
>|||Hi
That is by design, it is called Deferred Name Resolution.
You can overcome the problem in the views with using WITH SCHEMABINDING when
creating the view.
For validation, here is some code that checks if the objects referenced
exist.
--Credit to Dan Guzman, SQL Server MVP:
SELECT
N'SET FMTONLY ON EXEC ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS ProcedureName,
REPLICATE(N'NULL,',
ISNULL(
(SELECT COUNT(*) AS Parameters
FROM INFORMATION_SCHEMA.PARAMETERS p
WHERE p.SPECIFIC_SCHEMA = r.ROUTINE_SCHEMA AND
p.SPECIFIC_NAME = r.ROUTINE_NAME), 0)
) AS Parameters
INTO #FmtOnlyExecutes
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE r.ROUTINE_TYPE = 'PROCEDURE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTI
NE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
--remove trailing comma from parameter list
UPDATE #FmtOnlyExecutes
SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
WHERE RIGHT(Parameters, 1) = N','
--execute procs
DECLARE @.ExecuteStatement nvarchar(4000)
DECLARE ExecuteStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT RTRIM(ProcedureName) +
N' ' +
Parameters
FROM #FmtOnlyExecutes
ORDER BY ProcedureName
OPEN ExecuteStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM ExecuteStatements INTO @.ExecuteStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.ExecuteStatement, 0, 1) WITH NOWAIT
EXEC(@.ExecuteStatement)
END
CLOSE ExecuteStatements
DEALLOCATE ExecuteStatements
DROP TABLE #FmtOnlyExecutes
GO
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Just D." <no@.spam.please> wrote in message
news:zwthe.10252$D91.4971@.fed1read01...
> It's known that the SQL Server doesn't check the SPs and views when we
> modify the tables and if we try to use these old SPs after we applied any
> modifications like column names to the tables we'll get very serious
> problems.
> Is it possible to check all SPs and View from one script, like open each
> one, check if it's correct (maybe try to save?) and if it's a problem then
> save the name of this wrong SP to some table or just provide a list with
> these names?
> Dmitri.
>|||Jens,
My idea was to open the SP for modification but actually do not do anything
bad, but to let the Sql Server know that it was modified we can add a space
in the very end for example and then try to save it. If it fails then
report/add a name to some table, that this SP is wrong. That's easier that
you're suggesting.
I'm just wondering if anybody is already having this script?
Dmitri.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OdeAyxMWFHA.3636@.TK2MSFTNGP14.phx.gbl...
> Oh, thats a good one, the only thing that come in mind would be to figure
> out all Procedures (in all sysobjects) and to identify for every single
> procedure the parameters in syscolumns. Then you have to create a
> SQLStatement with the paramters and some dummy values within an automatic
> Rollback transaction and query after execution for the @.@.error to identify
> wheter it was executed valid or not.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Just D." <no@.spam.please> schrieb im Newsbeitrag
> news:zwthe.10252$D91.4971@.fed1read01...
>