Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:
>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
>[StartDate] [datetime] NULL ,
>[EndDate] [datetime] NULL ,
>[Quantity] [int] NULL ,
>[Bar_Code] [varchar] (10) NULL ,
>[Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))
|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
>
sql
Showing posts with label failed. Show all posts
Showing posts with label failed. Show all posts
Thursday, March 22, 2012
Creating a string - Cursor or Case
Hi all,
I need to perform a validation test on fields in stored data for each row an
d
updating the Comment field with the reasons why test failed where appropriat
e.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update commen
t
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:
>Hi all,
>I need to perform a validation test on fields in stored data for each row a
nd
>updating the Comment field with the reasons why test failed where appropria
te.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comme
nt
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
<
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>
I need to perform a validation test on fields in stored data for each row an
d
updating the Comment field with the reasons why test failed where appropriat
e.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update commen
t
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment =
CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via droptable.com"
<u24035@.uwe> wrote:
>Hi all,
>I need to perform a validation test on fields in stored data for each row a
nd
>updating the Comment field with the reasons why test failed where appropria
te.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comme
nt
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =
>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
<
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via droptable.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment =
> CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200703/1
>
Creating a string - Cursor or Case
Hi all,
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment = CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
--
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via SQLMonster.com"
<u24035@.uwe> wrote:
>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via SQLMonster.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment => CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>
I need to perform a validation test on fields in stored data for each row and
updating the Comment field with the reasons why test failed where appropriate.
The real scenario has quite a number of fields for me to use a CASE also I
want to avoid using a looping mechanism to build the string to update comment
field. I'm a novice of sort so I'm curious as to wether there is another
approach I use?
CREATE TABLE [dbo].[TableA] (
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Quantity] [int] NULL ,
[Bar_Code] [varchar] (10) NULL ,
[Comment] [varchar] (255)NULL
) ON [PRIMARY]
GO
My initial approch is roughly something like this
UPDATE [dbo].[TableA]
SET Comment = CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
(getdate()) THEN 'Invalid quantity' END
WHERE (0 = isnumeric(quantity)
or startdate > (getdate())
or enddate < (getdate())
or 0 = isdate (startdate)
or 0 = isdate (enddate))
--
obelix
"Whether you think you can or you think you cant you are right" ... Anon
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1Put each test in an individual CASE, concatenating the results to get
one string. The SUBSTRING is to get rid of the leading comma and
space that the first message will have.
UPDATE [dbo].[TableA]
SET Comment = SUBSTRING(
CASE WHEN 0 = isnumeric(quantity)
THEN ', Invalid quantity'
ELSE ''
END +
CASE WHEN startdate > getdate()
THEN ', startdate in future'
ELSE ''
END +
CASE WHEN enddate < getdate()
THEN ', enddate in past'
ELSE ''
END +
CASE WHEN 0 = isdate (startdate)
THEN ', invalid startdate'
ELSE ''
END +
CASE WHEN 0 = isdate (enddate)
THEN ', enddate in past'
ELSE ''
END, 3, 100)
WHERE (0 = isnumeric(quantity)
or startdate > getdate()
or enddate < getdate()
or 0 = isdate (startdate)
or 0 = isdate (enddate))
Roy Harvey
Beacon Falls, CT
On Sun, 18 Mar 2007 11:08:00 GMT, "obelix via SQLMonster.com"
<u24035@.uwe> wrote:
>Hi all,
>I need to perform a validation test on fields in stored data for each row and
>updating the Comment field with the reasons why test failed where appropriate.
>The real scenario has quite a number of fields for me to use a CASE also I
>want to avoid using a looping mechanism to build the string to update comment
>field. I'm a novice of sort so I'm curious as to wether there is another
>approach I use?
>CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
>) ON [PRIMARY]
>GO
>My initial approch is roughly something like this
>UPDATE [dbo].[TableA]
>SET Comment =>CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate <
>(getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
>WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
>(getdate()) THEN 'Invalid quantity' END
>WHERE (0 = isnumeric(quantity)
>or startdate > (getdate())
>or enddate < (getdate())
>or 0 = isdate (startdate)
>or 0 = isdate (enddate))|||Roy's method looks best for actually DOING the update. I will point out a
significant performance problem though. Your initial INSERT into the table
probably puts an empty string or NULL into the Comment field. Thus EVERY
row that gets updated will have to be forwarded (if a HEAP table) or have
the page possibly reordered/split (if table has clustered index) since the
Comment for those rows with a problem will be non-zero in length. This will
quickly result in VERY fragmented data and poor performance.
You may also wish to put in a tinyint field to track whether or not the data
has been scanned or not and set it to a specific value once you have done an
evaluation on a row. If indexed, this column could speed performance by
avoiding table scans for each update sweep once you get a large number of
rows in the table.
TheSQLGuru
President
Indicium Resources, Inc.
"obelix via SQLMonster.com" <u24035@.uwe> wrote in message
news:6f5c7e6489060@.uwe...
> Hi all,
> I need to perform a validation test on fields in stored data for each row
> and
> updating the Comment field with the reasons why test failed where
> appropriate.
> The real scenario has quite a number of fields for me to use a CASE also I
> want to avoid using a looping mechanism to build the string to update
> comment
> field. I'm a novice of sort so I'm curious as to wether there is another
> approach I use?
> CREATE TABLE [dbo].[TableA] (
> [StartDate] [datetime] NULL ,
> [EndDate] [datetime] NULL ,
> [Quantity] [int] NULL ,
> [Bar_Code] [varchar] (10) NULL ,
> [Comment] [varchar] (255)NULL
> ) ON [PRIMARY]
> GO
> My initial approch is roughly something like this
> UPDATE [dbo].[TableA]
> SET Comment => CASE WHEN (0 = isnumeric(quantity) AND startdate > (getdate()) AND enddate
> <
> (getdate()) THEN 'Invalid quantity, startdate in future, enddate in past'
> WHEN (0 = isnumeric(quantity) AND startdate < (getdate()) AND enddate >
> (getdate()) THEN 'Invalid quantity' END
> WHERE (0 = isnumeric(quantity)
> or startdate > (getdate())
> or enddate < (getdate())
> or 0 = isdate (startdate)
> or 0 = isdate (enddate))
> --
> obelix
> "Whether you think you can or you think you cant you are right" ... Anon
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>
Monday, March 19, 2012
Creating a Proxy Account
I am trying to run SSIS packages under SQL Server Agent 2005 and I keep getting a package failed error in the event viewer.
I've heard that I need to set up a proxy account. I have found the following code and need a little explanation on what all the parts mean since I am very new to this:
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'
Use msdb
Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'
Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'
Let's say for the sake of argument my domain is called CompanyInc and I log into windows with my name Philip_Jaques and my password is badpassw0rd. Would I modify the above code this way to create my proxy?
Use master
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'CompanyInc\Philip_Jaques', secret = 'badpassw0rd'
Use msdb
Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'
Sp_grant_login_to_proxy @.login_name='Philip_Jaques', @.proxy_name='MyProxy'
Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'
Also, when I create this proxy account where in SQL Server 2005 can I go to view it and its properties? And assuming I get the proxy account set up correctly, how do I get my current jobs to start using it so they will successfully run?
Thanks in advance for your help and advice!
I've never heard of having to create a proxy to get SqlAgent to run SSIS accounts. Sql Agent should be setup to run under a service account already, and will run SSIS packages no problem. Where did you find this information?|||This article pretty much explains my problem:
http://support.microsoft.com/default.aspx/kb/918760
Subscribe to:
Posts (Atom)