Professional Web Applications Themes

xp_cmdshell driving me nuts! Experts please help! - Microsoft SQL / MS SQL Server

Hi, I've been struggling with this for days now with limited sucess. Manybe you guys can help out. All this would be effortless in Oracle/Unix but I've been asked to work on SQL Server 2000. So I ask you gurus to help me out. I have a stored proc in which a variable var has multiple lines seperated by new lines. print var gives me the exact result I want. I now want to dump these lines to an operating system file. The problem is echo on DOS does not interpret the new lines characters as a result of whcih ...

  1. #1

    Default xp_cmdshell driving me nuts! Experts please help!

    Hi,

    I've been struggling with this for days now with limited sucess.
    Manybe you guys can help out. All this would be effortless in
    Oracle/Unix but I've been
    asked to work on SQL Server 2000. So I ask you gurus to help me out.

    I have a stored proc in which a variable var has multiple lines
    seperated by new lines. print var gives me the exact result I want. I
    now want to dump
    these lines to an operating system file.

    The problem is echo on DOS does not interpret the new lines
    characters as a result of whcih I get only the first line. I also
    dumped the value into the
    a temp table ##tempt and then selected it using osql command line
    utility.
    This works ok but the formatting is less than ideal because of what
    osql does.
    isqlw on the other hand is another new can of worms because I'd have
    to
    save a binary config file to get results in the right format. Even
    then it
    adds all sorts of blank lines.

    Ideally I want to be able to access teh contents of var somehow
    such that the contents are dumped to an OS file. I've tried all sorts
    of things but I'm still struggling.

    ANy suggestions would help.

    DrD
    Dr Deadpan Guest

  2. #2

    Default Re: xp_cmdshell driving me nuts! Experts please help!


    "Dr Deadpan" <drdeadpan> wrote in message
    news:a944d23e.0307080747.643e6817posting.google.c om...
    > Hi,
    >
    > I've been struggling with this for days now with limited sucess.
    > Manybe you guys can help out. All this would be effortless in
    > Oracle/Unix but I've been
    > asked to work on SQL Server 2000. So I ask you gurus to help me out.
    >
    Yeah xp_cmdshell kind of s.

    Anyway try this. It uses the scripting COM object wrappers for the windows
    filesystem api's.

    You would want something like

    declare var varchar(8000)
    set var = 'line1' + char(13) + char(10) + 'line2'
    exec file_write_text 'c:\foo.txt',var

    David


    create procedure file_write_text(file_name varchar(1000),
    text varchar(8000),
    append bit = 0)
    as
    /*
    exec file_write_text 'c:\foo.txt','hello'
    exec file_write_text 'c:\foo.txt',' world',1
    */

    declare fso int
    declare ts int
    declare rv int

    exec rv = sp_oacreate "scripting.filesystemobject", fso OUTPUT, 1
    if rv <> 0 goto eh
    if append = 1
    begin
    --open the text stream for append, will fail if the file doesn't exist
    exec rv = sp_oamethod fso,"opentextfile", ts OUTPUT, file_name, 8
    if rv <> 0 goto eh
    end
    else
    begin
    --create a new text file, overwriing if necessary
    exec rv = sp_oamethod fso,"createtextfile", ts OUTPUT, file_name, -1
    if rv <> 0 goto eh
    end
    exec rv = sp_oamethod ts,"write",null ,text
    if rv <> 0 goto eh
    exec rv = sp_oamethod ts,"close"
    if rv <> 0 goto eh

    exec sp_oadestroy ts
    exec sp_oadestroy fso

    return 0

    eh:
    declare es varchar(512)
    declare ed varchar(512)
    exec sp_oageterrorinfo null, es OUTPUT, ed OUTPUT
    raiserror(ed,16,1)
    exec sp_oadestroy ts
    exec sp_oadestroy fso
    return 1


    David Browne Guest

  3. #3

    Default Re: xp_cmdshell driving me nuts! Experts please help!

    "David Browne" <davidbaxterbrowne no potted com> wrote in message news:<phx.gbl>... 
    >
    > Yeah xp_cmdshell kind of s.
    >
    > Anyway try this. It uses the scripting COM object wrappers for the windows
    > filesystem api's.[/ref]

    Actually, Dave, I just used the unix version of the echo command and things
    are working great. The DOS echo does not interpret new line characters
    and all I needed to was fix that.

    Thanks for the tip though

    DrD
    Dr Guest

Similar Threads

  1. Please help - this has been driving me nuts!
    By I R Godzilla webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 2
    Last Post: February 3rd, 11:27 PM
  2. preg_replace driving me nuts!
    By Greg Brondo in forum PHP Development
    Replies: 6
    Last Post: January 30th, 05:14 PM
  3. DateTime driving me nuts
    By Simple Simon in forum ASP.NET General
    Replies: 1
    Last Post: July 28th, 11:35 PM
  4. Driving me nuts
    By Joshua Ghiloni in forum PHP Development
    Replies: 1
    Last Post: July 24th, 10:38 AM
  5. format driving me nuts
    By bob in forum ASP.NET General
    Replies: 0
    Last Post: July 15th, 01:41 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