check query status

Is it possible to create an SSIS package that checks for a running Query on my SQL db?

I need to some how check my SQL server and see if there is a query running, if its running I need to set an indicator in my table for my app. This job needs to be scheduled and run nightly (which I can do). But how can I query SQL and see if the query is still running?

There is nothing specific in SSIS that can give you that info; but perhaps you can put a query that gives you that inside of an execute sql task...|||

I want to use SSIS to create the package and use the SQL Task in the package, But how can I 'ping' the sql server to verify the sql query is still running or not? That's the portion I'm stuck on.

What would that query look like? Can this even be done?

|||Your question is one of a Transact-SQL nature and as such should probably be asked over in that forum. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Come back here if you need help implementing the resulting query inside SSIS.|||You can use sp_who or sys.sysprocesses to get lists of running processes. sys.sysprocesses would be easier to work with, if you are on 2005.

check job status

I want to have a query that can tell me all the jobs on a server that have
failed and need to be run again in the future. This is what I have come up
with:
select Server = 'BoxName', SJ.Name
from BoxName.msdb.dbo.SysJobHistory SJH
inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
where SJH.run_status = 0
and SJS.enabled = 1
and SJS.next_run_date <> 0
and SJS.next_run_time <> 0
group by Server, SJ.Name
The problem though is it would appear that SQL doesn't update the run_status
in the SysJobHistory table very often as I am getting values returned from
this query that did fail several hours ago, but have since succeeded. Does
anyone know of a better way to write this query? I know I can setup
Alerting, but need a backup for it.
TIA, ChrisR
Take a look at sp_help_job. Or look at the sql for
sp_help_job. It sounds like you may actually be looking for
last_run_outcome which sp_help_job obtains from sysjobsteps.
-Sue
On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>I want to have a query that can tell me all the jobs on a server that have
>failed and need to be run again in the future. This is what I have come up
>with:
>select Server = 'BoxName', SJ.Name
>from BoxName.msdb.dbo.SysJobHistory SJH
>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>where SJH.run_status = 0
>and SJS.enabled = 1
>and SJS.next_run_date <> 0
>and SJS.next_run_time <> 0
>group by Server, SJ.Name
>
>The problem though is it would appear that SQL doesn't update the run_status
>in the SysJobHistory table very often as I am getting values returned from
>this query that did fail several hours ago, but have since succeeded. Does
>anyone know of a better way to write this query? I know I can setup
>Alerting, but need a backup for it.
>TIA, ChrisR
>
|||Thanks Sue. The problem that I'm having though (for example) is that I have
a job used by Replication. It is technically the Log Reader Agent job. It
used to be set to run every 15 minutes until I recently changed it to run
continuosly. The last_run_outcome from sp_help_job still says 0 which is
accurate as that was the last completed outcome. If Im not mistaken, until
the job actually stops again, that outcome will stay 0? Therefore either
using my query or sp_help_job will indicate failure.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:e07l81lbfcg3lct0ii9frtug8q52mjch1e@.4ax.com...
> Take a look at sp_help_job. Or look at the sql for
> sp_help_job. It sounds like you may actually be looking for
> last_run_outcome which sp_help_job obtains from sysjobsteps.
> -Sue
> On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>

check job status

I want to have a query that can tell me all the jobs on a server that have
failed and need to be run again in the future. This is what I have come up
with:
select Server = 'BoxName', SJ.Name
from BoxName.msdb.dbo.SysJobHistory SJH
inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
where SJH.run_status = 0
and SJS.enabled = 1
and SJS.next_run_date <> 0
and SJS.next_run_time <> 0
group by Server, SJ.Name
The problem though is it would appear that SQL doesn't update the run_status
in the SysJobHistory table very often as I am getting values returned from
this query that did fail several hours ago, but have since succeeded. Does
anyone know of a better way to write this query? I know I can setup
Alerting, but need a backup for it.
TIA, ChrisRTake a look at sp_help_job. Or look at the sql for
sp_help_job. It sounds like you may actually be looking for
last_run_outcome which sp_help_job obtains from sysjobsteps.
-Sue
On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
<noemail@.bla.com> wrote:
>I want to have a query that can tell me all the jobs on a server that have
>failed and need to be run again in the future. This is what I have come up
>with:
>select Server = 'BoxName', SJ.Name
>from BoxName.msdb.dbo.SysJobHistory SJH
>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>where SJH.run_status = 0
>and SJS.enabled = 1
>and SJS.next_run_date <> 0
>and SJS.next_run_time <> 0
>group by Server, SJ.Name
>
>The problem though is it would appear that SQL doesn't update the run_status
>in the SysJobHistory table very often as I am getting values returned from
>this query that did fail several hours ago, but have since succeeded. Does
>anyone know of a better way to write this query? I know I can setup
>Alerting, but need a backup for it.
>TIA, ChrisR
>|||Thanks Sue. The problem that I'm having though (for example) is that I have
a job used by Replication. It is technically the Log Reader Agent job. It
used to be set to run every 15 minutes until I recently changed it to run
continuosly. The last_run_outcome from sp_help_job still says 0 which is
accurate as that was the last completed outcome. If Im not mistaken, until
the job actually stops again, that outcome will stay 0? Therefore either
using my query or sp_help_job will indicate failure.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:e07l81lbfcg3lct0ii9frtug8q52mjch1e@.4ax.com...
> Take a look at sp_help_job. Or look at the sql for
> sp_help_job. It sounds like you may actually be looking for
> last_run_outcome which sp_help_job obtains from sysjobsteps.
> -Sue
> On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>>I want to have a query that can tell me all the jobs on a server that have
>>failed and need to be run again in the future. This is what I have come up
>>with:
>>select Server = 'BoxName', SJ.Name
>>from BoxName.msdb.dbo.SysJobHistory SJH
>>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>>where SJH.run_status = 0
>>and SJS.enabled = 1
>>and SJS.next_run_date <> 0
>>and SJS.next_run_time <> 0
>>group by Server, SJ.Name
>>
>>The problem though is it would appear that SQL doesn't update the
>>run_status
>>in the SysJobHistory table very often as I am getting values returned from
>>this query that did fail several hours ago, but have since succeeded. Does
>>anyone know of a better way to write this query? I know I can setup
>>Alerting, but need a backup for it.
>>TIA, ChrisR
>

check job status

I want to have a query that can tell me all the jobs on a server that have
failed and need to be run again in the future. This is what I have come up
with:
select Server = 'BoxName', SJ.Name
from BoxName.msdb.dbo.SysJobHistory SJH
inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
where SJH.run_status = 0
and SJS.enabled = 1
and SJS.next_run_date <> 0
and SJS.next_run_time <> 0
group by Server, SJ.Name
The problem though is it would appear that SQL doesn't update the run_status
in the SysJobHistory table very often as I am getting values returned from
this query that did fail several hours ago, but have since succeeded. Does
anyone know of a better way to write this query? I know I can setup
Alerting, but need a backup for it.
TIA, ChrisRTake a look at sp_help_job. Or look at the sql for
sp_help_job. It sounds like you may actually be looking for
last_run_outcome which sp_help_job obtains from sysjobsteps.
-Sue
On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
<noemail@.bla.com> wrote:

>I want to have a query that can tell me all the jobs on a server that have
>failed and need to be run again in the future. This is what I have come up
>with:
>select Server = 'BoxName', SJ.Name
>from BoxName.msdb.dbo.SysJobHistory SJH
>inner join BoxName.msdb.dbo.SysJobs SJ on SJH.job_id = SJ.job_id
>inner join BoxName.msdb.dbo.SysJobSchedules SJS on SJ.Job_id = SJS.job_id
>where SJH.run_status = 0
>and SJS.enabled = 1
>and SJS.next_run_date <> 0
>and SJS.next_run_time <> 0
>group by Server, SJ.Name
>
>The problem though is it would appear that SQL doesn't update the run_statu
s
>in the SysJobHistory table very often as I am getting values returned from
>this query that did fail several hours ago, but have since succeeded. Does
>anyone know of a better way to write this query? I know I can setup
>Alerting, but need a backup for it.
>TIA, ChrisR
>|||Thanks Sue. The problem that I'm having though (for example) is that I have
a job used by Replication. It is technically the Log Reader Agent job. It
used to be set to run every 15 minutes until I recently changed it to run
continuosly. The last_run_outcome from sp_help_job still says 0 which is
accurate as that was the last completed outcome. If Im not mistaken, until
the job actually stops again, that outcome will stay 0? Therefore either
using my query or sp_help_job will indicate failure.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:e07l81lbfcg3lct0ii9frtug8q52mjch1e@.
4ax.com...
> Take a look at sp_help_job. Or look at the sql for
> sp_help_job. It sounds like you may actually be looking for
> last_run_outcome which sp_help_job obtains from sysjobsteps.
> -Sue
> On Tue, 17 May 2005 13:26:56 -0700, "ChrisR"
> <noemail@.bla.com> wrote:
>
>

Check Execution status when manually running Snapshot agents

I am working on a script to run in Query Analyzer to run all of my SnapShot
agents manually rather than wait for the schedule to do it. I am using
sp_start_job to run each agent such as:
exec msdb.dbo.sp_start_job @.Job_Name='SQLSERVER-SomeSnapshotAgent-167'
waitfor delay '00:30:00' --some fixed time interval to try and have jobs not
run into each other
exec msdb.dbo.sp_start_job @.Job_Name='SQLSERVER-AnotherSnapshotAgent-154'
If I put all of my Snapshot agent jobs in a script to run back to back, how
can I check for execution status so the next job does not run into the
previous agent already running?
Thanks,
Steve
Steve,
you can use the following rputine (modified slightly) to determine the job's
current status.
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||How do I obtain the Guid Job_id for the job name(s) that I am wanting to run?
Thank You
Steve
"Paul Ibison" wrote:

> Steve,
> you can use the following rputine (modified slightly) to determine the job's
> current status.
> http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Steve,
it'll be something like this - just replace the name with the name of the
snapshot agent:
select job_id FROM sysjobs
where name = 'UK-3XSW02J\PAULS2000INST-Pub1-2'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

CHECK Constraint to prevent a conditional duplicate

Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-Paul
CHECK constraint work at row-by-row basis. I suggest you use a trigger instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegro ups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>
|||Use trigger to enforce this requirement.
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>
|||You can also create a view for STATUS = 2 and create a unique clustered index
by [id] on the view.
Example:
use northwind
go
create table t (
colA int,
colB int
)
go
create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go
create unique clustered index ix_u_c_view1_colA on view1(colA)
go
insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go
insert into t values(1, 2)
go
select * from t
go
drop view view1
go
drop table t
go
AMB
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>
|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:

>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul
Hi Paul,
Apart from the trigger Tibor suggests, there are two other options:
1. Use an indexed view:
CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go
2. Use a computed column (assuming PKCol is the primary key):
ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go
(both versions untested - bewarer of typos!)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:

>You can also create a view for STATUS = 2 and create a unique clustered index
>by [id] on the view.
Hi Alejandro,
Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

CHECK Constraint to prevent a conditional duplicate

Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegroups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||Use trigger to enforce this requirement.
"pdlevine@.gmail.com" wrote:
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||You can also create a view for STATUS = 2 and create a unique clustered index
by [id] on the view.
Example:
use northwind
go
create table t (
colA int,
colB int
)
go
create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go
create unique clustered index ix_u_c_view1_colA on view1(colA)
go
insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go
insert into t values(1, 2)
go
select * from t
go
drop view view1
go
drop table t
go
AMB
"pdlevine@.gmail.com" wrote:
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:
>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul
Hi Paul,
Apart from the trigger Tibor suggests, there are two other options:
1. Use an indexed view:
CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go
2. Use a computed column (assuming PKCol is the primary key):
ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go
(both versions untested - bewarer of typos!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:
>You can also create a view for STATUS = 2 and create a unique clustered index
>by [id] on the view.
Hi Alejandro,
Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

CHECK Constraint to prevent a conditional duplicate

Hi,

I need to enforce that a table does not have "duplicates" for a
specific status type in the table.

If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.

I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.

Just when the status = 2, there can not be any other rows with the same
ID and status = 2.

Any ideas?

-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger instead.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/

<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegro ups.com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:

>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul

Hi Paul,

Apart from the trigger Tibor suggests, there are two other options:

1. Use an indexed view:

CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go

2. Use a computed column (assuming PKCol is the primary key):

ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go

(both versions untested - bewarer of typos!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

CHECK Constraint to prevent a conditional duplicate

Hi,
I need to enforce that a table does not have "duplicates" for a
specific status type in the table.
If the column "STATUS" = 2, then there can not be more than one row
with a specific "ID" column.
I can not use a unique key constraint because duplicate values for this
combo of columns is valid for the status = 1.
Just when the status = 2, there can not be any other rows with the same
ID and status = 2.
Any ideas?
-PaulCHECK constraint work at row-by-row basis. I suggest you use a trigger inste
ad.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<pdlevine@.gmail.com> wrote in message news:1112287067.125360.22800@.l41g2000cwc.googlegroups.
com...
> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||Use trigger to enforce this requirement.
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||You can also create a view for STATUS = 2 and create a unique clustered inde
x
by [id] on the view.
Example:
use northwind
go
create table t (
colA int,
colB int
)
go
create view view1
with schemabinding
as
select colA, colB
from dbo.t
where colB = 2
go
create unique clustered index ix_u_c_view1_colA on view1(colA)
go
insert into t values(1, 1)
insert into t values(1, 1)
insert into t values(1, 2)
go
insert into t values(1, 2)
go
select * from t
go
drop view view1
go
drop table t
go
AMB
"pdlevine@.gmail.com" wrote:

> Hi,
> I need to enforce that a table does not have "duplicates" for a
> specific status type in the table.
> If the column "STATUS" = 2, then there can not be more than one row
> with a specific "ID" column.
> I can not use a unique key constraint because duplicate values for this
> combo of columns is valid for the status = 1.
> Just when the status = 2, there can not be any other rows with the same
> ID and status = 2.
> Any ideas?
> -Paul
>|||On 31 Mar 2005 08:37:47 -0800, pdlevine@.gmail.com wrote:

>Hi,
>I need to enforce that a table does not have "duplicates" for a
>specific status type in the table.
>If the column "STATUS" = 2, then there can not be more than one row
>with a specific "ID" column.
>I can not use a unique key constraint because duplicate values for this
>combo of columns is valid for the status = 1.
>Just when the status = 2, there can not be any other rows with the same
>ID and status = 2.
>Any ideas?
>-Paul
Hi Paul,
Apart from the trigger Tibor suggests, there are two other options:
1. Use an indexed view:
CREATE VIEW Status2Only
WITH SCHEMABINDING
AS
SELECT SpecificID -- You may add other columns,
-- if that helps for other purposes
FROM dbo.MyTable
WHERE Status = 2
go
CREATE UNIQUE CLUSTERED INDEX NoDupsFor2 ON Status2Only(SpecificID)
go
2. Use a computed column (assuming PKCol is the primary key):
ALTER TABLE MyTable
ADD HelperColumn AS CASE
WHEN Status = 2
THEN SpecificID
ELSE PKCol
END
go
ALTER TABLE MyTable
ADD CONSTRAINT NoDupsFor2 UNIQUE (Status, HelperColumn)
go
(both versions untested - bewarer of typos!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 31 Mar 2005 09:31:07 -0800, Alejandro Mesa wrote:

>You can also create a view for STATUS = 2 and create a unique clustered ind
ex
>by [id] on the view.
Hi Alejandro,
Sorry for duplicating your reply - I posted my reply from
comp.databases.ms-sqlserver, where the original post was crossposted,
and only Tibor's reply showed there.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.