Post reply

How to put data from sql SERVER to notepad using t-sql

  • June 18, 2015 at 1:28 am

    #299070

    How to put data from sql SERVER to notepad using t-sql

    I want to run select * from t1 and result i want in Notepad saved in some location. Please help me how to do this.

  • June 18, 2015 at 1:43 am

    #1806270

    you can use bcp command in command prompt or place the script in a batch file to do that i.e.:

    bcp "select * from t1" queryout TextFile.txt -t, -c -S .\SQLINSTANCE -d DATABASE -T

  • June 18, 2015 at 1:50 am

    #1806271

    Thanks for the reply....

    But how to run this from SSMS

  • June 18, 2015 at 1:55 am

    #1806273

    rajeshjaiswalraj (6/18/2015)

    Thanks for the reply....

    But how to run this from SSMS

    Have you tried selecting Query / Results To File?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 18, 2015 at 1:59 am

    #1806274

    you can use xp_cmdshell proc to run a command in command prompt.

    EXEC xp_cmdshell 'bcp "SELECT * FROM database.dbo.table1" queryout "C:\Folder\TextFile.txt" -t, -c -S .\SQLEXPRESS -U user -P password'

  • June 18, 2015 at 2:10 am

    #1806279

    Yes i have tried and got result below

    Msg 102 , Level , State 1 , Line 17

    incorrect syntax near 'queryout'.

    I ran below command

    bcp"select * from t1" queryout "e:ew folder\textfile.txt" -t, -c -s SQLONE -d DataAsets -T

    I ran below command also

    bcp"select * from t1" queryout "textfile.txt" -t, -c -s SQLONE -d DataAsets -T

    Msg 102 , Level , State 1 , Line 17

    incorrect syntax near 'queryout'.

  • June 18, 2015 at 2:27 am

    #1806285

    You can't easily do this from T-SQL. T-SQL/SSMS executes inside of the server, not in the file system. There is no BULK EXPORT, mostly because the commands you run are on the server, not on your client. Your client sends them to the server.

    Are you looking for something formatted for notepad? Or a CSV file? Does this need to be interactive (something you do) or automated (happens without you at your machine?)

    You can certainly select all results in the grid (right click) and save this as a text file.

  • June 18, 2015 at 8:15 am

    #1806389

    I am again getting error as

    EXEC xp_cmdshell 'bcp "SELECT * FROM database.dbo.table1" queryout "C:\TextFile.txt" -t, -c -S SONY-VAIO\PROD -U sa -Password@12345 '

    result -

    'bcp' is not recognized as an internal or external command,

    operable program or batch file.

    Please help me on this

  • June 23, 2015 at 10:33 am

    #1807473

    bcp isn't in the path on the server. The server service account (or xp_cmdshell proxy) is calling bcp, but can't find it.

    Get the path on the server to bcp.exe and put the full path in the call.

  • June 23, 2015 at 2:16 pm

    #1807554

    I think Phil Parkin already provided the easiest answer.

    Have you tried selecting Query / Results To File?

    Different Options for Query Results in SQL Server Management Studio[/url]

    Joie Andrew
    "Since 1982"

  • June 23, 2015 at 3:14 pm

    #1807577

    Steve Jones - SSC Editor (6/23/2015)

    bcp isn't in the path on the server. The server service account (or xp_cmdshell proxy) is calling bcp, but can't find it.

    Get the path on the server to bcp.exe and put the full path in the call.

    BCP is normally automatically installed in a file path where SQL Server can find it when SQL Server is installed. Something else is going on here.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply