I’m trying to run a select IF theres something inside a folder:
INSERT INTO #tb (SubDirectory, Depth, (File)) EXEC master..xp_dirtree 'C:folder', 10, 1
This will populate a table with the name of the file ( if theres one inside the folder)
then, if there’s an excel file there ( or not) I will run this query:
IF (SELECT COUNT(1) FROM #listavarejo) > 0 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0','Excel.0;Database=C:folderexcelfile.xlsx', (Planilha1$)) ELSE RAISERROR('NO FILES INSIDE FOLDER.',16,10);
The problem is, when I try to run this:
IF (SELECT COUNT(1) FROM #listavarejo) > 0 RAISERROR('there are file inside the folder.',16,10); ELSE RAISERROR('theres no files inside the folder.',16,10);
it works perfectly. if theres a file inside the folder, the error “theres is a file inside the folder”, if theres no files, I can receive the error message after else.
but with the select * from openrowset, even with a file inside the folder or not, the query try to runs the select, and obviously I receive the error that the openrowset couldnt find the file.
even using BEGIN/END I cant fix this.