I have a difficulty in this piece of code inside a stored procedure.
SET NOCOUNT ON
select @DirectoryCommand = 'dir "D:\Indexed\" /B/S/AA'
if exists (select * from SYSOBJECTS where id = OBJECT_ID(N'IndexDocsTemp')
and OBJECTPROPERTY(id, N'IsUserTable')=1)
drop table IndexDocsTemp
CREATE TABLE IndexDocsTemp(PhyPath nvarchar(512))
CREATE INDEX IX_PhyPathTemp on IndexBrkDocsTemp(PhyPath) on [primary]
insert into IndexDocsTemp(PhyPath)
EXEC master..xp_cmdshell @DirectoryCommand
delete from IndexDocsTemp where IsNull(RTrim(LTrim(PhyPath)), '') = '' or IsNull(RTrim(LTrim(PhyPath)), '') = 'File Not Found' or
IsNull(RTrim(LTrim(PhyPath)), '') = 'The system cannot find the file specified.'
select * from IndexBrkDocsTemp
drop table IndexBrkDocsTemp
What I do is I create a temporary table, populate it with files at a physical location, return the rows of the table as a result set.
I when I execute the stored procedure, I could see the results, but a COM function which uses the stored procedure to get the result set is not able to get any result set.
Can some one tell me what could be the issue.
Thanks in advance,