Assistance with Trigger

Hi,

Hopefully this will be painless for you guys/gals - however due to my lack of skills/knowledge I need some clarification.

I have table_X which I have a trigger on INSERT setup.
This trigger updates Field_2 = '1' and inserts some rows in another table.

Is there some way that I can restrict this trigger to only run when Field_1 = "BLAH"
So essentially I am trying to find out how I can pull information/data from the record that fired the trigger and use this in the trigger? (ie to check if Field_1 = "BLAH" and to use Field_3 to further restrict the underlying triggers' updates and inserts)

Hopefully I have given enough information on this one - if not please let me know any points that I should need to clarify.

Thanks in advance for your help!!!

Cheersnow, you have to make sure you understand that no matter how many rows you insert, the trigger will fire only once per batch, ok?

if exists (select * from inserted where field_a = 'blah')
-- do your stuff|||Now that is a prompt response - thanks!

Yeah I understand that the trigger will only fire once per batch

So here is another stupid question - where you say select * from inserted. Is inserted a key word here or where you using that as my table name?
Because basically what I am after is (if it is possible) to have (for example) a record inserted with field_3 = 1234, and field_1 = "BLAH", then the trigger to go "okay" field_1 = BLAH and then carry on and use the field_3 value (ie 1234) in the remaining query to build other records in other tables.

If what I think is correct - the "inserted" word is actually just my table name - then the trigger will still be firing a lot of times because there is already a lot of records in the table with Field_1 = BLAH....

I fear I might be going round in circles and not making a hell of a lot of sense....*argh*|||no, inserted is an internal table visible only for the trigger itself. that's where new values resulting from your insert statement are stored.|||Sorry to keep asking a million and one questions, but so this table "inserted" which can be queried from the trigger (eg select * from updated), does this have the same structure (ie fields and field names) as the actual table which the data is going to be inserted into?

And is there any way where I can pull values from that table and use them as variables in the trigger?|||the answer is YES, except there are only 2 internal table, inserted and deleted. when the number of rows in both is the same it's an update operation, if deleted table is empty then it's an insert. and again, the structure of both is identical to the structure of the table for which the trigger was created.

edited:

and yes, you can store all values into variables from inserted, or reference that table throughout the execution of the trigger.|||Brilliant!!

Thanks heaps for your help!

Assistance with Stored Procedure and ASPX Page Needed

Hello, I have the following stored procedure and the following aspx page. I am trying to connect this aspx page to the stored procedure using the SqlDataSource. When the user enters a branch number in textbox1, the autonumber generated by the database is returned in textbox2. I am not quite sure what to do to get this to execute. Can someone provide me assistance? Will I need to use some vb.net code behind?

Stored Procedure

CREATE PROCEDURE InsertNearMiss @.Branch Int, @.Identity int OUT ASINSERT INTO NearMiss (Branch)VALUES (@.Branch)

SET @.Identity = SCOPE_IDENTITY()

GO

ASPX Page

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NearMissConnectionString%>" InsertCommand="InsertRecord"InsertCommandType="StoredProcedure"SelectCommand="InsertRecord" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameterControlID="TextBox1"Name="Branch"PropertyName="Text"Type="Int32"/> <asp:ControlParameterControlID="TextBox2"Direction="InputOutput"Name="Identity"PropertyName="Text"Type="Int32"/> </SelectParameters> <InsertParameters> <asp:ParameterName="Branch"Type="Int32"/> <asp:ParameterDirection="InputOutput"Name="Identity"Type="Int32"/> </InsertParameters> </asp:SqlDataSource> <asp:TextBoxID="TextBox1"runat="server"></asp:TextBox>

<asp:TextBoxID="TextBox2"runat="server"></asp:TextBox>

Here's all about getting the value of the most recently added record:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

SqlDataSource options are about 3/4 of the way down.

|||

It appears you are only doing an insert, so you can get rid of the whole <selectParameters> collection. In this case, I would create a handler for onInserted and have the handler insert the output value from the procedure into the textbox 2 box. To fire the event, put a button that has an onclick event handler that calls SqlDataSource1.Insert(). This fires the insert command of the datasource. Below is an example page and codebehind

12 id="testSource"3runat="server"4InsertCommand="usp_ins_test"5InsertCommandType="StoredProcedure"6ConnectionString='<%$ ConnectionStrings:test %>'7OnInserted="testSource_Inserted">89"TextBox1" Name="prm_b" Direction="Input" Type="int32" PropertyName="Text">10"prm_r" Direction="Output" Type="Int32">111213branch: "TextBox1" runat="server">
14returnedvalue: "TextBox2" runat="server">15"btnSubmit" runat="server" Text="Add New Branch" OnClick="btnSubmit_Click"/>

 Code Behind:

 
protected void btnSubmit_Click(object sender, EventArgs e) {if (Page.IsValid) { testSource.Insert(); } }protected void testSource_Inserted(object sender, SqlDataSourceStatusEventArgs e) {if (e.Exception !=null)//display an error message to the screen e.ExceptionHandled =true;else TextBox2.Text = e.Command.Parameters["@.prm_r"].Value.ToString(); }
 

That should do it.

--D

Assistance with Stored Procedure

I am running SQL Server 7.0 and using a web interface. I would like
for a user to be able to input multiple values into a single field
with some sort of delimiter (such as a comma). I want to pass this
field into a Stored Procedure and have the stored procedure use the
data to generate the resutls.

Example:

Web page would ask for ID number into a field called IDNum. User
could input one or many ID numbers separated by a comma or some other
delemiter - could even be just a space (113, 114, 145).

SQL statement in Stored Procedure is something like this:

Select * from tblEmployess where IDNumber = @.IDNum

I need the SQL statement to somehow use an "or" or a "loop" to get all
of the numbers passed and use the delimiter to distinguish when the
"loop" stops.

I obtained a module from a friend that allows me to do this in access,
but have recently converted everything to SQL server and web
interface. Now, everyone in the office expects to be able to
accomplish the same results via the web.

Any help is appreciated. If you need any additional information to
provide me some assistance, please email me at
tod.thames@.nc.ngb.army.mil.

Thanks in advance.

TodTake a look at http://www.algonet.se/~sommar/arrays-in-sql.html.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:5ed144f0.0310050454.369f4994@.posting.google.c om...
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
> Example:
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
> SQL statement in Stored Procedure is something like this:
> Select * from tblEmployess where IDNumber = @.IDNum
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@.nc.ngb.army.mil.
> Thanks in advance.
> Tod|||Everytime I try to get to the website you refrenced, I get TCP_ERROR.
Some sort of communication problem. Is there any other sites that have
the same sort of information?

Thanks for the response,

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I don't know if the content is mirrored elsewhere. The author, Erland
Sommarskog, frequents this newsgroup so maybe he'll jump in.

BTW, I don't have any problems accessing the site.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:3f803253$0$195$75868355@.news.frii.net...
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that
have
> the same sort of information?
> Thanks for the response,
> Tod
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||[posted and mailed]

Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that have
> the same sort of information?

Too bad. If you have the complete error message, I'm interested. I'm
inclined to suspect that this might be some firewall problem at your
side, but I might get carried away of the .mil in your address.

Anyway, here is an excerpt of the part which is most relevant to
you. If you want to read the entire article, just drop me a line.

An Extravagant List-of-integers Procedure

The technique in the previous section can of course be applied to a list
of integers as well, so what comes here is not a true port of the
iter_intlist_to_table function, but a version that goes head over heels
to validate that the list items are valid numbers to avoid a conversion
error. And to be extra ambitious, the procedure permits for signed
numbers such as +98 or -83. If a list item is not a legal number, the
procedure produces a warning. The procedure fills in a temp table that
has a listpos column; this column will show a gap if there is an illegal
item in the input.

CREATE PROCEDURE intlist_to_table_sp @.list ntext AS

DECLARE @.pos int,
@.textpos int,
@.listpos int,
@.chunklen smallint,
@.str nvarchar(4000),
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000)

SET NOCOUNT ON

SELECT @.textpos = 1, @.listpos = 1, @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SELECT @.chunklen = 4000 - datalength(@.leftover) / 2
SELECT @.tmpstr = ltrim(@.leftover + substring(@.list, @.textpos, @.chunklen))
SELECT @.textpos = @.textpos + @.chunklen

SELECT @.pos = charindex(' ', @.tmpstr)
WHILE @.pos > 0
BEGIN
SELECT @.str = rtrim(ltrim(substring(@.tmpstr, 1, @.pos - 1)))
EXEC insert_str_to_number @.str, @.listpos
SELECT @.listpos = @.listpos + 1
SELECT @.tmpstr = ltrim(substring(@.tmpstr, @.pos + 1, len(@.tmpstr)))
SELECT @.pos = charindex(' ', @.tmpstr)
END

SELECT @.leftover = @.tmpstr
END

IF ltrim(rtrim(@.leftover)) <> ''
EXEC insert_str_to_number @.leftover, @.listpos
go

-- This is a sub-procedure to intlist_to_table_sp
CREATE PROCEDURE insert_str_to_number @.str nvarchar(200),
@.listpos int AS

DECLARE @.number int,
@.orgstr nvarchar(200),
@.sign smallint,
@.decimal decimal(10, 0)

SELECT @.orgstr = @.str

IF substring(@.str, 1, 1) IN ('-', '+')
BEGIN
SELECT @.sign = CASE substring(@.str, 1, 1)
WHEN '-' THEN -1
WHEN '+' THEN 1
END
SELECT @.str = substring(@.str, 2, len(@.str))
END
ELSE
SELECT @.sign = 1

IF @.str LIKE '%[0-9]%' AND @.str NOT LIKE '%[^0-9]%'
BEGIN
IF len(@.str) <= 9
SELECT @.number = convert(int, @.str)
ELSE IF len(@.str) = 10
BEGIN
SELECT @.decimal = convert(decimal(10, 0), @.str)
IF @.decimal <= convert(int, 0x7FFFFFFF)
SELECT @.number = @.decimal
END
END

IF @.number IS NOT NULL
INSERT #numbers (listpos, number) VALUES (@.listpos, @.sign * @.number)
ELSE
RAISERROR('Warning: at position %d, the string "%s" is not an legal integer',
10, -1, @.listpos, @.orgstr)
go

Here is how you would use it:

CREATE PROCEDURE get_product_names_iterproc @.ids varchar(50) AS
CREATE TABLE #numbers (listpos int NOT NULL,
number int NOT NULL)
EXEC intlist_to_table_sp @.ids
SELECT P.ProductID, P.ProductName
FROM Northwind..Products P
JOIN #numbers n ON P.ProductID = n.number
go
EXEC get_product_names_iterproc '9 12 27 37'

The validation of the list item is in the sub-procedure
insert_str_to_number. For many purposes it would be sufficient to have
the test

@.str NOT LIKE '%[^0-9]%' AND len(@.str) BETWEEN 1 AND 9

which checks that @.str only contain digits and is at most nine digits
long (that is, you disapprove ten-digit numbers as well as signed
numbers).

You might guess that there is a performance cost for this extravaganza,
and indeed the procedure needs about 50% more time than the corresponding
function. Still, for many situations, the execution time is acceptable.

One note about the warning produced with RAISERROR: with ADO, this
warning may be difficult or impossible to detect on client level. If you
change the severity from 10 to 11, it will be an error, and raise an
error in your client code.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Thanks for the response - it's a little above my abilities, but I plan
on studying it and trying to make it work for me project.

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See if the following link helps..
http://tinyurl.com/6iil

--
-- Anith|||Tod,

Try this:

create procedure ListEmployees
@.IDNum char(1024)
as begin
set @.IDNum = ' ' + replace(@.IDNum, ',', ' ') + ' '
select *
from tblEmployess
where @.IDNum like ('% ' + ltrim(str(IDNumber)) + ' %')
end

Shervin

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:5ed144f0.0310050454.369f4994@.posting.google.c om...
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
> Example:
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
> SQL statement in Stored Procedure is something like this:
> Select * from tblEmployess where IDNumber = @.IDNum
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@.nc.ngb.army.mil.
> Thanks in advance.
> Tod

Assistance with Stored Procedure

I currently have a sql statement that works great. I want to convert it
to a stored procedure so I can generate results from a webpage. Below
is the stored procedure that is working fine.

select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
SIDPERS_PERS_UNIT_TBL.UNAME,
SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
[tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
SMOSC=(case [tblSTAP Info].[SMOS Considered]
when "1" then "Yes"
else "No"
end),
FIRSTSGTC =(case [tblSTAP Info].[1SG]
when "1" then "Yes"
else "No"
end),
CSMC=(case [tblSTAP Info].[CSM]
when "1" then "Yes"
else "No"
end),
tblPersonnel.*, [tblSTAP Info].*
FROM SIDPERS_PERS_UNIT_TBL
INNER JOIN (tblPersonnel INNER JOIN [tblSTAP Info] ON
tblPersonnel.SSN_SM = [tblSTAP Info].SSN)
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__')) and
(tblPersonnel.PAY_GR = 'E5')
and (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))

and ([tblSTAP Info].TotalPoints >=
(case tblPersonnel.PAY_GR
when "E4" then 350
when "E5" then 400
when "E6" then 450
when "E7" then 500
when "E8" then 600
else 0
end))
AND [tblSTAP Info].NotConsidered = 0
ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
tblPersonnel.NAME_IND;

I would like the 3 items under the where clause to recieve a variable
from the website:

(SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__'))

(tblPersonnel.PAY_GR = 'E5')

(SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))

Everytime I try to make this a stored procedure and try to pass multiple
values in the PMOS field, I get an error stating too many variables.

If anyone can tell me what the Stored Procedure should look like AND
what the ASP should look like to pass the variables, I would be much
obliged.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it![posted and mailed, please reply in news]

Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> I currently have a sql statement that works great. I want to convert it
> to a stored procedure so I can generate results from a webpage. Below
> is the stored procedure that is working fine.
> select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
> SIDPERS_PERS_UNIT_TBL.UNAME,
> SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
> [tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
> SMOSC=(case [tblSTAP Info].[SMOS Considered]
> when "1" then "Yes"
> else "No"
> end),
> FIRSTSGTC =(case [tblSTAP Info].[1SG]
> when "1" then "Yes"
> else "No"
> end),
> CSMC=(case [tblSTAP Info].[CSM]
> when "1" then "Yes"
> else "No"
> end),
> tblPersonnel.*, [tblSTAP Info].*
> FROM SIDPERS_PERS_UNIT_TBL
> INNER JOIN (tblPersonnel INNER JOIN [tblSTAP Info] ON
> tblPersonnel.SSN_SM = [tblSTAP Info].SSN)
> ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
> WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__')) and
> (tblPersonnel.PAY_GR = 'E5')
> and (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))
> and ([tblSTAP Info].TotalPoints >=
> (case tblPersonnel.PAY_GR
> when "E4" then 350
> when "E5" then 400
> when "E6" then 450
> when "E7" then 500
> when "E8" then 600
> else 0
> end))
> AND [tblSTAP Info].NotConsidered = 0
> ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
> tblPersonnel.NAME_IND;
> I would like the 3 items under the where clause to recieve a variable
> from the website:
> (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__'))
> (tblPersonnel.PAY_GR = 'E5')
> (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))
>
> Everytime I try to make this a stored procedure and try to pass multiple
> values in the PMOS field, I get an error stating too many variables.
> If anyone can tell me what the Stored Procedure should look like AND
> what the ASP should look like to pass the variables, I would be much
> obliged.

The SP would look like this:

CREATE PROCEDURE TodTahems @.rpt_seq_code_pattern varchar(25),
@.pay_gr char(2),
@.pmos text
select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
...
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
JOIN iter_charlist_to_table(@.pmos) AS pmos ON
SUBSTRING (tblPersonnel.PMOS,1,3) = pmos.str
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE @.rpt_seq_code) and
(tblPersonnel.PAY_GR = @.paygr)
...

The function iter_charlist_to_table unpacks a comma-separated list
into a table. You find the code here:
http://www.sommarskog.se/arrays-in-...list-of-strings

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I need a little more assistance. I did a copy and paste of the
"char_to_table_sp" to create the procedure in my DB. I followed the
examples in you email.

I have everything working to push the variables from the asp page to the
stored procedure. The pages work fine when I only put in one value,
however it doesn't work when I input more than one value.

The information below is provided.

standinglist2_test 'AAA_', 'E5', '71L, 75H'

doesn't return any values.

standinglist2_test 'AAA_', 'E5', '71L'

returns several rows.

Here is the SP I created.

CREATE procedure standinglist2_test
@.rsc varchar(4),
@.paygr varchar(3),
@.mos varchar (5)
as
CREATE TABLE #strings (str nchar (20) NOT NULL)
EXEC charlist_to_table_sp @.mos

select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
SIDPERS_PERS_UNIT_TBL.UNAME,
SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
[tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
SMOSC=(case [tblSTAP Info].[SMOS Considered]
when "1" then "Yes"
else "No"
end),
FIRSTSGTC =(case [tblSTAP Info].[1SG]
when "1" then "Yes"
else "No"
end),
CSMC=(case [tblSTAP Info].[CSM]
when "1" then "Yes"
else "No"
end),
tblPersonnel.*, [tblSTAP Info].*
FROM
#strings s INNER JOIN
SIDPERS_PERS_UNIT_TBL INNER JOIN
tblPersonnel INNER JOIN
[tblSTAP Info] ON
tblPersonnel.SSN_SM = [tblSTAP Info].SSN
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
ON (SUBSTRING(tblPersonnel.PMOS,1,3) = s.str)
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE (@.rsc)) and
(tblPersonnel.PAY_GR = @.paygr)
and (SUBSTRING (tblPersonnel.PMOS,1,3) IN (@.mos))

and ([tblSTAP Info].TotalPoints >=
(case tblPersonnel.PAY_GR
when "E4" then 350
when "E5" then 400
when "E6" then 450
when "E7" then 500
when "E8" then 600
else 0
end))
AND [tblSTAP Info].NotConsidered = 0
ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
tblPersonnel.NAME_IND;

Your help is really appreciated. If you need any other information to
assist, please let me know.

I am unable to access the website you reference in your first response
from my office. I had to wait until i got home to try it. Must be a
firewall issue.

Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Further information below:

I am using SQL 7, so I went to the SQL Server 7 link on your site. I
used the List-of-string Procedure to try and make it work as opposed to
information below.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> The information below is provided.
> standinglist2_test 'AAA_', 'E5', '71L, 75H'
> doesn't return any values.

There is a very simple explanation:

> CREATE procedure standinglist2_test
> @.rsc varchar(4),
> @.paygr varchar(3),
> @.mos varchar (5) <------

Change the declaration of @.mos to varchar(8000) or to text, to avoid
truncation issues.

> I am unable to access the website you reference in your first response
> from my office. I had to wait until i got home to try it. Must be a
> firewall issue.

I registered the domain in the beginning of December, so it could be
slow propagation somewhere. You could also try with
http://www.algonet.se/~sommar, which is the same site, but a less
pretty URL.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I tried changing this:

> @.mos varchar (5) <------

to

@.mos varchar (8000)

I had the same problem. When one variable is sent, it works fine, but
when several are sent, it returns no rows.

So, I tried changing it to:

@.mos text

and received this error:

Server: Msg 8114, Level 16, State 1, Line 1
Error converting data type text to ntext.
Server: Msg 306, Level 16, State 1, Procedure standinglist2_test, Line 9
The text, ntext, and image data types cannot be used in the WHERE,
HAVING, or ON clause, except with the LIKE or IS NULL predicates.

I think I am very close to getting this resolved. Does anyone else have
any ideas?

I tried the link you provided in your last post and still couldn't get
to the site. I think it must be the firewall here.

Tod Thames

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tod Thames (anonymous@.devdex.com) writes:
> I had the same problem. When one variable is sent, it works fine, but
> when several are sent, it returns no rows.

I went back to the stored procedure, and there are more problems:

and (SUBSTRING (tblPersonnel.PMOS,1,3) IN (@.mos))

You need to remove this condition.

If there are further problems, I would recommend that you do some
debugging on your own. First thing is to add a "SELECT * FROM #strings"
to see that the table is correct. Next is to remove condition, until
rows starts to pop up. That's probably a more effective way than asking
for help and wait for someone to come by in the newsgroups.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks so much for the assistance. It worked after I took that last
statement out of the SP. I actually tried some debugging, but I am not
very proficient at it. I did the "select * from #strings", but received
this message.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#stings'.

I couldn't figure out how to get the results from a temporary table.
Since I couldn't get the results from the table that is populated, I
didn't really know where to go from there.

Anyway, it is working now and I thank you very much. That sp you wrote
amazes me.

Tod Thames

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Tod Thames (anonymous@.devdex.com) writes:
> Thanks so much for the assistance. It worked after I took that last
> statement out of the SP. I actually tried some debugging, but I am not
> very proficient at it. I did the "select * from #strings", but received
> this message.
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#stings'.

Judging from the error message, you mispelled the table name. But that
may of course been a type when you posted.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

assistance with sql query

Thanks in advance, I am trying to display the count of uptimes and downtimes in a single query. i started with something like
SELECT DISTINCT servername,
(SELECT COUNT(*)
FROM pingtable
WHERE (status = '0')) AS Uptime,
(SELECT COUNT(*)
FROM pingtable
WHERE (status <> '0')) AS DownTime
FROM pingtable

but this gives me the
server1 7 2
server1 7 2
...

Table layout and data:
servername status

server1 up
server1 up
server1 down
server2 up
server2 up
server2 up
server3 down
server3 up
server3 up

the output I would like to have is

Server UpCount DownCount
Server1 2 1
Server2 3 0
Server3 2 1Lookup crosstab queries in books online. Near the bottom is some sample code you can modify for your needs.

You'll end up with something like this:

Select Server,
sum(Case Status when 0 then 1 else 0) Uptime,
sum(Case Status when <> 0 then 1 else 0) Downtime
From PingTable
Group by Server

I'm not sitting at a server console now, so I had to draft it from memory and it probably has syntax errors in it, but you should be able to get the idea.

blindman|||Thanks so much for holding my hand there,
Here is the final query that worked perfectly
Select Server,
sum(Case Status when 0 then 1 else 0 end) as Uptime,
sum(Case Status when 0 then 0 else 1 end) as Downtime
From eladmin.PingstatsNT
Group by Server

Originally posted by blindman
Lookup crosstab queries in books online. Near the bottom is some sample code you can modify for your needs.

You'll end up with something like this:

Select Server,
sum(Case Status when 0 then 1 else 0) Uptime,
sum(Case Status when <> 0 then 1 else 0) Downtime
From PingTable
Group by Server

I'm not sitting at a server console now, so I had to draft it from memory and it probably has syntax errors in it, but you should be able to get the idea.

blindman|||FYI, if your Status field always holds zeros or ones, set it's data type to bit to ensure that your code will always work correctly.

blindman

Assistance with selecting first visit record of patients

Hi all

I saw a similar query on another thread but was unable to use the answers to resolve my problem.

I have an access database with two tables - the first contains demographic data for patients (Initial visit table) and the second (followup visit) contains all visit records for these patients linked by a PID. Some of the data was entered retrospectively so the record number is not a reflection of date of visit. Have 25000 visits captured.

I need to devise a query to extract the first CD4 count (a blood result) available for each patient - cd4 not done on every visit. Have tried using:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit]b

where a.InitialVisitID = b.InitialVisitID

order by VisitDatetime desc)));

This initially generates a table with duplicates followed shortly thereafter by a warning that only one record can be returned by the subquery and then blanking out of all records in the query output.

Please help!

I believe you want something like this:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM FollowUpVisit a

inner join

(select Min(InitialVisitID) as InitialVisitID, VisitDatetime, CD4CountPercentage

from FollowUpVisit

group by InitialVisitID

) as b

on a.InitialVisitID = b.InitialVisitID

order by a.VisitDatetime desc

|||

Try:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit] b

where b.PID = a.PID

order by VisitDatetime desc)));

AMB

|||

Thanks for the quick response AMB

Your suggestion has the same result - except asks for the PID paramter to be entered.

Just for clarification InitialVisitID in this database is in fact the PID. So i tried it with changing PID to InitialVisitID but same outcome.

h

|||

Thanks for the response

For clarification the "InitialVisitId" is the PID - so selecting for min InitialVisitID does not work as every patient only has one InitialVisitID (is unique identifier and primary key). Your solution also gives a circular reference problem within the Selection list. Tried making it:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage
FROM FollowUpVisit a
inner join
(select Min(VisitDateTime) as VisitDateTime, InitialVisitID, CD4CountPercentage
from FollowUpVisit
group by InitialVisitID
) as b
on a.InitialVisitID = b.InitialVisitID
order by a.VisitDatetime desc

but got same problem

h

|||

Please, do not make us to guess your enviroment. Post some DDL, including constraints and indexes, sample data and expected result.

Can you post the error msg you are getting?. I do not think that (select top 1 c1, ..., cn from ... order by) can bring more than one row, without using keywords WITH TIES.

AMB

|||

I am confused by this table FollowUpVisit. If this was a Visit table, I might understand, but is the InitialVisit stored in the FollowUpVisit table?

And this seems to imply that you can have >1 InitialVisit?

In your query, be sure and use aliases for every column, just to be careful with the output:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 b.InitialVisitID from [FollowUpVisit] b

where a.InitialVisitID = b.InitialVisitID

order by b.VisitDatetime desc)));

I agree with Hunchback, in that I don't know how that top 1 query can return > 1 row.

assistance with rounding

Hi,
I have the foll
round((convert(decimal(4,0),d.new - d.old)/d.old)*100,0)
The result I get is -54.00000000000
How can I change it to only display -54
ThanksCONVERT(INT, ROUND(...))
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A140E6DC-02B6-4A2D-93DB-7BFF7667F6F4@.microsoft.com...
> Hi,
> I have the foll
> round((convert(decimal(4,0),d.new - d.old)/d.old)*100,0)
> The result I get is -54.00000000000
> How can I change it to only display -54
> Thanks|||hi
you can do it as
round((convert(decimal(4,0),d.new - d.old)/d.old)*100)
remove the decimal after 100.
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chris" wrote:

> Hi,
> I have the foll
> round((convert(decimal(4,0),d.new - d.old)/d.old)*100,0)
> The result I get is -54.00000000000
> How can I change it to only display -54
> Thanks|||Hi,
If I use int I get 0.
"Aaron Bertrand [SQL Server MVP]" wrote:

> CONVERT(INT, ROUND(...))
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:A140E6DC-02B6-4A2D-93DB-7BFF7667F6F4@.microsoft.com...
>
>|||You said you have:
SELECT round((decimal(4,0),d.new - d.old)/d.old)*100,0)
...and this yields -54.000000. So you are saying that if you change that
to:
SELECT CONVERT(INT, round((convert(decimal(4,0),d.new -
d.old)/d.old)*100,0) )
...suddenly it yields 0? I don't quite buy that, could you post a repro
(DDL, sample data)? See http://www.aspfaq.com/5006
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:906750AF-BB06-462E-91CD-5416359E89A6@.microsoft.com...
> Hi,
> If I use int I get 0.|||Are you sure you are evaluating convert(int,
round((convert(decimal(4,0),d.new - d.old)/d.old)*100,0)) ?
AMB
"Chris" wrote:
> Hi,
> If I use int I get 0.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Hi,
I got it to work. Wouldn't doind all these convert slow down a hugh query or
put a strain on the server?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You said you have:
> SELECT round((decimal(4,0),d.new - d.old)/d.old)*100,0)
> ...and this yields -54.000000. So you are saying that if you change that
> to:
> SELECT CONVERT(INT, round((convert(decimal(4,0),d.new -
> d.old)/d.old)*100,0) )
> ...suddenly it yields 0? I don't quite buy that, could you post a repro
> (DDL, sample data)? See http://www.aspfaq.com/5006
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:906750AF-BB06-462E-91CD-5416359E89A6@.microsoft.com...
>
>|||YES! This is why formatting is better done a tthe presentation layer, not
in the database...
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:D622E951-68D1-4AA5-955D-5C4EB34F4B95@.microsoft.com...
> Hi,
> I got it to work. Wouldn't doind all these convert slow down a hugh query
> or
> put a strain on the server?

Assistance with error message

I am trying to run this script in Query Analyser and it seems to have a
problem in line 3:
-
select ID, 9, '2004', (select id from type where type = 'MF') from Dept
where dept in ('Internal Medicine', 'Surgery')
and ID + (select id from type where type = 'MF') +
convert(varchar(2), 9) +
convert(varchar(4), '2004')
not in
(select m.deptID + m.typeid + convert(varchar(2), m.dtmonth) +
convert(varchar(4), m.dtyear) from monthdept m, dept d, type t
where m.DeptID *= d.ID
and m.TypeID *= t.Type
)
The error message is: Invalid operator for data type. Operator equals add,
type equals uniqueidentifier.
I have tried playing with CAST/CONVERT without success. I am trying to look
for records on a key that spans 4 fields. Any suggestions/thoughts?
Schoo
PS: SQL Server 2000 running on W2K Server
Hi Schoo,
From your descriptions, I understood that the following codes raise an
error in QA, correct me if I was wrong, however, when I copied codes you
provided to my QA and then Parse Query (Ctrl + F5), it passed. To make
further research on this issue, I would appreciated if you could provide me
detailed DDL of your database and some sample record for me to reporduce it
on my machine. One more question, have you upgraded to the latest SQL
Server Service Pack?
Here is the document on how to get DDL from Enterprise Manager
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Assistance with a query

I have 2 tables POheader and POline. I am running the following SQL
statement but am getting 2 records where as I should only be getting one.
Could someone please assist.
s_commar@.hotmail.com
select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
POline.Ponbr and POline.ponbr
= 16916;
I should just be getting one record but I get 2 records. Could someone
advise as to what I am doing wrong.
Thanks
s_commar@.hotmail.comIts hard to comment without seeing DDL+Sample data. My best
guess is that your POline table has 2 matching rows. If you dont want to
reference any columns from the POnline table in the final output,
you can probably rewrite your query like
Select POheader.ponbr from POheader
WHERE EXISTS(
SELECT 1 FROM POline WHERE POheader.Ponbr = POline.Ponbr
AND POline.ponbr = 16916)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"s commar" <scommar@.verizon.net> wrote in message
news:36iJe.1262$D4.785@.trndny07...
>I have 2 tables POheader and POline. I am running the following SQL
>statement but am getting 2 records where as I should only be getting one.
>Could someone please assist.
> s_commar@.hotmail.com
>
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
>
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
>|||"s commar" schrieb:

> I have 2 tables POheader and POline. I am running the following SQL
> statement but am getting 2 records where as I should only be getting one.
> Could someone please assist.
> s_commar@.hotmail.com
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
select POheader.ponbr
from POheader
inner join POline
on POheader.Ponbr = POline.Ponbr
WHERE POline.ponbr = 16916|||Since u hv 2 matching rows in the POLine table against the row in the
POHeader table, u r getting 2 records. Try below query
select DISTINCT POheader.ponbr from POheader inner join POline on
POheader.Ponbr = POline.Ponbr and POline.ponbr= 16916;
Rakesh
"s commar" wrote:

> I have 2 tables POheader and POline. I am running the following SQL
> statement but am getting 2 records where as I should only be getting one.
> Could someone please assist.
> s_commar@.hotmail.com
>
> select POheader.ponbr from POheader inner join POline on POheader.Ponbr =
> POline.Ponbr and POline.ponbr
> = 16916;
>
> I should just be getting one record but I get 2 records. Could someone
> advise as to what I am doing wrong.
> Thanks
> s_commar@.hotmail.com
>
>

assistance please....

What is the easiest way to check the date and time of service pack updates?

What do you mean by that `?

Assistance please using txt

I have received a table of data that has a field containing date
information. Unfortunately it was derived from a MainFrame dump and
originated as a txt file and was then ported into an Access MDB file
before it became an SQL table. The date format is vchar(50) and
actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
need to run a select statement for a range of dates such as 010104
thru 030104. Unfortunately being a charecter field this returns
incorrect results under a majority of cases. Back in my dBase days
there was a VAL() that could be used in this case but I have been
unable to find anything comperable in SQL. Can anyone help me please?

Thanks in advance

Steve"Steve" wrote:
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve

Steve,

I've never worked with dBase, but I assume that VAL() would convert to an
integral data type, so that wouldn't work either: 123103 would be greater
than 010104. Also, if you have any dates before Y2K, you'll have issues
there.

One more issue: you mentioned big iron... if the date field in the mainframe
file was PIC 9(6), you might want to verify that somewhere between the file,
Access, and SQL Server, you still have all leading and trailing zeroes...

select min(len(somefld)), max(len(somefld))
from sometable

...Anyhoo, I would recommend using string functions (like LEFT, RIGHT, and
SUBSTRING) and the CONVERT function to create a real datetime column.
Barring that, you could convert the data to a datetime for the query.

Assuming that every field is 6 characters in the format mmddyy, here's a
little test I cobbled together. Note that I'm assuming you're using SQL
Server 2K: otherwise the table variable won't work. Also, I chose what SQL
Server BOL calls the ANSI date format, but YMMV depending on your regional
date/time settings...

set nocount on

declare @.test table (
src varchar(50),
dest datetime
)

insert @.test values ('123198', null)
insert @.test values ('010199', null)
insert @.test values ('123103', null)
insert @.test values ('010104', null)

--Use 2: the ANSI (yy.mm.dd) style for conversion (the
--final parameter in the convert call)
update @.test
set dest = convert(
datetime,
right(src, 2) + '.' +
left(src, 2) + '.' +
substring(src, 3, 2),
2
)

--Out of whack
select * from @.test order by src

--In whack
select * from @.test order by dest

Craig|||Steve,
Datetime fields (there are no Date fields) in SQL Server need to be in
the format yyyymmdd, so you need to format the field first before inserting
it into your database. Do that like this:

declare @.myDate as varchar(50)

set @.myDate = '010104'

select cast(
'20' +
right(@.myDate,2) +
substring(@.myDate,3,2) +
left(@.myDate,2) as datetime)

I hard-coded '20' as the century.
Also, the way to import the file would probably to create a DTS package and
load the data into a temp table, then create a procedure using the above
statement as part of the stored procedure.
OK, now for the pitch. If you are new to SQL Server 2000, a great way to
get up to speed in just a few hours is with our video series on SQL Server
2000 at www.TechnicalVideos.net. Our videos give tips and tricks from
experts in the field, while they show you on the screen just how to do them.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Steve" <shull@.dpd.dallascityhall.com> wrote in message
news:6318603c.0403041803.7a69d4f4@.posting.google.c om...
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve

Assistance on SUM() statement

I have a table #changes with two columns: familyID, Versiontime

For each record in the #changes table I need to compute the income from another table FamilyIncome with colunms: familyID, IncomeType, EffectiveDate, Amt. Example Below:

FamID, IncType, EffDate, Amt
100, 10, 01/01/2003, $50.00
100, 20, 01/01/2003, $50.00
100, 30, 01/01/2003, $50.00
100, 10, 02/02/3003, $100.00
100, 20, 02/02/3003, $100.00
100, 30, 02/02/3003, $100.00
100, 40, 02/02/3003, $100.00
100, 20, 03/03/3003, $75.00
100, 30, 03/03/3003, $75.00
100, 40, 03/03/3003, $75.00

So if I'm looking for the Incomes on the following dates (which are in the #changes table) it should be:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003 is still effective (Type 10) plus the three records effective on 03/03/2003)

Any help is greatly appreciated,

BrentCan you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||Brett,
I have tried a couple variations on this theme and so far they do not deliver the desired results:

1. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid and EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE WORKS FOR THE FIRST DATE AND RETURNS THE $150.00 DESIRED, BUT ON ANY FUTURE DATES IT ADDS THE NEW INCOME AND KEEPS A RUNNING TOTAL (I.E. $550.00 INSTEAD OF $400.00)

2. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid
HAVING EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE ONLY RETURNS A RESULT SET FOR THE LAST DATE IN THE SEQUENCE AND THEN IT RETURNS $225.00 (ONLY THE RECORDS WITH A 03/03/2003) DATE)
At this time I'm just trying to isolate the effdate calculations. Also whoever reads this all the dates should be 2003 the 3003 for the year is a typo.

Thanks,

Brent
Originally posted by Brett Kaiser
Can you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||I don't understand your results...where do these dates come from:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003

They don't exists in your data...|||Originally posted by Brett Kaiser
I don't understand your results...where do these dates come from:

They don't exists in your data...

Those dates come out of the #changes table which I only included the fields not an example. What I have is 43,000 records in the #changes table that are familyID's and Dates on which I need to perform several calculations (size of the family, income, fee schedule, etc) right now I'm hung up on getting the income which I need in order to determine the fee (fee is based on family size and income)

Brent

Assistance on implementing Data Mining

Hi,

I'm new to SQL Server and data mining, so please forgive my ignorance...

I'm working on a project which requires me to use the datamining provided by SQL Server 2005. I've a table for which i want to predict the values in a table (Encyclopedia)

The table contains the following fields:

Component

Major Attribute

Minor Attributes(which is basically a list of CSV for attributes in no particular order)

I want to predict the component if i enter the attributes ..... my questions:

1. Should i change the table structure in any way to assist in data mining?

2. What model would be preferrable?

3. If i'm using the model will it extend to the data added to the table automatically or do i have to update it regularily.

I need to submit the project by 20th... and i'm not even started. I tried a lot on my own..... but couldn't get anywhere without definitive assistance from anyone.

Please help

Thanks and Regards,

Sundeep Singh

You should probably use a nested table which contains multiple rows for each of the Minor Attributes associated with a Component - so your mining model might look something like this:

CREATE MINING MODEL CompPredict(
CaseID LONG KEY,
Component TEXT DISCRETE PREDICT,
MajorAttribute TEXT DISCRETE,
MinorAttributes TABLE(
MinorAttribute TEXT KEY
)
)
USING Microsoft_Decision_Trees

If you add data to the source database that you process your model from, you will need to reprocess the model either manually or using scheduled job or Integration Service package.

|||

Hi Raman

Thankx for ur answer..................

I converted the tables as you said

TableComponent(ComponentID, BodyPart,MajorAttribute,ComponentName)

TableMinor(ComponentID,MinorAttribute)

Used TableComponent as Case(ComponentID as Key), and TableMinor as Nested(MinorAttribute as key)

I tried predicting Component Name using BodyPart, MajorAttribute and Minor Attribute as Input.....

I tried it using Microsoft Decision trees and it generated only single node for it.........

What do you think can be the problem....................

Moreover i need to generate the association rules from the data but again it is generating no rules from the given data...........

Thanks

Sundeep Singh

|||How big is your data set? Have you tried tweaking the algorithm parameters (lower COMPLEXITY_PENALTY, MINIMUM_LEAF_CASES, MINIMUM_SUPPORT)?|||

Hi,

I tried to reduce the parameters and got some rules... but there is a problem. the association rules that are being generated use a single minorAtt at a time.

I mean that in the entire collection there is not one that uses two MinorAttributes to Predict the column.

And can you please help me with the query... assuming that i have a major symptom and some minor symptoms and i want to predict what Components they can be for along with the probability of correctness..

Thanks

Sundeep Singh

|||

Hi,

played with the algo params and it worked.. i don't know how as yet.. but i got some rules with more than one minor symptom.

But the problem with the query remains... somebody please help!!!!!!!! only four days left for submission....

Thanks

Sundeep Singh

|||

You can do a SELECT COUNT(*) FROM TableMinor and SELECT COUNT(DISTINCT ComponentID) FROM TableMinor. If these numbers are the same, than you have one minor/component, otherwise you do have multiple minors/component and everything's OK (except maybe your data).

If you don't have multiple minors/component, you don't need a nested table. From the initial description it seemed like there were.

|||

The number of minor are more than one, but the query that i asked for was to predict the component when i enter the minor and major attributes along with the probability of match...

Supposing that i have named my model as Encyclopedia.

Thanks

|||Try this to get the top 3 predictions for component:

SELECT FLATTENED
TopCount(PredictHistogram(Component), $AdjustedProbability, 3)
FROM [ComponentPredictModel]
NATURAL PREDICTION JOIN
(SELECT 'xxx' as MajorAttribute,
(SELECT ( SELECT 'x' AS Minor UNION
SELECT 'y' AS Minor )
AS [MinorAttributes])
) AS t

Assistance in connecting a SQL Server Client to SQL Server sitting in a shared environment

Hi,

I need to connect to a SQL server thats running in say abc.trident.com and also sits in a shared environment..

I have couple of questions

1) That SQL server is accessible from my network, yet when I swtich on my enterprise manager I am unable to view that in the list of running SQL servers in the populated list.

Is it because its sitting in a shared environment I am unable to view that?

2) What is the connection striing I should use to connect to the server..When I try to configure a SQL server registrations it normally asks for SQL Server name along with the user authentication

Should I mention fill the Server: field as

abc.trident.com

or

// abc.trident.com/abc.trident.com

to connect to the clustered server.

Can some one tell me the proper connection string if both aforementioned ones are incorrect?

Thanks in Advance

What error it throws !?

1) That SQL server is accessible from my network, yet when I swtich on my enterprise manager I am unable to view that in the list of running SQL servers in the populated list.

Is it because its sitting in a shared environment I am unable to view that?

>> Is this server is out side your network!!!? have you tried connecting using QA? is NET DDE and NET DDE DDSM service are running ? Is NETBIOS enabled?

2) What is the connection striing I should use to connect to the server..When I try to configure a SQL server registrations it normally asks for SQL Server name along with the user authentication

Should I mention fill the Server: field as

abc.trident.com

or

// abc.trident.com/abc.trident.com

to connect to the clustered server.

Refer connectionstring.com for different connection strings.

Regards

Hemantgiri S. Goswami

|||

1) is NET DDE and NET DDE DDSM service are running ? Is NETBIOS enabled?

Yes

2) The connectionstring.com site appears to be dead and has some irrevlant ads. can you tell me if my connection string format to represent a Clustered SQL server is correct or any other site from which I can get the accurate info

Efforts in googling ended in vain.

3) The error

<SQLServer does not exist or access denied ConnectionOpen.connect())>

Here the SQL server lies out side my network and the network is even accessible from a Tracert.

but the SQL server is up and running according to the Admin.

|||No takers!|||

Hi,

You have said that the server is outside network.... If it is behind firewall have you check the proper firewall rule is placed in order to get you access !? and Port too...

Refer this KB for the workaround http://support.microsoft.com/kb/328306/en-us

Hemantgiri S. Goswami

Assistance developing Query

Good Day;

I would appreciate assistance developing a query that I haven't been
able to develop without using a second table. I wish to count the
number of records that are still open on the first of each month.
Each record has an open date and a close date or the close date is
null i.e., the record is not yet closed. I've previously beaten this
by building a table, simply a list of the dates for the first of each
month for the next ten years or so, and then selecting values based
upon a date selected from that table. However I'd be happier if I
could do it without the second table. I'd be prepared to accept the
Min(Date) for each month as being the first of the month.

I've included some DDL statements to build and populate the table if
that helps. Since the selection is rather small and all the open
dates are very close together I think the result will be simply a
decreasing count from the month the first record is opened till today.

A pseudo code select statement might look like

Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
FROM DetailT
Where DateReceived > DateOfInterest or DateReceived is Null and
DateOpened < DateOfInterest
Group by Min(DateOpened)
Order by Min(DateOpened)

I hope I've explained it sufficiently well.

CREATE TABLE [dbo].[DetailT] (
[Autonum] [int] IDENTITY (1, 1) NOT NULL ,
[QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOpened] [smalldatetime] NOT NULL ,
[DateReceived] [smalldatetime] NULL ,

)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C15788', '06/04/2005 9:35', 07/04/2005)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B16091', '06/04/2005 9:36', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B15001', '06/04/2005 9:51', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('M18696', '06/04/2005 9:56', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C14969', '06/04/2005 10:05', '10/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('O10091', '06/04/2005 10:08', '12/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('D01197', '06/04/2005 10:13')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('H15001', '06/04/2005 10:15', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('J15090', '06/04/2005 10:24', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('J01202', '06/04/2005 10:31')
Insert into DetailT (QDNumber, DateOpened)
VALUES('G01193', '06/04/2005 10:32')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01164', '06/04/2005 10:35')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01162', '06/04/2005 10:48')
Insert into DetailT (QDNumber, DateOpened)
VALUES('F01124', '06/04/2005 10:59')
Insert into DetailT (QDNumber, DateOpened)
VALUES('H01147', '06/04/2005 11:01')
Insert into DetailT (QDNumber, DateOpened)
VALUES('S15068', '06/04/2005 11:10')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('E12322', '06/04/2005 11:32', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('A12205', '06/04/2005 11:37', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('D12259', '06/04/2005 11:40', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('C03394', '06/04/2005 11:51')

If you made it this far thank you for your patience. Any help would be
appreciated.

Thank you.

BillBill wrote:

> Good Day;
> I would appreciate assistance developing a query that I haven't been
> able to develop without using a second table. I wish to count the
> number of records that are still open on the first of each month.
> Each record has an open date and a close date or the close date is
> null i.e., the record is not yet closed. I've previously beaten this
> by building a table, simply a list of the dates for the first of each
> month for the next ten years or so, and then selecting values based
> upon a date selected from that table. However I'd be happier if I
> could do it without the second table. I'd be prepared to accept the
> Min(Date) for each month as being the first of the month.
> I've included some DDL statements to build and populate the table if
> that helps. Since the selection is rather small and all the open
> dates are very close together I think the result will be simply a
> decreasing count from the month the first record is opened till today.
> A pseudo code select statement might look like
> Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
> FROM DetailT
> Where DateReceived > DateOfInterest or DateReceived is Null and
> DateOpened < DateOfInterest
> Group by Min(DateOpened)
> Order by Min(DateOpened)
> I hope I've explained it sufficiently well.
> CREATE TABLE [dbo].[DetailT] (
> [Autonum] [int] IDENTITY (1, 1) NOT NULL ,
> [QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateOpened] [smalldatetime] NOT NULL ,
> [DateReceived] [smalldatetime] NULL ,
> )
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C15788', '06/04/2005 9:35', 07/04/2005)
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B16091', '06/04/2005 9:36', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B15001', '06/04/2005 9:51', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('M18696', '06/04/2005 9:56', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C14969', '06/04/2005 10:05', '10/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('O10091', '06/04/2005 10:08', '12/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('D01197', '06/04/2005 10:13')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('H15001', '06/04/2005 10:15', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('J15090', '06/04/2005 10:24', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('J01202', '06/04/2005 10:31')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('G01193', '06/04/2005 10:32')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01164', '06/04/2005 10:35')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01162', '06/04/2005 10:48')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('F01124', '06/04/2005 10:59')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('H01147', '06/04/2005 11:01')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('S15068', '06/04/2005 11:10')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('E12322', '06/04/2005 11:32', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('A12205', '06/04/2005 11:37', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('D12259', '06/04/2005 11:40', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('C03394', '06/04/2005 11:51')
> If you made it this far thank you for your patience. Any help would be
> appreciated.
> Thank you.
> Bill

Where the SQL statement you would use to load the table?
Put parentheses around it.
Go from there.
There may be far more elegant solutions but your the one getting
the paycheck. ;-)
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||On 18 Apr 2005 16:36:38 -0700, Bill wrote:

>I would appreciate assistance developing a query that I haven't been
>able to develop without using a second table.
(snip)
>I've previously beaten this
>by building a table, simply a list of the dates for the first of each
>month for the next ten years or so, and then selecting values based
>upon a date selected from that table. However I'd be happier if I
>could do it without the second table.

Hi Bill,

Why do you want to do it wothout a second table? Having a permanent
auxiliary calendar table in your database is actually quite useful and I
think that no database should ever be without one.
Here's a link to an article that shows how to create a general
all-purpose calendar table, how to fill it with data and several
examples of how to use it: http://www.aspfaq.com/show.asp?id=2519.

And here's how I'd write your query, using the table described above:

SELECT c.dt,
COUNT(*) as "Qty Still Open"
FROM Calendar AS c
INNER JOIN DetailT
ON ( DateReceived > c.dt OR DateReceived IS NULL )
AND DateOpened < c.dt
WHERE c.D = 1
AND c.dt BETWEEN (SELECT MIN(DateOpened)
FROM DetailT)
AND DATEADD(month, 1, (SELECT MAX(DateReceived)
FROM DetailT))
GROUP BY c.dt
ORDER BY c.dt

This one cuts off the listing at the lowest number. If you increase the
number (1) in the DATEADD expression, you can see that the number of
open cases remains constant after the last month listed.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||DA Morgan <damorgan@.x.washington.edu> wrote in message news:<1113891730.819997@.yasure>...
> Bill wrote:
> > Good Day;
> > I would appreciate assistance developing a query that I haven't been
> > able to develop without using a second table.

> > Thank you.
> > Bill
> Where the SQL statement you would use to load the table?
> Put parentheses around it.
> Go from there.
> There may be far more elegant solutions but your the one getting
> the paycheck. ;-)

Dan;

I'm sorry but I don't understand what you're trying to tell me. If
your first question is "Where is the SQL statement you would use to
load the table?" I don't have one, the data base is interactively
updated through an ASP based HTML form over the web. I did go to the
work of providing insert statements to assist anyone who might try to
help.

I don't understand what putting parentheses around it would do?

I hope there is a more elegant solution but unfortuantely I haven't
figured it out and was simply asking for assistance. If you don't
wish to help thats ok with me. Yes, I'm getting paid for the work I
do, but developing this solution is not what I was trained or educated
to do an so I'm learning as I go. I appologize if I've bothered you
by asking for help.

I will continue to explore the solution.

Cheers;

Bill|||Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<8oqa61p4rgmhtcfh7rgsc8dup9pj1a9d1t@.4ax.com>...
> On 18 Apr 2005 16:36:38 -0700, Bill wrote:
> >I would appreciate assistance developing a query that I haven't been
> >able to develop without using a second table.
> (snip)
> >I've previously beaten this
> >by building a table, simply a list of the dates for the first of each
> >month for the next ten years or so, and then selecting values based
> >upon a date selected from that table. However I'd be happier if I
> >could do it without the second table.
> Hi Bill,
> Why do you want to do it wothout a second table? Having a permanent
> auxiliary calendar table in your database is actually quite useful and I
> think that no database should ever be without one.
...
> This one cuts off the listing at the lowest number. If you increase the
> number (1) in the DATEADD expression, you can see that the number of
> open cases remains constant after the last month listed.
> Best, Hugo

Hugo;

Thank you for your assistance. I've reviewed the article you pointed
me to and have spent my spare time today building a number table and a
date table. I was reluctant to duplicate my earlier date table, which
only contained the dates for the first of the month into the SQL
Server environment since I felt that it was both cheating and
confusing. However the article showed that this can be a very useful
table and since it's been published I don't feel too bad about
emulating someone's work who know more about application development
than I do.

I do have one question, based mainly on my lack of formal training in
SQL Server and my experience this morning building the calendar table.
I was copying the code from the article and pasting it into SQL Query
Analyzer and running it, as I'm not certain where or how this code
should be executed. Most things ran very quickly after I modified
them properly to meet my environment. However the adding of row's to
the Calendar table (4,096) took several hours (2-3) I was really
surprised by this and wondered if I was doing something wrong but
since it finished successfully and subsequent code samples executed
quickly I moved on. My question is am I using the right part of the
SQL Server environment for this sort of work?

Thank you once again for your time and your assistance. Now that I've
overcome my reluctance to using the calendar table I'm comfortable
enough to go on and using you sample query to get what I was looking
for.

Thank you.

Cheers;

Bill|||On 20 Apr 2005 16:06:36 -0700, Bill wrote:

(snip)
>the article showed that this can be a very useful
>table and since it's been published I don't feel too bad about
>emulating someone's work who know more about application development
>than I do.

Hi Bill,

You certainly should not feel bad about it - sharing the code is exactly
the reason why Aaron has published it on his site.

Copying work from others may be a sin in artistic creative work, but in
software development, it's a sin NOT to copy and adapt proven solutions.

(snip)
>Most things ran very quickly after I modified
>them properly to meet my environment. However the adding of row's to
>the Calendar table (4,096) took several hours (2-3) I was really
>surprised by this

And so am I. Okay, the server does have a bit of work to do when
populating the table, but under normal circumstances, I would not expect
it to run for so long! A couple of minutes, maybe. Not hours.

I'm not sure if you still care to investigate this (since you now have
the table, and it's a one-time job after all), but if you do, then could
you please post the exact code you used to create and popultae the
tables?

>My question is am I using the right part of the
>SQL Server environment for this sort of work?

Yes, Query Analyzer is exactly the tool to use for these jobs.

Best, Hugo
--

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

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on the
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_id
Hi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id
|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by the
trigger.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> All what you have to do it join the virtual table INSERTED in your query on
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, and
> if the e-mail sending fails, your update will get rolled back (and you might
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> for
> value
> to
> the
> where
>
>
|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...[vbcol=seagreen]
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:

> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you heed
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>
>
|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.

> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...[vbcol=seagreen]
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on the
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_idHi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by the
trigger.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> All what you have to do it join the virtual table INSERTED in your query on
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, and
> if the e-mail sending fails, your update will get rolled back (and you might
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> > I hope you guys can help. I am looking to create a trigger that watches
> for
> > updates to the sch_status column and fires when the update changes the
> value
> > to 4. The code that will be executed is below with addition of some code
> to
> > e-mail the results. It is the actual code to create the update trigger on
> the
> > schedule table I need some help with.
> >
> > select s.sch_id,
> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> > s.e_subject,
> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> where
> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> > dl.sch_id = s.sch_id),
> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> > convert(varchar(12),getdate(),112) +'.log'
> > from schedule s
> > where s.sch_status = 4
> > order by s.sch_id
>
>|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> All what you have to do it join the virtual table INSERTED in your query
>> on
>> the primary key.
>> It is not recommended that you send e-mails from within a trigger, as it
>> hurts performance, has horrible consequences with locking and blocking,
>> and
>> if the e-mail sending fails, your update will get rolled back (and you
>> might
>> have sent e-mails to 1/2 your customer base). Rather write a record into
>> a
>> queue type table where another process comes along a sends the e-mails.
>> Books On Line has some good examples on CREATE TRIGGER and how to use the
>> virtual tables INSERTED and DELETED.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> > I hope you guys can help. I am looking to create a trigger that watches
>> for
>> > updates to the sch_status column and fires when the update changes the
>> value
>> > to 4. The code that will be executed is below with addition of some
>> > code
>> to
>> > e-mail the results. It is the actual code to create the update trigger
>> > on
>> the
>> > schedule table I need some help with.
>> >
>> > select s.sch_id,
>> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> > s.e_subject,
>> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
>> where
>> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
>> > dl.sch_id = s.sch_id),
>> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> > convert(varchar(12),getdate(),112) +'.log'
>> > from schedule s
>> > where s.sch_status = 4
>> > order by s.sch_id
>>|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:
> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you heed
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> > In case it leads to a different outcome I plan to have the select
> > statement
> > and the e-mail (not sql mail) in a stored procedure and have it called by
> > the
> > trigger.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> All what you have to do it join the virtual table INSERTED in your query
> >> on
> >> the primary key.
> >>
> >> It is not recommended that you send e-mails from within a trigger, as it
> >> hurts performance, has horrible consequences with locking and blocking,
> >> and
> >> if the e-mail sending fails, your update will get rolled back (and you
> >> might
> >> have sent e-mails to 1/2 your customer base). Rather write a record into
> >> a
> >> queue type table where another process comes along a sends the e-mails.
> >>
> >> Books On Line has some good examples on CREATE TRIGGER and how to use the
> >> virtual tables INSERTED and DELETED.
> >>
> >> Regards
> >> --
> >> Mike Epprecht, Microsoft SQL Server MVP
> >> Zurich, Switzerland
> >>
> >> IM: mike@.epprecht.net
> >>
> >> MVP Program: http://www.microsoft.com/mvp
> >>
> >> Blog: http://www.msmvps.com/epprecht/
> >>
> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> >> > I hope you guys can help. I am looking to create a trigger that watches
> >> for
> >> > updates to the sch_status column and fires when the update changes the
> >> value
> >> > to 4. The code that will be executed is below with addition of some
> >> > code
> >> to
> >> > e-mail the results. It is the actual code to create the update trigger
> >> > on
> >> the
> >> > schedule table I need some help with.
> >> >
> >> > select s.sch_id,
> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> >> > s.e_subject,
> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> >> where
> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> >> > dl.sch_id = s.sch_id),
> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> >> > convert(varchar(12),getdate(),112) +'.log'
> >> > from schedule s
> >> > where s.sch_status = 4
> >> > order by s.sch_id
> >>
> >>
> >>
>
>|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:
>> You still have the same issues (concurrency/performance), even if you
>> execute a proc in order to send email from the trigger. I suggest you
>> heed
>> Mike's suggestion and employ a queue-type table so that you can send the
>> actual email asynchronously. It's fairly simple to implement.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>> > In case it leads to a different outcome I plan to have the select
>> > statement
>> > and the e-mail (not sql mail) in a stored procedure and have it called
>> > by
>> > the
>> > trigger.
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> All what you have to do it join the virtual table INSERTED in your
>> >> query
>> >> on
>> >> the primary key.
>> >>
>> >> It is not recommended that you send e-mails from within a trigger, as
>> >> it
>> >> hurts performance, has horrible consequences with locking and
>> >> blocking,
>> >> and
>> >> if the e-mail sending fails, your update will get rolled back (and you
>> >> might
>> >> have sent e-mails to 1/2 your customer base). Rather write a record
>> >> into
>> >> a
>> >> queue type table where another process comes along a sends the
>> >> e-mails.
>> >>
>> >> Books On Line has some good examples on CREATE TRIGGER and how to use
>> >> the
>> >> virtual tables INSERTED and DELETED.
>> >>
>> >> Regards
>> >> --
>> >> Mike Epprecht, Microsoft SQL Server MVP
>> >> Zurich, Switzerland
>> >>
>> >> IM: mike@.epprecht.net
>> >>
>> >> MVP Program: http://www.microsoft.com/mvp
>> >>
>> >> Blog: http://www.msmvps.com/epprecht/
>> >>
>> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> >> > I hope you guys can help. I am looking to create a trigger that
>> >> > watches
>> >> for
>> >> > updates to the sch_status column and fires when the update changes
>> >> > the
>> >> value
>> >> > to 4. The code that will be executed is below with addition of some
>> >> > code
>> >> to
>> >> > e-mail the results. It is the actual code to create the update
>> >> > trigger
>> >> > on
>> >> the
>> >> > schedule table I need some help with.
>> >> >
>> >> > select s.sch_id,
>> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> >> > s.e_subject,
>> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list
>> >> > dl
>> >> where
>> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl
>> >> > where
>> >> > dl.sch_id = s.sch_id),
>> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> >> > convert(varchar(12),getdate(),112) +'.log'
>> >> > from schedule s
>> >> > where s.sch_status = 4
>> >> > order by s.sch_id
>> >>
>> >>
>> >>
>>