March 20, 2012 at 1:21 pm
The following script goes through each database on each server to execute a space check procedure and everything works fine except for the databases that have spaces in their names (Yes, I know, I didn't name them) I am stuck with how to proceed as it will only process the first part of the name and not the full thing, thus throwing an error. Any help would be great!
$servers = get-content c:\InstanceList.txt
$query = "DECLARE @db varchar(max)
SET @db = DB_NAME()
exec ('use '+@db)
SELECT CONVERT(sysname, SERVERPROPERTY('ServerName')) AS ServerName
,DB_NAME() AS db
,[fileid],
[File_Size_MB] = convert(decimal(12,2),round(/128.000,2))
,[Space_Use_MB] = convert(decimal(12,2),round(fileproperty([name],'SpaceUsed')/128.000,2))
,[Free_Space_MB]=convert(decimal(12,2),round((-fileproperty([name],'SpaceUsed'))/128.000,2))
,[Percent_Free_MB]=CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2))
,CASE WHEN maxsize =-1 THEN 'unlimited'
WHEN maxsize/128 < 1024 THEN CAST(maxsize/128 as varchar)
ELSE CAST(maxsize/(128*1024) as varchar)
END as maxsize
,RTRIM(LTRIM([name])) AS FileName
,RTRIM(LTRIM([filename])) AS FilePath
FROM dbo.sysfiles"
foreach($server in $servers)
{
$sqlserver = new-object “Microsoft.SqlServer.Management.Smo.Server” $server
foreach ($db in $sqlserver.Databases)
{
invoke-sqlcmd -Query $query -database $db.name -ServerInstance $sqlserver.name -IgnoreProviderContext
}
}
March 20, 2012 at 1:38 pm
Can't you just change
exec ('use '+@db)
to
exec ('use ['+@db+']')
?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 20, 2012 at 2:34 pm
Wow thank you very much, I have no idea how I did not think of that. I was so concentrated on the sqlcmd part I forgot about my own query. That fixed all the space in database name issues but now I am getting the following only for a few databases. It is pretty general.
Invoke-Sqlcmd : Line 1: Incorrect syntax near 'max'.
Must declare the variable '@db'.
Must declare the variable '@db'.
At C:\PSLooperScript.ps1:34 char:14
+ invoke-sqlcmd <<<< -Query $query -database $db.name -ServerInstance $sqlserver.name -IgnoreProviderContext
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply