June 18, 2003 at 11:48 am
Hi
I've got a procedure ...
CREATE PROCEDURE [warehouse].[findfile]
@InterfaceFile varchar(255),
@ErrorSave int OUTPUT
AS
--Create temporary table
create table #fileexists (
doesexist smallint,
fileindir smallint,
direxist smallint)
-- Insert into the temporary table
Insert into #fileexists exec master..xp_fileexist @InterfaceFile
--Queries the temporary table to see if the file exists
If exists (select doesexist from #fileexists FE
where FE.doesexist = 1)
SET @ErrorSave = 0
Else
SET @ErrorSave = 1
-- Clean up TempDB
DROP TABLE #fileexists
RETURN @ErrorSave
GO
which is based on the excellent Mr. Knights posting
http://qa.sqlservercentral.com/columnists/bknight/xpfileexist.asp
which I have tested via:
declare @rtnval int
declare @Interface varchar(255)
set @Interface = '\\MDSPFSAPS03\C$\xxxx\xxxx\xxxx\xxxx.csv'
exec warehouse.findfile @Interface, @ErrorSave = @rtnval OUTPUT
if (@rtnval <> 0 )
print 'No File'
else
print 'File Present'
and all is well, but, if I insert:
declare @rtnval int
declare @Interface varchar(255)
set @Interface = '\\MDSPFSAPS03\C$\xxxx\xxxx\xxxx\xxxx.csv'
exec warehouse.findfile @Interface, @ErrorSave = @rtnval OUTPUT
into an 'Execute SQL Task', I get
Error Source : Microsoft OLE DB Provider for SQL Server
Error Description : Invalid Object name '#fileexists'
and I was wondering why?
Rgds
Ryan Wall
June 18, 2003 at 8:55 pm
I am no very sure but I think temp tables are not permitted on the DTS packages.
you can verify it for yourself.
June 19, 2003 at 2:40 am
Hi
it doesn't seem to matter if it is a temporary table or a physical table - parsing the query in the Exec SQL task complains about the table name, wherever it is located ????
any other ideas?
thanks
Ryan
June 19, 2003 at 3:30 am
If you are using the temp table in another DTS object you will need to make it global ie ##fileexists
Steven
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply