automating query output to a file

• John Campbell-96530

  SSC-Addicted

  Points: 488

  More actions

  September 11, 2003 at 6:23 am

  #58853

  Clint Herring's new script for today is great. How can I set this up as a sp and automatically have my output go to a file so that I can automate and run as a job without having to run by hand each time. I want to automate the task of going to the query window and specifying the file path\name for the output and then running the query. Is there a way to automate this naming of the query output as a parameter? In dos we could do a pipe :>(

• Greg Larsen

  SSC-Insane

  Points: 20961

  More actions

  September 11, 2003 at 8:39 am

  #473738

  Here I put the code into an SP (see below).

  Place this command in a sql server agent job step:

  osql -E -S<yourserver> -Q"dba.dbo.usp_get_dbinfo" -oc:\\temp\dbinfo.txt

  CREATE PROCEDURE usp_get_dbinfo

  AS

  /*******************************************************************/

  --Name : dbinfo.sql

  --Server : Generic

  --Description : Captures general SQL server info.

  -- : Works in ISQL/W, ISQL, OSQL & Query Analyzer

  --Author : Clint Herring

  --Modified:

  -- By Greg Larsen on 9/11/2004 to support being a SP and running out of

  -- the DBA library

  /*******************************************************************/

  begin

  Set NOCOUNT On

  --Use master -- May need to modify calls to use master

  Select 'Server: '+ @@Servername

  Select substring(@@version,1,27) +

  substring(@@version,charindex('corporation',@@version) + 13,18) + ' ' +

  'version ' + substring(@@version,charindex('-',@@version) + 2,8) +

  substring(@@version,charindex(' on ',@@version),47)

  --go

  -- Create a temp table to hold an db exclude list

  If (Select object_id('tempdb.dbo.#dblist')) > 0

  Exec ('Drop table #dblist')

  Create table #dblist(dbname sysname null)

  Declare @rtn int

  -- Exclude list for filename and filegroup info

  Insert into #dblist Values('model')

  Insert into #dblist Values('Northwind')

  Insert into #dblist Values('pubs')

  Insert into #dblist Values('tempdb')

  Select 'Start time = ', GetDate()

  Select 'Running sp_monitor '

  Execute ('sp_monitor')

  Select 'Running sp_configure '

  Execute ('sp_configure')

  Select 'Running sp_helpdevice '

  Execute ('sp_helpdevice')

  Select 'Running sp_helpserver '

  Execute ('sp_helpserver')

  Select 'Running sp_helpdb '

  Execute ('sp_helpdb')

  Select 'Running sp_databases '

  Execute ('sp_databases')

  Select 'Running sp_server_info '

  Execute ('sp_server_info')

  Select 'Running xp_msver '

  Execute ('master.dbo.xp_msver')

  Select 'Running xp_loginconfig '

  Execute ('master.dbo.xp_loginconfig')

  Select 'Running xp_logininfo '

  Execute ('master.dbo.xp_logininfo')

  Declare @name varchar(30)

  select @name = min(name)

  from master.dbo.sysdatabases

  where name not in (select dbname from #dblist)

  while @name is not null

  begin

  select 'Running sp_helpfilegroup for ' + @name

  exec('use ' + @name + ' exec sp_helpfilegroup')

  select 'Running sp_helpfile for ' + @name

  exec('use ' + @name + ' exec sp_helpfile')

  select @name = min(name)

  from master.dbo.sysdatabases

  where name > @name

  and name not in (select dbname from #dblist)

  end

  Select 'End time = ', GetDate()

  Set NOCOUNT Off

  end

  GO

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Edited by - greg larsen on 09/11/2003 08:39:23 AM

  Gregory A. Larsen, MVP