can this script be made quicker/easier
-
Declare @result int
Declare @cmd varchar(8000)
Declare @FullPath varchar(7900)
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PeterExists')
Drop Table PeterExists
Create Table PeterExists ("FullPath" varchar(8000), "Exists" bit)
IF EXISTS(SELECT cursor_name FROM master..syscursors WHERE cursor_name = 'cursor_FullPath')
DEALLOCATE cursor_FullPath
Declare cursor_FullPath CURSOR STATIC FOR
Select Distinct FullPath from PeterDoc Order by FullPath FOR READ ONLY
OPEN cursor_FullPath
FETCH NEXT from cursor_FullPath INTO @FullPath
WHILE @@FETCH_STATUS = 0
BEGIN
Set @cmd = 'IF EXIST "'+Rtrim(@FullPath)+'" echo %ERRORLEVEL%'
EXEC @result = master..xp_cmdshell @cmd, no_output
IF (@result = 0)
Insert Into PeterExists ("FullPath","Exists") Values (Rtrim(@FullPath),1)
ELSE
Insert Into PeterExists ("FullPath","Exists") Values (Rtrim(@FullPath),0)
FETCH NEXT FROM cursor_FullPath INTO @FullPath
END
DEALLOCATE cursor_FullPath
Select "Exists",Count(*) As 'Count' from PeterExists Group by "Exists"
-
Not sure if it can because the xp_cmdshell doesn't take a set as an argument.
Steve Jones
-
ah bummer
-
From the look of your code, basically all you are doing is checking for the existence of a directory.
Why not use xp_fileexist instead of shelling out to a command prompt?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
yeah thats what it is doing
-
i have never used that command, could you give me an example of how i could work it in?
-
Take a look at the following,
http://www.sqlservercentral.com/columnists/bknight/xpfileexist.asp
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
ok, thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply