wait inside a proc for job to finish

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 2008 Forums

Transact-SQL (2008)

wait inside a proc for job to finish

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-01-12 : 05:51:23

I have a procedure which calls a job to do an FTP, it then inserts the data into a table. I need the insert to wait for the FTP to finish. I know I can use WAITFOR DELAY but I'd rather wait until job is complete. Is this possible?
Here is my proc:

ALTER PROCEDURE [dbo].[INS_FromFile]
AS
DECLARE @err int
--delete earlier file before FTP job runs
EXEC master..xp_cmdshell 'del C:\test\fileA.csv'
--First run FTP job to get latest file
EXEC msdb.dbo.sp_start_job N'FTPgetFileA'
-- then import file into table
SET @err =@@ERROR
IF @err != 0 GOTO HANDLE_ERROR
set dateformat dmy
declare @StrFile varchar(max)
set @StrFile ='C:\test\fileA.csv'

--*******************************************************
--This is where I need to check if FTP job finished
--WAITFOR DELAY '000:05:00'
--*******************************************************

BEGIN TRAN
declare @sql varchar (max)
set @sql ='
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ''TableA'') DROP TABLE TableA
CREATE TABLE TableA
(ID int, Code varchar (50), DtTimeStamp datetime, Units varchar (20), Value decimal (18,2)
)

BULK
INSERT TableA
FROM ''' + @StrFile + '''
WITH
(
FIRSTROW =2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n''
)
--I for invoice
INSERT INTO TableB (ID,DTTimeStamp,Value)
select ID,DTTimeStamp,Value from TableA'
--Print @sql
Exec (@sql)
IF @err != 0 GOTO HANDLE_ERROR

COMMIT TRAN

HANDLE_ERROR:
RETURN @err

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-12 : 06:10:02

Make all steps as jobsteps in one job and the problem is gone.

No, you're never too old to Yak'n'Roll if you're too young to die.

lappin
Posting Yak Master

182 Posts

Posted - 2011-01-12 : 06:47:05

I followed your advice and split the proc into stages of a job then I call job inside a proc, e.g: EXEC msdb.dbo.sp_start_job N'FTPgetFileA'
This works great but - now I cannot return to my webpage how many rows were imported in the last stage of the job. Is this possible?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-12 : 11:28:02

Just execute a "SELECT COUNT(*) as NumRows FROM TableA" from your webpage?

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources