MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1759

Great tip. I was trying to add the datafiles, log files and their sizes to the export to Excel but, couldn't get the right property. Suggestion on how to get it?

When I try to execute a following statement $Excel = $Excel.Workbooks.Add() in powershell I receive this error:

"Old format
or invalid type library. (Exception from HRESULT: 0x80028018
(TYPE_E_INVDATAREAD))" message..

What can I do to get around this ?

You can add this inside the ForEach loop for the databases collection but you do need to have it formatted properly in Excel as each database would have at least two files

    $fileGroups = $db.FileGroups
    ForEach ($fg in $fileGroups)
    {
        $fg.Files | Select $db.Name, Name, FileName, size, UsedSpace
    }
    $db.LogFiles | Select $db.Name, Name, FileName, Size, UsedSpace

This might be because of the difference between the local user settings and Office settings.  Check the Regional Settings in your user profile or you can include a line in the PowerShell script to call the CultureInfo class but that's a bit geeky

CultureInfo Class

http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo.aspx 

Here's a Microsoft KB article for this

http://support.microsoft.com/default.aspx?scid=kb;en-us;320369

Hi, I too got the same error. I've found the following code but can't seem to get any further :-(

$xl = New-Object -comobject "excel.application"

$xl.visible = $true

$xlbooks =$xl.workbooks

$newci = [System.Globalization.CultureInfo]"en-GB"

$xlbooks.PSBase.GetType().InvokeMember("Add", [Reflection.BindingFlags]::InvokeMethod, $null, $xlbooks, $null, $newci)

Any ideas how I can incorporate the code from the article below, into the above, so I can reference the $Sheets object? - ($Sheets.Cells.Item() etc)

Great script.

 I was looking to format my output like this....

server1,db1

server1,db2

server1,db3

server2,db1

but haven't been able to figure out how to get $s.name and $s.databases to output that way.  Any suggestions?

If it is just the output without the Excel thing, you can simply do this. Remember, I am just reading the names of the SQL Server instances from a text file so you can simply use that variable beside the database name

#################################################
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "D:\SQL_Servers.txt")
{

#GetDBs.ps1
#This script gets SQL Server database information using PowerShell


[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $s.Databases

foreach ($db in $dbs)
    {
        Write-Host $instance , $db.Name
    }

}