Monday, March 19, 2012
Conditional Trigger
CREATE TRIGGER tr_feedback_date ON Calls
FOR UPDATE AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
if update (feedback) or update (analyst) or update (coordinator) or update (technical) or update (fixtime)
or update (phonewait) or update (resp)
UPDATE hold_complete
set hold_complete.feedbackdt = getdate()
from inserted with (nolock)
where hold_complete.fkey = inserted.callidThe create trigger statement can take an IF UPDATE ( column ) clause. You can read about it in Books Online.
Another option it to compare the values in the INSERTED table to the values in the DELETED table. Since updates involve both insertions and deletions, your affected record will exist in both and you can compare the differences based on the primary key.
blindman|||Also, there is very useful function COLUMNS_UPDATED() which you can use in trigger for checking what fields were updated.
Sunday, March 11, 2012
Conditional Send Mail Task
Hi,
I want send email if certain conditions are met (by send mail task)... if compnay records does not exists in some table (returns null)... not task failure.... how do I achieve this without using Script task?
does any one have an idea about it,
regards
You need conditional workflow: http://www.sqlis.com/default.aspx?306
You can base your expression on a boolean variable that can be set from various places, including a script task.
-Jamie
|||
In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?
ur help will be appreciated
|||Zadoras wrote:
In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?
ur help will be appreciated
Hmmm interesting one. probably the easiest way is to push that data into a flat file destination and then email that file as an attachment.
-Jamie
|||
that's what i was thinking... but i m searching for other way (may be the easiest way)
BTW... thanx for your support
if ne one come across to ne better idea than that please let me know
Conditional Running of DTS Jobs
However, there are certain specific holidays we don't want to run these jobs
that are stored in a holiday table. Since parameters can't be passed between
job steps, what is the best way for aborting the job on a specific day
without running the DTS job? Is there a way to set a success or failure flag
in jobs and use this flag to abort before running the next job step?
--
Larry Menzin
American Techsystems Corp.Couldn't you check your holiday table in each job step, using sql...for
example...
/*get today*/
select @.today = (select getdate())
/*if today isn't in the holiday table then kick off dts job*/
if not exists (select * from holiday_table where holiday = @.today)
xp_cmdshell 'dtsrun .....blah, blah'
Not sure if my syntax is quite right, and not sure how your dates are
formatted, but something like this should work.|||You could also create a job step as the first step, that checks the date and
fails the job.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:624FC617-C35C-4FDF-95F9-D31C5349C85D@.microsoft.com...
> We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
> However, there are certain specific holidays we don't want to run these
> jobs
> that are stored in a holiday table. Since parameters can't be passed
> between
> job steps, what is the best way for aborting the job on a specific day
> without running the DTS job? Is there a way to set a success or failure
> flag
> in jobs and use this flag to abort before running the next job step?
> --
> Larry Menzin
> American Techsystems Corp.
Conditional Running of DTS Jobs
However, there are certain specific holidays we don't want to run these jobs
that are stored in a holiday table. Since parameters can't be passed between
job steps, what is the best way for aborting the job on a specific day
without running the DTS job? Is there a way to set a success or failure flag
in jobs and use this flag to abort before running the next job step?
Larry Menzin
American Techsystems Corp.
Couldn't you check your holiday table in each job step, using sql...for
example...
/*get today*/
select @.today = (select getdate())
/*if today isn't in the holiday table then kick off dts job*/
if not exists (select * from holiday_table where holiday = @.today)
xp_cmdshell 'dtsrun .....blah, blah'
Not sure if my syntax is quite right, and not sure how your dates are
formatted, but something like this should work.
|||You could also create a job step as the first step, that checks the date and
fails the job.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:624FC617-C35C-4FDF-95F9-D31C5349C85D@.microsoft.com...
> We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
> However, there are certain specific holidays we don't want to run these
> jobs
> that are stored in a holiday table. Since parameters can't be passed
> between
> job steps, what is the best way for aborting the job on a specific day
> without running the DTS job? Is there a way to set a success or failure
> flag
> in jobs and use this flag to abort before running the next job step?
> --
> Larry Menzin
> American Techsystems Corp.
Conditional Running of DTS Jobs
However, there are certain specific holidays we don't want to run these jobs
that are stored in a holiday table. Since parameters can't be passed between
job steps, what is the best way for aborting the job on a specific day
without running the DTS job? Is there a way to set a success or failure flag
in jobs and use this flag to abort before running the next job step?
--
Larry Menzin
American Techsystems Corp.Couldn't you check your holiday table in each job step, using sql...for
example...
/*get today*/
select @.today = (select getdate())
/*if today isn't in the holiday table then kick off dts job*/
if not exists (select * from holiday_table where holiday = @.today)
xp_cmdshell 'dtsrun .....blah, blah'
Not sure if my syntax is quite right, and not sure how your dates are
formatted, but something like this should work.|||You could also create a job step as the first step, that checks the date and
fails the job.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:624FC617-C35C-4FDF-95F9-D31C5349C85D@.microsoft.com...
> We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
> However, there are certain specific holidays we don't want to run these
> jobs
> that are stored in a holiday table. Since parameters can't be passed
> between
> job steps, what is the best way for aborting the job on a specific day
> without running the DTS job? Is there a way to set a success or failure
> flag
> in jobs and use this flag to abort before running the next job step?
> --
> Larry Menzin
> American Techsystems Corp.
Thursday, March 8, 2012
Conditional Joins
(Assume tblStock and tblFilter are 1-to-1 on StockID)
DECLARE @.Min TINYINT , @.Max TINYINT
SELECT @.Min = 5 , @.Max = 10
SELECT
StockID
FROM
tblStock
INNER JOIN
tblFilter
ON
tblStock.StockID = tblFilter.StockID
AND
(
tblFilter.Value BETWEEN @.Min AND @.Max
OR
@.Min IS NULL OR @.Max IS NULL
)
Preferably I'd like to not do the join at all if either variable is null,
and I'd like it in a stored procedure so it's precompiled.
This is a simplified version of the query, in our system there are
aproximately 8 filters, which may or may not be required - and in any
combination.
I could, in theory create a stored proc for each combination of filter
inclusion - but this will be hard to maintain - especially if/when more
filters are introduced.
Thanks.
Rebecca.Please always include DDL with questions like this. It also helps to
include sample data and required results.
If Value is not nullable then maybe you just want to set @.min and @.max
to the min and max values for INT. Otherwise, use EXISTS:
SELECT stockid
FROM tblstock
WHERE EXISTS
(SELECT *
FROM tblfilter
WHERE tblStock.StockID = tblFilter.StockID
AND (stockid BETWEEN @.min AND @.max
OR @.min IS NULL
OR @.max IS NULL)) ;
David Portas
SQL Server MVP
--|||I have included the full ddl for the sample i've included.
Essentially what I want to do is put an IF around the Inner joins to exclude
the join if the parameters filtering the value are null.
Currently, we have the query dynamically built in the client, but this gives
compaliation overheads when various combinations are excluded/inculded and
we are investigating a pre-compiled version.
USE tempdb
/* Create tables */
IF OBJECT_ID('dbo.tblFilter2') IS NOT NULL DROP TABLE dbo.tblFilter2
IF OBJECT_ID('dbo.tblFilter1') IS NOT NULL DROP TABLE dbo.tblFilter1
IF OBJECT_ID('dbo.tblStock') IS NOT NULL DROP TABLE dbo.tblStock
CREATE TABLE dbo.tblStock ( StockID INT PRIMARY KEY CLUSTERED , Other
VARCHAR(5) NULL , Fields VARCHAR(5) )
CREATE TABLE dbo.tblFilter1 ( StockID INT PRIMARY KEY NONCLUSTERED , Value
INT , CONSTRAINT FK_dbo_tblFilter1_dbo_tblStock FOREIGN KEY ( StockID )
REFERENCES dbo.tblStock ( StockID ) )
CREATE TABLE dbo.tblFilter2 ( StockID INT PRIMARY KEY NONCLUSTERED , Value
DATETIME , CONSTRAINT FK_dbo_tblFilter2_dbo_tblStock FOREIGN KEY ( StockID )
REFERENCES dbo.tblStock ( StockID ) )
CREATE CLUSTERED INDEX CIX_dbo_tblFilter1 ON dbo.tblFilter1 ( Value )
CREATE CLUSTERED INDEX CIX_dbo_tblFilter2 ON dbo.tblFilter2 ( Value )
/* Populate sudo-random data */
INSERT INTO
dbo.tblStock
SELECT
N AS StockID
, NULL AS Other
, NULL AS Fields
FROM
dbo.tblNumbers /* contains n = 1 to 1,000,000 */
WHERE
N BETWEEN 1 AND 5000
INSERT INTO
dbo.tblFilter1
SELECT
StockID
, ( 5000 - StockID ) + 1 AS Value
FROM
dbo.tblStock
INSERT INTO
dbo.tblFilter2
SELECT
StockID
, DATEADD( d , -StockID , [Now] ) AS Value
FROM
dbo.tblStock
CROSS JOIN
( SELECT GETDATE() AS [Now] ) vwDate
/* Search Query */
GO
CREATE PROCEDURE
dbo.prRunFilter
@.Min INT
, @.Max INT
, @.FilerMonth DATETIME /* assumes midnight, first of month */
AS
SET DATEFORMAT YMD
DECLARE @.StartOfMonth DATETIME , @.EndOfMonth DATETIME
SELECT @.StartOfMonth = @.FilerMonth , @.EndOfMonth = DATEADD( ms , -3 ,
DATEADD( m , 1 , @.FilerMonth ) )
SELECT
tblStock.StockID
, tblFilter1.Value
, tblFilter2.Value
FROM
dbo.tblStock tblStock
INNER JOIN
(
SELECT
StockID
, Value
FROM
dbo.tblFilter1
WHERE
Value BETWEEN @.Min AND @.Max
) tblFilter1
ON
tblStock.StockID = tblFilter1.StockID
INNER JOIN
(
SELECT
StockID
, Value
FROM
dbo.tblFilter2
WHERE
Value BETWEEN @.StartOfMonth AND @.EndOfMonth
) tblFilter2
ON
tblStock.StockID = tblFilter2.StockID
GO
EXEC dbo.prRunFilter @.Min = 4490 , @.Max = 4500 , @.FilerMonth = {d
'2004-05-01'}|||I would create a view:
create view t1
as
SELECT
StockID
FROM
tblStock
INNER JOIN
tblFilter
ON
tblStock.StockID = tblFilter.StockID
in the procedure, I would
if @.Min IS NULL OR @.Max IS NULL
-- the plan for this may be simpler
select * from t1
else
-- this query might need a different plan
select * from t1
where tblFilter.Value BETWEEN @.Min AND @.Max
this way both the maintenance of the query is easy, as it's in a view,
and you have 2 plans|||Except that, as stated in my original post, we have 8 such filters...
This would result in 64 if/else statements and views to handle all the
combinations.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1128437238.893216.20870@.o13g2000cwo.googlegroups.com...
> I would create a view:
> create view t1
> as
> SELECT
> StockID
> FROM
> tblStock
> INNER JOIN
> tblFilter
> ON
> tblStock.StockID = tblFilter.StockID
> in the procedure, I would
> if @.Min IS NULL OR @.Max IS NULL
> -- the plan for this may be simpler
> select * from t1
> else
> -- this query might need a different plan
> select * from t1
> where tblFilter.Value BETWEEN @.Min AND @.Max
> this way both the maintenance of the query is easy, as it's in a view,
> and you have 2 plans
>|||no - just one view, and yes, some if ... else statements.
Believe me or not, if you are selecting from big tables, then the
performance penalty of running the generic plan may be pretty high.
That, of course, has been said assuming that there are indexes that
could be used for some of your filters.
Saturday, February 25, 2012
Conditional Count in RDLC Expression
doesnt equal certain values. Here is what I tried:
=Count(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
Obviously this didn't work. Can anyone point me in the right
direction?
Thanks,
JasonOn Mar 14, 3:36=A0pm, Jason Wilson <wils...@.ausrad.com> wrote:
> I'd like to put a count in a textbox but only a count where a column
> doesnt equal certain values. =A0Here is what I tried:
> =3DCount(Fields!ReportStatus.Value<>"MAILED" AND Fields!
> ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
> Obviously this didn't work. =A0Can anyone point me in the right
> direction?
> Thanks,
> Jason
See if this works:
=3DCOUNT(IIF(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!
ReportStatus.Value<>"TRANSMIT",1,0))
HTH
toolman|||On Mar 14, 3:36=A0pm, Jason Wilson <wils...@.ausrad.com> wrote:
> I'd like to put a count in a textbox but only a count where a column
> doesnt equal certain values. =A0Here is what I tried:
> =3DCount(Fields!ReportStatus.Value<>"MAILED" AND Fields!
> ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
> Obviously this didn't work. =A0Can anyone point me in the right
> direction?
> Thanks,
> Jason
See if this works:
=3DSUM(IIF(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!
ReportStatus.Value<>"TRANSMIT",1,0))
HTH
toolman|||It did thanks
Tuesday, February 14, 2012
Concerning .net and SQL Procedures
Recently i had to write a script in sql to compare multiple tables to get a result of items that do not conform to certain business logic. In doing so i wrote all of this information into a sql parameter which branches out to a few other parameters within the parameter.
Now if you need the code just let me ask, but this is a general question to see if it has occured for anyone else.
The problem i am recieving is when i access the code from a .net windows application it tells me:
Error Message:
Insert Error: Column name or number of supplied values does not match table definition.
Insert Error: Column name or number of supplied values does not match table definition.
Procedure Errored On: val_GetDuplicateItemsFromAssignment
Line Number: 16
However when i run the sql parameter within SQL it accesses it just find. This is using the same parameter values.
Does anyone know why this could be happening?
Please do show the code used to insert the values.