Hello,
My table was created by importing from an Excel spreadsheet. Typical fields in the rows are a date and various stock values like Open, High, Low, and Close. Unfortunately, many of the values have the wrong characteristics. These are my problems:
1. The date field has time in addition to the date. I don't want the
time in this field.
2. Many of the amount fields have a large precision, for example,
1.9399999999999999. I want to allow for a precision of 5 and a
scale of 2.
Can I make changes to my table at this point? I looked into Design Table but I don't see any feature allowing me to make changes 'on the fly'.
Any suggestions are welcome.
JoeHowdy,
Usually you can query a datetime column to extract just the date, so dont worry too much about that.
The column precision can be changed ( on the fly as it were ) using an alter table command ( see BOL ) that will automatically change the column precision and in the process round the values to what you want.
Cheers,
SG
( PS - theres nothing quite like a V8 Holden ute.....)|||Thank you for the reply. It occurs to me that one can end up creating a great many different queries if one is interested in comparing possible results/outputs. If DBA's want to save their queries for possible use later do they typically like to store them in a standard folder? Or should one create a special folder within the 'Databases' folder?
Thanks again. I am still new to working with Query Analyzer.
Joe|||I would also like to ask anyone if he or she could advise me as to how I can display only a date (without the time) when I do a query using Query Analyzer. I really don't want to see time displayed.
Can someone assist?
Thanks again.
Joe|||Howdy,
Well, sadly SQL doesnt handle splitting out dates from datetime fields very well.
Assuming you had a column called DATE in a table called INFO, if you want to display JUST the date, you need to extract the hour, min, seconds as characher values then reconstruct into a character format ( and later change to datetime , which by the way gives a defualt date of 01/01/1900)
Now, assuming you have a small table called INFO, with one column called DATE with one value of 2003-10-10 17:23:34
If you xxtract using time the following code -
select convert(varchar(2),datepart(hh,DATE))
+':'+convert(varchar(2),datepart(mm,DATE))
+':'+convert(varchar(2),datepart(ss,DATE))
from INFO
This gives -
17:23:34 ( but in varchar format).
Note too that single digit values WILL NOT have a '0' put in front of them unless you test for it & code for it accordingly.
Unless you get all dates into the same format - e.g. all varchar/char or all in datetime, mixing & matching will give you a headache.
Cheers,
SG.
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
How to change columns to rows
I have a need to change the columns in a table to rows with values
/****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Test] (
[UserID] [int] NULL ,
[NurseID] [int] NULL ,
[NurseID2] [int] NULL ,
[NurseID3] [int] NULL ,
[ReceptionID] [int] NULL ,
[OfficemanID] [int] NULL ,
[NurseTrainID] [int] NULL ,
[ResidentTrainID] [int] NULL ,
[ResidentTrainID2] [int] NULL ,
[ResidentTrainID3] [int] NULL
) ON [PRIMARY]
END
Insert into test
(UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
values
(1,3,9,4,7)
Select * from Test would give
UserID NurseID, NurseID2
1 3 9
and I need to transform to using SQL2000
Description Users
UserID 1
NurseID 3
NurseID2 9
I may not need the description column
Thanks for the help
Stephen K. MiyasatoHi Stephen,
2005 allows using UNPIVOT clause. Not sure about 2000 though..
http://msdn2.microsoft.com/en-us/library/ms177410.aspx
"Stephen K. Miyasato" wrote:
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>
>|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||For SS2000, you can refer to the following.
- How to rotate a table in SQL Server
http://support.microsoft.com/defaul...kb;en-us;175574
Martin C K Poon
Senior Analyst Programmer
====================================
"Stephen K. Miyasato" <miyasat@.flex.com> bl
news:%23d67IiofGHA.4864@.TK2MSFTNGP05.phx.gbl g...
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>|||Thanks very much,
That is what I was looking for.
Stephen
"dterrie" <dterrie@.axiomadvisors.net> wrote in message
news:1148404941.573763.96380@.i39g2000cwa.googlegroups.com...
> If you were using SQL Server 2005, you could use an UNPIVOT statement.
> In this case, I think you'll just have to use a series of UNION ALL
> statements to transform the data, as in:
> select
> 'UserID' as Description,
> UserID as Users
> from test
> UNION ALL
> select
> 'NurseID' as Description,
> NurseID as Users
> from test
> UNION ALL
> select
> 'NurseID2' as Description,
> NurseID2 as Users
> from test
>
/****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Test] (
[UserID] [int] NULL ,
[NurseID] [int] NULL ,
[NurseID2] [int] NULL ,
[NurseID3] [int] NULL ,
[ReceptionID] [int] NULL ,
[OfficemanID] [int] NULL ,
[NurseTrainID] [int] NULL ,
[ResidentTrainID] [int] NULL ,
[ResidentTrainID2] [int] NULL ,
[ResidentTrainID3] [int] NULL
) ON [PRIMARY]
END
Insert into test
(UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
values
(1,3,9,4,7)
Select * from Test would give
UserID NurseID, NurseID2
1 3 9
and I need to transform to using SQL2000
Description Users
UserID 1
NurseID 3
NurseID2 9
I may not need the description column
Thanks for the help
Stephen K. MiyasatoHi Stephen,
2005 allows using UNPIVOT clause. Not sure about 2000 though..
http://msdn2.microsoft.com/en-us/library/ms177410.aspx
"Stephen K. Miyasato" wrote:
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>
>|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||For SS2000, you can refer to the following.
- How to rotate a table in SQL Server
http://support.microsoft.com/defaul...kb;en-us;175574
Martin C K Poon
Senior Analyst Programmer
====================================
"Stephen K. Miyasato" <miyasat@.flex.com> bl
news:%23d67IiofGHA.4864@.TK2MSFTNGP05.phx.gbl g...
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>|||Thanks very much,
That is what I was looking for.
Stephen
"dterrie" <dterrie@.axiomadvisors.net> wrote in message
news:1148404941.573763.96380@.i39g2000cwa.googlegroups.com...
> If you were using SQL Server 2005, you could use an UNPIVOT statement.
> In this case, I think you'll just have to use a series of UNION ALL
> statements to transform the data, as in:
> select
> 'UserID' as Description,
> UserID as Users
> from test
> UNION ALL
> select
> 'NurseID' as Description,
> NurseID as Users
> from test
> UNION ALL
> select
> 'NurseID2' as Description,
> NurseID2 as Users
> from test
>
how to change colum values in a trigger
Hi
Does anyone of you know how I can create a trigger to do the following
Table tbs contain 3 columns : total, num_col and alpha_col
I'm doing an insert in table tbs:
insert into tbs (total) values ('111aaa')
Now I want the trigger to split '111aaa' in to parts: An numeric and a characterpart. The trigger should store the numeric part (111) in column num_col. And the characterpart should be stored in alpha_col.
I'm working on a MS SQL Server 7
Can anyone help ?!?!?I am using sql server 2000. I don't know if INSTEAD OF TRIGGERS are available in that version. But here is a piece of code that I came up with at least to deal with the parsing of the string. This assumes that the numeric part is always at the beginning. You could use a cursor inside the trigger to process the info or if you don't want to use cursors you could write the parsing of the string as a function and use that in your SELECT statement for inserting into your table.
DECLARE @.vInput VARCHAR(20);
DECLARE @.vNum VARCHAR(10);
DECLARE @.vAlpha VARCHAR(10);
DECLARE @.vPosition INTEGER;
DECLARE @.vNumPos INTEGER;
SET @.vInput = '111aaa';
SET @.vNum = '';
SET @.vAlpha = '';
SET @.vPosition = 1;
WHILE @.vPosition <= DATALENGTH(@.vInput)
BEGIN
WHILE ASCII(SUBSTRING(@.vInput, @.vPosition, 1)) BETWEEN 48 AND 57
BEGIN
SET @.vNum = @.vNum + SUBSTRING(@.vInput, @.vPosition, 1);
SET @.vPosition = @.vPosition + 1;
END -- while number
SET @.vAlpha = @.vAlpha + SUBSTRING(@.vInput, @.vPosition,1);
SET @.vPosition = @.vPosition + 1;
END -- while string
print @.vNum;
print @.vAlpha;sql
Does anyone of you know how I can create a trigger to do the following
Table tbs contain 3 columns : total, num_col and alpha_col
I'm doing an insert in table tbs:
insert into tbs (total) values ('111aaa')
Now I want the trigger to split '111aaa' in to parts: An numeric and a characterpart. The trigger should store the numeric part (111) in column num_col. And the characterpart should be stored in alpha_col.
I'm working on a MS SQL Server 7
Can anyone help ?!?!?I am using sql server 2000. I don't know if INSTEAD OF TRIGGERS are available in that version. But here is a piece of code that I came up with at least to deal with the parsing of the string. This assumes that the numeric part is always at the beginning. You could use a cursor inside the trigger to process the info or if you don't want to use cursors you could write the parsing of the string as a function and use that in your SELECT statement for inserting into your table.
DECLARE @.vInput VARCHAR(20);
DECLARE @.vNum VARCHAR(10);
DECLARE @.vAlpha VARCHAR(10);
DECLARE @.vPosition INTEGER;
DECLARE @.vNumPos INTEGER;
SET @.vInput = '111aaa';
SET @.vNum = '';
SET @.vAlpha = '';
SET @.vPosition = 1;
WHILE @.vPosition <= DATALENGTH(@.vInput)
BEGIN
WHILE ASCII(SUBSTRING(@.vInput, @.vPosition, 1)) BETWEEN 48 AND 57
BEGIN
SET @.vNum = @.vNum + SUBSTRING(@.vInput, @.vPosition, 1);
SET @.vPosition = @.vPosition + 1;
END -- while number
SET @.vAlpha = @.vAlpha + SUBSTRING(@.vInput, @.vPosition,1);
SET @.vPosition = @.vPosition + 1;
END -- while string
print @.vNum;
print @.vAlpha;sql
Monday, March 12, 2012
How to Capture variables values in a Profiler trace
I have the variables that I pass to the SP, but there are several queries
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.
No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>
|||Don't think so. Have you looked into the SQL Debugger?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>
|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
queries[vbcol=seagreen]
variables
>
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.
No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>
|||Don't think so. Have you looked into the SQL Debugger?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>
|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
queries[vbcol=seagreen]
variables
>
How to Capture variables values in a Profiler trace
I have the variables that I pass to the SP, but there are several queries
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||Don't think so. Have you looked into the SQL Debugger?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
queries[vbcol=seagreen]
variables[vbcol=seagreen]
>
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||Don't think so. Have you looked into the SQL Debugger?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
queries[vbcol=seagreen]
variables[vbcol=seagreen]
>
How to Capture variables values in a Profiler trace
I have the variables that I pass to the SP, but there are several queries
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||Don't think so. Have you looked into the SQL Debugger?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > I have the variables that I pass to the SP, but there are several
queries
> > within the SP that get other variables from the tables being used by it.
> >
> > Is there a way to create a SQL 2000 trace that will capture the
variables
> > being used within the stored procedure?
> >
> > Thanks.
> >
> >
>
within the SP that get other variables from the tables being used by it.
Is there a way to create a SQL 2000 trace that will capture the variables
being used within the stored procedure?
Thanks.No. You might want to write those variables into a table for debugging
purposes. Or simply print them out using PRINT command, while debugging.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||Don't think so. Have you looked into the SQL Debugger?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mike" <Mike@.Comcast.net> wrote in message
news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> I have the variables that I pass to the SP, but there are several queries
> within the SP that get other variables from the tables being used by it.
> Is there a way to create a SQL 2000 trace that will capture the variables
> being used within the stored procedure?
> Thanks.
>|||I have used the debugger to see them, but I was hoping that I would be able
to capture them to make the tuning a lot easier.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:er9hLl5YEHA.996@.TK2MSFTNGP12.phx.gbl...
> Don't think so. Have you looked into the SQL Debugger?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Mike" <Mike@.Comcast.net> wrote in message
> news:OpVXZM5YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > I have the variables that I pass to the SP, but there are several
queries
> > within the SP that get other variables from the tables being used by it.
> >
> > Is there a way to create a SQL 2000 trace that will capture the
variables
> > being used within the stored procedure?
> >
> > Thanks.
> >
> >
>
How to capture the entire DML statement in a DML trigger?
Hi,
In Yukon, is it possible to capture the entire DML statement with the parameter values that triggered the DML trigger inside the trigger body?
Basically trying to see the equivalent as the eventdata() in a DDL trigger, that provides the CommandText()
Rgds
No. This is not possible.|||Thanks for the quick reply UJ.Was just trying to create a detailed Audit trace. (DDL, DML, App Login & Logout)
Can a CLR Trigger extract such data from the SQL Profiler - say using SMO in Yukon etc. - i'm not sure.
Though this would make it a very heavy trigger, but then we can run it async.
Rgds|||SMO cannot be used within CLR trigger right now. It is not supported. Also, using profiler to do these type of actions from trigger code is not ideal. Triggers should usually be very light weight and efficient. The more complex logic that you put inside your trigger the harder it is in terms of performance, development and management. Also, I am not sure how useful it will be to know which statement actually caused the trigger to fire. Note that this might not be very straight-forward too. For example, the update trigger might be fired by UPDATE statement from different SPs or triggers even. In this case, it might be useful to know the SP call that fired the trigger indirectly. What you are asking for is a call / stack trace which is not available in T-SQL now.|||Thanks UJ.
Shall keep you posted on this.
Can we expect SMO in triggers/ Call stack in Yukon?
Friday, March 9, 2012
how to call external exe from Database Trigger
Hi All,
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
>> Hi All,
>> There is a measurement data which storing some measurement values.
>> Now i want to write a trigger on this to achive following goal.
>> insert in a history table if the measurement.DataValue is increase by
>> some predefine value. also insert the time for this.
>> update in the time in same table if the measurement.DataValue come to
>> normal (i mean below that predefine value).
>> And also want to fier some external exe file on first insert scenario
>> (increase value)
>> Below is my stuff, it's working fine. But i don't know how do i call
>> external exe file to execute on specific condition.
>> Thanks & Regards
>> Rushikesh
>>
>> -- Trigger--
>> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
>> FOR INSERT
>> AS
>> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
>> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
>> inserted, Triggers
>> Where
>> Triggers.LocationID = inserted.LocationID
>> AND inserted.Value >= Triggers.TriggerValue
>> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
>> inserted)
>> where Trigger_History.TriggerID
>> IN( select t1.TriggerID from Triggers t1,inserted t2
>> Where t1.LocationID = t2.LocationID
>> AND t2.Value < t1.TriggerValue)
>>
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
>> Hi All,
>> There is a measurement data which storing some measurement values.
>> Now i want to write a trigger on this to achive following goal.
>> insert in a history table if the measurement.DataValue is increase by
>> some predefine value. also insert the time for this.
>> update in the time in same table if the measurement.DataValue come to
>> normal (i mean below that predefine value).
>> And also want to fier some external exe file on first insert scenario
>> (increase value)
>> Below is my stuff, it's working fine. But i don't know how do i call
>> external exe file to execute on specific condition.
>> Thanks & Regards
>> Rushikesh
>>
>> -- Trigger--
>> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
>> FOR INSERT
>> AS
>> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
>> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
>> inserted, Triggers
>> Where
>> Triggers.LocationID = inserted.LocationID
>> AND inserted.Value >= Triggers.TriggerValue
>> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
>> inserted)
>> where Trigger_History.TriggerID
>> IN( select t1.TriggerID from Triggers t1,inserted t2
>> Where t1.LocationID = t2.LocationID
>> AND t2.Value < t1.TriggerValue)
>>
how to call external exe from Database Trigger
Hi All,
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] fro
m
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...[vbcol=seagreen]
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
>
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] fro
m
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] f
rom
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...[vbcol=seagreen]
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
>
how to call external exe from Database Trigger
Hi All,
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)
Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs
|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh
|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...[vbcol=seagreen]
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)
Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegr oups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs
|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh
|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>
|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...[vbcol=seagreen]
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
how to call external exe from Database Trigger
Hi All,
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
>
There is a measurement data which storing some measurement values.
Now i want to write a trigger on this to achive following goal.
insert in a history table if the measurement.DataValue is increase by
some predefine value. also insert the time for this.
update in the time in same table if the measurement.DataValue come to
normal (i mean below that predefine value).
And also want to fier some external exe file on first insert scenario
(increase value)
Below is my stuff, it's working fine. But i don't know how do i call
external exe file to execute on specific condition.
Thanks & Regards
Rushikesh
-- Trigger--
CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
FOR INSERT
AS
INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
inserted, Triggers
Where
Triggers.LocationID = inserted.LocationID
AND inserted.Value >= Triggers.TriggerValue
UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
inserted)
where Trigger_History.TriggerID
IN( select t1.TriggerID from Triggers t1,inserted t2
Where t1.LocationID = t2.LocationID
AND t2.Value < t1.TriggerValue)Hi
Try to avoid calling an external programs from triggers. If some error
occurs the transaction is still open amd may hurt the perfomance and lock
others to use the table
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||In SQL Server 2000 you could use extended stored procedure (xp_cmdshell), in
SQL server 2005 CLR would be the way to go.
Before using xp_cmdshell read a bit about it and extended stored procedures.
They are not all that safe and stable.
MC
<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||Hi,
I wouldnt do any external things like sending mails, executing a
program etc in a trigger though it happen synchronously, that means the
transaction wil block you data during the execution, better do that in
a job with falgging some table that this job has to be done.
But if you really want t, you can call the app with this here:
(...TriggerCode...)
IF (SomeCondition)
BEGIN
EXEC XP_CMDSHELL 'C:\someFolder\SomeCommand.exe'
END
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||<rushikesh.joshi@.gmail.com> wrote in message
news:1143632197.419095.107480@.j33g2000cwa.googlegroups.com...
: Hi All,
:
: There is a measurement data which storing some measurement values.
:
: Now i want to write a trigger on this to achive following goal.
:
: insert in a history table if the measurement.DataValue is increase by
: some predefine value. also insert the time for this.
:
: update in the time in same table if the measurement.DataValue come to
: normal (i mean below that predefine value).
:
: And also want to fier some external exe file on first insert scenario
: (increase value)
:
: Below is my stuff, it's working fine. But i don't know how do i call
: external exe file to execute on specific condition.
:
: Thanks & Regards
: Rushikesh
:
:
: -- Trigger--
:
: CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
: FOR INSERT
: AS
:
: INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
: select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
: inserted, Triggers
: Where
: Triggers.LocationID = inserted.LocationID
: AND inserted.Value >= Triggers.TriggerValue
:
: UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
: inserted)
: where Trigger_History.TriggerID
: IN( select t1.TriggerID from Triggers t1,inserted t2
: Where t1.LocationID = t2.LocationID
: AND t2.Value < t1.TriggerValue)
:
which database? MS-SS or Oracle? you posted to both groups, but your syntax
is MS-SS.
++ mcs|||Hi All,
Thank you to reply me...
Thanks to indicate me to not to call external exe. but i want to notfiy
all users by mail and sms so for that i want to call the external
application.
but any way i will write a winServices to do the same.
But have u all check the syntex of the script. Is it ok. i mean
performance wise is it ok?
Thanks & Regards,
Rushikesh|||Do not do anything in the trigger except store a row in some other table...
Let the other program poll the other table, or use notification services, or
a sql alert to send the email...
This will scale better,and help to keep you out of trouble... just de-couple
it a little.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rushikesh.joshi@.gmail.com" wrote:
> Hi All,
> There is a measurement data which storing some measurement values.
> Now i want to write a trigger on this to achive following goal.
> insert in a history table if the measurement.DataValue is increase by
> some predefine value. also insert the time for this.
> update in the time in same table if the measurement.DataValue come to
> normal (i mean below that predefine value).
> And also want to fier some external exe file on first insert scenario
> (increase value)
> Below is my stuff, it's working fine. But i don't know how do i call
> external exe file to execute on specific condition.
> Thanks & Regards
> Rushikesh
>
> -- Trigger--
> CREATE TRIGGER [Measurement_Insert] ON [dbo].[Measurement]
> FOR INSERT
> AS
> INSERT INTO Trigger_History(TriggerID, TriggerValue, StartTime)
> select Triggers.TriggerID, inserted.[Value], inserted.[DateTime] from
> inserted, Triggers
> Where
> Triggers.LocationID = inserted.LocationID
> AND inserted.Value >= Triggers.TriggerValue
> UPDATE Trigger_History SET EndTime = (Select inserted.[DateTime] from
> inserted)
> where Trigger_History.TriggerID
> IN( select t1.TriggerID from Triggers t1,inserted t2
> Where t1.LocationID = t2.LocationID
> AND t2.Value < t1.TriggerValue)
>|||I'll echo Wayne's concern. While I understand your desire to notify users of
changes, doing so in a trigger is a very dangerous plan.
-Paul Nielsen
www.SQLServerBIble.com
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:11FE65FF-1AC1-4070-A0FB-9D46DEEB4D94@.microsoft.com...
> Do not do anything in the trigger except store a row in some other
> table...
> Let the other program poll the other table, or use notification services,
> or
> a sql alert to send the email...
> This will scale better,and help to keep you out of trouble... just
> de-couple
> it a little.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "rushikesh.joshi@.gmail.com" wrote:
>
Wednesday, March 7, 2012
How to Calculate Sum for distinct values in MDX
Hi ALL,
I need help in calculating sum of market value based on property_id. The sum should be calculated by finding the average market value for a given property and then sum the individual average of the property to get the Distinct sum.
I have a very little knowlegde in Cubes and analysis services. I need to perform this distinct sum by using calculated members using MDX on SQL server 2000.
Data example
P_Code Cus Prpty_Id Mrkt Val
3000 1234 1111 $10,000 $10,000
3000 1234 2222 $20,000
3000 1234 3333 $30,000 $20,000
3000 5678 1111 $10,000
3000 5678 2222 $20,000 $30,000
3000 5678 3333 $30,000
3000 1020 1111 $10,000
3000 1020 3333 $30,000
Distinct Sum $60,000
Thanks in Advance
BrijeshWhat about SELECT SUM(DISTINCT Mrkt)
FROM tbl
GROUP BY P_Code|||Nope: http://weblogs.sqlteam.com/jeffs/archive/2007/07/31/60274.aspx
I need help in calculating sum of market value based on property_id. The sum should be calculated by finding the average market value for a given property and then sum the individual average of the property to get the Distinct sum.
I have a very little knowlegde in Cubes and analysis services. I need to perform this distinct sum by using calculated members using MDX on SQL server 2000.
Data example
P_Code Cus Prpty_Id Mrkt Val
3000 1234 1111 $10,000 $10,000
3000 1234 2222 $20,000
3000 1234 3333 $30,000 $20,000
3000 5678 1111 $10,000
3000 5678 2222 $20,000 $30,000
3000 5678 3333 $30,000
3000 1020 1111 $10,000
3000 1020 3333 $30,000
Distinct Sum $60,000
Thanks in Advance
BrijeshWhat about SELECT SUM(DISTINCT Mrkt)
FROM tbl
GROUP BY P_Code|||Nope: http://weblogs.sqlteam.com/jeffs/archive/2007/07/31/60274.aspx
Subscribe to:
Posts (Atom)