InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 11:19:49 GMT20http://www.sqlservercentral.com/Forums/Topic1302603-1351-1.aspxHello,I am new to Powershell and I have been asked to get the SQL Server Database data path and insert this veriable into a script to gather permissions from the folders and inserts them into a csv file. I have insert my code so far belowAny help would be much appriciated.Param ( [STRING] $SQLSERVER = "Server, port")$permsLogFile = "C:\Users\Output.txt"Write-Output "Start of the script" > $permsLogFile$SqlConnection = New-Object System.Data.SqlClient.SqlConnection$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=master;Integrated Security=True"$SqlCmd = New-Object System.Data.SqlClient.SqlCommand$SqlCmd.CommandText = "SELECT SUBSTRING(filename,1,CHARINDEX('A\master.',filename)) from master..sysdatabases WHERE name = 'master'"$SqlCmd.Connection = $SqlConnection$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter$SqlAdapter.SelectCommand = $SqlCmd$DataSet = New-Object System.Data.DataSet$SqlAdapter.Fill($DataSet)$networkPath = $DataSet.Tables[0]$subFolders = Get-ChildItem -Name $networkPathforeach ($Folder in $subFolders){ $cmd = "cacls $networkPath\$Folder" Write-Output "Running Commamnd:" $networkPath >> $permsLogFile Invoke-Expression $cmd | Write-Output >> $permsLogFile}$SqlConnection.Close()Fri, 18 May 2012 09:06:30 GMTEaton12http://www.sqlservercentral.com/Forums/Topic1305113-1351-1.aspxGreetings! I have a command(below) in a powershell script that has been working for well over a year, but started failing about 4 days ago. I'm at a beginner level with powershell so it's not clear to me what's wrong. I've done some searching on the web to find out if anyone else has had issues with the SqlServiceAdvancedProperty being invalid, but I didn't find anything. Two different things happened the day this started happening. 1) I installed SQL PSX 2.3.2.1 . I installed the same powershell mod on another server and the command below works just fine on the other server so I'm not convinced the mod caused the issue. 2) there were windows updates applied to the server. The server was rebooted after the updates. If anyone has any suggestions on what my next steps should be for troubleshooting this issue, I would greatly appreciate it.The command:Get-WmiObject -Class "SqlServiceAdvancedProperty" -Namespace "root/Microsoft/SqlServer/ComputerManagement10" |? { $_.SqlServiceType -eq 1 } |% { $_.ServiceName } | Sort -Uniquethe error:Get-WmiObject : Invalid classAt line:1 char:14 + Get-WmiObject <<<< -Class "SqlServiceAdvancedProperty" -Namespace "root/Microsoft/SqlServer/ComputerManagement10" |? { $_.SqlServiceType -eq 1 } |% { $_.ServiceName } | Sort -Unique + CategoryInfo : InvalidOperation: (:) [Get-WmiObject], ManagementException + FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommandThank you,KimWed, 23 May 2012 10:18:42 GMTkstjacqueshttp://www.sqlservercentral.com/Forums/Topic1304432-1351-1.aspxHi all,I've been trying to find a way to do set-based inserts into a SQL Server.As near as I can tell, there are only 2 ways:1) convert the data to be inserted to XML, pass that SQL Server, either a sproc or a file;2) export to file and BULK INSERT in SQL Server.Neither of these are especially appealing.Is there not something equivalent to $Table.Insert($NewData)Save-IntoSQLTable appears to be on the right track, but also seems to want only to work with traces.Any ideas?Tue, 22 May 2012 13:01:04 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1301283-1351-1.aspxHello, I’m having problems when debugging the database as it grows and grows TMG and the idea is to use a script to delete old records. I’m ussing remote SQL logging. I make this two script but seems not to work I have an ODBC connection. Could you please help me?——————————————————————————————-Const adOpenStatic = 3Const adLockOptimistic = 3Const adUseClient = 3Set objConnection = CreateObject(“ADODB.Connection”)objConnection.ConnectionTimeout=1200objConnection.Open “DSN=BaseTMGF;UID=xxx;PWD=xxxxxxxxx”objConnection.EXECUTE “delete FROM dptmgf.dbo.FirewallLog WHERE logtime <= getdate()-60 "objConnection.Close——————————————————————————————-Const adOpenStatic = 3Const adLockOptimistic = 3Const adUseClient = 3Set objConnection = CreateObject("ADODB.Connection")Set objRecordset = CreateObject("ADODB.Recordset")objConnection.ConnectionTimeout=1200objConnection.Open "DSN=BaseTMGW;UID=xxx;PWD=xxxxxxxx"objConnection.EXECUTE "delete FROM dptmgw.dbo.WebProxyLog WHERE logtime <= getdate()-60 "objConnection.CloseWed, 16 May 2012 12:31:38 GMTshayro.mendezhttp://www.sqlservercentral.com/Forums/Topic1301127-1351-1.aspxHi folksI'm trying to adapt the powershell script at [url=http://sysadmingrunt.blogspot.com/][/url] into my own environmentThe modifications I made to the original code was to pass a *.sqb in the end of the identified filename for each database backup (I have 4 sqb files per database backup). The original script was built to handle one backup file per database.The script is working, it does restore the databases but errors are being thrown in the redgaterestore function:Error message:Exception calling "ExecuteNonQuery" with "0" argument(s): "Timeout expired. The timeout period elapsed prior to completion of the operation or the server isnot responding."At T:\restoresp.ps1:87 char:32+ $SQLCommand.ExecuteNonQuery <<<< () | Out-Null + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodExceptionException calling "ExecuteNonQuery" with "0" argument(s): "Timeout expired. The timeout period elapsed prior to completion of the operation or the server isnot responding."At T:\restoresp.ps1:87 char:32+ $SQLCommand.ExecuteNonQuery <<<< () | Out-Null + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodExceptionAnd when running this script as part of a SQL job, the job itself fails as this error is thrown.Any help would be greatly appreciatedWed, 16 May 2012 09:16:58 GMTmitzyturbohttp://www.sqlservercentral.com/Forums/Topic1299834-1351-1.aspxBasic issue: after loading sqlps module network share is not accessible...Bug is already logged but not yet acknowledged by MS...http://connect.microsoft.com/SQLServer/feedback/details/737324/sqlps-unc-path-issue#tabscan someone run below 3 command and confirm if they see the same in their env, third command should fail...Get-ChildItem "\\servername\c$\" | Select-Object -first 1Import-Module 'sqlps' -DisableNameCheckingGet-ChildItem "\\servername\c$" | Select-Object -first 1 Mon, 14 May 2012 13:26:34 GMTPrakash Hedahttp://www.sqlservercentral.com/Forums/Topic1297285-1351-1.aspxWe need to downgrade a lot of our SQL Server licenses from Enterprise to Development. Is there a way to do this with Powershell since we have so many machines to go against.Wed, 09 May 2012 11:08:34 GMTrussell.younghttp://www.sqlservercentral.com/Forums/Topic1295417-1351-1.aspxI am having a problem in gettin this regex statemnent to work.if (a.FullPath.StartsWith("Q:")) monitoringpath = "\\\\eldorado\sasknowledge\\Programs\\Data Distribution\\CollectionPoint"; FullPath2 = Regex.Replace(a.FullPath, "Q:", ""); FullPath2 = monitoringpath + FullPath2;The a.FullPath variable contains: Q:\1111_drop1.TXTThe end result is:\\eldorado\sasknowledge\Programs\Data Distribution\CollectionPointQ:\1111_drop1.TXTI expect the Q: in the .FullPath to be removed but it isn't happening. Any ideas why this is happening?Thanks,Fri, 04 May 2012 12:09:41 GMTTeraByteMehttp://www.sqlservercentral.com/Forums/Topic1295491-1351-1.aspxI'm using Powershell (v2.0) to get the members of local groups on remote servers. I've got that working with the get-wmiobject cmdlet with one strange exception: If a local groupname contains a $, that character seems to be ignored in the name matching. For instance, I made two test groups on a server, test and test$sjs, each containing different users. I then runget-wmiobject -ComputerName 'PTS-GPSQL01' -query "select * from win32_groupuser where GroupComponent = `"Win32_Group.Domain='PTS-GPSQL01'`,Name='test'`""andget-wmiobject -ComputerName 'PTS-GPSQL01' -query "select * from win32_groupuser where GroupComponent = `"Win32_Group.Domain='PTS-GPSQL01'`,Name='test$sjs'`""But each command returns the members of the test group. I'm using a PS script function I found at http://gallery.technet.microsoft.com/scriptcenter/List-local-group-members-762b48c5. That contains a comment saying dollar signs will screw up the WMI query if they are in the computer name and filters them out of there. Seems they might mess up the group names too. And since SQL 2005 creates groups with names like SQLServer2005MSSQLUser$PTS-GPSQL01$MSSQLSERVER, that kinda makes things difficult.Has anyone come across a workarounds for this? Or am I missing something?ShaunFri, 04 May 2012 15:31:49 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic1287191-1351-1.aspxHello,I am just starting to work with PowerShell, and have the most basic problem that I am sure is due to my misunderstanding something very fundamental. Let's say I create a simple, 1-line script that consists of: Write-Host "Hello". I can invoke the script from the PowerShell command line and it displays "Hello" just as you would expect. I next tried to place the same one line within a function, as: Function Test-Hello { Write-Host "Hello" }Nothing I do can get this command to display anything. I don't get any error, but it simply returns to the command line with no output displayed. I must be missing something really simple, but I can't figure out what it is. Please help!Thanks!Fri, 20 Apr 2012 08:30:52 GMTrickeb1http://www.sqlservercentral.com/Forums/Topic859078-1351-1.aspxDoes anyone know if this is possible?Wed, 03 Feb 2010 14:51:57 GMTBrad Piconehttp://www.sqlservercentral.com/Forums/Topic1283405-1351-1.aspxHi All,I am writing this script to check if the sql agent job is running or not and if it is not running disable it.Some how I am not able to disable it and it is not evaluating the if loop. I am missing some stuff here :-Dalso, how can I make the script to take a user input and enable or disable the job.[code="plain"]$srv = New-Object Microsoft.SqlServer.Management.SMO.Server("server\instance")$jobs = $srv.jobserver.jobs#$jobs | where-object {$_.Isenabled -eq $true -and $_.name -like "*backup*"}if ($_.IsEnabled -eq $True -and $_.name -match "*backup*"){ $JobsExecuting = 0 foreach ($job in $jobs) { #if the job is running if ($job.CurrentRunStatus.ToString() -ne "Idle") { write-host "you hit inner loop" $jobExecuting = 1 write-host "The backup job is running ..." break } } if($JobsExecuting -eq 0) { #if the job is not executing write-host "no jobs are running on $($srv)" #disable the job $jobs.IsEnabled = $TRUE $jobs.Alter() }}else{write-host "$($srv.name) has running jobs, try again later" -foregroundcolor Red}[/code]Fri, 13 Apr 2012 13:30:23 GMTSQLQuest29http://www.sqlservercentral.com/Forums/Topic1281769-1351-1.aspxI'm running into an issue where I can't access the following registry key remotely, either via Powershell or xp_regread. I can see it in Regedit or with powershell when I'm logged onto the box. This isn't on all servers but it does happen on the ones I'm more interested in. If I back the key up to "Microsoft SQL Server" I can get sub keys but not all are listed and it lists a couple that aren't actually there. Any idea what's going on?[quote]$MachineName = "server"$type = [Microsoft.Win32.RegistryHive]::LocalMachine$regkey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $MachineName)$basePath = "SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL"$regKey = $regKey.OpenSubKey($basePath)$regKey.GetSubKeyNames()Foreach($val in $regKey.GetValueNames()){ Write-Host $val.PadRight(30) -nonewline Write-Host $regKey.GetValue("$val")}[/quote]Wed, 11 Apr 2012 10:58:38 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1282812-1351-1.aspxHi All,I am trying to automate logshipping failover process using powershell.I can do a last tail log backup on primary and disable the backup job.the last tail log will be name like -- db_name_OriginalPrimarytail_yyyymmdd.trnOnly thing I am stuck is onHow can I check if all the logs are being restored on the secondary and if they are not then run the copy and load job on secondary using powershell ?I am fine with bringing the secondary databases online.And then how can I have the script to accept an input e.g failback Y or N and then do the failback process ?I know the failback process.Appreciate your help :-)Thu, 12 Apr 2012 15:30:19 GMTSQLQuest29http://www.sqlservercentral.com/Forums/Topic1281799-1351-1.aspxI'm trying to write a script to loop through a series of sql instances, execute a t sql statement. (this on in particular is adding operators)[code="sql"]function Get-SqlCommand{ param($serverName,$databaseName,$query) $SqlConnection = New-Object System.Data.SqlClient.SQLConnection $SqlConnection.ConnectionString = "Server=$servername;Database=$databasename;Integrated Security=SSPI;" $SqlConnection.Open() $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $query $SqlCmd.ExecuteNonQuery() $SqlConnection.Close() } #Get-SqlCommand[/code]However, I'm receiving this error message:[code="other"]Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery: Connection property has not been initialized."At C:\Documents and Settings\jds015\My Documents\operatorcreator.ps1:46 char:28+ $SqlCmd.ExecuteNonQuery <<<< () + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException[/code]Stepping through the code, I can verify that my parameters are correct. What else am I missing for this?Wed, 11 Apr 2012 11:47:35 GMTjshurakhttp://www.sqlservercentral.com/Forums/Topic1281045-1351-1.aspxI'm using this bit of code in a powershell script to see if a job is enabled:[code="plain"] $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver; foreach ($job in $srv.Jobserver.Jobs) $jobEnabled = $job.IsEnabled;[/code] However that value always comes back as "true". I've disabled the job and I've disabled the schedule for that job but still comes back as true. All the other properties seem to be correct (name, LastRunDate, etc.) Am I doing something wrong or does anyone have any suggestions?Thanks!DerekTue, 10 Apr 2012 12:39:57 GMTDerek Gemmenhttp://www.sqlservercentral.com/Forums/Topic1274785-1351-1.aspxI have a FileSystemWatcher app that is watching a folder for any file creations events and when theya are raised I am attempting to open the file and complete a hash of its contents to be saved out to a database. The problems start when the file is locked by some other processes. I receive an access file error and the app fails to complete the hash. Does any one know of some way to ping the file and be alerted to when it is available to be opened so a hash can be completed?Thanks,SeanWed, 28 Mar 2012 18:03:47 GMTTeraByteMehttp://www.sqlservercentral.com/Forums/Topic1268483-1351-1.aspxI have the two script below, the first responses to a windows file creation event on a specified folder, and then writes data to a log file. The second, does a hash on a file and writes to the console. I have tried many different ways to get the windows file creation script to call the hash script but not having any luck. Been trying to pass the $path and $name variables from the first to the second. Ultimately I want to have the file creation data being combined with the hash data (for the specified file that was created) and have that all write inline to the log file. Hoping someone can see the tree through the forest where as I can't? Thanks# Below is the function for created file events on folderfunction get-newfiles{$folder = 'C:\Scripts'$filter = '*.*' # <-- set this according to your requirements$destination = '<full path to the destination folder>'$fsw = New-Object IO.FileSystemWatcher $folder, $filter -Property @{ IncludeSubdirectories = $true # <-- set this according to your requirements NotifyFilter = [IO.NotifyFilters]'FileName, LastWrite'}$onCreated = Register-ObjectEvent $fsw Created -SourceIdentifier FileCreated -Action { $path = $Event.SourceEventArgs.FullPath $name = $Event.SourceEventArgs.Name $changeType = $Event.SourceEventArgs.ChangeType $timeStamp = $Event.TimeGenerated $outfile = "$name, $changeType, $timeStamp" $outfile | out-file c:\scripts\outcreatefile.txt -append}}--------------------------------------------------------------# Below is the hash table script-functionfunction Get-MD5([System.IO.FileInfo] $file = $(throw 'Usage: Get-MD5 [System.IO.FileInfo]')){ $stream = $null; $cryptoServiceProvider = [System.Security.Cryptography.MD5CryptoServiceProvider]; $hashAlgorithm = new-object $cryptoServiceProvider $stream = $file.OpenRead(); $hashByteArray = $hashAlgorithm.ComputeHash($stream); $stream.Close(); ## We have to be sure that we close the file stream if any exceptions are thrown. trap { if ($stream -ne $null) { $stream.Close(); } break; } return [string]$hashByteArray;}Fri, 16 Mar 2012 13:09:26 GMTTeraByteMehttp://www.sqlservercentral.com/Forums/Topic1269817-1351-1.aspxThe 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([size]/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(([size]-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” $serverforeach ($db in $sqlserver.Databases){invoke-sqlcmd -Query $query -database $db.name -ServerInstance $sqlserver.name -IgnoreProviderContext}}Tue, 20 Mar 2012 13:21:24 GMTchris.brown 6880http://www.sqlservercentral.com/Forums/Topic1263392-1351-1.aspxI have been working on a project that soon will need to be run from a server through an SSIS package. I am trying to anticipate the System Administrator questions I am going to get. How are PowerShell scripts and processing handled typically in a secure company network? My understanding is that the PowerShell process will be run from the SQL Server Agent. I am not clear about other things and considerations.ThanksWed, 07 Mar 2012 19:15:49 GMTTeraByteMehttp://www.sqlservercentral.com/Forums/Topic1260521-1351-1.aspxI'm looking for a way to script our deployments. We have multiple databases and our deployments include dropping and creating stored procedures and creating new tables. Currently we do this manually via SQL Server Management Studio. As we grow and add databases, this is unsustainable. Is deployment best done via PowerShell, via SSIS, via something else? Can you point me to a resource I can use to learn more? ThanksThu, 01 Mar 2012 16:31:34 GMTscreenshothttp://www.sqlservercentral.com/Forums/Topic1257430-1351-1.aspxI currently have a ps script that provides me with the SQL Server Agent job information from a failed/successful job perspective. Currently the data that I am returning contains, Job Name, Job Last Run, Job Last Run Date, Job Next Run Date, Job Enabled, and the Job Schedule. The Job Schedule part is what I am having trouble with. I would like to return the job frequency such as Daily, Weekly and etc. I am not quite sure how to do this. Anybody have any pointers or know of a good site that may have this information?Fri, 24 Feb 2012 07:56:43 GMTBrandon Carl Goodmanhttp://www.sqlservercentral.com/Forums/Topic1257509-1351-1.aspxHiI am using the following code from All White's article [url]http://sqlblog.com/blogs/allen_white/archive/2009/10/09/performance-data-gathering.aspx[/url] to collect performance data.[code="plain"]$mab = New-Object System.Diagnostics.PerformanceCounter$mab.CategoryName = 'Memory'$mab.CounterName = 'Available MBytes'$mabv = $mab.NextValue()[/code]I have added the following line to get this data for a remote machine $mab.MachineName = $srv. This works fine for some servers but on other servers it fails with the following messageException calling "NextValue" with "0" argument(s): "Category does not exist."At C:\Users\rparlapalli\documents\powershell\scripts\getperf.ps1:141 char:29+ $mabv = $mab.NextValue<<<< () + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodExceptionIf I log into the machine (that fails) and specify the machineName as the current machine name it works fine. Its only when I run it remotely it fails. The fact that it is working on some and failing on others tells me this could be a setting I am missing on some machines. The Execution-Policy on machine that is working and not working is Remote-Singed.Thanks for looking into this. Let me know if you need any other info.Fri, 24 Feb 2012 09:37:00 GMTSQLbackhttp://www.sqlservercentral.com/Forums/Topic1255602-1351-1.aspxHi Iam trying to run my SSIS package in 32 bit. Please help me with the script to execute the packages.Thanks for your helpSajinTue, 21 Feb 2012 14:35:13 GMTdemin99http://www.sqlservercentral.com/Forums/Topic1252017-1351-1.aspxHi All,I am stumbling on how to connect to SybaseASE and get data out using powershell.I came across this http://techjunkie.tv/connect-to-sybase-using-powershell and it works fine.[quote]$query="select * from syslogins"$conn=New-Object System.Data.Odbc.OdbcConnection$conn.ConnectionString= "driver={Adaptive Server Enterprise};dsn=SERVER_NAME;db=master;na=IP,PORT;uid=SOMEUSER;pwd=******;"$conn.open()$cmd=new-object System.Data.Odbc.OdbcCommand($query,$conn)$cmd.CommandTimeout=30write-host $query$ds=New-Object system.Data.DataSet$da=New-Object system.Data.odbc.odbcDataAdapter($cmd)write-host $ds$da.fill($ds)$ds.Tables[0] | out-gridview$conn.close()[/quote]This gives a grid, but I want to have the output on the powershell window/screen.Also want to pipe that output to a sql table.Any one have idea how to do that .I am using Powershell v2 and ASE 15.03Tue, 14 Feb 2012 09:53:12 GMTSQLQuest29http://www.sqlservercentral.com/Forums/Topic1254111-1351-1.aspxHello,I am having the hardest time with the below script. I have a log file that contains text like this:2/16/2012 12:27:32 AM -- Found file 1776ELIG_20120215.txt for Processing -- Group 17762/16/2012 12:27:32 AM -- File Copied -- Group 17762/16/2012 12:27:38 AM -- 27376 Records IN: 0 A, 0 E, 0 N, 0 F -- Group 17762/16/2012 12:37:42 AM -- 0 Errors found in Eligiblity Load -- Group 1776I want to get the results from my script to come back in the format:DateTime FileName GroupNumber Errors---------------- -------------------- ------------------- ----------------------2/16/2012 12:27:32 AM 1776ELIG_20120215.txt 1776 0Below is the script I currently have.$path = pushd C:\Scripts$path$eligibility = @()$data = Get-Content -Path "resultsfile.txt"for ($i = 0; $i -lt $data.Length; $i += 3){ $name = ($data[$i] -split 'Found file ')[1] $group = ($data[$i] -split 'Processing -- Group ')[1] $DateTime = ($data[$i] -split '[ -- ]')[1] $eligibility += New-Object PSObject -Property @{ FileName = $name GroupNumber = $group DateTime= $Time }}$eligibility | Format-TableBTW, I have looked at using match and but don't know how to implement it with my current script (-split).This works for date pattern recognition:"2/16/2012 12:42:55 AM" -match "\b\d\D\d\d\D\d\d\d\d\s\d\d\S\d\d\S\d\d\s\D\D\b"Any assistance is greatly appreciated!SeanFri, 17 Feb 2012 12:06:55 GMTTeraByteMehttp://www.sqlservercentral.com/Forums/Topic1253397-1351-1.aspxHello,I am trying to parse through a log file searching for lines that contain strings. The below scripts works to pull all line that have "Found file" in it but I can't get it to retrieve conditions on other lines (i.e. "Records IN" & "File copied"). How can I modify the below to pull lines with those characters strings in them as well?$path = pushd \\Eldorado\el\Processing\IVR_New\Log$files = Dir -filter *.txt | Where-Object { $_.CreationTime -gt (Get-Date).AddDays(-2) } ForEach ($file in $files) { Get-Content $file | Select-String -pattern "Found file"| Format-Table -property Line } Thanks,SeanThu, 16 Feb 2012 13:14:26 GMTTeraByteMehttp://www.sqlservercentral.com/Forums/Topic1253167-1351-1.aspxHi all, I made a script to read a list of computers from a txt file. then run a WMI query against them to print out what Cores and logical processors they have. The script for some reason keeps erroring on some boxs but i dont understand why. Any ideaS? Script is below:[code="other"]$Computers = Get-Content -Path 'C:/SQLInstances.txt'$Computers | % { $property = "systemname","maxclockspeed", "numberOfCores", "NumberOfLogicalProcessors" Get-WmiObject -ComputerName $_ -class win32_processor -Property $property | Select-Object -Property $property }[/code]The error i get is:Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)At line:6 char:5+ Get-WmiObject -ComputerName $_ -class win32_processor -Property $property | ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Get-WmiObject], COMException + FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands.GetWmiObjectCommandExample of what errors: I have a 2 node cluster It reports on the passive node. But fails on the active node. I have some VMWare boxs that report back and others that dont. There is only 10 servers total in the list. Any ideas? Thanks Thu, 16 Feb 2012 08:08:38 GMTstebennettsjbhttp://www.sqlservercentral.com/Forums/Topic1250654-1351-1.aspxI have this idea for work that I can somehow parse existing log files that record the processing of files. There is an application that processes files and writes entries to a log file. Things like "file found", "processing file", "file name", "processed date/time". I want to use PowerShell to parse through these logs and if comes to "file found" read the next lines that correspond with that file processing and preferably write them to a table in a database or to another log for processing later. I am pretty familiar with how to write to a log file or table but not in how to use PowerShell to iterate through a log file parsing and writing output based off conditions.Has any one had experience in implementing PS in this type of way? Any helpful links or suggestions you can share?ThanksSeanFri, 10 Feb 2012 19:50:39 GMTTeraByteMehttp://www.sqlservercentral.com/Forums/Topic1243847-1351-1.aspxHello. I have a questionConsider[code]# Open Connection to Server # $conn = New-Object System.Data.SqlClient.SqlConnection "Server=$s;Database=$d;Integrated Security=SSPI;"; $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message }; $conn.add_InfoMessage($handler); $conn.FireInfoMessageEventOnUserErrors = $true; $conn.Open(); # Invoke Stored Procedrue # $cmd = $conn.CreateCommand(); $cmd.CommandText = "execute TestProc DatabaseThatDoesNotExist"; $res = $cmd.ExecuteNonQuery(); $conn.Close();[/code]All of that is in a try...catch. If I run it as is, forcing an error (passing in DatabaseThatDoesNotExist to TestProc), I see the error text returned to the client, but control is not transferred to the catch block. If I remove the $handler lines (indented), then the error fires and control does go to the catch block, but I don't see the text of the SQL Server error message in the transcript. Is it possible to both see the error text, and have control pass to the catch block?Thank you.Mon, 30 Jan 2012 12:27:38 GMTJason Bunnhttp://www.sqlservercentral.com/Forums/Topic1242936-1351-1.aspxIf I run this it scripts them out all into one file. Can this be adjusted to put them in to individual files or is there a better way to do this?Add-PSSnapin SqlServerCmdletSnapin100Add-PSSnapin SqlServerProviderSnapin100[system.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.sqlserver.smo’) |out-nullCLSCD SQLSERVER:\SQL\OM37345\DEFAULT\LinkedServersGCI | %{$_.script()} | out-file "C:\LinkedServers.sql"Fri, 27 Jan 2012 08:55:48 GMTMichael Robeyhttp://www.sqlservercentral.com/Forums/Topic1159453-1351-1.aspxWe are using VDI virtual machine desktops, and we are told *not* to write to the C: drive because it will get recreated every time our administrator pushes out a new virtual machine. Unfortunately, the $pshome PowerShell 2.0 variable points to "C:\Windows\System32\WindowsPowerShell\v1.0" [sic], which means that we will lose the contents of the profile script on the next virtual machine rollout if we create a script there.How can I edit the $pshome variable so that I can point it to a persistent disk instead of the default location on the C: drive? I've tried using the Windows Control Panel to define as an environment variable at a user level (not system level). cmd.exe [sic] can see the variable, but PowerShell refuses to use it. I don't have the required permissions to add a system level variable, but I don't see pshome defined at the system level anyway. However, PowerShell has to be picking up the defintion from somewhere...Any ideas?Fri, 12 Aug 2011 12:14:32 GMTshewhttp://www.sqlservercentral.com/Forums/Topic1240480-1351-1.aspxcreate a c:\servers.txt file with list of serversand copy the following into a file .ps1enjoyfunction getwmiinfo ($svr) { gwmi -query "select * from Win32_ComputerSystem" -computername $svr | select Name, Model, Manufacturer, Description, DNSHostName, Domain, DomainRole, PartOfDomain, NumberOfProcessors, SystemType, TotalPhysicalMemory, UserName, Workgroup | export-csv -path .\$svr\BOX_ComputerSystem.csv -noType gwmi -query "select * from Win32_OperatingSystem" -computername $svr | select Name, Version, FreePhysicalMemory, OSLanguage, OSProductSuite, OSType, ServicePackMajorVersion, ServicePackMinorVersion | export-csv -path .\$svr\BOX_OperatingSystem.csv -noType gwmi -query "select * from Win32_PhysicalMemory" -computername $svr | select Name, Capacity, DeviceLocator, Tag | export-csv -path .\$svr\BOX_PhysicalMemory.csv -noType gwmi -query "select * from Win32_LogicalDisk where DriveType=3" -computername $svr | select Name, FreeSpace, Size | export-csv -path .\$svr\BOX_LogicalDisk.csv –noType}function get-databasescripts {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.Databases | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "Databases.sql") }function get-backupdevices {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.BackupDevices | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "BackupDevices.sql") }function get-triggers {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.Triggers | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "Triggers.sql") }function get-endpointscripts {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.EndPoints | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "EndPoints.sql") }function get-errorlogs {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.ReadErrorLog() | export-csv -path $($directoryname + "Box_errorlogs.csv") -noType}function get-sqlagentscript {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.JobServer | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "sqlagentscript.sql") }function get-jobscripts {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.JobServer.Jobs | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "jobs.sql") }function get-linkscripts {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.LinkedServers | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "linkedservers.sql") }function get-userlogins {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.Logins | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "logins.sql") }function get-roles {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.Roles | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "roles.sql") }function get-alerts {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.JobServer.Alerts | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "alerts.sql") }function get-operators {[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver$srv.JobServer.Operators | foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "operators.sql") }$servers = get-content c:\servers.txtforeach ($server in $servers){ if (!(Test-Path -path .\$server)) { New-Item .\$server\ -type directory }$directoryname = "c:\downloads\DRTest\" + '\' + $server + '\' $sqlserver = $server$serverfilename = $servergetwmiinfo $serverget-databasescriptsget-errorlogsget-triggersget-backupdevicesget-endpointscriptsget-sqlagentscriptget-jobscriptsget-linkscriptsget-userloginsget-operatorsget-alerts}Mon, 23 Jan 2012 13:54:00 GMTgenehunter29009http://www.sqlservercentral.com/Forums/Topic1237184-1351-1.aspxWe are having around 100 servers and we need below information1) Find number of databases in each instance2) Find on which date last backup happened3) Get the location of the last backup file4) Space remaining on the backup foldersIf you have any powershell scripts handy for this, could you please share it.Tue, 17 Jan 2012 06:45:19 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1239183-1351-1.aspxHaving some bizarre issues with Power Shell. I have 4 physical servers: Srv1, Srv2, Srv3 and Srv4. Each server is divided into 2 virtual nodes with 1 SQL Server Instance each: SQL1V\Inst1, SQL2V\Inst2, SQLV3\Inst3 and SQLV4\Inst4.What I have is a dynamic Power Shell script that builds the application database. It does a check to see what instance it's on so that a database does not get built on the incorrect instance. Everythin has been working fine until today. I used the script and deployed App1DB on SQL1V\Inst1, App2DB on SQL2V\Inst2 and App3DB on SQL3V\Inst3.However, on the Srv4 servers it failed. Long story short this portion of the script returned an empty name:get-item "SQLRegistration\Database Engine Server Group" | Select NameOn Srv1 and Srv2 (it doesn't matter which one I run that command) I get both instancesget-item "SQLRegistration\Database Engine Server Group" | Select NameName-----Srv1\Inst1Srv2\Inst2When I run it on Srv3 and Srv4 I only getName-----Srv3\Inst3But on Srv4 nothing. Just as interesting,If I look at SQL Congifuration Manager, on SRV3 (where Inst3 is currently running) is shows Inst3 as stopped and Inst4 running.On Srv4 (where Inst4 is running), the opposite: That is Inst3 running and Inst4 is stopped.That makes me think it's not a Power Shell thing but something possibly misconfigured within one of the intances.Thoughts?Thu, 19 Jan 2012 20:18:02 GMTScalability Doughttp://www.sqlservercentral.com/Forums/Topic1237666-1351-1.aspxHello Everyone,Hope all is well.I have a question on shutting down the applications running on a remote servers. By applications I meant they are services running from a console on remote servers and all I want to is to stop the service. Currently I am logging in to the server and hitting an enter key to stop this service running from console but the problem is I have almost like 30 servers and I had to logon to each inividual server and hit enter, so I want to do this through psexec. On the remaining servers I have VB 6 and VB 8 applications running which I want to stop as well(I dont want to kill anything but stop). I was thinking if there are any commands in powershell that I can use and have psexec invoke them on remote servers.Did anyone come up with such a scenario. I need your valuable inputs.Thanks a bunch in advance.Tue, 17 Jan 2012 22:27:36 GMTSapenhttp://www.sqlservercentral.com/Forums/Topic1202532-1351-1.aspxHi all,I was trying to develop a script that will delete old backup files in a backup device/file directory if1) backup files that older than 7 days 2) backup folder contain more than 2 backup files(each database has its own subfolder)The script need to run daily on the backup device server, which is not a SQL server.The reason for requirement 2) is that if a backup job failed for a few days, we don't want to delete the old backup files.The requirement 1) is easy..but I don't know how to implement 2)..Tue, 08 Nov 2011 15:50:59 GMTShuannahttp://www.sqlservercentral.com/Forums/Topic1236604-1351-1.aspxI want to make a script which insert data into the database of the principal server. Does somebody have experience to do that with Invoke-SQLCMD or with SMO ? How must the connection string look like ?Mon, 16 Jan 2012 07:21:23 GMTFecker Elmarhttp://www.sqlservercentral.com/Forums/Topic1231229-1351-1.aspxHi,I am trying to insert some data from a PS command into a table, but getting this error:"a positional parameter cannot be found that accepts argument 'system.object '" Any ideas?Thanks.$serverName = "MyServer" $databaseName = "DBMonitor"$Connection = New-Object System.Data.SQLClient.SQLConnection$Output = New-Object WMISearcher$Connection.ConnectionString ="Server=$serverName;Database=$databaseName;trusted_connection=true;"$Connection.Open()$Command = New-Object System.Data.SQLClient.SQLCommand$Command.Connection = $Connection$Item = @("DeviceId", "MediaType", "Size", "FreeSpace")$Output = Get-WmiObject Win32_logicaldisk Format-Table DeviceId, MediaType, Size, FreeSpace -auto foreach ($row in $Output) {$Command.CommandText ="INSERT into DiskSpace ([Drive], [MediaType], [Size], [FreeSpace]) VALUES ('$($Output.DeviceId)', '$($Output.MediaType)', '$($Output.Size)', '$($Output.FreeSpace)')"$Command.ExecuteNonQuery() | out-null} $Connection.Close() Thu, 05 Jan 2012 19:41:08 GMTRoust_mhttp://www.sqlservercentral.com/Forums/Topic1181586-1351-1.aspxI have a simple PS script which reads the first line of a text file:param($File = "C:\SSIS\Import\ACME1_ExternalEvents_Test.txt")$FirstLine = (get-content $File)[0]Write-Output $FirstLineThis script is called from a SSIS process task which appears to function correctly. I have set the process task to use a 'Standard Output Variable' of @User::FirstLine.There is a further SQL control flow task which makes use of this variable and creates a table.The SQL task works correctly when I specify the @User::FirstLine variable directly but fails when I try to use the value when it is set by the powershell process task.How do I associate the output of the PS script with the the 'Standard Output Variable' of the process task? I think it's a problem with my lack of understanding of Powershell.:(Tue, 27 Sep 2011 03:41:22 GMTKevin O'Connor