DECLARE @result INT
EXEC master.dbo.xp_fileexist '\\sharebear\client_project_folders\453064052\Allocations\Rony.txt', @result OUTPUT
Select @result
USE [TPAManager_be]
GO
/****** Object: UserDefinedFunction [dbo].[fc_FileExists] Script Date: 1/30/2017 05:40:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fc_FileExists](@path varchar(8000))
RETURNS BIT
AS
BEGIN
DECLARE @result INT
EXEC master.dbo.xp_fileexist @path, @result OUTPUT
RETURN cast(@result as bit)
END;
-----------------------CURSOR---ForEach Loop------------
DECLARE cur_savings CURSOR FOR
Select FilePathID,ProjectID,FilePath from ProjectFile where AccountID=453064052 and ProjectID=50752 and WebAccess=1
DECLARE @ProjectID int,@AccountID int,@strSql VARCHAR(4000),@Path varchar(200),@cmd varchar(1000)
OPEN cur_savings
FETCH NEXT FROM cur_savings INTO @AccountID,@ProjectID,@Path
WHILE @@FETCH_STATUS = 0
BEGIN
--create table #File(AccountID Int,ProjectID Int,FileExist Bit)
insert #File
Select @AccountID,@ProjectID,dbo.[fc_FileExists] (@Path)
FETCH NEXT FROM cur_savings INTO @AccountID,@ProjectID,@Path
END
CLOSE cur_savings
DEALLOCATE cur_savings
GO
Select * from #File where FileExist=0
EXEC master.dbo.xp_fileexist '\\sharebear\client_project_folders\453064052\Allocations\Rony.txt', @result OUTPUT
Select @result
USE [TPAManager_be]
GO
/****** Object: UserDefinedFunction [dbo].[fc_FileExists] Script Date: 1/30/2017 05:40:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fc_FileExists](@path varchar(8000))
RETURNS BIT
AS
BEGIN
DECLARE @result INT
EXEC master.dbo.xp_fileexist @path, @result OUTPUT
RETURN cast(@result as bit)
END;
-----------------------CURSOR---ForEach Loop------------
DECLARE cur_savings CURSOR FOR
Select FilePathID,ProjectID,FilePath from ProjectFile where AccountID=453064052 and ProjectID=50752 and WebAccess=1
DECLARE @ProjectID int,@AccountID int,@strSql VARCHAR(4000),@Path varchar(200),@cmd varchar(1000)
OPEN cur_savings
FETCH NEXT FROM cur_savings INTO @AccountID,@ProjectID,@Path
WHILE @@FETCH_STATUS = 0
BEGIN
--create table #File(AccountID Int,ProjectID Int,FileExist Bit)
insert #File
Select @AccountID,@ProjectID,dbo.[fc_FileExists] (@Path)
FETCH NEXT FROM cur_savings INTO @AccountID,@ProjectID,@Path
END
CLOSE cur_savings
DEALLOCATE cur_savings
GO
Select * from #File where FileExist=0
No comments:
Post a Comment