code_junkie

What is the best method for checking if a file exists from a SQL Server 2005 stored procedure?

Nov 10th, 2011
131
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.34 KB | None | 0 0
  1. using System;
  2. using System.Data;
  3. using System.Data.SqlClient;
  4. using System.Data.SqlTypes;
  5. using Microsoft.SqlServer.Server;
  6. using System.Security.Principal;
  7.  
  8. public partial class StoredProcedures
  9. {
  10. [Microsoft.SqlServer.Server.SqlProcedure]
  11. public static void FileExists(SqlString fileName, out SqlInt32 returnValue)
  12. {
  13. WindowsImpersonationContext originalContext = null;
  14.  
  15. try
  16. {
  17. WindowsIdentity callerIdentity = SqlContext.WindowsIdentity;
  18. originalContext = callerIdentity.Impersonate();
  19.  
  20. if (System.IO.File.Exists(Convert.ToString(fileName)))
  21. {
  22. returnValue = 1;
  23. }
  24. else
  25. {
  26. returnValue = 0;
  27. }
  28. }
  29. catch (Exception)
  30. {
  31. returnValue = -1;
  32. }
  33. finally
  34. {
  35. if (originalContext != null)
  36. {
  37. originalContext.Undo();
  38. }
  39. }
  40. }
  41. }
  42.  
  43. USE master
  44. GO
  45. CREATE ASYMMETRIC KEY FileUtilitiesKey FROM EXECUTABLE FILE = 'J:FileUtilities.dll'
  46. CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY FileUtilitiesKey
  47. GRANT EXTERNAL ACCESS ASSEMBLY TO CLRLogin
  48. ALTER DATABASE database SET TRUSTWORTHY ON;
  49.  
  50. DECLARE @i INT
  51. --EXEC FileExists '\\server\share\folder\file.dat', @i OUT
  52. EXEC FileExists 'j:\file.dat', @i OUT
  53. SELECT @i
Add Comment
Please, Sign In to add comment