question

sandeepiii avatar image
sandeepiii asked

sql server SSAS Powershell

Hi, Due to server idle time setting to 100mintes.My connection gets disconnected and cube processing gets interuptted. or stopped. How can i overcome this may set some connection property so tht it does not go idle and the connection is not disconnected.My job takes 3hrs to complete. param($ServerName="W96847\INST103", $DBName="RHRH_MFI", $ProcessTypeDim="ProcessFull",$ProcessTypeMG="ProcessFull", $Transactional="Y", $Parallel="Y",$MaxParallel=2,$MaxCmdPerBatch=5, $PrintCmd="N") ## Add the AMO namespace $loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") [Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") [Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") if ($Transactional -eq "Y") {$TransactionalB=$true} else {$TransactionalB=$false} if ($Parallel -eq "Y") {$ParallelB=$true} else {$ParallelB=$false} $server = New-Object Microsoft.AnalysisServices.Server $server.connect($ServerName) if ($server.name -eq $null) { Write-Output ("Server '{0}' not found" -f $ServerName) break } $DB = $server.Databases.FindByName($DBName) if ($DB -eq $null) { Write-Output ("Database '{0}' not found" -f $DBName) break } Write-Output("Load start time {0}" -f (Get-Date -uformat "%H:%M:%S") ) Write-Output("----------------------------------------------------------------") Write-Output("Server : {0}" -f $Server.Name) Write-Output("Database: {0}" -f $DB.Name) Write-Output("DB State: {0}" -f $DB.State) Write-Output("DB Size : {0}MB" -f ($DB.EstimatedSize/1024/1024).ToString("#,##0")) Write-Output("----------------------------------------------------------------") Write-Output("DB processing started. Time: {0}" -f (Get-Date -uformat "%H:%M:%S")) $server.CaptureXml=$TRUE # Just capture server statements, dont execute them #Process dimensions foreach ($dim in $DB.Dimensions) { $dim.Process($ProcessTypeDim) } # Dimensions #Process cubes foreach ($cube in $DB.Cubes) { foreach ($mg in $cube.MeasureGroups) { foreach ($part in $mg.Partitions) { $part.Process($ProcessTypeMG) } } } # Separate step to process all linked measure groups. Linke MG does not have partitions foreach ($cube in $DB.Cubes) { foreach ($mg in $cube.MeasureGroups) { if ($mg.IsLinked) { $mg.Process($ProcessTypeMG) } } } $server.CaptureXML = $FALSE # Finish capturing statements. All statements are in Server.CaptureLog $cmdBatch = @" ##ProcessCmd## "@ $cmdBatch = $cmdBatch -replace("##MaxParallel##",$MaxParallel) #$ErrorActionPreference = "SilentlyContinue" $currentCmdNo=0; $currentCmdInBatchNo=0; $processCmd="";$currentBatchNo=0 $TotalCmdCount = $Server.CaptureLog.Count foreach ($cmdLine in $Server.CaptureLog) { $currentCmdNo = $currentCmdNo + 1 $processCmd = $processCmd + $cmdLine + "`n" $currentCmdInBatchNo=$currentCmdInBatchNo + 1 if ($currentCmdInBatchNo -ge $MaxCmdPerBatch -or $currentCmdNo -eq $TotalCmdCount) { #MaxCmdPerBatch reached, execute commands $processCmd = $cmdBatch -replace("##ProcessCmd##", $processCmd) if ($PrintCmd -eq "Y") { Write-Output($processCmd) } $currentBatchNo = $currentBatchNo + 1; Write-Output("=== Starting batch No {0}. Time: {1} ..." -f $currentBatchNo, (Get-Date -uformat "%H:%M:%S")) $Result = $Server.Execute($processCmd) # Report errors and warnings foreach ($res in $Result) { foreach ($msg in $res.Messages) { if ($msg.Description -ne $null) {Write-Output("{0}" -f $msg.Description)} } } # Reset temp values $processCmd = ""; $currentCmdInBatchNo=0; } }#foreach Write-Output("----------------------------------------------------------------") Write-Output("DB processing completed. Time: {0}" -f (Get-Date -uformat "%H:%M:%S")) Write-Output("----------------------------------------------------------------") Write-Output("Listing objects that are not processed")
ssaspowershell
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.