how to change the data label in SQL?

I have the following SQL statement..

select month(dbo.udfAddUTCBias(start_date_time, 180)) as report_month, direction as direction, count(*) as total_records from traffic with (nolock) where dbo.udfAddUTCBias(start_date_time, 180) >= '2/24/2007' and dbo.udfAddUTCBias(start_date_time, 180) < DateAdd(dd, 1, '2/24/2007') group by month(dbo.udfAddUTCBias(start_date_time, 180)), direction

but the result of the direction field is 1 and 0..

however, I need to show inbound when the result is 0 and outbound when the result is 1 without changing the actual data in the table.

You could use CASE keyword: http://msdn2.microsoft.com/en-us/library/aa258235(SQL.80).aspx

select month(dbo.udfAddUTCBias(start_date_time, 180)) as report_month

, case direction

when 0 then 'inbound'

when 1 then 'outbound'

end

as direction

, count(*) as total_records from traffic with (nolock) where dbo.udfAddUTCBias(start_date_time, 180) >= '2/24/2007' and dbo.udfAddUTCBias(start_date_time, 180) < DateAdd(dd, 1, '2/24/2007') group by month(dbo.udfAddUTCBias(start_date_time, 180)), direction

how to change the crystal report database location at runtime in vb.net

Hello,

I am using the following codings to change the databse location at runtime. But i found an error 'invalid field name in formula' in some of the reports. The error occurs when we use the databse fields in 'formula field editor' of crystal reports. let me know the reason why its happen? find and send a solution to solve this problem.

Dim logOnInfo As New TableLogOnInfo()
Dim crtableLogoninfo As New TableLogOnInfo()
Dim crConnectionInfo As New ConnectionInfo()
Dim TableLocation As String

Dim vTable As Table
crConnectionInfo.ServerName = gConnServerName
crConnectionInfo.DatabaseName = gConnDatabase
crConnectionInfo.UserID = gConnUserName
crConnectionInfo.Password = gConnPassword

RptDoc.SetDatabaseLogon(gConnUserName, gConnPassword, gConnServerName, gConnDatabase)

For Each vTable In RptDoc.Database.Tables
crtableLogoninfo = vTable.LogOnInfo
crtableLogoninfo.ConnectionInfo = crConnectionInfo
vTable.ApplyLogOnInfo(crtableLogoninfo)
'' vTable.TestConnectivity()
'' MsgBox(vTable.TestConnectivity)
'' MsgBox(vTable.Fields(0).Name())

vTable.Location = Trim(crConnectionInfo.DatabaseName) & ".dbo." & Trim(vTable.Location.Substring(vTable.Location.LastIndexOf(".") + 1))

Next

thank you .
shanthipl reply

How to change Query Timeout?

Greetings

I have encountered the following problem:

I currently develop an application for my company that actually uses rather long queries, with many records.

I have a particular query (Written using SQL string inside the .NET application rather than Stored proceedures),that needs to run in 2 databases (both SQL Server):

The first one is a test database that we use when in developing time quota to test our data

The second one is the real thing a data base that contains lots of records.

When criteria are placed in the query, it returs few records in both the databases , but if no criteria are placed (So it fetches all the records..) In the test Database works ok, but in the real one it "jams" till 30 seconds pass and I get a time out message...

I tried to change the Query time out time from inside the SQL Server from

Tools/Options/Advanced

but it doesn't seem to work out... it still times out after 30 secs

Any Ideas?

Thanx in advance :DCould it be that this is not a SQL problem but an ADO-ADO.NET problem, i'm sure the default commandtimeout for command objects in ADO-ADO.NET is 30 seconds if not specified, specify 0 for unlimited timeouts and a value in seconds if you want different from the default.

I'd go for a 45 seconds property on the commandtimeout property...

regards,
J.

P.S. If i'm off base here I appologise, this is my first post, total newb...|||Thanks a lot for the advise Dreamweaver, but I need to specify that I tested the queries from inside the SQL Server enterprise Manager creating two new Views (One in each separate Data Base) and pasting in their SQL section the SQL string that I "Pinched off/Copied " from the debugger of the .NET just before it is fed to the adapter...

So I have side by side two new viewes with the exactly same SQL, but belonging to two different data bases...

So the problem arises through the SQL server...

Any more Ideas guys?...

Thanks!. :D|||I've just been looking at the settings on a server I am looking after. Surprisingly the timeout properties in Tools/Options/Advanced differ from right clicking the server and selecting properties and then looking at the bottom part of the connection tab, on this server it has;

0 in Tools/Options/Advanced

and

600 in Server Properties/Connection Tab

I'd have a look in right click Server/Properties/Connection Tab and see if that says 30,

regards,
J.|||first set sp_configure to display advanced options
execute reconfigure with override to force a "LIVE" change
then set remote query timeout to an appropriate value.
run reconfigure with override again.

USE master
EXEC sp_configure 'show advanced option', '1'
/*
Here is the message:
Configuration option 'show advanced options' changed from 0 to 1.
Run the RECONFIGURE command to install.
*/

RECONFIGURE with override
EXEC sp_configure

--XXXX change to query timeout value (example '800')
sp_configure 'remote Query Timeout', 'XXXX'

:eek: I was so tired at the time i wrote this that i lost myself in coloring the code in the message posting areasql

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

How to catch sql exceptions gracefully when deleting some records

I use the following function (in the BLL) to delete some records:

PublicFunction DeleteStep4Dashboards()AsBoolean

Try

adpDashboards.DeleteStep4Dashboards()

Catch exAs Exception

ReturnFalse

EndTry

ReturnTrue

EndFunction

How can I catch the sql database errors when deleting the records goes wrong.

You can add a Catch for a SqlException e.g.

Catch sqlExAs SqlException

|||

You can specify the type of the exception that you want to catch, e.g. Catch ex as SqlException (rather than as Exception).

However, catching exceptions and returning a value indicating success or failure is in general bad coding style. An exception means that something has gone wrong with what you are trying to do, and you either need to take some remedial action such as correcting parameters and retrying, or report it to the user and/or administrator.

Returning false or true puts the onus of detecting and handling errorconditions on all your method's callers, and there could be several ofthose scattered through your code, making it difficult to maintain. On the other hand, throwing an exception means that your callers only need to write code to handle the error condition if they are actually able to do something about it. Otherwise they need to be allowed to bubble up to the top layer so that they can be logged and/or the user can be notified that something went wrong.

|||

Another option is to put the delete into a stored procedure and handle it there -- in fact, you may be able to prevent any errors by checking the state of your data first to make sure the delete will work before actually trying it (eg, if a foreign key might prevent the delete)

|||

Is it really best practise to bubble up the error.

Can't I show an general error message to the user.

And handle the error (logging, e-mail to administrator) in the BLL?

|||

JohanNL:

Is it really best practise to bubble up the error.

Without meaning to answer for James, I would say that you be misundestanding what he is suggesting. As he wrote, the rule of thumb is to catch an exception only if you intend to do something about it, and that may mean logging the error and showing the user a general message. I would say that it's an application specific decision about where you want to log the errors, but if you think about it, it's not worth the effort to put try...catch everywhere if all you're going to do is log it and move on. OTOH, updates often cry out for special handling in the event of a sql error, especially since it's often helpful to catch an error right where it happens.

The best thing to do, of course, is to anticipate every possible error and code in such a way that you prevent them from happening. If you think you might get a divide by zero exception, for instance, do the check before attempting the division and give the user a message. I do this in stored procedures all the time. However, if you could think of everything you probably wouldn't need an exception log anyway<g>

How to carry forward inventory balances

I have data stored in a table in the following format.
Product# Month Year Inventory Balance
For example the inventory balances for a Product A exists in the
table as follows:
Month Qty on Hand
======= ===========
001.2004 120
004.2004 235
006.2004 89
011.2004 42
003.2005 980
======= ===========
Now if the user is trying to report all inventory balances as of
001.2005 even though I do not have a value in the cube for product A I
expect the system to go and pick the last know stock on hand which is
is 42 the known stock on hand qty in 011.2004
Please advice how I can implement these kind of query to solve this
problem. Bottom line what I expect is for a given product A if there is
data in periods 1, 5 and 9 the system carry forward the balance in period
1 to periods 2,3,4 similarly the balance in period 5 must be carried
over to periods 6,7 & 8 similarly the balance in period 9 must be carried
forward to period 10, 11 and 12
Please help me coding the query SQL or the algorithm to arrive at this
kind of reporting.
Thanks
KarenTry,
use northwind
go
create table t (
colA datetime,
quantity int
)
go
insert into t values('20040101', 120)
insert into t values('20040401', 235)
insert into t values('20040601', 89)
insert into t values('20040701', 42)
insert into t values('20050301', 980)
go
select top 12
identity(int, 1, 1) as number
into
number
from
sysobjects as a
go
create view my_view
as
select
*
from
(
select
ltrim(col_year) + right('00' + ltrim(number.number), 2) + '01'
from
(
select distinct
year(colA)
from
t
) as a(col_year)
cross join
number
) as ym(col_date)
left join
t
on t.colA = ym.col_date
go
select
a.col_date,
coalesce(a.quantity, b.quantity)
from
my_view as a
left join
my_view as b
on a.quantity is null and b.col_date = (select max(c.colA) from t as c
where c.colA < a.col_date and c.quantity is not null)
go
drop view my_view
go
drop table number, t
go
AMB
"Karen Middleton" wrote:

> I have data stored in a table in the following format.
> Product# Month Year Inventory Balance
>
> For example the inventory balances for a Product A exists in the
> table as follows:
>
> Month Qty on Hand
> ======= ===========
> 001.2004 120
> 004.2004 235
> 006.2004 89
> 011.2004 42
> 003.2005 980
> ======= ===========
>
> Now if the user is trying to report all inventory balances as of
> 001.2005 even though I do not have a value in the cube for product A I
> expect the system to go and pick the last know stock on hand which is
> is 42 the known stock on hand qty in 011.2004
> Please advice how I can implement these kind of query to solve this
> problem. Bottom line what I expect is for a given product A if there is
> data in periods 1, 5 and 9 the system carry forward the balance in period
> 1 to periods 2,3,4 similarly the balance in period 5 must be carried
> over to periods 6,7 & 8 similarly the balance in period 9 must be carried
> forward to period 10, 11 and 12
>
> Please help me coding the query SQL or the algorithm to arrive at this
> kind of reporting.
>
> Thanks
> Karen
>

how to capture o/p of SELECT .. FOR XML AUTO.

Hi,
Can u please tell me how can i store o/p of following query into a variable
and process it.
"SELECT TOP 1 FIRSTNAME, LASTNAME FROM PATIENT FOR XML AUTO"
Thanks
Gopinath M.
"Gopinath Munisifreddy" <Gopinath@.Microsoft.com> wrote in message
news:eaMr75zEEHA.3336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can u please tell me how can i store o/p of following query into a
variable
> and process it.
> "SELECT TOP 1 FIRSTNAME, LASTNAME FROM PATIENT FOR XML AUTO"
You can't store the results of a for xml query just within SQL. The XML is
created by the provider.
Bryant
|||There are some ugly workarounds using the sp_OA stored procedures.
In SQL Server 2005 (beta2 comming soon, watch this space for nomination
registration), you will be able to do so.
Best regards
Michael
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:Ogsvi70EEHA.3568@.tk2msftngp13.phx.gbl...
> "Gopinath Munisifreddy" <Gopinath@.Microsoft.com> wrote in message
> news:eaMr75zEEHA.3336@.TK2MSFTNGP12.phx.gbl...
> variable
> You can't store the results of a for xml query just within SQL. The XML is
> created by the provider.
> --
> Bryant
>

How to call Integration Services Project from Web UI

I just got done finishing an Integration Services Project (which I have to say was sickening easy!) which does the following:

1) Imports a comma delimited txt file

2) Exports it into a table

3) I do some manipulation and other table creation using SQL

4) Outputs a table to a flat file again

I now need to allow the user to run this process. I'd like to either:

a) Provide them a shortcut that when clicked on their desktop starts the process that I have defined in my Integration Services Project

b) Better yet, create a web U I that has a button they can click on, something that shows the progress in time, and then provides the output file as a downloadable link

I'd like to kno whow to do a & b just in case I decide to do one or the other at the end, I'd like to know how to do both for future reference ?

If the package is on the local machine (as well as SSIS), the easiest solution is to use the object model to load and execute the package. See "Running an Existing Package from a Client Application" in BOL. You can do this from any type of managed application, although you'll have extra permissions issues to iron out in a Web app.

If the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

-Doug

|||

I>>>>f the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

My Integration Services Project is calling the stored proc from within an "Execute SQL Task" module in my project. The whole point her is to take advantage of Integration services and it's workflow, after all why would I start a stored procedure outside of this when it's integrated in my packge?

So essentially, I want ASP.NET web button to fire of the start of my Integration Services package, just the same as I go into VS 2005 and click Play to run it. What command can call my package remotely to run it? How can I determine when it's done so I can show a processing bar on my web app?

I know this is possible, there has to be a way programically to invoke / call your Integration Services Project to run from a web page. That's the whole point in using Integration Services to do the dirty work with this stuff, I just want to be able to call it remotely from a web app in ASP.NET - a button or something that runs a script to run the project wherever it resides.

|||

I'm not certain how my response was misunderstood. My answer was precisely about the "way to programmatically invoke an Integration Services package from a Web page" or any other application.

If the package is local, I'd recommend using the API as described in the topic that I quoted, which takes about 2 lines of code (Load and Execute, as well as variable declarations).

You can also call dtexec.exe. If the package is not local, the normal way to launch it is through SQL Agent, by calling the sp_startjob stored procedure to launch the remote package, after configuring an unscheduled job that runs the package. In code, you would use ADO.NET to launch the stored procedure on the remote server.

-Doug

|||Ok, per your last response, now I understand. I have never done any of that before so I needed a more in depth response. Thanks.|||

No problem. Here's the VB code to launch a local package using the API, in case this wasn't in the RTM version of BOL...

Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

Sub Main()

Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

pkgLocation = _
"C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
pkgResults = pkg.Execute()

Console.WriteLine(pkgResults.ToString())
Console.ReadKey()

End Sub

End Module

|||I really appreciate it, I didn't really know how to go about it. Thanks a lot!|||I'm also assuming I can run a package that is not local if I just tweak the filepath to use UNC or something?|||

Hi, I thought I had asked this question but don't see it in the topic thread. How do you call a SQL Agent job from a client app to run a SSIS package?

Thanks

|||

Using ADO.Net.

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

-Doug

|||

You could also - as I would prefer - use SMO.

here are some hints:
http://www.fits-consulting.de/blog/PermaLink,guid,09a62245-9c7f-4d2a-aee2-7f30bbdee1c6.aspx

and here is the detailed link:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.start.aspx

cheers,
Markus

|||Thanks, the VB example in the article is what I was looking for.

How to call Integration Services Project from Web UI

I just got done finishing an Integration Services Project (which I have to say was sickening easy!) which does the following:

1) Imports a comma delimited txt file

2) Exports it into a table

3) I do some manipulation and other table creation using SQL

4) Outputs a table to a flat file again

I now need to allow the user to run this process. I'd like to either:

a) Provide them a shortcut that when clicked on their desktop starts the process that I have defined in my Integration Services Project

b) Better yet, create a web U I that has a button they can click on, something that shows the progress in time, and then provides the output file as a downloadable link

I'd like to kno whow to do a & b just in case I decide to do one or the other at the end, I'd like to know how to do both for future reference ?

If the package is on the local machine (as well as SSIS), the easiest solution is to use the object model to load and execute the package. See "Running an Existing Package from a Client Application" in BOL. You can do this from any type of managed application, although you'll have extra permissions issues to iron out in a Web app.

If the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

-Doug

|||

I>>>>f the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

My Integration Services Project is calling the stored proc from within an "Execute SQL Task" module in my project. The whole point her is to take advantage of Integration services and it's workflow, after all why would I start a stored procedure outside of this when it's integrated in my packge?

So essentially, I want ASP.NET web button to fire of the start of my Integration Services package, just the same as I go into VS 2005 and click Play to run it. What command can call my package remotely to run it? How can I determine when it's done so I can show a processing bar on my web app?

I know this is possible, there has to be a way programically to invoke / call your Integration Services Project to run from a web page. That's the whole point in using Integration Services to do the dirty work with this stuff, I just want to be able to call it remotely from a web app in ASP.NET - a button or something that runs a script to run the project wherever it resides.

|||

I'm not certain how my response was misunderstood. My answer was precisely about the "way to programmatically invoke an Integration Services package from a Web page" or any other application.

If the package is local, I'd recommend using the API as described in the topic that I quoted, which takes about 2 lines of code (Load and Execute, as well as variable declarations).

You can also call dtexec.exe. If the package is not local, the normal way to launch it is through SQL Agent, by calling the sp_startjob stored procedure to launch the remote package, after configuring an unscheduled job that runs the package. In code, you would use ADO.NET to launch the stored procedure on the remote server.

-Doug

|||Ok, per your last response, now I understand. I have never done any of that before so I needed a more in depth response. Thanks.|||

No problem. Here's the VB code to launch a local package using the API, in case this wasn't in the RTM version of BOL...

Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

Sub Main()

Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

pkgLocation = _
"C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
pkgResults = pkg.Execute()

Console.WriteLine(pkgResults.ToString())
Console.ReadKey()

End Sub

End Module

|||I really appreciate it, I didn't really know how to go about it. Thanks a lot!|||I'm also assuming I can run a package that is not local if I just tweak the filepath to use UNC or something?|||

Hi, I thought I had asked this question but don't see it in the topic thread. How do you call a SQL Agent job from a client app to run a SSIS package?

Thanks

|||

Using ADO.Net.

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

-Doug

|||

You could also - as I would prefer - use SMO.

here are some hints:
http://www.fits-consulting.de/blog/PermaLink,guid,09a62245-9c7f-4d2a-aee2-7f30bbdee1c6.aspx

and here is the detailed link:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.start.aspx

cheers,
Markus

|||Thanks, the VB example in the article is what I was looking for.

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

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

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:

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

how to call C/C++ DLL in stored Procedure?

How do I call a C/++ DLL in SQL Server stored procedure?
The C function has following prototype.
double function_name(char* x,int i)
ThanksRead about extended stored procedures...

How to Call a sproc from within a UDF that returns a table?

Hi. This is a SQL question.

I am trying to wrap a stored procedure with a UDF so I can do selects against the result. The following doesn't work, but is it possible to do something like:

Create Function test()
returns @.tmptable ( myfieldint)
as
begin
insert into @.tmp (field1)
Exec dbo.MySprocWhichRequires3ParmsAndReturnsATable 5, 6, 10 output
return
end

Thanks

There's a chance something similar to this could work

http://blogs.claritycon.com/blogs/curtis_swartzentruber/archive/2006/04/12/384.aspx

how to calculate the least anmongst a list of Quarters

Hi,

i get a list of Quarters that look like following as an input to my proc.

'Q306/Q106/Q406/Q405'

I need to find out whihc one of the above is the least Quarter value.

In the above example it is 'Q405'

The Quarter values that come into the proc keep changing...It could be even 1 or 2 or 3 Quarters etc...

Is there any datatype called Quarter in SQL Server.It shows Quarter as a key word...

Can some one please help on this....

Hello,

There is no "quarter" datatype in sql server.

For your example, the parameter string will need to first be parsed (to split the quarters - you can use a recursive cte for this) then ordered. If you are using SQL 2005, you can also roll your own "quarter" datatype that could include ranking functions such as MIN/MAX etc.

What version of sql are you using?

Cheers

Rob

|||

Thanks for the reply...

I am using SQL Server2000. Can you please be more elaborate on this as I am a novice.what exactly is recursive cte...

Can you please send some sample code if possible...

Thanks...

|||

This function will help split up string with a delimited character. It was written in SQL 2005, but you should be able to change MAX to a length less than 8000 to make it work.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[SplitData] (@.Data VARCHAR(MAX), @.delimeter CHAR(1))

RETURNS @.v_Values TABLE ([Value] VARCHAR(MAX), [Position] BIGINT IDENTITY(1,1))

AS

BEGIN

DECLARE @.v_value VARCHAR(MAX),

@.v_pos BIGINT

SET @.v_Pos = 1

WHILE @.data > '' AND @.v_Pos > 0

BEGIN

SELECT @.v_pos = CHARINDEX(@.delimeter, @.data,1),

@.v_Value = CASE @.v_Pos

WHEN 0 THEN

@.data

ELSE

LEFT(@.data, @.v_pos-1)

END,

@.data = CASE

WHEN @.v_Value = @.data THEN

NULL

ELSE

SUBSTRING(@.data, @.v_pos + 1, LEN(@.data))

END

INSERT INTO @.v_Values ([Value])

VALUES (@.v_Value)

END

return

END

|||

I couldnt really understand how to use the above function...

Is it possible to get the seperate values by splitting the following data

'Q106/Q206/Q306 Version1/Q406 Version2/Current'

I need to be able to spilt the above Data...the delimiter is '/'...

is there something like a spilt function in Sql Server2000....

Please get back.

Thanks

Swapna

|||

The split function is the function I posted. The first parameter is the value and the second parameter is the delimiter.

SELECT Value

FROM dbo.SplitData('Q106/Q206/Q306 Version1/Q406 Version2/Current','/')

Hope this helps...

|||

Here is the version for 2000.

Run it with this:

SELECT * FROM dbo.SplitData('Q106/Q206/Q306 Version1/Q406 Version2/Current','/')

/*

Results:

Value Position

Q106 1

Q206 2

Q306 Version1 3

Q406 Version2 4

Current 5

*/

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[SplitData] (@.Data VARCHAR(8000), @.delimeter CHAR(1))

RETURNS @.v_Values TABLE ([Value] VARCHAR(1000), [Position] BIGINT IDENTITY(1,1))

AS

BEGIN

DECLARE @.v_value VARCHAR(1000),

@.v_pos BIGINT

SET @.v_Pos = 1

WHILE @.data > '' AND @.v_Pos > 0

BEGIN

SELECT @.v_pos = CHARINDEX(@.delimeter, @.data,1),

@.v_Value = CASE @.v_Pos

WHEN 0 THEN

@.data

ELSE

LEFT(@.data, @.v_pos-1)

END,

@.data = CASE

WHEN @.v_Value = @.data THEN

NULL

ELSE

SUBSTRING(@.data, @.v_pos + 1, LEN(@.data))

END

INSERT INTO @.v_Values ([Value])

VALUES (@.v_Value)

END

return

END

GO

|||

Thanks a lot....this almost saved my project...

Thanks

How To Calculate Deviation in Matrix?

Hi All,

I want to create following matrix report on SSRS.

Yearmonth

Jan-07

Feb-07

Mar-07

Apr-07

May-07

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Product X1 100 0 110 10 130 20 100 -30 90 -10

Here the Problem is i want calculate Deviation:

Deviation for Feb 07= Sales in Feb - Sales In Jan

Deviation For Mar 07 = Sales in Mar - Sales in Feb.

I don't know how to apply formula in Matrix:

Is anybody hele me?

Thank You.

Balwant Patel.

I do not think there is a striaght-forward formula for this since use of the Previous function is not supported in the Matrix data cell. You can either try calculating the deviation from the query itself, or implement it in the following manner using Custom Code:

Place the following VB.Net code in the code tab under report properties:

Shared Public PrevSales as Integer

Public Function CalDeviation(ColCount as Integer,CurSales as Integer) as Integer
Dim deviation as Integer

If ColCount=1 Then
PrevSales=CurSales
deviation=0

Else
deviation=CurSales-PrevSales
PrevSales=CurSales

End If

Return deviation

End Function

Then use the following call to the function in the textbox where you want to show the deviation:

=Code.CalDeviation(RunningValue(Fields!Month.Value,Count,"matrix1_Product"),Sum(Fields!Sales.Value))

Here, I have assumed the names Product, Month & Sales for the respective fields. Also, the Matrix Row Grouping is on Product & Column Grouping is on Month.

Hope this suits your purpose

-Aayush

|||

Hi Aayush,

Thank you very much for your response...

I tried the code given by you...But when try to write the code in code window as below:

=Code.CalDeviation

the function .CalDeviation doesn't come up....

Thank you,

Regards,

Balwant Patel.

|||

Hi Aayush,

The idea that you give me works now but it will not work when i view Report for Year Or Quarter .....

Please help me on this issue...

Thank you,

Regards,

Balwant Patel.

|||I guess it depends on your requirement, and how your Matrix is designed. What kind of functionality do you desire? Suppose you replace your Column Grouping from Month to either Year or Quarter, then it will work. If your column grouping changes on some condition etc, then it might be a bit more complicated.|||

Hi Aayush,

Thank you very much for your response...

I did solve the problem....I applied following logic to solve the problem...In my Data Table I add Six Columns Like CurrMonthSales , PrevMonthSales , CurreQuarterSales , PrevQuarterSales , CurrYearSales , PrevYearSales...And then Updates these columns through Stored proc...And Then Based in Inscope Function...I Apply The Formula.

Thank you very much again..

Regards,

Balwant Patel.

How To Calculate Deviation in Matrix?

Hi All,

I want to create following matrix report on SSRS.

Yearmonth

Jan-07

Feb-07

Mar-07

Apr-07

May-07

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Sales

Deviation

Product X1 100 0 110 10 130 20 100 -30 90 -10

Here the Problem is i want calculate Deviation:

Deviation for Feb 07= Sales in Feb - Sales In Jan

Deviation For Mar 07 = Sales in Mar - Sales in Feb.

I don't know how to apply formula in Matrix:

Is anybody hele me?

Thank You.

Balwant Patel.

I do not think there is a striaght-forward formula for this since use of the Previous function is not supported in the Matrix data cell. You can either try calculating the deviation from the query itself, or implement it in the following manner using Custom Code:

Place the following VB.Net code in the code tab under report properties:

Shared Public PrevSales as Integer

Public Function CalDeviation(ColCount as Integer,CurSales as Integer) as Integer
Dim deviation as Integer

If ColCount=1 Then
PrevSales=CurSales
deviation=0

Else
deviation=CurSales-PrevSales
PrevSales=CurSales

End If

Return deviation

End Function

Then use the following call to the function in the textbox where you want to show the deviation:

=Code.CalDeviation(RunningValue(Fields!Month.Value,Count,"matrix1_Product"),Sum(Fields!Sales.Value))

Here, I have assumed the names Product, Month & Sales for the respective fields. Also, the Matrix Row Grouping is on Product & Column Grouping is on Month.

Hope this suits your purpose

-Aayush

|||

Hi Aayush,

Thank you very much for your response...

I tried the code given by you...But when try to write the code in code window as below:

=Code.CalDeviation

the function .CalDeviation doesn't come up....

Thank you,

Regards,

Balwant Patel.

|||

Hi Aayush,

The idea that you give me works now but it will not work when i view Report for Year Or Quarter .....

Please help me on this issue...

Thank you,

Regards,

Balwant Patel.

|||I guess it depends on your requirement, and how your Matrix is designed. What kind of functionality do you desire? Suppose you replace your Column Grouping from Month to either Year or Quarter, then it will work. If your column grouping changes on some condition etc, then it might be a bit more complicated.|||

Hi Aayush,

Thank you very much for your response...

I did solve the problem....I applied following logic to solve the problem...In my Data Table I add Six Columns Like CurrMonthSales , PrevMonthSales , CurreQuarterSales , PrevQuarterSales , CurrYearSales , PrevYearSales...And then Updates these columns through Stored proc...And Then Based in Inscope Function...I Apply The Formula.

Thank you very much again..

Regards,

Balwant Patel.

How to bulk insert a file from a computer other than the sql serve

I am executing the following bulk insert command:
BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
The sql server and file:'E:\myOutput.txt' are in different computers.
The sql server generated the following error msg:
Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
Operating system error code 21(The device is not ready.).
Any suggestions?
Thank you!
Is E: drive letter mapped to a network path to the share that has
myOutput.txt on it? If so, and you can see the file from a process on the
box with SQL Server on then BULK INSERT should work. I suspect you're
specifying E: as the drive letter of the remote computer - that's not going
to work.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"she" <she@.discussions.microsoft.com> wrote in message
news:6E44D770-FE7D-4EBC-B351-67273CB2C18B@.microsoft.com...
> I am executing the following bulk insert command:
> BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
> The sql server and file:'E:\myOutput.txt' are in different computers.
>
> The sql server generated the following error msg:
> Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
> Operating system error code 21(The device is not ready.).
> Any suggestions?
> Thank you!
>

How to bulk insert a file from a computer other than the sql serve

I am executing the following bulk insert command:
BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
The sql server and file:'E:\myOutput.txt' are in different computers.
The sql server generated the following error msg:
Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
Operating system error code 21(The device is not ready.).
Any suggestions?
Thank you!Is E: drive letter mapped to a network path to the share that has
myOutput.txt on it? If so, and you can see the file from a process on the
box with SQL Server on then BULK INSERT should work. I suspect you're
specifying E: as the drive letter of the remote computer - that's not going
to work.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"she" <she@.discussions.microsoft.com> wrote in message
news:6E44D770-FE7D-4EBC-B351-67273CB2C18B@.microsoft.com...
> I am executing the following bulk insert command:
> BULK INSERT xSat FROM 'E:\myOutput.txt' WITH (FIELDTERMINATOR = ' ')
> The sql server and file:'E:\myOutput.txt' are in different computers.
>
> The sql server generated the following error msg:
> Could not bulk insert because file 'E:\myOutput.txt' could not be opened.
> Operating system error code 21(The device is not ready.).
> Any suggestions?
> Thank you!
>