July 25, 2002 at 8:04 am
Hi, I've found a DMO script that displays the version, and I would like get rid of the version information (oServer.VersionString) and instead add a sql query that I have. I want to add the query in the loop. Below is the current code:
'This script outputs the version info
'from a list of servers provided in a text file.
IF Wscript.Arguments.Count = 0 THEN
MsgBox "Server List Missing", vbCritical
Wscript.Quit
END IF
SET oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True
SET fso = CreateObject("Scripting.FileSystemObject")
SET oFileTargets = fso.OpenTextFile(Wscript.Arguments(0),1)
DO UNTIL oFileTargets.AtEndOfStream
sTarget = oFileTargets.ReadLine
oServer.Connect sTarget
Wscript.Echo sTarget & ": " & oServer.VersionString
oServer.Disconnect
LOOP
''''Clean up'''''
SET oServer = Nothing
SET oFileTargets = Nothing
SET fso = Nothing
July 25, 2002 at 8:23 am
You can run queries using the executeimmediate method, but it returns a queryresult object rather than a recordset. Basically an array. If you're just running an update its fine, if you're going to work with the results of the query you'd be better off to use ADO.
Andy
July 25, 2002 at 8:56 am
The query is going to search each server given from a text file for the last backup date for each database on that server. Here's the code to search for the last backup date:
set nocount on
Print '--Databases Backed up on: '
select
(substring ( database_name, 1, 32)) as Database_Name,
abs(DateDiff(day, GetDate(), Max(backup_finish_date))) as DaysSinceBackup,
Max(backup_finish_date)
from msdb.dbo.backupset
where database_name like '%' and type = 'D' and database_name in (select name from master.dbo.sysdatabases)
group by database_name
order by database_name
go
Print '--All Databases on: '
select name as database_name from master.dbo.sysdatabases
order by name
July 25, 2002 at 11:04 am
As I said earlier, you can do it from DMO or ADO, either will work.
Andy
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply