Professional Web Applications Themes

use extended procedure without cursors - Microsoft SQL / MS SQL Server

xp_fileexist only takes one filename as parameter, so you need a cursor for that. If all you files are in one directory you can use something like: CREATE TABLE #files (dir_result VARCHAR (260) ) INSERT INTO #files (dir_result) EXEC xp_cmdshell 'dir ......' UPDATE Doents SET on_disk = 1 WHERE file_name IN (SELECT RTRIM(SUBSTRING(dir_result, 45, 215) "Tom Holmes Jr." <com> wrote in message news:phx.gbl...  to ...

  1. #1

    Default Re: use extended procedure without cursors

    xp_fileexist only takes one filename as parameter, so you need a cursor for
    that.
    If all you files are in one directory you can use something like:

    CREATE TABLE #files (dir_result VARCHAR (260) )

    INSERT INTO #files (dir_result)
    EXEC xp_cmdshell 'dir ......'

    UPDATE Doents SET on_disk = 1
    WHERE file_name IN (SELECT RTRIM(SUBSTRING(dir_result, 45, 215)




    "Tom Holmes Jr." <com> wrote in message
    news:phx.gbl... 
    to 


    Jacco Guest

  2. #2

    Default Re: use extended procedure without cursors

    Why use a cursor?

    Write a UDF like this

    create function fn_fileexist(fqfn nvarchar(4000))
    returns int
    as
    begin
    declare exist int,
    rc int

    exec rc = master.dbo.xp_fileexist fqfn, exist output

    return exist
    end
    go

    create table filelist
    (
    fqfn nvarchar(4000) null,
    exist int null default -1
    )
    go

    And use it inside the UPDATE statement like this:
    update filelist
    set exist = dbo.fn_fileexist(fqfn)

    Net

    Please reply only to the newsgroups.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    You assume all risk for your use.
    Copyright SQLDev.Net 1991-2003 All rights reserved.

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    for 
    > to [/ref]
    my 
    >
    >[/ref]


    Gert Guest

  3. #3

    Default Re: use extended procedure without cursors

    Nice solution Gert.

    Not that in my experience UDF's work that much faster than cursors anyway,
    so I was more looking for a set based solution, which you can get with dir
    or xp_dirtree.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Gert E.R. Drapers" <Net> wrote in message
    news:#W$phx.gbl... 
    rights. 
    > for [/ref][/ref]
    defaulted [/ref]
    > my [/ref][/ref]
    the [/ref][/ref]
    every 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

  4. #4

    Default Re: use extended procedure without cursors

    I tried the function part and ooops, it looks like you can't do that in MS
    SQL 7.0 which is what I am using? So it looks like I might have to use a
    cursor or temp table in order to accomplish my goals.

    I haven't created many cursors in my time, so any help on creating a cursor
    to solve my needs woudl be much appreciated.

    Thanks.
    Tom

    "Gert E.R. Drapers" <Net> wrote in message
    news:#W$phx.gbl... 
    rights. 
    > for [/ref][/ref]
    defaulted [/ref]
    > my [/ref][/ref]
    the [/ref][/ref]
    every 
    > >
    > >[/ref]
    >
    >[/ref]


    Tom Guest

  5. #5

    Default Re: use extended procedure without cursors

    Correct SQL 7.0 does not have UDF's, only SQL 2000.
    For creating a cursor please read books online, it has enough comprehensive
    examples.

    Net

    Please reply only to the newsgroups.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    You assume all risk for your use.
    Copyright SQLDev.Net 1991-2003 All rights reserved.

    "Tom Holmes Jr." <com> wrote in message
    news:%phx.gbl... 
    cursor 
    > rights. [/ref][/ref]
    cursor [/ref]
    > defaulted [/ref][/ref]
    update [/ref]
    > the [/ref]
    > every 
    > >
    > >[/ref]
    >
    >[/ref]


    Gert Guest

  6. #6

    Default Re: use extended procedure without cursors

    Hi Tom,

    Nope, user defined functions are a feature that was only introduced in SQL
    Server 2000.

    Try the following for a cursor:

    DECLARE filename VARCHAR(260)
    DECLARE exists INT
    DECLARE CURSOR c LOCAL FASTFORWARD FOR
    SELECT [filename] FROM DOENTS
    OPEN c
    WHILE 1=1
    BEGIN
    FETCH NEXT FROM c INTO filename
    IF FECTCH_STATUS <> 0 BREAK -- End of rows
    EXEC master.dbo.xp_fileexist filename, exist output
    UPDATE Doents SET on_disk = exists WHERE [filename] = filename
    END
    CLOSE c
    DEALLOCATE c

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Tom Holmes Jr." <com> wrote in message
    news:#phx.gbl... 
    cursor 
    > rights. [/ref][/ref]
    cursor [/ref]
    > defaulted [/ref][/ref]
    update [/ref]
    > the [/ref]
    > every 
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

  7. #7

    Default Re: use extended procedure without cursors

    Thanks very much for all the help!!!!

    I did take Gert Drapers advice and looked up the help within MS SQL 7.0 and
    found an example of how to use cursors. So, I followed the directions and
    added piece-by-piece and kept checking to make sure the changes were valid
    SQL. I finally came up with a procedure that looks something like yours.

    My cursor was a little slow, understandably so. But I think I make it go a
    little faster if I add some of the things you did like 'LOCAL FASTFORWARD'.

    So, I got something that works based on the help I got from you and Gert. I
    could have done something like this in Java or VB, but then I would have had
    to deploy code to production machines which is something I don't have access
    to do. But, since the database is backed up and I can just run with this
    procedure, I can't do that much harm.

    Thanks again!!!!!!!!

    Tom

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... [/ref]
    MS [/ref]

    > cursor 
    > > rights. [/ref]
    > cursor 
    > > defaulted [/ref]
    > update [/ref][/ref]
    against [/ref][/ref]
    (filename) 
    > > every 
    > >
    > >[/ref]
    >
    >[/ref]


    Tom Guest

Similar Threads

  1. Cursors
    By flashster in forum Macromedia Director Lingo
    Replies: 8
    Last Post: December 12th, 01:47 PM
  2. cursors problem
    By sylmy webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 1
    Last Post: October 22nd, 07:05 PM
  3. Extended stored procedure and Cursors
    By Partha Mandayam in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 02:34 AM
  4. Are Cursors in Triggers really bad?
    By George Wynne in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 1st, 03:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139