sqlserver 2005 linked server to folder get filenames

Fraser_Admin
Fraser_Admin used Ask the Experts™
on
in sqlserver 2005 i have a linked server which refers to a folder on the local hard drive.  how can i traverse that folder so that i can capture the file names which are in it for processing.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior SAP CRM Consultant
Commented:
there might well be a better way to do it, but this works...

create table #mytmp
(output varchar(4000))
go
insert #mytmp
exec xp_cmdshell 'dir c:\files\*.txt /B'
select * from #mytmp where [output] is not null

Author

Commented:
But it won't let me do this???

Set @sql = 'SELECT Trim(BOL), GrossLbs, TareLbs, Date, [%Dry] FROM ' + @myFileName
SELECT *
   FROM OPENQUERY (LincolnJU,@sql)


Most Valuable Expert 2015
Commented:
I think you'll need to use dynamic sql for that

SET @sql = 'SELECT * FROM OPENQUERY (LincolnJU,''SELECT Trim(BOL), GrossLbs, TareLbs, Date, [%Dry] FROM ' + @myFileName +''')'
EXEC (@sql)

Author

Commented:
thanks that did it

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial