Post reply

execute SQL server query from Dos command prompt/ batch file.

  • October 15, 2007 at 6:33 pm

    #69260

    Hi All,

    I need to wirte a batch file, which connects to SQL server, executes a simple Query and output the result to a text file.

    in the result if we are having 3 cloumns c1, c2, c3 insted of getting 3 vertical columns the result sud be as

    c1 item1

    c2 item1

    c3 item1

    c1 item2

    c2 item2

    c3 item2

    c1 item3

    c2 item3

    c3 item3

    Your help will be well appriciated.

    THanks & Regards,

    Sai.K.k

  • October 16, 2007 at 5:21 am

    #742282

    Look at the osql and isql command line utilities.

  • October 16, 2007 at 6:44 am

    #742319

    And since this is 2005, the sqlcmd command line utility as well.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 16, 2007 at 6:47 am

    #742322

    He is correct - use SQLCmd (it is the new version - I forgot I was in the 2005 forum).

    Books Online has this well documented.

  • October 17, 2007 at 7:37 am

    #742713

    Yes, use SQLCMD. However, if you don't like the output format that SQLCMD provides and/or you want to massage the data a bit before writing to the file, the next step up would be a quick VBScript (executed by CSCRIPT) using the SQL Server Provider for OLE-DB.

    I do it all the time to produce delimited (e.g., tab, pipe, etc.) flat files as well as to extract BLOBs (former TEXT and IMAGE data types) out as separate files in the file system.

    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • October 17, 2007 at 6:33 pm

    #742935

    Hi,

    I am new to SQL infact.

    Is that SQLCMD comes with sql server 2005, if so I need another solution.

    We use SQL server2000 and our clients cant shift from this. Prior to sql server 2005 and SQLCMD there might be an option of executing queries from CMD.

    IF sou can you help me in that.

    Thank & regards,

    Sai krishna.k

  • October 18, 2007 at 7:22 am

    #743111

    For SQL Server 2000, use OSQL, which is a replacement for ISQL. See the Books Online for details about the utility and examples.

    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • October 18, 2007 at 9:31 am

    #743190

    I wonder why nobody has mentioned anything about BCP???

    --Ramesh

  • October 19, 2007 at 10:17 am

    #743702

    to run the SQL use SQL Command, but use the following to format the data the way you requested

    DROP TABLE [#testtable]

    GO

    SELECT [name],

    [object_id] AS c1,

    principal_id AS c2,

    [schema_id] AS c3

    INTO #testtable

    FROM sys.tables

    SELECT NAME,Column_name,ItemValue FROM (SELECT *

    FROM [#testtable]) AS a

    UNPIVOT (ItemValue FOR Column_name IN (c1,c2,c3)) AS unpvt

    ORDER BY 1,2

  • January 14, 2008 at 3:03 pm

    #767889

    I had a somewhat related question.

    I want to use a SQL command to write a string to a text file using Master...xp_cmdshell.

    essentially clobbering the contents.

  • October 29, 2008 at 7:52 am

    #891227

    A script of how to do that can be found here

    http://www.databasejournal.com/features/mssql/archives.php

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

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