Check for file exists or not in sql server?

by

Create a function like so:

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

Edit your table and add a computed column (IsExists BIT). Set the expression to:

dbo.fn_FileExists(filepath)

Then just select:

SELECT * FROM dbo.MyTable where IsExists = 1

Update:

To use the function outside a computed column:

select id, filename, dbo.fn_FileExists(filename) as IsExists
from dbo.MyTable

Update:

If the function returns 0 for a known file, then there is likely a permissions issue. Make sure the SQL Server’s account has sufficient permissions to access the folder and files. Read-only should be enough.

And YES, by default, the ‘NETWORK SERVICE’ account will not have sufficient right into most folders. Right click on the folder in question and select ‘Properties’, then click on the ‘Security’ tab. Click ‘Edit’ and add ‘Network Service’. Click ‘Apply’ and retest.

More Related Contents:

  1. How to delete 0 values but not blanks from a column in SQL Server 2008 programming?
  2. Unpivot with column name
  3. How can I delete using INNER JOIN with SQL Server?
  4. Is there a way to access the “previous row” value in a SELECT statement?
  5. What this query does to create comma delimited list SQL Server?
  6. SQL Server – Return value after INSERT
  7. SQL NVARCHAR and VARCHAR Limits
  8. Convert varchar into datetime in SQL Server
  9. sql query to return differences between two tables
  10. Check if a row exists, otherwise insert
  11. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  12. SQL Server : dynamic pivot over 5 columns
  13. How to select the last record of a table in SQL?
  14. How to create table using select query in SQL Server?
  15. Group DateTime into 5,15,30 and 60 minute intervals
  16. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  17. SQL SERVER: Get total days between two dates
  18. Store multiple bit values in a single table column
  19. Which SQL query is faster? Filter on Join criteria or Where clause?
  20. SQL Server equivalent to MySQL enum data type?
  21. UPDATE and REPLACE part of a string
  22. Improve SQL Server query performance on large tables
  23. Using IF ELSE statement based on Count to execute different Insert statements
  24. Select SQL Server database size
  25. Backup a single table with its data from a database in sql server 2008
  26. COALESCE Function in TSQL
  27. Cannot Resolve Collation Conflict
  28. Try_Convert for SQL Server 2008 R2
  29. Display next event date
  30. Sql query for tree table

Leave a Comment