Using Xp_cmdshell In INSERT Trigger: Bad Idea?
Jul 20, 2005
Whenever something is inserted to a given table, I want to run some
shell commands using xp_cmdshell. Would it be a bad idea to put this
xp_cmdshell in the INSERT trigger of this table?
I understand that when using xp_cmdshell, the sql thread in question
waits until xp_cmdshell finishes what it's doing. Does this mean if
my xp_cmdshell call takes 30 seconds, that nobody else can insert to
this table until my xp_cmdshell and rest of the insert trigger finish
up?
The alternative solution seems to be frequent polling of the table in
question; while this isn't really a great solution it would seem to
circumvent a table lock brought about by the INSERT trigger.
Thoughts?
Joel
View 4 Replies
ADVERTISEMENT
Jul 20, 2005
I have created the following trigger:CREATE TRIGGER [CreateFile] ON OutputTableFOR INSERTASDeclare @filename nvarchar(35)Declare @filecontents nvarchar(2000)Declare @strcmdshell varchar(150)SELECT @filecontents = OutputText FROM INSERTEDSELECT @filename = 'c:' + OutputFileName FROM INSERTEDSELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filenameexec master..xp_cmdshell @strcmdshellIt works fine as long as the column OutputText has no carriagereturns.I have used my Access2K front end to dump about ten lines of text intoOutputText, each line broken by vbcrlf so that OutputText looks like:line1testline2textline3textetc.The trigger won't fire with this text.Is there something I can do to remedy this?lq
View 4 Replies
View Related
Jul 2, 2007
<p>Hi ,
I would like to know anyway to insert two strings into a row with 2 lines created?My codes are as below
If NodeName = "subProductPrice" Then If xmlrder.NodeType = Xml.XmlNodeType.Text Then SubPrPriceList.Add(xmlrder.Value)
For i = 0 To SubPrPriceList.Count - 1 SubPrPrice = CStr(SubPrPriceList.Item(i)) PriceBrkDownStr &= SubPrPrice
Next
PricePerDay = SvcDate & PriceBrkDownStr dr("dailyPrice") = PricePerDay End If End If
Although both the SvcDate and PriceBrkDownStr are inserted into the same row but they are displayed into one line such as below:
<u>dailyPrice </u>02/03/2007 03/03/2007 120 230
Any idea how to make the date and price separate into two rows in the same table grid row? thanks =)</p>
View 3 Replies
View Related
Jul 20, 2005
I'm using xp_cmdshell to output a text file from a trigger like thisCREATE TRIGGER [CreateFile] ON tblApplicationsFOR INSERTASDECLARE @FirstName varchar(75)DECLARE @LastName varchar(75)Declare @strcmdshell varchar(150)Declare @filename varchar(100)Declare @filecontents varchar(150)SELECT @FirstName = FirstName FROM INSERTEDSELECT @LastName = LastName FROM INSERTEDSELECT @filecontents = 'FirstName: ' + @FirstName+char(13)+'LastName: ' +@LastName+char(13)+SELECT @filename = 'c: est.txt'SELECT @strcmdshell = 'echo '+ @filecontents+ ' >'+ @filenameexec master..xp_cmdshell @strcmdshellWhen I concatenate @FirstName and @LastName(neither is NULL) and try toinsert a blank line with char(13) the command doesn't run. If I don't usechar(13) it works fine. Does this make sense? IS there any other way I canformat the output file.Thanks
View 3 Replies
View Related
Apr 4, 2003
I can run the xp_cmdshell command with any other command until I put something like this is.
CREATE TRIGGER RunUpdateProgram ON [dbo].[UpdateQue]
FOR INSERT
AS
declare @MybatchID varchar(32),
@sendcommand varchar(32)
select @MybatchID = batchid from updateque
select @sendcommand = '"d:xanaduostarterprog.exe ' + @MybatchID + '"'
exec master..xp_cmdshell @sendcommand, no_output
The starterprog is setup to receive commandline input and then do its thing but everytime I try to add in a commandline to the xp_cmdshell command i get nothing.
The security is right and the program is right because i can run it until i try to add that last part "@MybatchID". If I take out the quotes then it runs the program but without the commandline input. I've tested the program manually and it correctly accepts commandlines that way.
Anyone got any ideas on what I've done wrong here.
View 1 Replies
View Related
Jan 19, 2007
I have a trigger that should be execute on each row insert and only ifappcode = 'I' and datasent = 0. It should execute a DTS package. TheDTS package by itself runs about 6 seconds. Trigger was createdsuccessfuly. When I try to insert a row my db hangs. I can see thatSPID on my db is hang by SPID from master db. It doesn't completeuntil I kill that SPID. Why is that?This is the trigger that I am trying to execute:CREATE TRIGGER myExportON ruExportFOR INSERTASdeclare @appcode varchar (10)select @appcode = appcode from ruexport where appcode = 'I' anddatesent = 0if @appcode = 'I'exec master..xp_cmdshell 'DTSRun /S "KOCL384017SQL" /N "Sys_Export" /G"{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'goAny suggestions?
View 2 Replies
View Related
Jul 12, 2004
Hi All,
I'm building a quick and dirty SQL/IIS application and I need to have the server print a 'receipt' page whenever a record is submitted from one of several workstations. I am looking for good ways to do this. The receipt will need to have custom fonts (barcodes). Anyone have any suggestions? Reliability is key.
Thanks!
alfa
View 3 Replies
View Related
Oct 30, 2007
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
View 17 Replies
View Related
Feb 5, 2008
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
View 9 Replies
View Related
Mar 13, 2006
BCP thru xp_cmdshell from stored procedure:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE
EXEC xp_cmdshell 'bcp database.dbo.table in c:scheduled.csv -S SERVERSQLEXPRESS -T -t, -r -c -e "error.txt"';
This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.
=================================================================
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
=================================================================
Error message when using BULK INSERT as follows:
BULK INSERT database.dbo.table from 'c:unscheduled.csv' with
(FIELDTERMINATOR = ',', ERRORFILE = 'c:error.txt');
Returns the following error message:
=================================================================
Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9
Cannot bulk load because the file "c:error.txt" could not be opened. Operating system error code 80(The file exists.).
Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9
Cannot bulk load because the file "c:error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).
=================================================================
The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.
EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2
View 1 Replies
View Related
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
View 5 Replies
View Related
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
Nov 25, 2006
In case of a bulk insert, the “FOR INSERT� trigger fires for each recod or only once?
Thanks,
View 1 Replies
View Related
Nov 20, 2006
I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have some similar sample code?
Thank you very much
View 1 Replies
View Related
Dec 3, 2006
I would like to have the value of a field to be set the return value of
System.Web.Security.Membership.GeneratePassword(12,4)
every time a a row is inserted.
Can you guide with this?
Do you have some similar sample code?
Thank you very much
View 1 Replies
View Related
Nov 12, 2001
Should a insert trigger fire my update trigger as well? it fires it automatically after the insert? Is this a bug?
View 1 Replies
View Related
Aug 31, 2000
I have to create a insert trigger that calls a stored procedure usp_a
I need to pass two parameters to this stored procedure, value coming from the row inserted...
What is the syntax? Any help will be appreciated?
View 1 Replies
View Related
Jan 14, 2005
Hi.
Replication is not an option for me so I am trying to creat a trigger that will basically copy any new record to another database. I am using an on insert trigger and I get all records from the table inserted to the new db not just the new record.
Any ideas?
Thanks.
Bill
View 4 Replies
View Related
Apr 26, 1999
I am trying to write a pre-insert trigger. I want a row to be deleted first and then have the new row inserted. The end result is an insert/update statement. If anyone knows how to do this or has a better way, let me know, Please.
View 3 Replies
View Related
Oct 13, 1998
I am trying to write an insert trigger for the following problem. I have a table that contains a field (amount) that contains currency amount. Each row could be a record from a transaction in a different country. I would like each record to be converted into U.S. currency as it is inserted. So I created an other table that contains that exchange rate. How can I create a trigger that does this?
Tax Detail Table Exchange Table
Account# int Country char
Description char ExchangeRate Money
Amount money
Country char
I am working with MSS 6.5. Any and all help will be greatly appreciated.
Fidencio Peña
View 3 Replies
View Related
Dec 8, 1998
MSSQL 6.5
Is there any way to change some columns on inserted lines in ON INSERT trigger?
My notice: I try to find something about it in SQLBOOKS, but I found nothing.
It's possibly not TRUEEE!!!!! It wants me to grumble.
Thank for your answer.
View 1 Replies
View Related
Nov 28, 2005
Really simple question - I have the following trigger for the INSERT event for Tbl_B:
CREATE TRIGGER calc_total ON [dbo].[Tbl_B]
FOR INSERT
AS
DECLARE @VATRate AS decimal(19,2)
SET @VATRate = (
SELECT Val
FROM Tbl_Numeric_Refs
WHERE Ref = 'VATRate'
)
UPDATE Tbl_B
SET [Total] = ((@VATRate / 100) * [PriceA]) + [PriceA],
[VATRate] = @VATRateHow can I restrict the UPDATE within the trigger to the record(s) being INSERTed? Or am I over-complicating things, and should use a simple UDF for this instead?
View 2 Replies
View Related
Oct 22, 2007
Is it possible to pick up the value that is being inserted within the scope of a trigger? For example, if I were to run the following
INSERT INTO people (firstname, lastname)
VALUES ('George', 'V')
Would it be possible to access these values in a trigger before they are inserted?
CREATE TRIGGER trigger1
ON people
FOR INSERT
AS
IF EXISTS (SELECT 1 FROM table1 WHERE firstname = <the value being inserted>) BEGIN
Print '1'
END
ELSE BEGIN
Print '2'
END
GO
View 4 Replies
View Related
Nov 15, 2007
Rightio - this is probably a simple question for you SQL afficionados.I have written a trigger to update a master table with a CreateDate field. Simple enough I thought but it updates this field when the existing record is edited as well - not so good.This is how it looks like:CREATE TRIGGER CreateDateON MasterFOR UPDATE ASDECLARE@idMaster intSELECT @idMaster = idMaster from Insertedupdate Masterset CreatedDate = getdate()where idMaster = @idMasterGOWell I know I can write an IF statement that will basically say if the field is not null then only update - fair enough - but is there a simpler way to do this? Is there a way I need to write my CREATE TRIGGER statement that ONLY makes it run when it is a NEW INSERT ONLY?THANKS!
View 8 Replies
View Related
Jan 27, 2004
Hi, i'm a newbie in writting trigger and stored procedure, need to get help on this:
I cannnot find anyway to run the trigger before insert, anyway to do this?
Thanks.
View 2 Replies
View Related
Mar 2, 2004
So... dear friends
I want to insert values to a table without firing the foreign key Constraint.
I think one way is to insert values into the parent table first and then perform the initial insert.
Is this possible with an INSTEAD OF INSERT Trigger or another way? If yes can you make an example? I want to check for dublicate values in the parent table too!!
Thanx!
View 6 Replies
View Related
May 4, 2008
I have an EMPLOYEE table and DEPARTMENT table, i have to create one to many relationship where one employee can be in many departments, please guide me
create table employee (e_id INTEGER, e_fname varchar(30), e_lname varchar(30), d_id INTEGER references department(d_id))
create table department (d_id INTEGER, d_name varchar(30))
but here the problem would be while inserting into employee table since i have no primary key... do i need to use trigger to insert the right values.
View 10 Replies
View Related
May 18, 2008
I'm having table as below,
StudentEnroll_Thismonth
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
1 | 0021 | A890 | 4/1/2008
2 | 0021 | A830 | 4/1/2008
3 | 0025 | A890 | 4/1/2008
4 | 0025 | A112 | 4/1/2008
5 | 0026 | A545 | 4/1/2008
.............
.............
99 | 0021 | A900 | 4/30/2008
100 | 0021 | A902 | 4/30/2008
101 | 0025 | A900 | 4/30/2008
*The table above contains what subject taken by student
Let's say, GETDATE()=5/1/2008
If execute Trans-SQL below,
INSERT INTO StudentEnroll_ThisMonth(StudentID,Subject,TransDate) values(0023,'B328',GETDATE())
How the trigger looks like to make sure the data in table shown as follow,
StudentEnroll_Thismonth
TransID(AutoNumber) | StudentID | Subject | TransDate
-----------------------------------------------------------
120 | 0023 | B328 | 5/1/2008
*All the previous month record move to StudentEnroll_PreviousMonth automatically
StudentEnroll_PreviousMonth
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
..................
..................
..................
200 | 0021 | A890 | 4/1/2008
201 | 0021 | A830 | 4/1/2008
203 | 0025 | A890 | 4/1/2008
204 | 0025 | A112 | 4/1/2008
205 | 0026 | A545 | 4/1/2008
.................
.................
301 | 0021 | A900 | 4/30/2008
302 | 0021 | A902 | 4/30/2008
303 | 0025 | A900 | 4/30/2008
Anyone can help me to show the trigger?
View 4 Replies
View Related
Jun 5, 2008
Hi there,
After some head scratching and much research and trying I am unable to figure out how to do the following Insert Trigger. Maybe it shows a flaw in my database design?
I have a user front end where a user can assign a certain job to be done and assign it to one and only one shift and one or more equipments to do this job on.
When user clicks on Assign button it will insert rows into Jobs table via an insert stored procedure. Jobs table has following fields : ShiftID, and a JobEquipmentID since Job can be done on one or more equipment. This field, JobEquipmentID, will refer to a child table JobEquipment. What is the best approach to update this secondary table? I need to update JobEquipmentID but I do not have it until I update JobEquipment table? CRAZY!!! HELP
View 16 Replies
View Related
Jun 10, 2008
I'm new to triggers but basically what I want to do is the following, I have 2 databases name Prod and Dev In the Prod database I have a table call Sales I’m only interested in the Sale_ID field. On the Dev database I have a table call QMD and once more I’m only interested in the Sale_ID field. What I would like to do is as soon as a new record gets created in the Sales table located in the Prod database I would like to grab the Sale_ID and create a new record on the QMD table of the Dev database with the same Sale_ID, and leaving the other field blank of the newly created record.
Thank You
View 3 Replies
View Related
Oct 5, 2007
Hi,
I need to update a createdDt field,
with currentdate only when there is a insert in the table.
Please help me with how to do this.
Thanks
View 4 Replies
View Related
Jan 10, 2008
I have an Insert Trigger on the table
I would like to know what would happen when record gets inserted from GUI(using stored procedure).
1. Record gets inserted into table , then trigger fires and stored procedure sends output to GUI.
2. Record gets inserted stored procedure sends output and then trigger fires.
Thanks
View 2 Replies
View Related
Feb 3, 2008
Dear all,
I have a table named Messages with fields
Id numeric
SmsBody varchar(4000)
mobilenumber varchar(15)
Status char(1).
and also there is another table named MsgHistory with fields
Id numeric
MsgId numeric -- (will be the Id from table Messages)
SmsBody varchar(4000)
Status char(1).
I need to work a trigger if any new message (record) is inserted into table Messages, at the same time the trigger should execute to insert MsgId and SmsBody to table MsgHistory from table Messages.
How I can write trigger for this?
with regards
Shaji
View 1 Replies
View Related
