Check file availability using cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check file availability using cursor

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-04-21 : 04:09:10

My Table filelocation contains the following values

\\us-liv-ssis\sqlhub\file.txt
\\us-liv-sql3\cus
\\us-liv-dwprod1\file3.txt

I am creating a cursor to check the files availability in each
location one by one .If file is exists in first location it will move
to next for file .And if does not contain the file it should not move
to next record .
I try to create a cursor but it is not working
find the below cursor and please feed your inputs what i am doing wrong
.

DECLARE @FILENAME VARCHAR(MAX)
DECLARE @File_Exists int
DECLARE CURSOR_FILECHECK cursor
FOR SELECT RetValue FROM #MyTempTable

OPEN CURSOR_FILECHECK

FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
WHILE (@@FETCH_STATUS=0)
BEGIN
EXEC Master.dbo.xp_fileexist @FileName,@File_Exists OUT
IF @File_Exists = 1
--PRINT 'File Found'
FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
ELSE
BREAK

--PRINT 'File Not Found'
END
CLOSE CURSOR_FILECHECK
Deallocate CURSOR_FILECHECK

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 05:01:53

CHANGE
DECLARE @FILENAME VARCHAR(MAX)
TO
DECLARE @FILENAME VARCHAR(8000)

I think that after changes it should work. If you face any issue then please let us know.

Important note:Master.dbo.xp_fileexist --> Its an undocumented command. Try to avoid it because you never know when microsoft will remove the command.

Regards,
Bohra.
I am here to learn from Masters and help new bees in learning.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 05:09:14

CREATE TABLE T1
(RETvALUE VARCHAR(255))

INSERT INTO T1
SELECT 'd:\UnderNourScreen.rdl' UNION
SELECT 'd:\NONEXISTFILE.TXT' UNION
SELECT 'd:\f.dmp'

BEGIN
DECLARE @FILENAME VARCHAR(8000)
DECLARE @File_Exists int
DECLARE CURSOR_FILECHECK cursor
FOR SELECT RetValue FROM T1

OPEN CURSOR_FILECHECK

FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
WHILE (@@FETCH_STATUS=0)
BEGIN
EXEC Master.dbo.xp_fileexist @FileName,@File_Exists OUT

IF @File_Exists = 1
begin
PRINT 'File Found'
FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
end
ELSE
BREAK

--PRINT 'File Not Found'
END
CLOSE CURSOR_FILECHECK
Deallocate CURSOR_FILECHECK
END

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-04-21 : 05:56:04

Hi dear,

Thanks again for your help .But i am again stucked somewhere and the problem is if first record is having a file exists then it will move to second otherwise if not available ,it should come out from the while loop .... as you used break statement but it is not coming out from the loop.As second record does not have file still it is reading third record and printing as well and i dont want it .I want to exist from the loop if the file is not availble...

Is there any other option apart from break statement

d:\NorthAmerica.txt
c:d:\data\Asia.txt

quote:
Originally posted by pk_bohra

CREATE TABLE T1
(RETvALUE VARCHAR(255))

INSERT INTO T1
SELECT 'd:\UnderNourScreen.rdl' UNION
SELECT 'd:\NONEXISTFILE.TXT' UNION
SELECT 'd:\f.dmp'

BEGIN
DECLARE @FILENAME VARCHAR(8000)
DECLARE @File_Exists int
DECLARE CURSOR_FILECHECK cursor
FOR SELECT RetValue FROM T1

OPEN CURSOR_FILECHECK

FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
WHILE (@@FETCH_STATUS=0)
BEGIN
EXEC Master.dbo.xp_fileexist @FileName,@File_Exists OUT

IF @File_Exists = 1
begin
PRINT 'File Found'
FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
end
ELSE
BREAK

--PRINT 'File Not Found'
END
CLOSE CURSOR_FILECHECK
Deallocate CURSOR_FILECHECK
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 06:18:48

