Professional Web Applications Themes

Using XP_CMDSHEll with SQL Server Agent - Microsoft SQL / MS SQL Server

HI , First of all My special (("Thanks")) to John Bell and Erland Sommarskog for helping me to "get it right" with xp_cmdshell. I would really appeciate you guys out there who are really doing a wonderful job in helping "lost people" like me. I was wrecking my life over the above mentioned issues. Thank you once again. Now can any one help me with / explain the ste by step procedure to run this procedure... using Sql Server Agent as a job scheduled to run everyday. And also alternatives (vbscript/Jscript...) if any to use "this procedure" without setting up ...

  1. #1

    Default Using XP_CMDSHEll with SQL Server Agent



    HI ,

    First of all My special (("Thanks")) to John Bell and Erland
    Sommarskog for helping me to "get it right" with xp_cmdshell.

    I would really appeciate you guys out there who are really doing a
    wonderful job in helping "lost people" like me.

    I was wrecking my life over the above mentioned issues. Thank you
    once again.

    Now can any one help me with / explain the ste by step procedure
    to run this procedure... using Sql Server Agent

    as a job scheduled to run everyday. And also alternatives
    (vbscript/Jscript...) if any to use "this procedure" without setting up SQL
    Server Agent Job schedule.

    Thanks once again to you John for helping me with the code:-


    Create procedure USP_DeloldFiles path varchar(25),duration int
    as
    --Objective: To delete files older than certain period from a folder
    --Usage example:
    --Exec USP_DelOldFiles 'c:\test',30
    -- which deletes files older than todaydate-30
    --Created by :MAK
    --Created date: Jan 7,2003
    --OS: windows 2000*/
    declare myquery varchar(1000)
    declare query varchar(1000)
    declare name varchar(100)
    set myquery = 'exec master.dbo.xp_cmdshell ''dir '+ ltrim(rtrim(path)) +
    '\*.* /a/od'''
    print query
    print 'Iam first'
    create table #Filenames (id int identity(1,1) ,name varchar(100))

    insert #Filenames(name)
    exec (myquery)
    delete from #Filenames
    where substring(name,3,1) <> '/'
    or name is null
    or substring(name,25,1) ='<'

    select name, SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2)
    from #Filenames

    /* Make sure dates are in comparable formats */
    Declare mycursor cursor for
    select name from #Filenames
    where SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2) <=
    CONVERT(char(8),DATEADD(d,duration,getdate()),112 )

    open mycursor

    fetch next from mycursor into name
    while fetch_status = 0
    begin
    set query = 'exec master.dbo.xp_cmdshell ''del '+ path + '\'+
    ltrim(rtrim(substring(name,40,59)))+''''
    print query
    exec (query)
    fetch next from mycursor into name
    end
    close mycursor
    deallocate mycursor

    drop table #Filenames

    Thank you beautiful people for your thoughts.
    Raj




    raj Guest

  2. #2

    Default Re: Using XP_CMDSHEll with SQL Server Agent

    Hi

    Check out the following section and subsections. It will take you through
    each step.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
    _automate_7awj.asp?frame=true

    John

    "raj" <com> wrote in message
    news:#phx.gbl... 

    you 
    procedure 
    SQL 



    John Guest

Similar Threads

  1. ODBC Agent & Server Services
    By CoffeeCup in forum Coldfusion Server Administration
    Replies: 1
    Last Post: November 3rd, 05:49 AM
  2. CFUSION ODBC Agent & Server
    By giacomo in forum Coldfusion Server Administration
    Replies: 0
    Last Post: May 13th, 10:39 AM
  3. Coldfusion MX ODBC Server/Agent
    By tomrippity02 in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 8th, 03:51 PM
  4. ODBC Server/Agent problems
    By LordSkitch in forum Coldfusion Server Administration
    Replies: 0
    Last Post: March 17th, 08:34 PM
  5. DTS package failed on sql server agent
    By yanping in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 9th, 10:13 AM

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