SQL Server: how to write variable value into the file?

Posted on 2014-08-28
Last Modified: 2014-09-23
how to write variable value into the file instead of Results or Messages windows?

Question by:quasar_ee
    LVL 75

    Expert Comment

    by:Anthony Perkins
    From within SSMS you have to choose the output as Text, Grid or File.  This is a function of the client tool (SSMS) and not SQL Server, so it is an all or nothing approach you cannot pick and choose.
    LVL 29

    Assisted Solution

    by:Rich Weissler
    Putting aside the possibilities of calling a windows command shell from within SQL, the only other option I can think of would be to create your own procedure in another language and link it into SQL.  There isn't native support in T-SQL.
    LVL 40

    Accepted Solution

    EXEC  sp_OACreate 'Scripting.FileSystemObject', @Object OUT
    EXEC sp_OAMethod @Object , 'OpenTextFile' , @FileID OUT , 'c:\myfile.txt' , 2 , 1
    EXEC sp_OAMethod @FileID , 'WriteLine' , Null , @myvariable
    Exec sp_OADestroy @FileID

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now