October 24, 2006 at 1:03 pm
How do I execute a DTS package in a trigger? I know nothing about SQL and am a 400 Programmer. Any and all help would be appreciated.
October 24, 2006 at 1:04 pm
I'm not sure if it is possible, but if it is, why would you want to hold a transaction open while a DTS package runs? What are you trying to accomplish?
October 24, 2006 at 1:15 pm
I have the trigger set to fire on insert and I want to copy that record up to the 400 for processing.
October 24, 2006 at 1:29 pm
What happens if the 400 is down or there are network problems between the SQL box and the 400? The INSERT transaction will be held open while the DTS package attempts to connect to the 400. The transaction will be holding open locks to the table and the indexes on the table which, on top of using up database resources, will block other users.
Search this forum for posts on linked servers and triggers. You will find some alternative solutions for using the trigger.
October 24, 2006 at 2:44 pm
Is there a way in SQL to use a DNS connection?
October 24, 2006 at 11:44 pm
You can create a linked server connection to the MySQL database and execute queries directly across the linked server.
However, you don't want this in a trigger. Set up a process to move the data across and schedule it to run at set intervals.
October 25, 2006 at 9:11 pm
I need this in real time as it processes manufacturing data and will hold the build process of equipment. we have an sql 2000 server collecting data from PLC machines and we want the server to push the data to our AS400 as it handles the build jobs and FGI. Is there a way within an sql trigger to create a connection to an AS400 using IBM client access and insert a record to a table on the 400? Any help would be greatly appreciated.
October 25, 2006 at 10:53 pm
You should consider using transactional replication.
October 26, 2006 at 6:38 am
How about scheduling a DTS package to handle your transactions. You can set the schedule to kick of hourly or every minute if you have to.
Regards,
Matt
October 26, 2006 at 7:57 am
You can execute the package from a stored procedure and then call that stored procedure from the trigger. (1) There are some internal MS-SQL stored procedures that can create objects and use them they have the name starting with sp_OA... or (2) you can also use the xp_cmdshell sp to exeecute the dtsrun utilitary.
Check this:
(1) http://articles.techrepublic.com.com/5110-10878-5164098.html
(2) http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=317687
However i suggest you to find a asynchronous method so that if for some reason the connection to AS400 is down, the process in MS-SQL would still go on without intrerruptions.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
October 26, 2006 at 12:20 pm
our current trigger is as follows:
CREATE TRIGGER UpdateTable ON dbo.TableScan
AFTER INSERT
AS
EXEC master..xp_cmdshell 'dtsrun /S 10.0.0.0 /N Transfer /E'
When we run the DTS manually, the record is transfered to the AS400. When we insert a record into the TableScan table, nothing happens. Should I be trying to execute the Transfer DTS differently?
October 26, 2006 at 12:23 pm
FYI:
We have successfully executed this command from the DOS prompt. It just has not worked within the trigger.
Any help will be greatly appreciated,
Thanks,
Ted
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply