Professional Web Applications Themes

how to script the text file output from stored procedure? - Microsoft SQL / MS SQL Server

background: sql7 on NT servers instead of using dts, is it possible to script the output process from within a stored procedure? (a sql table to a text file somewhere on the network, the text file name will be generated on the fly). can anyone help? thank you steve :)...

  1. #1

    Default how to script the text file output from stored procedure?

    background: sql7 on NT servers

    instead of using dts, is it possible to script the output process from
    within a stored procedure? (a sql table to a text file somewhere on the
    network, the text file name will be generated on the fly).
    can anyone help?
    thank you

    steve :)


    === Guest

  2. #2

    Default Re: how to script the text file output from stored procedure?

    something like this...

    declare sql nvarchar(4000)
    set sql='bcp "exec [db].[dbo].[your_sp]" queryout
    "\\server\shared\abc.txt" -c -T -S'+servername

    exec master..xp_cmdshell sql, no_output

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "=== Steve L ===" <hotmail.com> wrote in message
    news:phx.gbl... 


    oj Guest

  3. #3

    Default Re: how to script the text file output from stored procedure?

    Steve,

    Use OSQL with the -o option in a Windows script or batch file.

    Hope this helps,

    Bill

    "=== Steve L ===" <hotmail.com> wrote in message
    news:phx.gbl... 


    Alter Guest

  4. #4

    Default Re: how to script the text file output from stored procedure?

    Thanks oj,

    Here was what happened. (SQL7 on NT server)

    First, I ran this script in QA

    exec master..xp_cmdshell 'bcp Raintree_rpt.dbo.ClaimHeaders out
    c:\test.txt -c'

    Raintree_rpt is the database name

    ClaimHeaders is the table I want to export

    Text.txt is the target text output file

    The script ran fine, it created a test.txt file on the local sql server's C
    drive. Here is the msg from QA (1445 records were written out)

    output

    ----------------------------------------------------------------------------
    -----------------------------------------------------

    Password:

    NULL

    Starting copy...

    1000 rows successfully bulk-copied to host-file. Total received: 1000

    NULL

    1445 rows copied.

    Network packet size (bytes): 4096

    Clock Time (ms.): total 78 Avg 0 (18525.64 rows per sec.)

    Second, I ran the same script, but changed the target server to a file
    server called cfs-share2 (it's not a sql server)

    Raintree_rpt is the database name

    ClaimHeaders is the table I want to export

    Text.txt is the target text output file

    And the file path is "\\cfs-share2\shared\dchs
    applications\TRIX\Implementation\To_Import\Prevali dation

    exec master..xp_cmdshell 'bcp Raintree_rpt.dbo.ClaimHeaders out
    "\\cfs-share2\shared\dchs
    applications\TRIX\Implementation\To_Import\Prevali dation\Test.txt" -c -Scfs-
    share2'

    It failed and the err is shown here.

    output

    ----------------------------------------------------------------------------
    -----------------------------------------------------

    Password:

    SQLState = 08001, NativeError = 6

    Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server
    not found.

    SQLState = 01000, NativeError = 2

    Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen
    (CreateFile()).

    So what happened here? Does it mean that I can only output a table from a
    SQL server to a SQL server, but not from a SQL server to a file Server?

    Thank you.

    steve

    "oj" <com> wrote in message
    news:#phx.gbl... 
    2000 [/ref]
    the [/ref]
    text 
    > server [/ref][/ref]
    from [/ref]
    > the 
    > >
    > >[/ref]
    >
    >[/ref]


    === Guest

  5. #5

    Default Re: how to script the text file output from stored procedure?

    Steve,

    The problem is that you've mistaken "-S" as the option flag for the
    FileServer.

    Your correct query would be something like below. It will query the data out
    of the local sql server and output it to the fileserver.

    exec master..xp_cmdshell 'bcp Raintree_rpt.dbo.ClaimHeaders out
    "\\cfs-share2\shared\dchs
    applications\TRIX\Implementation\To_Import\Prevali dation\Test.txt" -c

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "=== Steve L ===" <hotmail.com> wrote in message
    news:phx.gbl... 

    -- 
    applications\TRIX\Implementation\To_Import\Prevali dation\Test.txt" -c -Scfs- 
    -- 
    server [/ref]
    to 
    > 2000 [/ref]
    when [/ref]
    able [/ref]
    > the [/ref]
    > text 
    > > server [/ref][/ref]
    Pipes]ConnectionOpen [/ref]
    > from [/ref][/ref]
    on 
    > >
    > >[/ref]
    >
    >[/ref]


    oj Guest

  6. #6

    Default Re: how to script the text file output from stored procedure?

    hi oj

    thanks again for replying. i did what you suggested, but got a different err
    msg this time. i searched the msdn site and couldn't figure out how to fix
    it?

    the script i executed:

    exec master..xp_cmdshell 'bcp Raintree_rpt.dbo.ClaimHeaders out
    "\\cfs-share2\shared\dchs
    applications\TRIX\Implementation\To_Import\Prevali dation\Test.txt" -c'

    and the err msg output:

    output
    ----------------------------------------------------------------------------
    --------------------------------------------------Password:
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file


    "oj" <com> wrote in message
    news:phx.gbl... 
    out [/ref]
    server's 
    >
    > --------------------------------------------------------------------------
    > -- 
    >[/ref]
    applications\TRIX\Implementation\To_Import\Prevali dation\Test.txt" -c -Scfs- 
    >
    > --------------------------------------------------------------------------
    > -- 
    > server [/ref]
    a [/ref][/ref]
    server_name 
    > > 2000 [/ref]
    > when [/ref]
    > able [/ref][/ref]
    for [/ref][/ref]
    a [/ref][/ref]
    SQL [/ref]
    > Pipes]ConnectionOpen [/ref][/ref]
    process [/ref]
    > on 
    > >
    > >[/ref]
    >
    >[/ref]


    === Guest

  7. #7

    Default Re: how to script the text file output from stored procedure?

    Steve,

    It's actually a very good error. <G>

    Simply put, you have no access to the shared or sqlserver cannot see the
    see.

    What do you get with this query?

    exec master..xp_cmdshell 'Dir "\\cfs-share2\shared\dchs
    applications\TRIX\Implementation\To_Import\Prevali dation\*.*" '

    Also, you want to read up on this article about xp_cmdshell. Do pay
    attention to the permission section.

    http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp?frame=t
    rue


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "=== Steve L ===" <hotmail.com> wrote in message
    news:phx.gbl... 
    err 
    -- 
    data-file 



    oj Guest

  8. #8

    Default Re: how to script the text file output from stored procedure?

    dear oj

    problems solved. the err actually caused by the space in the file path. the
    cmdshell commands do not like space or special characters in a file path.

    i can't thank you enough for taking time to help me out with this problem.
    thank you! thank you!!

    steve

    "oj" <com> wrote in message
    news:phx.gbl... 
    http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp?frame=t 
    > err [/ref]
    fix 
    >
    > --------------------------------------------------------------------------
    > -- 
    > data-file 
    >
    >
    >[/ref]


    === Guest

Similar Threads

  1. Gridview Stored Procedure output paramters or return value
    By LehrSJ in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: December 21st, 02:25 PM
  2. Stored Procedure Output using ASP
    By m3ckon in forum ASP Database
    Replies: 1
    Last Post: May 10th, 10:21 PM
  3. Replies: 6
    Last Post: February 13th, 04:40 PM
  4. General Purpose osql Stored Procedure to Write To Text File
    By Steve in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 30th, 01:10 AM
  5. Stored Procedure with Image Output
    By Mark in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 1st, 09:57 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