R
robboll
Do any DBAs out there have preferably an MS Access routine that does
the following:
You populate a table name with either the server name or the server IP
address. With that information a VBA routine interrogates the server
for:
1. Versions of SQL Server instances on the server.
2. Databases in each instance.
3. Users of each database.
4. Size of each database.
5. Location of each database MDF file.
I found a VB Script that does some of this, but I would like to
automate this process in an MS Access database:
Just paste it in a text file and change the txt extension to vbs:
strInitialDB = "master" 'default database
strSQLServer=InputBox("What is the name of the Server?
",strTitle,"My_Default_Server")
strQuery = "SELECT [name], [dbid], [crdate], [filename], [version]
FROM [master].[dbo].[sysdatabases]"
Set objConn = CreateObject("ADODB.Connection")
' open the master data source
objConn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=" & strInitialDB & ";Data Source="
& strSQLServer
'set the stored procedure to execute
set objRS = objConn.execute(strQuery)
if objRS.recordCount = 0 then
wscript.echo "No records were found."
wscript.quit
end if
strHTML = "<body>ALL SQL SERVER DATABASES ON SERVER: " & UCASE
(strSQLServer)
strHTML = strHTML & "<table><tr bgcolor = #6699cc>"
strHTML = strHTML & "<td>DATABASE NAME<td>DATABASE ID<td>DATE
CREATED<td>DATABASE FILE PATH<td>VERSION"
strHTML = strHTML & "</tr>"
while not objRS.EOF
strHTML = strHTML & "<tr bgcolor = #ffe4c4>"
for i = 0 to objRS.fields.Count -1
strHTML = strHTML & "<td>" & objRS.fields(i).value
next
strHTML = strHTML & "</tr>"
objRS.moveNext
wend
strHTML = strHTML & "</body></table>"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate("about:blank")
objIE.ToolBar = 0
objIE.StatusBar = 0
objIE.width = 800
Set objDoc = objIE.Document.Body
objIE.Visible = True
objIE.Document.Body.innerHTML=strHTML
objRS.close
objConn.close
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thanks for any help with this!
RBollinger
the following:
You populate a table name with either the server name or the server IP
address. With that information a VBA routine interrogates the server
for:
1. Versions of SQL Server instances on the server.
2. Databases in each instance.
3. Users of each database.
4. Size of each database.
5. Location of each database MDF file.
I found a VB Script that does some of this, but I would like to
automate this process in an MS Access database:
Just paste it in a text file and change the txt extension to vbs:
strInitialDB = "master" 'default database
strSQLServer=InputBox("What is the name of the Server?
",strTitle,"My_Default_Server")
strQuery = "SELECT [name], [dbid], [crdate], [filename], [version]
FROM [master].[dbo].[sysdatabases]"
Set objConn = CreateObject("ADODB.Connection")
' open the master data source
objConn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=" & strInitialDB & ";Data Source="
& strSQLServer
'set the stored procedure to execute
set objRS = objConn.execute(strQuery)
if objRS.recordCount = 0 then
wscript.echo "No records were found."
wscript.quit
end if
strHTML = "<body>ALL SQL SERVER DATABASES ON SERVER: " & UCASE
(strSQLServer)
strHTML = strHTML & "<table><tr bgcolor = #6699cc>"
strHTML = strHTML & "<td>DATABASE NAME<td>DATABASE ID<td>DATE
CREATED<td>DATABASE FILE PATH<td>VERSION"
strHTML = strHTML & "</tr>"
while not objRS.EOF
strHTML = strHTML & "<tr bgcolor = #ffe4c4>"
for i = 0 to objRS.fields.Count -1
strHTML = strHTML & "<td>" & objRS.fields(i).value
next
strHTML = strHTML & "</tr>"
objRS.moveNext
wend
strHTML = strHTML & "</body></table>"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate("about:blank")
objIE.ToolBar = 0
objIE.StatusBar = 0
objIE.width = 800
Set objDoc = objIE.Document.Body
objIE.Visible = True
objIE.Document.Body.innerHTML=strHTML
objRS.close
objConn.close
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thanks for any help with this!
RBollinger