SQL Job hanging - SQL Server Forums

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

SQL Server Administration (2008)

SQL Job hanging

Author

Topic

terrybogard4
Starting Member

1 Post

Posted - 2012-08-22 : 13:18:49

I have the following stored procedure:

CREATE PROCEDURE [dbo].[PName]

AS

declare @nmbr int, @counter int,@firstrow char, @destTable varchar(75), @importFile varchar(500),@filedir varchar(500)

DECLARE @filename nvarchar(500)

DECLARE @SIZE int,@error int

DECLARE @fileobj INT , @fsobj INT,@exists int

DECLARE @src VARCHAR(255), @desc VARCHAR(255)

DECLARE @CMD VARCHAR(8000)

set @filedir = 'C:\'

set @counter =0

set @nmbr = (select count (*) from table1)

while @counter <@nmbr

begin

set @counter = @counter +1

select @destTable=destTable, @importFile=importfile, @firstrow=firstrow from table_config where id = @counter

set @filename = @filedir + '\' + @importFile

EXEC sp_OACreate 'Scripting.FilesystemObject', @fsobj

OUT

IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fsobj, @src OUT, @desc
OUT

SELECT error=CONVERT(varbinary(4),@error), Source=@src, Description=@desc

RETURN
END

exec @error = sp_OAMethod @fsobj, 'FileExists', @exists OUT,@filename

if @exists <>0
begin

exec sp_OAMethod @fsobj, 'GetFile' , @fileobj OUTPUT,@filename

EXEC @SIZE = sp_OAGetProperty @fileobj, 'Size', @Size OUT

EXEC @error = sp_OADestroy @fileobj

EXEC @error = sp_OADestroy @fsobj

if @SIZE >'0'

begin

SET @CMD = 'TRUNCATE TABLE ' + @destTable

EXEC (@CMD)?

SET @CMD = 'BULK INSERT ' + @destTable + ' FROM ''' + @FileName + ''' WITH (FIRSTROW = ' + @firstrow + ', FIELDTERMINATOR = ''|'',ROWTERMINATOR = '''+CHAR(10)+''' )'

EXEC (@CMD)

end

end

end

GO

?
?The stored procedure runs fine when I execute it using:

??exec 'PName'

??But when I call it from a SQL Job in SQL Server Agent, the job step hangs. What am I doing wrong?

?

?Terry

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-22 : 14:12:27

Does the account running the job have the same permissions?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources