How to Save Query output to separate text file - Each time you run any querry?

• Perry_Newbie

  Valued Member

  Points: 64

  More actions

  December 22, 2011 at 8:23 pm

  #265347

  Hello,

  I wanted to save a query result to a text file. --This Part was easy and can be done in many ways(I opted OSQL).

  osql.exe -E -SPERRY-PC -dMASTER -Q"EXEC SP_HELPDB" > C:\SQL\TEST.TXT

  > parameter overwrites the result set each time I ran.

  But I want the "query result to be saved into a separate output file - each time I run any query". Please advice on anything?

  I know we can do some tweaks with SP_OA procedure to do any such thing. But is there any other simple way to do so?

  I appreciate your help on this,

  Thanks,

  Perry

• Jim Mackenzie

  Hall of Fame

  Points: 3728

  More actions

  December 23, 2011 at 2:16 am

  #1425061

  Hi,

  Is there no way to parametrise the text file name?

  osql.exe -E -SPERRY-PC -dMASTER -Q"EXEC SP_HELPDB" > C:\SQL\TEST {INSERT PARAMETER HERE} .TXT

  SQL SERVER Central Forum Etiquette[/url]

• Perry_Newbie

  Valued Member

  Points: 64

  More actions

  December 23, 2011 at 6:37 pm

  #1425304

  Created a Batch file (.bat) and then put the below osql command in that file. In Batch file, I can access the current date & time values. Referred this link for details - http://blueonionsoftware.com/blog.aspx?p=40656a9d-021b-4061-b296-36ad5211f4b2

  My query becomes:

  osql.exe -dMASTER -Q"EXEC SP_HELPDB" > C:\SQL\login_%DATE:~4,2%_%DATE:~7,2%_%DATE:~-4%.TXT

  I can use this method as a temporary purpose. Is there any better way to achieve this?

  I wanted everything to be covered within the management studio.

  Now my problem is - how can i schedule a .bat file to run via the SQL Agent job?

  Any inputs??

• Perry_Newbie

  Valued Member

  Points: 64

  More actions

  December 23, 2011 at 6:46 pm

  #1425305

  Found it:

  cmd.exe /c "c:\test.bat"

• Perry_Newbie

  Valued Member

  Points: 64

  More actions

  December 24, 2011 at 9:17 am

  #1425341

  At last, I have found a better solution with in the OSQL code itself. No need of any BATCH or a SHELL SCRIPTING to achieve this...

  Below is my code and it runs perfectly fine...

  Declare @cmd varchar(500)

  SET @cmd = 'osql.exe -E -SPERRY-PC -dMASTER -Q"EXEC SP_HELPDB" > "C:\SQL\DB_INFO_'+CONVERT(VARCHAR(10),GETDATE(),110)+'.txt"'

  exec xp_cmdshell @cmd

  Hope it helps to someone else too...