Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

execute SQL server query from Dos command prompt/ batch file. Expand / Collapse
Author
Message
Posted Monday, October 15, 2007 6:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2008 12:45 AM
Points: 2, Visits: 6
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
Post #411075
Posted Tuesday, October 16, 2007 5:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
Look at the osql and isql command line utilities.
Post #411200
Posted Tuesday, October 16, 2007 6:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #411245
Posted Tuesday, October 16, 2007 6:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
He is correct - use SQLCmd (it is the new version - I forgot I was in the 2005 forum).
Books Online has this well documented.
Post #411249
Posted Wednesday, October 17, 2007 7:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:53 AM
Points: 1,182, Visits: 1,949
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.



(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.
Post #411718
Posted Wednesday, October 17, 2007 6:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2008 12:45 AM
Points: 2, Visits: 6
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
Post #411982
Posted Thursday, October 18, 2007 7:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:53 AM
Points: 1,182, Visits: 1,949
For SQL Server 2000, use OSQL, which is a replacement for ISQL. See the Books Online for details about the utility and examples.



(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.
Post #412197
Posted Thursday, October 18, 2007 9:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,556, Visits: 2,593
I wonder why nobody has mentioned anything about BCP???

--Ramesh

Post #412286
Posted Friday, October 19, 2007 10:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 06, 2012 2:39 PM
Points: 954, Visits: 683
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

Post #412899
Posted Monday, January 14, 2008 3:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 12:57 PM
Points: 6, Visits: 14
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.
Post #442717
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse