Elsasoft Forums
Elsasoft Forums
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SqlSpec
 Tips and Tricks
 scheduling SqlSpec
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jesse
Forum Admin

732 Posts

Posted - Feb 17 2007 :  12:05:44 AM  Show Profile  Reply with Quote
One way to keep your documentation up to date in SQL Server is to schedule a SQL Agent Job to run SqlSpec nightly against all your
databases, and then copy the generated files to a location where they are published on your intranet.

If you don't use SQL Server, you could also use any sort of nightly batch process, such as a scheduled task in windows.

Brad
Starting Member

13 Posts

Posted - Mar 21 2007 :  2:38:02 PM  Show Profile  Reply with Quote
I use the following to generate a batch file for an entire server:


/*
	Generates ElsaSoft.Org SqlSpec 3.5.3 batch execution files  
	for all databases of a SQL Server instance server except Master, Model, Msdb, and Temp
	Assumptions:
		1) Output is a .chm file
		2) If you want to use a XML config file, it is available
		3) The output of this pup will be run in the elsasoft directory
		4) The @CollectionDirectory actually exists
	
	*** Set query to Output To 
		text & then copy/paste 
		or output to file
*/


	set nocount on
	declare 	@OptionalDomainName varchar(100)	-- Optional - Holds name of domain	
		, 	@ServerName varchar(120)		-- Holds name of server
		,	@CollectionDirectory varchar(80)	-- Name of the directory you want to put all the final .chm files into
		,	@LineSeparator varchar(2)		-- Your system's text line separation character
		,	@UseConfigFile bit			-- Use a XML config file (0 = No, 1 = Yes)
		,	@DynamicConfigFile bit			-- If @UseConfigFile = 1 shoud the name of the config file be created dynamically? (0 = No, 1 = Yes)
		,	@StaticConfigFileName varchar(100)	-- If @DynamicConfigFile = 0 the name of the config file to use
		,	@FileHeader varchar(100)		-- First line of output text per database

	set @OptionalDomainName = '.XioDev.Dom'
	set @ServerName = CONVERT(varchar(20), SERVERPROPERTY('ServerName'))
	set @CollectionDirectory = 'C:\Program Files\ElaSoft\ToSharePoint'
	set @LineSeparator = char(13)
	set @UseConfigFile = 1
	set @DynamicConfigFile = 0
	set @StaticConfigFileName = ' /d "SqlSpec_Static_ConfigFile.xml"'
	set @FileHeader = 'rem 	ElsaSoft.Org SqlSpec 3.5.3 batch execution file created ' + convert(varchar(23),getutcdate(), 121) + ' UTC' + @LineSeparator

	declare @ExcludeDBs table (Exclude sysname)
	/*
		-- use to generate list of inserts into @ExcludeDBs for all DBs - then copy/paste the ones you want to exclude
		select 
			'insert into @ExcludeDBs Values (''' + name + ''')'
		from 
			sysdatabases 
		where 
			dbid > 4 
		order by 
			name
	*/
	insert into @ExcludeDBs Values ('bubba')
	insert into @ExcludeDBs Values ('limbo')
	insert into @ExcludeDBs Values ('pubs')


	select 
		  @FileHeader
		+ 'rem 	This file will generate documentation for Server: ' + @ServerName  + @OptionalDomainName + ', Database: ' + name
		+ @LineSeparator
		+ 'rem 	File name: SqlSpec_' + @ServerName + '_' + name + '.bat'
		+ @LineSeparator
		-- SqlSpec batch execution command
		+ '"C:\Program Files\ElaSoft\SqlSpec.exe" /c "server=' + @ServerName + @OptionalDomainName + '; database=' + name + '; trusted_connection=yes" /o "C:\Program Files\ElaSoft\output\' + @ServerName + '\' + name + '" /n "' + @ServerName + '.' + name + '" /t 360 /r 1 /T 0 /a 0 /k 2 /j 80 /w 1 /x 1 /y 0 /u 0 /D 120 /R 0 /S 100 /E 0 /cs "utf-8"'
			-- optional config file
		+ case @UseConfigFile
			when 0 then ''
			when 1 then 
				case @DynamicConfigFile
					when 0 then @StaticConfigFileName
					when 1 then ' /d "SqlSpec_' + @ServerName + '_' + name + '.xml"'
					else ''
				end 
			else ''
		  end
		+ @LineSeparator
		-- put .chm file in a central location for upload to repository
		+ 'copy "C:\Program Files\ElaSoft\output\' + @ServerName + '\' + name + '\*.chm" "' + @CollectionDirectory + '\*.*"'
		+ @LineSeparator
		-- delete all working files
		+ 'del "C:\Program Files\ElaSoft\output\' + @ServerName + '\' + name + '\*.*" /f /s /q'
		+ @LineSeparator
		-- delete working directories
		+ 'rmdir "C:\Program Files\ElaSoft\output\' + @ServerName + '\' + name + '" /s /q'
		+ @LineSeparator 
		+ @LineSeparator
	from 
		sysdatabases 
	where 
			dbid > 4 -- exclude SQL Server systems databases 
		and
			name not in (select Exclude from @ExcludeDBs)
	order by 
		name
	

Go to Top of Page

Jesse
Forum Admin

732 Posts

Posted - Mar 22 2007 :  09:43:34 AM  Show Profile  Reply with Quote
very nice
Go to Top of Page

Jonathan
Starting Member

3 Posts

Posted - Dec 20 2010 :  1:22:43 PM  Show Profile  Reply with Quote
I am trying to schedule the SqlSpec bat file to run from Task Scheduler in Windows 7 Professional. I have the task scheduled to start while I am logged in but the screen is locked. The task starts, but exits after 6 seconds with (0x1) as the result. I have not been able to find any information on any error generated, or any log file that lists what happened.

When I manually started the task in the Task Scheduler, it worked correctly. When I scheduled the task to start while I was working, it worked correctly. When I scheduled it to run two mintues from "now", then locked the screen and waited five mintues, it started and was running when I unlocked the screen. When I schedule it to start at 8:00 pm on Saturday night, it runs for six seconds, then ends (this has happened five weeks in a row).

Any thoughts or things to try? Is there anywhere to look for a log file to see why the job stopped after six seconds or if it encountered an error?
Go to Top of Page

Jesse
Forum Admin

732 Posts

Posted - Dec 20 2010 :  1:29:56 PM  Show Profile  Reply with Quote
there should be a log file called sqlspec_progress.log file generated in the output directory. if there is an error, it will be logged there.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Elsasoft Forums © Elsasoft LLC Go To Top Of Page
Snitz Forums 2000