textcopy loop
-
I'm using textcopy from a stored procedure to create photo's from a SQL 2k DB.
I call the sp from a second sp which loops through records using a cursor.
The problem is it only produces 22 out of 458 photo's. In debug mode the sp that executes the textcopy stalls on 'EXEC Master..xp_cmdShell @cmd' while the calling sp keeps running and looping through records indefinitely.
I've tried pausing for 5 secs after each loop, nothing changed.
Any ideas would be appreciated.
Thanks
Bruce
-
Can you paste the code here..
-
here it is:
declare @fullname varchar(100),
@runpath varchar(100), -- textCopy Location
@srvr varchar(50), -- server TO LOAD
@db varchar(50), -- DATABASE TO LOAD
@usr varchar(50), -- login USER
@pwd varchar(50), -- login password
@tbl varchar(50), -- TABLE TO load/unload
@col varchar(50), -- COLUMN TO load/unload
@whr varchar(200), -- WHERE clause
@fil varchar(1000), -- filename including path
@mod char(1), -- I FOR LOAD INTO Sql , O FOR output FROM SQL
@n varchar(20)
set @runpath = 'C:\textCopy.exe'
set @srvr = 'server'
set @db = 'database'
set @usr = 'username'
set @pwd = 'password'
set @tbl = 'dbo.tablename'
set @col = 'columnname'
set @whr = ' " WHERE person_ref = ' -- add ref
set @fil = '\\Lincssps\c$\Inetpub\Images_Staff\' -- add file name
set @mod = 'O'
/*
set @whr = @whr + '''0000000059'' " '
--set @fil = @fil + 'AnyName.jpg'
*/
Declare emp_cursor cursor for
SELECT person_ref,(forename1 + ' ' + surname + ' - ' + job) as fullname FROM v_NameLatestPosition
open emp_cursor
fetch next from emp_cursor into @n,@fullname
while @@fetch_status = 0
begin
set @whr = @whr + '''' + @n + ''' " '
set @fil = ' "' + @fil + @fullname + '.jpg" '
exec dbo.coffs_imp_exp_images @runpath,@srvr,@db,@usr,@pwd,@tbl,@col,@whr,@fil,@mod
set @whr = ' " WHERE person_ref = ' -- add ref
set @fil = '\\Lincssps\c$\Inetpub\Images_Staff\' -- add file name
fetch next from emp_cursor into @n,@fullname
end
close emp_cursor
deallocate emp_cursor
GO
coffs_imp_exp_images code just compiles the textcopy command and executes it.
I'm trying to sort the data to see if thats doing it.
Bruce
-
found the problem, it was the data, textcopy.exe must only allow 58 characters in it's file name.
code:
if datalength(@fullname)>58
begin
set @fullname = replace(@fullname,' - ','-')
set @fullname = substring(@fullname,1,58)
print @fullname
end
Thanks... me
-
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply