July 3, 2012 at 3:34 pm
Hi Im trying to see if a file exists and am trying to get the file name from a list of values in a column in my table, I then want to get the file if it doesnt exist but for now just want to print if the files exist or dont exist, here is the script. if I type the @filename value it works but when I enter a table from my db it doesnt, can anyone point me in the right direction.
declare@Paths varchar(128) ,
@FileName varchar(128),
@FileExtension varchar(128)
select@Paths = 'C:\xmls\' ,
@FileName = dbo.table.Product_ID,
@FileExtension = '.xml'
declare@objFSys int
declare @i int
declare@File varchar(1000)
select @File = @Paths + @FileName + @FileExtension
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
if @i = 1
print 'Its There'
else
print 'Doesnt Exist'
exec sp_OADestroy @objFSys
Thanks
John.
July 4, 2012 at 12:16 am
This is incorrect syntax:
select @Paths = 'C:\xmls\' ,
@FileName = dbo.table.Product_ID,
@FileExtension = '.xml'
There is no FROM clause, so basically you are trying to put the string dbo.table.Product_ID into the FileName variable, but since it is not enclosed in single quotes the query will fail.
Try this instead:
SELECT
@Paths = 'C:\xmls\'
,@FileName = Product_ID
,@FileExtension = '.xml'
FROM dbo.table
WHERE Product_ID = 'someValue'
You also need to ensure this query returns only 1 row, otherwise the assignment to the variable will go wrong.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 4, 2012 at 3:02 am
Thanks SSCrazy Eights makes sense knew it wouldnt be that easy 🙂 . however im trying to pull back all values for a column and run through all of them, do I need to make the column a variable then pass that in ?
July 4, 2012 at 3:17 am
In that case you'd need a table variable or a temporary table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 4, 2012 at 3:19 am
I changed the code to do that however it just returns one answer can you spot anything wrong ?
Thanks for you help its much appreciated, this has been driving me crazy 🙂
DECLARE @ProductIds TABLE
(Product_ID int)
INSERT INTO @ProductIds (Product_ID)
SELECT Product_ID
FROM
declare@Paths varchar(128) ,
@FileName varchar(128),
@FileExtension varchar(128)
select@Paths = 'C:\xmls\' ,
@FileName = Product_ID,
@FileExtension = '.xml'
from @ProductIds
declare@objFSys int
declare @i int
declare@File varchar(1000)
select @File = @Paths + @FileName + @FileExtension
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
if @i = 1
print 'Its There'
else
print 'Doesnt Exist'
exec sp_OADestroy @objFSys
July 4, 2012 at 3:22 am
You are selecting an entire resultset into a single string variable.
This means every row is overwriting the previous one. The result you see is the one from the last row.
You probably need a WHILE loop or a cursor to loop over your table variable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 4, 2012 at 3:25 am
Thank you. Ill get reading..
July 4, 2012 at 5:46 am
Got it working thanks for the direction here is the script i used 🙂
DECLARE @Product_ID INT
DECLARE Product CURSOR
FOR SELECT Product_ID
FROM dbo.table
OPEN Product
declare@Paths varchar(128) ,
@FileName varchar(128),
@FileExtension varchar(128)
FETCH NEXT FROM Product
INTO@Product_ID
WHILE @@FETCH_STATUS = 0
BEGIN
select@Paths = 'C:\Data\xmls\' ,
@FileExtension = '.xml',
@FileName = @Product_ID
declare@objFSys int
declare @i int
declare@File varchar(1000)
select @File = @Paths + @FileName + @FileExtension
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
if @i = 1
print 'Allready There ' + CONVERT(VARCHAR(36), @Product_ID)
else
PRINT 'Neet to get ' + CONVERT(VARCHAR(36), @Product_ID) + '.xml From Source.'
FETCH NEXT FROM Product
INTO @Product_ID
exec sp_OADestroy @objFSys
END
CLOSE Product
DEALLOCATE Product
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply