launch an appl from a trigger ?

Is there a way to launch an application(c:\hello.exe)
from a trigger when a new record is inserted into the
table or
a record is updated in the table?
If so, what is the correct syntax ?
THANKS!Understand that launching an application that requires the input from a =user can extremely critical ... This is because this app is invoked in =the same process of SQL Server and the credential of the user to =interact with desktop maynot be there ... Hence such operation can stop =the SQL Server service ... Hence avoid such operations ...
On the contrary you like to send a popup using the network messenger =service then use the xp_cmdshell command ... But it is not recommended =in production code ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"amy" <achen@.comverge.com> wrote in message =news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> Is there a way to launch an application(c:\hello.exe)
> from a trigger when a new record is inserted into the > table or > a record is updated in the table?
> > If so, what is the correct syntax ?
> > THANKS!|||Amy,
You can use the xp_cmdshell extended procedure to start an application from
the command prompt. For exact syntax, do please refer to Books OnLine.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"amy" <achen@.comverge.com> wrote in message
news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> Is there a way to launch an application(c:\hello.exe)
> from a trigger when a new record is inserted into the
> table or
> a record is updated in the table?
> If so, what is the correct syntax ?
> THANKS!|||I would investigate having the .exe be part of a "job"
under DTS Local Packages and having the trigger start
that job - not really sure if this is possible. If it
did work, it would get around the issues raised here
about having the .exe hang things up, etc.
>--Original Message--
>Amy,
>What are you trying to do ? Ofcourse you can launch an
application by using
>master..xp_cmdshell, as in
>exec master..xp_cmdshell "dtsrun /?"
>But for every insert and update? well , no..I wont do
that.Also, if this
>hello.exe has any GUI as in exec
master..xp_cmdshell "notepad" ,
>master..xp_cmdshell would hang leading to timeout
problems.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"amy" <achen@.comverge.com> wrote in message
>news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
>> Is there a way to launch an application(c:\hello.exe)
>> from a trigger when a new record is inserted into the
>> table or
>> a record is updated in the table?
>> If so, what is the correct syntax ?
>> THANKS!
>
>.
>|||Steve,
Since you replied to my post...
It may work or may be we can find a workaround but I still wont do that.In a
heavy OLTP system, triggers are always a overhead for the DML and a trigger
which calls a exe, gui or no gui , would be a recipe for disaster.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
> I would investigate having the .exe be part of a "job"
> under DTS Local Packages and having the trigger start
> that job - not really sure if this is possible. If it
> did work, it would get around the issues raised here
> about having the .exe hang things up, etc.
> >--Original Message--
> >Amy,
> >
> >What are you trying to do ? Ofcourse you can launch an
> application by using
> >master..xp_cmdshell, as in
> >
> >exec master..xp_cmdshell "dtsrun /?"
> >
> >But for every insert and update? well , no..I wont do
> that.Also, if this
> >hello.exe has any GUI as in exec
> master..xp_cmdshell "notepad" ,
> >master..xp_cmdshell would hang leading to timeout
> problems.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"amy" <achen@.comverge.com> wrote in message
> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> >> Is there a way to launch an application(c:\hello.exe)
> >> from a trigger when a new record is inserted into the
> >> table or
> >> a record is updated in the table?
> >>
> >> If so, what is the correct syntax ?
> >>
> >> THANKS!
> >
> >
> >.
> >|||I agree that other paths might serve the same purpose,
but I'm not sure what the need really is. It would make
more sense to me to have a "scheduled" job run regularly
(every x minutes) and sweep up handling all the "rows"
inserted since the last sweep. Or code the .exe in C and
make it an extended stored procedure - run right from
inside the trigger. Or simply code the need in T-Sql and
get rid of the .exe requirement...
>--Original Message--
>Steve,
>Since you replied to my post...
>It may work or may be we can find a workaround but I
still wont do that.In a
>heavy OLTP system, triggers are always a overhead for
the DML and a trigger
>which calls a exe, gui or no gui , would be a recipe for
disaster.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Steve Z" <szlamany@.antarescomputing.com> wrote in
message
>news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
>> I would investigate having the .exe be part of a "job"
>> under DTS Local Packages and having the trigger start
>> that job - not really sure if this is possible. If it
>> did work, it would get around the issues raised here
>> about having the .exe hang things up, etc.
>> >--Original Message--
>> >Amy,
>> >
>> >What are you trying to do ? Ofcourse you can launch an
>> application by using
>> >master..xp_cmdshell, as in
>> >
>> >exec master..xp_cmdshell "dtsrun /?"
>> >
>> >But for every insert and update? well , no..I wont do
>> that.Also, if this
>> >hello.exe has any GUI as in exec
>> master..xp_cmdshell "notepad" ,
>> >master..xp_cmdshell would hang leading to timeout
>> problems.
>> >
>> >--
>> >Dinesh.
>> >SQL Server FAQ at
>> >http://www.tkdinesh.com
>> >
>> >"amy" <achen@.comverge.com> wrote in message
>> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
>> >> Is there a way to launch an application
(c:\hello.exe)
>> >> from a trigger when a new record is inserted into
the
>> >> table or
>> >> a record is updated in the table?
>> >>
>> >> If so, what is the correct syntax ?
>> >>
>> >> THANKS!
>> >
>> >
>> >.
>> >
>
>.
>|||Steve,
Well if you are asking which of the requirement to take then, my =suggestion would be to use the T-SQL method and a jobs framework ... It =is simply because you are closer to the data and getting out of process =of SQL and then get the job done is something not advisable ...
Now trigger can prove costlier as this will fire with every insert =operation ... But would make it more live data ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"Steve Z" <szlamany@.antarescomputing.com> wrote in message =news:0eb201c35779$6045c3b0$a401280a@.phx.gbl...
> I agree that other paths might serve the same purpose, > but I'm not sure what the need really is. It would make > more sense to me to have a "scheduled" job run regularly > (every x minutes) and sweep up handling all the "rows" > inserted since the last sweep. Or code the .exe in C and > make it an extended stored procedure - run right from > inside the trigger. Or simply code the need in T-Sql and > get rid of the .exe requirement...
> >--Original Message--
> >Steve,
> >
> >Since you replied to my post...
> >
> >It may work or may be we can find a workaround but I > still wont do that.In a
> >heavy OLTP system, triggers are always a overhead for > the DML and a trigger
> >which calls a exe, gui or no gui , would be a recipe for > disaster.
> >
> >-- > >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"Steve Z" <szlamany@.antarescomputing.com> wrote in > message
> >news:0c5801c35772$9a1f0bc0$a601280a@.phx.gbl...
> >> I would investigate having the .exe be part of a "job"
> >> under DTS Local Packages and having the trigger start
> >> that job - not really sure if this is possible. If it
> >> did work, it would get around the issues raised here
> >> about having the .exe hang things up, etc.
> >>
> >> >--Original Message--
> >> >Amy,
> >> >
> >> >What are you trying to do ? Ofcourse you can launch an
> >> application by using
> >> >master..xp_cmdshell, as in
> >> >
> >> >exec master..xp_cmdshell "dtsrun /?"
> >> >
> >> >But for every insert and update? well , no..I wont do
> >> that.Also, if this
> >> >hello.exe has any GUI as in exec
> >> master..xp_cmdshell "notepad" ,
> >> >master..xp_cmdshell would hang leading to timeout
> >> problems.
> >> >
> >> >-- > >> >Dinesh.
> >> >SQL Server FAQ at
> >> >http://www.tkdinesh.com
> >> >
> >> >"amy" <achen@.comverge.com> wrote in message
> >> >news:0d7f01c3576d$ed56db10$a401280a@.phx.gbl...
> >> >> Is there a way to launch an application
> (c:\hello.exe)
> >> >> from a trigger when a new record is inserted into > the
> >> >> table or
> >> >> a record is updated in the table?
> >> >>
> >> >> If so, what is the correct syntax ?
> >> >>
> >> >> THANKS!
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

lastUpdatedTs and userID from Inserted table

I wrote the following trigger:

CREATE TRIGGER dbo.tru_Employee
ON Employee
AFTER update
AS

DECLARE @.lastUpdatedTs datetime
DECLARE @.table NVARCHAR(50)
DECLARE @.transID INT
DECLARE @.userID INT

SET @.table='Employee'

SELECT @.userID = lastUpdatedBy,
@.lastUpdatedTs=lastUpdatedTs
FROM inserted

INSERT INTO AUD_tracking VALUES(@.table,@.lastUpdatedTs,@.userID)
SET @.transID=@.@.IDENTITY

Every time this triger fires it always reads the same value for userID and lastUpdatedBy. It's like 'inserted' table is not even affected by my UPDATE statement while @.oldvalue and @.newvalue that I populated the other table with (Tracking details table) are just fine.

What could it be?
ThanksYour trigger fires once for each operation, even if many records were updated. Try this more common syntax:

INSERT INTO AUD_tracking (Table, LastUpdatedTs, UserID)
SELECT 'Employee', LastUpdatedTs, LastUpdatedBy
FROM Inserted

Tip: I like to add a field to my audit table where I can store the Primary Key of the affected records so that I also know what was altered. Sometimes, I even store the before and after values. It makes a thorough paper trail that has proven helpful in diagnosing problems and averting blame.

blindman|||Originally posted by blindman
Your trigger fires once for each operation, even if many records were updated. Try this more common syntax:


Thank you for your reply.

I am sorry for giving a confusing information. This was only a beginning part of the trigger. It is followed by the part for updating each and every column. Also I am creating two tables: one that I already mentioned and the other one with tracking details(oldvalue, newvalue, etc.).

As for the timestamp amd userID being the same, I just noticed that lastUpdatedTs and lastUpdatedBy doesn't change on update in the original table either. At least when I simply run update with QA.
So I guess I have to go back to this on Monday.

Last time a table was updated - SQL 2005

How can one determine the last time when records have been inserted,
updated or deleted for a table?
Thanks.Hi
Tony has posted this sometime ago
In SQL Server 2005 you can use the sys.dm_db_index_usage_stats data
management view, look at the column last_user_update, you can also see when
the table was last accessed (last_user_seek and last_user_scan - do a MAX on
them).
select *
from sys.dm_db_index_usage_stats
where database_id = db_id( 'readpasttest' )
<Wing9897@.hotmail.com> wrote in message
news:1190639143.805215.112750@.d55g2000hsg.googlegroups.com...
> How can one determine the last time when records have been inserted,
> updated or deleted for a table?
> Thanks.
>|||Wing,
The sys.dm_db_index_usage_stats is very helpful in an ongoing operational
period, e.g. answering questions such as whether the table had been updated
in the last hour.
However, if you have tables that are only updated rarely such as every few
weeks or month, this is probably not the answer for you. These counters are
initialized to empty whenever SQL Server service is restarted. Also, a
database detach or Auto-close removes the rows for that database.
RLF
<Wing9897@.hotmail.com> wrote in message
news:1190639143.805215.112750@.d55g2000hsg.googlegroups.com...
> How can one determine the last time when records have been inserted,
> updated or deleted for a table?
> Thanks.
>

Last Inserted row

I am trying to access the row which was just inserted into a table. Will the following statements be equivalent of each other?

STATEMENT 1:
SELECT JobNumber
from tblCustServiceHistoryHdr
where ServiceType='On-site' AND ServiceStatus = 'NEW' AND DateModified =(SELECT MAX(DateModified) from tblCustServiceHistoryHdr) /* i thought by using DateModified i would get the last modified row*/

STATEMENT 2:
SELECT JobNumber
from inserted
where ServiceType='On-site' AND ServiceStatus = 'NEW' /* i don't need the DateModified criteria here since i am extracting from the INSERTED table*/Would you like to know the last row inserted by your session, or by all sessions ?

About statement 2, remember that the pseudo-table "inserted" can contain many rows. In that case, select jobnumber from inserted is still not an exact answer to your question.

Maybe if you us a little more info about your objective, forum members might have some ideas to help you out.|||Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.

But for now I will only need to know the inserted row for my session.|||How about introducing a new column to your table which will hold the time stamp for new rows inserted?|||I thought the DateModified column acted like a timestamp...u know everytime there r updates to that particular row the DateModified column is altered using getdate().|||Why not put an Identity Seed field on your table ?

Then do the insert through a stored procedure and pass back the identity field value.

By the way, @.@.IDENTITY returns the last generated value for the newly inserted row.|||What do you want to know the inserted row for ?
Might help us with a solution|||Originally posted by gayamantra
Basically there will be many ppl inserting to this table and we need to extract the specific rows that they have inserted and then update them to another table. This will be happening later on.

But for now I will only need to know the inserted row for my session.
Still confused, your objective is to trigger some action for all the rows that were inserted in your table. To my point of view, your STATEMENT 2 is exactly what you need. I'm still confused with your idea of getting the LAST row inserted, as you will apparently want to do something for ALL rows inserted.

large value data type (nvarchar(max))

hi,

I have inserted a new column in my db table (type: nvarchar(max))
now I can't process the cube anymore ... is there a possibility to store large text files in the cube?

thanks,

rhapsy

Hi Rhapsy

What are you trying to achieve with this data? Would it not be easier to try and have an action to get that text if it is needed?

Regards

Mike

|||hey mike,

probably it would be easier, but I'd like to get all my information from the same source ...
and I'm a little bit curious if thias is possible ...

thanks,
rhapsy