Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
Try:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>
|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tuxTry:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>

Creating a string from Date Fields

I have a table with a startdatetime and an enddatetime column such as:

StartDateTime EndDateTime what I want to see returned
is:
01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
1:30PM"
01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
10:30AM"

Maybe someone has accomplished this aready in a stored procedure and
has an example of how to do it?
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a table with a startdatetime and an enddatetime column such as:
> StartDateTime EndDateTime what I want to see returned
> is:
> 01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
> 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
> 10:30AM"
> Maybe someone has accomplished this aready in a stored procedure and
> has an example of how to do it?

Looks like you need to use the following T-SQL functions/operators:

convert() - to format the date.
substring() - to extract the portions of the end time you want to display
CASE - to determine whether all of or just part of endtime is to be
included.

Then again, a lot of these display issuses are often best handled client
side.

The above-mentioned functions are all documented in Books Online, see
the T-SQL Reference. convert() may be tricky to find, as it is under
the topic CAST and CONVERT.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401291443.47b9d2c8@.posting.google.c om...
> I have a table with a startdatetime and an enddatetime column such as:
> StartDateTime EndDateTime what I want to see returned
> is:
> 01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/2004
> 1:30PM"
> 01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/2004
> 10:30AM"
> Maybe someone has accomplished this aready in a stored procedure and
> has an example of how to do it?
> lq

You could do this with CONVERT() and various string functions, but it would
be better to use your client application to handle this. The dates above are
not correct for most European formats, for example, and it's much easier to
deal with client locale settings in a client-side application.

Simon

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 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

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
>

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
>

Creating a Password Retrieval Stored Procedure

I would like to create a stored procedure to pass a login name and password
to and have it verify the info and return a few fields of data back to the
calling function from a javascript page. I have tried a few things but can'
t
seem to verify the data and return a value to verify the data.One method is to use NOT EXISTS:
CREATE PROC MyProc
@.UserID varchar(128),
@.Password varchar(128)
AS
SET NOCOUNT ON
IF NOT EXISTS
(
SELECT MyData
FROM Users
WHERE UserID = @.UserID AND
Password = @.Password
)
BEGIN
RAISERROR ('Invalid user or password', 1, 0)
END
SELECT MyData FROM MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"EvanK" <EvanK@.discussions.microsoft.com> wrote in message
news:210A9660-5874-4AA0-9DF9-1CFE9487868B@.microsoft.com...
>I would like to create a stored procedure to pass a login name and password
> to and have it verify the info and return a few fields of data back to the
> calling function from a javascript page. I have tried a few things but
> can't
> seem to verify the data and return a value to verify the data.|||I changed the code as follows:
CREATE PROCEDURE dbo.MM_Chk_User
@.UserID varchar(128),
@.Password varchar(128)
AS
SET NOCOUNT ON
IF NOT EXISTS
(
SELECT First_Name, Last_Name
FROM LogonNames
WHERE Name = @.UserID AND
Password = @.Password
)
BEGIN
RAISERROR ('Invalid user or password', 1, 0)
END
SELECT First_Name, Last_Name
FROM LogonNames
WHERE Name = @.UserID AND
Password = @.Password
GO
I get the following error
[SQL Server]Invalid value 0 for state. Valid ranges is from 1 to 127
"Dan Guzman" wrote:

> One method is to use NOT EXISTS:
> CREATE PROC MyProc
> @.UserID varchar(128),
> @.Password varchar(128)
> AS
> SET NOCOUNT ON
> IF NOT EXISTS
> (
> SELECT MyData
> FROM Users
> WHERE UserID = @.UserID AND
> Password = @.Password
> )
> BEGIN
> RAISERROR ('Invalid user or password', 1, 0)
> END
> SELECT MyData FROM MyTable
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "EvanK" <EvanK@.discussions.microsoft.com> wrote in message
> news:210A9660-5874-4AA0-9DF9-1CFE9487868B@.microsoft.com...
>
>

creating a matching program

Hi,

I'm just looking for some general advice on how to approach something.

I have two tables A and B, containg common fields of product, sales
date and qtyo.
Some records contain the same data and I want to dump them in a new
table called C and leave Tables A and B containg only data that
doesn'r match.

How do i go about approaching this??

Regards,
CiarnOn 22 Nov 2004 02:18:39 -0800, Ciar?n wrote:

>Hi,
>I'm just looking for some general advice on how to approach something.
>I have two tables A and B, containg common fields of product, sales
>date and qtyo.
>Some records contain the same data and I want to dump them in a new
>table called C and leave Tables A and B containg only data that
>doesn'r match.
>How do i go about approaching this??
>Regards,
>Ciarn

Hi Ciarn,

INSERT INTO C
SELECT A.Column1, A.Column2, ..., A.ColumnN
FROM A
INNER JOIN B
ON B.Column1 = A.Column1
AND B.Column2 = A.Column2
......
AND B.ColumnN = A.ColumnN

DELETE FROM A
WHERE EXISTS (SELECT *
FROM C
WHERE C.Column1 = A.Column1
AND C.Column2 = A.Column1
.......
AND C.ColumnN = A.ColumnN)

DELETE FROM B
WHERE EXISTS (SELECT *
FROM C
WHERE C.Column1 = B.Column1
AND C.Column2 = B.Column1
.......
AND C.ColumnN = B.ColumnN)

Enclose this all in a transaction, add error handling and you're set.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Cheers Hugo

Creating a formula on a table column

Hi.
Some background first...
The tables are in SQL server 2000 and the front end is an Access
project.
I have a table with multiple ID fields which is populated by several
forms and other tables. The Main ID has a autonumber function on it. 1
other is made from various components from the row in the table.
The column i am having trouble with, all i want to do is increment it
by 1 ( just to prevent any duplication of numbers ) but i want to do
this on the table,
I could do it on the form when data is inputted, but due to large
number of forms etc... if it could be done on the table it would be
easier.
There is a formula function but i can't get it return the Max column
value from a table. Is this type of thing possible.
I can retun values, but how do i define a query within this function?
Many thanks for any help / advice
john."john" <j.w.horlock@.leeds.ac.uk> wrote in message
news:8456800a.0310020656.c97d681@.posting.google.com...
> Hi.
> Some background first...
> The tables are in SQL server 2000 and the front end is an Access
> project.
> I have a table with multiple ID fields which is populated by several
> forms and other tables. The Main ID has a autonumber function on it. 1
> other is made from various components from the row in the table.
> The column i am having trouble with, all i want to do is increment it
> by 1 ( just to prevent any duplication of numbers ) but i want to do
> this on the table,
> I could do it on the form when data is inputted, but due to large
> number of forms etc... if it could be done on the table it would be
> easier.
> There is a formula function but i can't get it return the Max column
> value from a table. Is this type of thing possible.
> I can retun values, but how do i define a query within this function?
> Many thanks for any help / advice
> john.
On the SQL table define the column thus:
ALTER TABLE tblFlibble(
Col1 int Identity (1,1) NOT NUL,
col2,
col3, etc)
If you already have data in the table and want to preserve the existing
values, change the IDENTITY to
IDENTITY(*number higher than your highest existing number*,1)
To find the number you have just created when you ad a row, select the
@.@.IDENTITY variable
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003

Creating a Date - or Time - Only Column in SQL Server

I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a tabl
e
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO

> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Why don't you let the presentation side of things handle the formatting an
d
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.

Creating a Date - or Time - Only Column in SQL Server

I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a table
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO
> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Why don't you let the presentation side of things handle the formatting and
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.

Creating a Date - or Time - Only Column in SQL Server

I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a table
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George
> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO

> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Why don't you let the presentation side of things handle the formatting and
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.

creating a database, tables, records, and fields in a Sql server 2005 express

I am at my wits end as to how to do this.
I have downloaded Sql server management Studio and tried to create a database but I can't figure it out. There is an almost nonexistant help file so I am lost as to how to start. I have succeeded in looking at some system databases but that is about the extent of it. Can someone show me the proper procedure? Am I using the wrong tool?
Thanks,
Paul

If you are using the managment studio, right click on the Databases node, --> New Database. If you are using a script tool for this, the fastest way to create a database is CREATE DATABASE <name>, which stores the data files in the defautl location, specified at setup time.

HTH, Jens Suessmeyer.

|||Thanks, Jen! boy was that ever easy! I've got tables and columns now too! Tell me though, can I input some data in the management Studio Also?
Thanks,
Paul
|||Well I think I spoke too soon. Now in VWD I drop a gridview and try to connect it to my database. When I am building a connection string, I get the dialog box, "Add Connection. When I try to browse and select my database I get this real long error, the essence of "Unable to open the phsical file c:\blah|blah\blah\prince.mdb. Operating system error 32(The process cannot access the file because it is being used by another process)" I know that it isn't being used because I closed the database and exited Management Studio.
Thanks,
Paul
|||

Make sure the database is AutoClose=true. If the database is not set to automatically close, the SQL Server service will keep it open as long as the server is running.

You can check this in the Options page of the database properties dialog in Management Studio.

Hope this helps,

Steve

|||Thanks, Steve. That was the problem. However, I still can't add data or insert blank records in the database. I noticed when I used an Access database I was able to edit and delete the data. I would like to be able to do that with Sql server express database also and in addition to inserting a blank record. I am using a a gridview component.

In Visual basic 6.0 I could drop a few buttons on the form and with next, previous delete and insert methods I could alter the database where the columns were bound to individule Textboxes.
I have a lot to learn and there are very few books and tutorials written with info on what I want to learn.

I guess the gridview tasks are limited because I have no data to edit or delete, and I don't know how to use the Management Studio to enter the data. :(
|||I tried executing a query like this:
INSERT INTO family VALUES ("Paul", "Handley"); family is my table and there are only two columns. I tried putting the column names but got an error. I also get this error when I try to run the above query:
Msg 128, Level 15, State 1, Line 1
The name "Paul" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Any suggestions?
Paul
|||

You can either set the following command:

SET QUOTED_IDENTIFIER is OFF

to use double quotation marks, or you use single quotation marks which wont cause the problem.

HTH, jens Suessmeyer.

|||Thanks, Jen. That did the trick. As far as the inserting and the editing of a database record within the web application I am developing, I still can't do that. Is it possible to attach a query to a button and just insert a blank record. Like this:
Insert into family Values( '','') **Those are single quotes**
Thanks,
Paul
|||Ok I've got to the point where when I am configuring the datasource and I get to the "Configure the select statement and I choose "Specify columns from a table or view. I click on te advanced tab and I want to choose"Generate Insert, Update and Delete Statements" but the whole dialog box is grayed out! What do I do now? I want the capabilities of editing, insertsing, deleteing and updating my records
Thanks,
Paul

|||Ok I found the where I could add the various links to Update Insert and edit my fields and when I click on New I get the blank fields to add new data but when I click on update I get this error msg:

Inserting is not supported by data source 'SqlDataSource1' unless InsertCommand is specified.

I feel sure If I could fix this I would be on my way, but the as I stated above the whe option are grayed out!

Will someone pleeese answer this post!

|||Ok, that's it. If you are going to just leave me hanging here, I'm through. I'm going back to Linux and PHP and Mysql. I should have know better than to try this Microsoft garbage!
Paul
|||

Hi Paul,

there is no need yelling in this group. As we are doing this all in our spare free time and on a voluntary basis, the poster of a question sometimes has to wait until he gets an answer. IMHO I would rather learn the basis things first, before comming to the groups and posting question which would have been solved if you just took a walkthough-easy-to-use-example.

The question you are posting is related that you appearantly didn′t specifiy which command has to be executed in the case of an insert. YOu can either specify your own command or use a commanbuilder (But that are , as I told below some basic things which wouldn′t concern you if you have done some ADO.NET basics first)

Fell free to come any time again in this group, but be aware that you sometime need to have a bit patience.

HTH; jens Suessmeyer.

|||p3aul,
your statement:
INSERT INTO family VALUES ("Paul", "Handley");
should be:
INSERT INTO family (fieldname, fieldname) VALUES ("Paul", "Handley");
where fieldname is the name of your field.
|||Well I wasn't aware that I was shouting, I just c & p the error that appeared on the webpage. i figured it would just paste as normal 10 or 12 pt type. I enlarged the fon't and chose the color red in my plea for help because Time was going by and I was beginning to lose my train of thought.. I don't sit idley by and wait for someone to give me an idea that might help, I try to persue a solution on my on. If I leave the a file i am working on and load something else in and put my mind ont I am apt to forget what I was doing on the first file. I apologise for feeling frustrated but Microsoft tools are frustraing things to work with.

tonic999 Thank you for the post. I don't remember where I was now. In working with MySQL , though, in the insert statement (fieldname, fieldname) are optional.

My original question, If I can find the file I was working on, still stands, to wit:
Ok I've got to the point where when I am configuring the datasource and I get to the "Configure the select statement and I choose "Specify columns from a table or view. I click on te advanced tab and I want to choose"Generate Insert, Update and Delete Statements" but the whole dialog box is grayed out! What do I do now? I want the capabilities of editing, insertsing, deleteing and updating my records
Thanks,
Paul

Creating a chart from only one row of data

I am trying to generate a chart from only one row of data that contains the
twelve fields that represent the data-points for the chart ( the chart will
have 4 sets of two columns and a 4 point line graph for a total of twelve
points). I know which field is which datapoint but the chart object will not
let me define individual data-points. It expects to create a series from
multiple rows of data. Grouping and Series fields also rely on multiple rows
of data. I can put the fields into a table on the report, but cannot
generate a chart off of the table. I am not a programmer, meaning that I
only know how to manipulate properties - code samples will not help. I would
appreciate any help that could be offered to a layman report designerIt may be possible to write a query such that the columns in the table
appear as rows. For example, if A, B and C are columns of table FOO then
SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO will
return 3 rows even if the table has only 1 row.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
>I am trying to generate a chart from only one row of data that contains the
> twelve fields that represent the data-points for the chart ( the chart
> will
> have 4 sets of two columns and a 4 point line graph for a total of twelve
> points). I know which field is which datapoint but the chart object will
> not
> let me define individual data-points. It expects to create a series from
> multiple rows of data. Grouping and Series fields also rely on multiple
> rows
> of data. I can put the fields into a table on the report, but cannot
> generate a chart off of the table. I am not a programmer, meaning that I
> only know how to manipulate properties - code samples will not help. I
> would
> appreciate any help that could be offered to a layman report designer|||A minor clarification: You'd have to convert all columns in the SELECTs in
the UNION to same datatype using Convert() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rajeev Karunakaran [MSFT]" <rajeevkarunakaran@.online.microsoft.com> wrote
in message news:eDCdqlthEHA.596@.TK2MSFTNGP11.phx.gbl...
> It may be possible to write a query such that the columns in the table
> appear as rows. For example, if A, B and C are columns of table FOO then
> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO will
> return 3 rows even if the table has only 1 row.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
> >I am trying to generate a chart from only one row of data that contains
the
> > twelve fields that represent the data-points for the chart ( the chart
> > will
> > have 4 sets of two columns and a 4 point line graph for a total of
twelve
> > points). I know which field is which datapoint but the chart object
will
> > not
> > let me define individual data-points. It expects to create a series
from
> > multiple rows of data. Grouping and Series fields also rely on multiple
> > rows
> > of data. I can put the fields into a table on the report, but cannot
> > generate a chart off of the table. I am not a programmer, meaning that
I
> > only know how to manipulate properties - code samples will not help. I
> > would
> > appreciate any help that could be offered to a layman report designer
>|||I am having a similar problem. I have 3 columns (cntY1, cntY2, cntY3)
I would like each one to be a separate bar on the graph. The stored
Procedure will only ever return one row.
How can you do this using Reporting Services? What should I put in the
category column?
Any help will be appreciated,
Rajeev Karunakaran [MSFT] wrote:
> It may be possible to write a query such that the columns in the
table
> appear as rows. For example, if A, B and C are columns of table FOO
then
> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO
will
> return 3 rows even if the table has only 1 row.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
> >I am trying to generate a chart from only one row of data that
contains the
> > twelve fields that represent the data-points for the chart ( the
chart
> > will
> > have 4 sets of two columns and a 4 point line graph for a total of
twelve
> > points). I know which field is which datapoint but the chart
object will
> > not
> > let me define individual data-points. It expects to create a
series from
> > multiple rows of data. Grouping and Series fields also rely on
multiple
> > rows
> > of data. I can put the fields into a table on the report, but
cannot
> > generate a chart off of the table. I am not a programmer, meaning
that I
> > only know how to manipulate properties - code samples will not
help. I
> > would
> > appreciate any help that could be offered to a layman report
designer|||Changing the stored procedure is not an option.|||Just create 3 values - one for each column, no categories or series group.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hovercraft2x@.gmail.com> wrote in message
news:1109017211.242947.155410@.c13g2000cwb.googlegroups.com...
>I am having a similar problem. I have 3 columns (cntY1, cntY2, cntY3)
> I would like each one to be a separate bar on the graph. The stored
> Procedure will only ever return one row.
> How can you do this using Reporting Services? What should I put in the
> category column?
> Any help will be appreciated,
> Rajeev Karunakaran [MSFT] wrote:
>> It may be possible to write a query such that the columns in the
> table
>> appear as rows. For example, if A, B and C are columns of table FOO
> then
>> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO
> will
>> return 3 rows even if the table has only 1 row.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
>> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
>> >I am trying to generate a chart from only one row of data that
> contains the
>> > twelve fields that represent the data-points for the chart ( the
> chart
>> > will
>> > have 4 sets of two columns and a 4 point line graph for a total of
> twelve
>> > points). I know which field is which datapoint but the chart
> object will
>> > not
>> > let me define individual data-points. It expects to create a
> series from
>> > multiple rows of data. Grouping and Series fields also rely on
> multiple
>> > rows
>> > of data. I can put the fields into a table on the report, but
> cannot
>> > generate a chart off of the table. I am not a programmer, meaning
> that I
>> > only know how to manipulate properties - code samples will not
> help. I
>> > would
>> > appreciate any help that could be offered to a layman report
> designer
>

Creatiing a link to download an attachment

I have a database where one of the fields is a large binary object. I would
like to create a report where one of the columns shows a link to the
"attachment". When the user clicks on the link, they should be able to
download the raw binary data (with the mime type of my choosing). Can this
be done?
Thanks,
SteveYou would need to put a URL into the table cell as a hyperlink and then on
your web server, process that URL by obtaining the attachment from the
database and returning it. There is no native support for this in Reporting
Services.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Stephen Walch" <swalch@.proposion.com> wrote in message
news:%23WNOOaQkEHA.644@.tk2msftngp13.phx.gbl...
> I have a database where one of the fields is a large binary object. I
would
> like to create a report where one of the columns shows a link to the
> "attachment". When the user clicks on the link, they should be able to
> download the raw binary data (with the mime type of my choosing). Can
this
> be done?
> Thanks,
> Steve
>
>

Createing SQL data base record for comma delimted records

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

hi,

have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720600&SiteID=1... you can there find a hint on how to bulk insert using a format file into an existing table, but, again, this is to populate a table object..

if you have to create a brand new database, you have to execute a "CREATE DATABASE newDB" statement, using the syntax provided at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp, then you have to create the table as well or use a SELECT .. INTO statement which creates the destination table at execution time (the table must of course not already exists )

regards

|||

Thank you for the info! I was planning to create the database and file directly in SQL Express! It the stored procedure that will populate the data file that has me hung up! The original file (the CVS - comma delimited file) needs to be inserted into the database file. I seem to know the direction to go--but how to

Once again thank you

|||

hi,

I do apologise and please excuse my poor english, but I'm not able to understand your requirements...

can you please rephrase?

again, please excuse my poor understanding..

regards

|||Guess I am not explaining my problem correctly, my lack of understanding SQL is not helping. I have been tasked to create a data base using SQL Express (this part I think I know how to do) an then write a stored procedure to create a table. The input table is a CSV file that will have up to ten fields in it. Being a CSV file the fields are seperated by comma. After I create this table (with the data in it) I have to display it using VB.NET.|||

hi,

try having a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=709813&SiteID=1..

regards

Createing SQL data base record for comma delimted records

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

hi,

have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720600&SiteID=1... you can there find a hint on how to bulk insert using a format file into an existing table, but, again, this is to populate a table object..

if you have to create a brand new database, you have to execute a "CREATE DATABASE newDB" statement, using the syntax provided at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp, then you have to create the table as well or use a SELECT .. INTO statement which creates the destination table at execution time (the table must of course not already exists )

regards

|||

Thank you for the info! I was planning to create the database and file directly in SQL Express! It the stored procedure that will populate the data file that has me hung up! The original file (the CVS - comma delimited file) needs to be inserted into the database file. I seem to know the direction to go--but how to

Once again thank you

|||

hi,

I do apologise and please excuse my poor english, but I'm not able to understand your requirements...

can you please rephrase?

again, please excuse my poor understanding..

regards

|||Guess I am not explaining my problem correctly, my lack of understanding SQL is not helping. I have been tasked to create a data base using SQL Express (this part I think I know how to do) an then write a stored procedure to create a table. The input table is a CSV file that will have up to ten fields in it. Being a CSV file the fields are seperated by comma. After I create this table (with the data in it) I have to display it using VB.NET.|||

hi,

try having a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=709813&SiteID=1..

regards