Post reply

How spool the out put to a file

  • July 30, 2009 at 12:02 pm

    #140343

    Hi,

    I want to have the out put of the below query in a file. How to do this? Not the results out to a file option in management studio. I already used this but I'm getting "(1 row(s) affected)". But I want like sps is updated to sql and ewr is updated to sms.

    Something similar to the spool option ORACLE.

    UPDATE [Mydb].[dbo].

    SET [abccol] = 'sps' where abccol = 'sql'

    UPDATE [Mydb].[dbo].

    SET [abccol] = 'ewr' where ssgDesc = 'sms'

    thanks

  • July 30, 2009 at 12:12 pm

    #1032588

    your only option is SSMS's output results to file.

    SQL server does not have a native TSQL command to output to a file; the expectation is the presentation layer would do that for you. the issue is code executed on the server would not know where your C:\ drive is for example, so the data has to be handled by something on your work station.

    you can use bcp out to send results to a file, but that is simply an external program that can be called;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 30, 2009 at 12:27 pm

    #1032600

    You could use the SQL Agent, there is an output option you can point it to a file. So you could just create a SQL Agent job and execute the T-SQL statement on demand or schedule it.

    You could also use xp_cmdshell but security risk are associated with turning this option on.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

Viewing 3 posts - 1 through 2 (of 2 total)

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