Professional Web Applications Themes

communicate between xp_cmdexec and t-sql - Microsoft SQL / MS SQL Server

i wanna delete some files in sqlserver jobs which files name are feteched from the database through t-sql store procs. ex: select 'pic'+picIdx as filename from PicData how do i manage to do that?...

  1. #1

    Default communicate between xp_cmdexec and t-sql

    i wanna delete some files in sqlserver jobs which files name are feteched
    from the database through t-sql store procs.
    ex:
    select 'pic'+picIdx as filename from PicData
    how do i manage to do that?


    Tommy Guest

  2. #2

    Default Re: communicate between xp_cmdexec and t-sql

    Can you be a bit more specific? Which files are you refering to? Or do you
    want to delete the jobs/job steps?

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Re: communicate between xp_cmdexec and t-sql

    the situation is like this:
    file system:
    \pic\pic01.jpg
    \pic\pic02.jpg
    \pic\pic03.jpg
    \pic\pic04.jpg
    \pic\pic05.jpg
    \pic\pic06.jpg
    database:
    'picDB' table
    column 'picIdx' contains picture ids such as '01','02','03'

    the problem is to delete the files picXX.jpg on the filesystem based on
    t-sql query on picDB table.



    > Can you be a bit more specific? Which files are you refering to? Or do you
    > want to delete the jobs/job steps?
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    Tommy Guest

  4. #4

    Default Re: communicate between xp_cmdexec and t-sql

    You can do this many ways, the most recommended one it to use a client
    application language to delete the files based on a resultset returned from
    the Server. You can also use sp_OA* procedures to access the filesystem
    object directly. Another method is to use xp_cmdShell to access the command
    shell from T-SQL. Using a cursor you can do:

    DECLARE pic CHAR(2),
    SQLx VARCHAR(30)
    DECLARE cur CURSOR FOR
    SELECT picIdx FROM picDB
    OPEN cur
    FETCH NEXT FROM cur INTO pic
    WHILE FETCH_STATUS = 0
    BEGIN
    SET SQLx = 'DEL C:\pic\pic' + pic + '.jpg'
    EXEC master..xp_cmdShell SQLx
    FETCH NEXT FROM cur INTO pic
    END
    CLOSE cur
    DEALLOCATE cur

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  5. #5

    Default Re: communicate between xp_cmdexec and t-sql

    thanks
    > You can do this many ways, the most recommended one it to use a client
    > application language to delete the files based on a resultset returned
    from
    > the Server. You can also use sp_OA* procedures to access the filesystem
    > object directly. Another method is to use xp_cmdShell to access the
    command
    > shell from T-SQL. Using a cursor you can do:
    >
    > DECLARE pic CHAR(2),
    > SQLx VARCHAR(30)
    > DECLARE cur CURSOR FOR
    > SELECT picIdx FROM picDB
    > OPEN cur
    > FETCH NEXT FROM cur INTO pic
    > WHILE FETCH_STATUS = 0
    > BEGIN
    > SET SQLx = 'DEL C:\pic\pic' + pic + '.jpg'
    > EXEC master..xp_cmdShell SQLx
    > FETCH NEXT FROM cur INTO pic
    > END
    > CLOSE cur
    > DEALLOCATE cur
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    Tommy Guest

Similar Threads

  1. Can not get CF7 to Communicate with DB2 v9.1
    By Bob Balas in forum Coldfusion Server Administration
    Replies: 0
    Last Post: December 13th, 04:56 PM
  2. Best way to communicate with MySQL?
    By LucaLT in forum Macromedia Flash Data Integration
    Replies: 4
    Last Post: December 6th, 05:13 AM
  3. MIAW - To communicate
    By Mas Fabien in forum Macromedia Director Basics
    Replies: 0
    Last Post: April 16th, 01:53 PM
  4. Can any web app communicate with another?
    By rooster575 in forum ASP.NET Web Services
    Replies: 2
    Last Post: September 26th, 11:32 AM
  5. Cannot communicate 98 to XP
    By Doug in forum Windows Networking
    Replies: 0
    Last Post: June 30th, 09:17 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