Maybe you are doing something different?
pk_bohra's code is working.
I have extended the print command so you can see the actual checked filename


CREATE TABLE T1
(RETvALUE VARCHAR(255))


INSERT INTO T1
SELECT 'c:\io.sys' UNION
SELECT 'd:\NONEXISTFILE.TXT' UNION
SELECT 'd:\migration.zip' 

BEGIN
DECLARE @FILENAME VARCHAR(8000)
DECLARE @File_Exists int
DECLARE CURSOR_FILECHECK cursor 
FOR SELECT RetValue FROM T1 

OPEN CURSOR_FILECHECK 

FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
WHILE (@@FETCH_STATUS=0)
BEGIN
EXEC Master.dbo.xp_fileexist @FileName,@File_Exists OUT 

IF @File_Exists = 1
begin
PRINT 'File Found: '+ @FileName
FETCH NEXT FROM CURSOR_FILECHECK INTO @FILENAME
end
ELSE 
begin
print 'not found: '+ @FileName
BREAK
end
--PRINT 'File Not Found' 
END 
CLOSE CURSOR_FILECHECK 
Deallocate CURSOR_FILECHECK
END
drop table t1

No, you're never too old to Yak'n'Roll if you're too young to die.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-04-25 : 06:21:48

HI,
I need your help again .I posted this problem long back but i was not
getting any reply on this .I hope you will again help me out
I want to shrink the databases if they are online and in simple recovery mode i have written a store stored procedure but the problem is when i execute it it says that cursor does not exists..Here is the code

CREATE PROCEDURE SRINKDATABASE
AS

CREATE TABLE #TEMP
( DBNAME VARCHAR(50),
STATUS VARCHAR(20),
UPDATEABILITY VARCHAR(50),
RECOVERY VARCHAR(10))

INSERT INTO #TEMP SELECT A.DBNAME,A.STATUS,A.UPDATEABILITY,A.RECOVERY FROM (
SELECT DBname=name,
Status= CONVERT(VARCHAR(20),DatabasePropertyEx(name,'Status')),
Updateability= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'Updateability')),
UserAccess= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'UserAccess')),
Recovery= CONVERT(VARCHAR(50),DatabasePropertyEx(name,'Recovery')),
Version= CONVERT(VARCHAR(10),DatabasePropertyEx(name,'Version'))
from sysdatabases ) A WHERE A.DBNAME NOT IN ('MASTER','TEMPDB','MODEL','MSDB')

DECLARE @CUR_NM VARCHAR(50)
DECLARE @CUR_STAT VARCHAR(20)
DECLARE @CUR_UPDT VARCHAR(50)
DECLARE @CUR_REC VARCHAR(10)
DECLARE @MSG VARCHAR(100)

DECLARE CHKDATABASE_CUR CURSOR FOR
SELECT DBNAME,STATUS,RECOVERY FROM #TEMP

OPEN CHKDATABASE_CUR

FETCH NEXT FROM CHKDATABASE_CUR INTO @CUR_NM,@CUR_STAT,@CUR_REC
WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@CUR_STAT = 'ONLINE' AND @CUR_REC = 'SIMPLE')
BEGIN
DBCC SHRINKDATABASE (@CUR_NM, TRUNCATEONLY)
SET @MSG = 'NAME OF THE DATABSE SHRINKED IS ' + @CUR_NM
PRINT @MSG
FETCH NEXT FROM CHKDATABASE_CUR INTO @CUR_NM,@CUR_STAT,@CUR_REC
END
CLOSE CHKDATABASE_CUR
Deallocate CHKDATABASE_CUR
END

Please help me to dig it out.Below is the error maessage it is keep giving.

Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'CHKDATABASE_CUR' does not exist.
Msg 16916, Level 16, State 1, Line 25

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-25 : 13:06:11

Set the close and deallocate statements after the last END.

No, you're never too old to Yak'n'Roll if you're too young to die.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-25 : 13:55:19

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143534

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources