Trigger firing multiple stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger firing multiple stored procedure

Author

Topic

afaa
Starting Member

16 Posts

Posted - 2008-06-11 : 12:29:16

DESCRIPTION: I have an FTP server set up to log via ODBC into a table FTPLog. The trigger on table FTPLog fires when new files are received to process and load the file via a stored procedure.

CREATE TRIGGER tr_new_file ON FTPLog
AFTER INSERT
AS
SET NOCOUNT ON
DECLARE @filename varchar(50), @logtime datetime
DECLARE c1 CURSOR
FOR SELECT filename, logtime
FROM inserted
OPEN c1
FETCH NEXT FROM c1
INTO @filename, @logtime
WHILE @@fetch_status = 0
BEGIN
EXEC sp1 @filename, @logtime
FETCH NEXT FROM c_inserted
INTO @filename, @logtime
END
CLOSE c1
DEALLOCATE c1
END

PROBLEM: There are multiple problems with this setup. The first problem is that when the stored procedure gets executed it takes a long time to process the file and the FTP server never returned a completion code to the ftp client and ended with a connection time out from the client. My users keep asking if the FTP failed but it didnt fail. The server returned a completion code too late.

PROBLEM2: When multiple files are ftp to the server on the same session, only the first one gets process. Even though my code loops through all the records because the processing takes a long time the second one never gets executed. If I replace the EXEC sp1 statement with a PRINT statement then it's working fine.

SOLUTIONS and SUGGESTIONS highly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 12:33:27

Can you try replacing cursor with a while loop ?

CREATE TRIGGER tr_new_file ON FTPLog
AFTER INSERT
AS
SET NOCOUNT ON
DECLARE @ID int,@filename varchar(50), @logtime datetime

SELECT @ID =MIN(PKCol)
FROM inserted

WHILE @ID IS NOT NULL
BEGIN

SELECT @filename=filename, @logtime=logtime
FROM inserted
WHERE PKCol=@ID

EXEC sp1 @filename, @logtime

SELECT @ID =MIN(PKCol)
FROM inserted
WHERE PK > @ID
END

PK is primary key of your table

afaa
Starting Member

16 Posts

Posted - 2008-06-11 : 13:12:05

That did not help. I still get the same problems. Only the first file gets processed. Is there a way to execute the store procedure and tell it not to wait for return code and just move on?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 13:40:55

quote:
Originally posted by visakh16

Can you try replacing cursor with a while loop ?

visakh16, could you explain why a while loop would have any benefit here?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-06-11 : 13:41:23

What does the stored procedure do?

e4 d5 xd5 Nf6

afaa
Starting Member

16 Posts

Posted - 2008-06-11 : 14:23:20

I think the initial thinking of a while loop vs a cursor is the cursor being slow in performance causes the issue. But the issue here is actually the stored procedure taking a long time to run.

The store procedure reads the text file that just got FTPed to the server and upsert the data to the tables. The text file can be as large as half million recrods therefore taking a long time.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 14:25:38

quote:
Originally posted by afaa

I think the initial thinking of a while loop vs a cursor is the cursor being slow in performance causes the issue.

A while loop does not help performance over a cursor.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

afaa
Starting Member

16 Posts

Posted - 2008-06-11 : 14:48:43

I ended up storing the sql string EXEC sp1 @filename, @logtime in a table TB1. Then create a schedule job to read the sql string and execute it from the sql agent and then truncate the table TB1. This created two extra steps which I didnt want. I'll have to live with it for now until I get better alternative.

Please keep the suggestions coming.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-11 : 15:01:20

I guess there wont be any performance benifits of using while instead of Cursor other then
cursor would be an server side object and would require additional memory for execution.

I Guess it would be better if you can pass the CSV to your stored procedure and work around with that in your sp, there are plenty of example you would get for making it work through CSVs and then applying set based methods.

Chirag

http://www.chirikworld.com

afaa
Starting Member

16 Posts

Posted - 2008-06-11 : 15:07:15

quote:
[i]
I Guess it would be better if you can pass the CSV to your stored procedure and work around with that in your sp, there are plenty of example you would get for making it work through CSVs and then applying set based methods.

This still won't solve the problem with the trigger waiting for the executing stored procedure to complete with a return code. Therefore, multiple files won't get executed and the ftp client still won't get a return code. As long as the code inside the trigger expects a return code from the executing stored procedure, I will have this problem.

CTDBA
Starting Member

9 Posts

Posted - 2008-06-11 : 15:47:34

Have you tried creating an SSIS task for this? You might need to create a custom file watcher task (http://www.sqlis.com/23.aspx), but this seems like a classic ETL task.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-06-11 : 16:44:23

quote:
Originally posted by afaa

The store procedure reads the text file that just got FTPed to the server and upsert the data to the tables. The text file can be as large as half million recrods therefore taking a long time.

You should NOT be doing this in a trigger. Load the data into a staging table and then process it from there.

e4 d5 xd5 Nf6

afaa
Starting Member

16 Posts

Posted - 2008-06-12 : 11:32:41

Actually I have a DTS package that does the actual data load into a staging table. The trigger is just a mechanism to tell when to execute the DTS package. The stored procedure is a DTS launcher wrapper that feeds all the parameters to the DTS. The reason the trigger is there is because I have many sources of data coming in from FTP from uncontrolled sources. So I don't know when and who is sending the file. The trigger is useful in this case to launch the SP and the DTS package as soon as the file is received.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-06-12 : 12:09:48

The scope of a trigger should be limited to its table if possible, but never beyond its database. Using a trigger to fire a DTS package is not a good idea.
You would be better off running a scheduled job to detect whether new files have arrived, or have whatever process transfers the files kick off your SP.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources