Professional Web Applications Themes

Extended stored procedure and Cursors - Microsoft SQL / MS SQL Server

the code looks good. is it giving any error? "Richard" <richard.peopleslacdc.org> wrote in message news:062a01c345a3$6b779250$a301280aphx.gbl... > Can you use a extended stored procedure in a cursur? What > I am trying to do is to create a cursor that will use the > extended stored procedure "xp_fixeddrives" to look at the > drive space and e-mail me if the drive space gets below a > certain level. Has anyone ever wrote a script to do that? > > Here is my attempt!! > > use master > go > DECLARE drive varchar(55), > space varchar(55), > message_text varchar(255) > > ...

  1. #1

    Default Re: Extended stored procedure and Cursors

    the code looks good. is it giving any error?

    "Richard" <richard.peopleslacdc.org> wrote in message
    news:062a01c345a3$6b779250$a301280aphx.gbl...
    > Can you use a extended stored procedure in a cursur? What
    > I am trying to do is to create a cursor that will use the
    > extended stored procedure "xp_fixeddrives" to look at the
    > drive space and e-mail me if the drive space gets below a
    > certain level. Has anyone ever wrote a script to do that?
    >
    > Here is my attempt!!
    >
    > use master
    > go
    > DECLARE drive varchar(55),
    > space varchar(55),
    > message_text varchar(255)
    >
    > DECLARE enabled_cursor CURSOR FOR
    > Exec xp_fixeddrives
    >
    > OPEN enabled_cursor
    > FETCH NEXT FROM enabled_cursor into drive, space
    > WHILE FETCH_STATUS = 0
    > BEGIN
    > --FETCH NEXT FROM enabled_cursor into drive and space
    > select message_text = ('Drive ' + drive + ' on
    > Demeter has only' + space + 'left')
    > FETCH NEXT FROM enabled_cursor into drive, space
    >
    > EXEC master..xp_sendmail
    > recipients = 'Richard Peoples',
    > message = message_text,
    > subject = 'Demeter Drive Space'
    > END
    > Go
    > CLOSE enabled_cursor
    > DEALLOCATE enabled_cursor

    Partha Mandayam Guest

  2. #2

    Default Re: Extended stored procedure and Cursors

    One method is to insert the results of the proc into a temp table:

    CREATE TABLE #xp_fixeddrives
    (
    Drive char(1) NOT NULL,
    Space int NOT NULL
    )

    INSERT INTO #xp_fixeddrives
    EXEC xp_fixeddrives

    DECLARE enabled_cursor CURSOR
    LOCAL FAST_FORWARD READ_ONLY FOR
    SELECT * FROM #xp_fixeddrives

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    [url]http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800[/url]
    [url]http://www.sqlserverfaq.com[/url]
    [url]http://www.mssqlserver.com/faq[/url]
    -----------------------

    "Richard" <richard.peopleslacdc.org> wrote in message
    news:062a01c345a3$6b779250$a301280aphx.gbl...
    > Can you use a extended stored procedure in a cursur? What
    > I am trying to do is to create a cursor that will use the
    > extended stored procedure "xp_fixeddrives" to look at the
    > drive space and e-mail me if the drive space gets below a
    > certain level. Has anyone ever wrote a script to do that?
    >
    > Here is my attempt!!
    >
    > use master
    > go
    > DECLARE drive varchar(55),
    > space varchar(55),
    > message_text varchar(255)
    >
    > DECLARE enabled_cursor CURSOR FOR
    > Exec xp_fixeddrives
    >
    > OPEN enabled_cursor
    > FETCH NEXT FROM enabled_cursor into drive, space
    > WHILE FETCH_STATUS = 0
    > BEGIN
    > --FETCH NEXT FROM enabled_cursor into drive and space
    > select message_text = ('Drive ' + drive + ' on
    > Demeter has only' + space + 'left')
    > FETCH NEXT FROM enabled_cursor into drive, space
    >
    > EXEC master..xp_sendmail
    > recipients = 'Richard Peoples',
    > message = message_text,
    > subject = 'Demeter Drive Space'
    > END
    > Go
    > CLOSE enabled_cursor
    > DEALLOCATE enabled_cursor

    Dan Guzman Guest

Similar Threads

  1. Stored Procedures returning cursors fail
    By roybrown in forum Coldfusion Database Access
    Replies: 3
    Last Post: April 1st, 02:31 AM
  2. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  3. help with a stored procedure
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 3rd, 05:35 PM
  4. Extended Stored Proc diff btw 7.0 and 2000
    By Gert E.R. Drapers in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 11:20 PM
  5. Extended Stored Procedures accepting Real Parameters
    By Microfirm in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 09:59 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