Professional Web Applications Themes

General Purpose osql Stored Procedure to Write To Text File - Microsoft SQL / MS SQL Server

I'm trying to write a general purpose stored procedure to produce a text file. Why? Because application role security will allow applications to write to a folder where users on their own are barred. I keep on getting close to wrapping up this SP, and in fact I can make it work if the text to be written is purely numeric. However, I am at my wits end due to inability to get a string variable into the osql code. Here is an example of what does work, except that the text file body is hard-coded in the SP rather ...

  1. #1

    Default General Purpose osql Stored Procedure to Write To Text File

    I'm trying to write a general purpose stored procedure to produce a
    text file. Why? Because application role security will allow
    applications to write to a folder where users on their own are barred.

    I keep on getting close to wrapping up this SP, and in fact I can make
    it work if the text to be written is purely numeric. However, I am at
    my wits end due to inability to get a string variable into the osql
    code.

    Here is an example of what does work, except that the text file body
    is hard-coded in the SP rather than coming from a paramter as needed:

    Alter Procedure WriteToTextFile
    TextBody as varchar(8000),
    FullPath as varchar(1000)
    As
    Declare ExecObj Varchar(8000)
    Declare PrintStatement Varchar(8000)
    Set PrintStatement = 'Print ' + Convert(Varchar,TextBody)
    Set ExecObj =
    'master..xp_cmdshell ''osql /w8000 /o' +
    FullPath +
    ' /d' + 'MyDatabase' + ' /Q"' +
    'Print ' + '''''ShouldBeVariableHere''''' + '" /E'''
    Exec (ExecObj)

    Note the literal "ShouldBeVariableHere" two lines above. It seems
    ridiculous that it is has to be closed off with no less than five
    quote marks on either side, but at least it does work that way.

    Here's how I invoke it:

    EXEC WriteToTextFile 'WhatIWantToWrite', '\\MyFullFolderPath\test.txt'

    The exec above works, except that, of course, "WhatIWantToWrite"
    doesn't go into the text file. When I substitute TextBody for
    '''''ShouldBeVariableHere''''', like this:

    'Print ' + TextBody + '" /E'''

    the SP works but the output text file reads:

    "Msg 128, Level 15, State 1, Server PHILSDCSQL2, Line 1
    The name 'WhatIWantToWrite' is not permitted in this context. Only
    constants, expressions, or variables allowed here. Column names are
    not permitted."

    I've tried all kinds of variations on the quote marks, including
    throwing in Char(39), with no luck. And I have gotten similar quote
    mark problems when trying to run this example:

    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=147d01c18a71%2403307750%2437ef2ecf%40TKMSFT NGXA13

    It's late in the day and this one has got me completely beat. All
    ideas on how to get where I need to be will be VASTLY appreciated.

    Steve Eisenberg Wynnewood, Pennsylvania U.S.A.
    Steve Guest

  2. #2

    Default Re: General Purpose osql Stored Procedure to Write To Text File

    I think this is what you are looking for.

    'print ''''' + textbody + '''''" /E'''

    The quotes needed to be in the osql part of the string, not the cmdshell
    part.


    "Steve Eisenberg" <com> wrote in message
    news:google.com... 
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=147d01c18a71%2403307750%2437ef2ecf%40TKMSFT NGXA13 


    Casey Guest

  3. #3

    Default Re: General Purpose osql Stored Procedure to Write To Text File

    You can simplify things a bit by using ECHO instead of OSQL:

    ALTER proc WriteToTextFile
    TextBody as varchar(8000),
    FullPath as varchar(1000)
    AS
    DECLARE ExecObj nvarchar(4000)
    SET ExecObj = 'ECHO ' +
    TextBody +
    ' > "' + FullPath + '"'
    EXEC master..xp_cmdshell ExecObj, NO_OUTPUT
    GO

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

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

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

    "Steve Eisenberg" <com> wrote in message
    news:google.com... 
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=147d01c18a71%2403307750%2437ef2ecf%40TKMSFT NGXA13 


    Dan Guest

Similar Threads

  1. Source for general purpose background graphics
    By Tim_Horchler@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 3
    Last Post: October 18th, 03:20 PM
  2. General Purpose Transactions
    By T. Onoma in forum Ruby
    Replies: 2
    Last Post: November 14th, 01:28 AM
  3. Stored Procedure Parameters (text)
    By Chopper in forum ASP
    Replies: 10
    Last Post: September 22nd, 10:14 PM
  4. Replies: 1
    Last Post: July 10th, 01:39 PM
  5. New Stored Procedure. Help me to write it.
    By Andrea Moro in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 4th, 06:12 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