InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 00:51:30 GMT20http://www.sqlservercentral.com/Forums/Topic1463926-1351-1.aspxHeh... First time I've had to admit being a "newbie" in a long time so be gentle with me. I might not even know enough to ask the right questions.I managed to pick up the following PS script from the internet. Lot's of you know what it is. It gets some disk space information from every disk device for the given computer. I've removed all field formatting just to keep it super simple.[code="plain"]Get-WmiObject Win32_LogicalDisk -computer 'SomeComputerName' | Select SystemName,DeviceID,VolumeName,Size,FreeSpace | Format-Table[/code]What I'd like to do is write the output to a table in a given SQL Server. I've Googled for answers and have found some (IMHO) very over complicated methods. Some are generic (which is very cool) but not what I need. I just want to write this one output to a table.The table for this example is simple as well (I've removed all but the necessary columns including a Date/Time column)...[code="sql"] CREATE TABLE dbo.DiskSpace ( SystemName VARCHAR(128), DeviceID CHAR(2), VolumeName VARCHAR(128), Size BIGINT, FreeSpace BIGINT );[/code]I realize that we're going to need a connection to the database. I'd like it to be a trusted connection so we don't have to hardcode a user name or password. Assume that the server name is "TestServer" and that the database name is "TestDB". From what I've been able to read up on, the business of the connection would look something like this (please correct it if I'm wrong)...[code="plain"]$conn=new-object System.Data.SqlClient.SQLConnection $ConnectionString = "Server=TestServer;Database=TestDB;Integrated Security=True;Connect Timeout=0"$conn.ConnectionString=$ConnectionString $conn.Open()...something goes here but I don't know what... $conn.Close() [/code]Like I said, I'm brand-spanking-new to PowerShell. I sure could use some help on how to get the data from the first script above into the table that I posted the CREATE TABLE code for without having to build a bunch of functions like in the following link.[url]http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx[/url]Thanks for the help, folks. And, yeah... if you have a good book recommendation for the syntax and how to "start thinking in PowerShell" like I do in T-SQL, it would also be much appreciated.Sat, 15 Jun 2013 22:40:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1461569-1351-1.aspxI am trying to run a Powershell script, which opens and modifies an Excel spreadsheet, from a SQL Agent job step, . The script runs fine by itself, and from BIDS. I have tried running as an SSIS pkg with a proxy, and when that didn't work I setup the step to simply run the Powershell script.This is the error I receive:A job step received an error at line 17 in a PowerShell script. The corresponding line is '$Workbook=$objExcel.Workbookds.Open("\\files\reporting\Dailyreport_MMDDYYYY.xls",0,$False,1,"password")'Correct the script and reschedule the job.The error information returned by PowerShell is: "Exception calling "Open" with "5" arguments(s)."Microsoft Excel cannot access the file "\\files\reporting\Dailyreport_MMDDYYYY.xls".There are several possible reasons: The file name or path does not exist [i](Yes, it does!)[/i]The file is being used by another program [i](No, it’s not!)[/i]The workbook you are trying to save has the same name as a currently open workbook." [i](No again, no workbooks open)[/i]Process Exit Code -1. The step failedIt appears that it does not want to open Excel, but why?FYI: 64-bit System, 64-bit SQL 2008 R2, 32-bit Office 2010. I have already tried adding "Desktop" to SysWoW64 and editing my DCOM settings, with no change in results.Thanks,LornaMon, 10 Jun 2013 07:58:15 GMTLrobinson 93181http://www.sqlservercentral.com/Forums/Topic1457468-1351-1.aspxI'm a total noob with posh :-) I got it powershell running and then hacked together this script.I was following the article about powershell restores (https://www.simple-talk.com/sql/backup-and-recovery/backup-and-restore-sql-server-with-the-sql-server-2012-powershell-cmdlets/).I made a script based on this, but I had some further needs not met in the article.First, SingleUserMode. Maybe it's just my environment, but restores of our big DB doesn't work without this. Application just keeps making connections. Second, I have to perform some actions I've currently figured was easiest done with TSQL. Here I'm just looking at the last restore date.In reality, I have to reset passwords, remove/add logins, attach logins to users, sometimes run a special script to change some default settings... Just a few executenonquery type of things.I'll also have to add moving the backup file as well or as a separate method/function. Whats it called when you encapsulate in powershell?First, if there is a 100% powershell way to do most of these things, I'd love to learn it!But I'd also love to learn how one would best run a query with results and without results against a server.And that last bit? That's a total hack on displaying the information. Can anyone point to a better way to displaying it? [code="plain"]cls#import SQL Server moduleImport-Module SQLPS -DisableNameChecking# Sets context for getting a db reference.# I use the reference to change the user access and verify the backup date after the restoreSet-Location SQLSERVER:\SQL\(server)\(instance)\Databases\(database)$dbName = "db"$db = Get-Item .echo $db.UserAccess# The next few lines handle changing the databases user access mode which prevents restoring due to active connections# I run this if the db is set to Multiple (it normally is)#$db.UserAccess = "Multiple"$db.UserAccess = "Single"$db.Parent.KillAllProcesses($dbName)$db.Alter()Restore-SqlDatabase -Database $dbName -BackupFile "(backup path).bak" -ReplaceDatabase # Here and below is my TSQL hammer approach to solve the problem of verifing the restore date on the DB we're attempting to restore.# I'm sure that this can be done more elegantly within Posh and even using a TSQL query, this has to be a hacky way to display the output of a known schema result.cls$BackupTimeQuery = @"SELECT TOP 1 destination_database_name As dbname, restore_date, user_name As RestoredBy FROM msdb.dbo.restorehistory RH WHERE RH.destination_database_name = '$dbName' ORDER BY restore_date DESC;"@<# Commented out the StringCollection as I don't need it for a single query# Setting up StringCollection to use with ExecuteWithResults for multiple queries. $sqlQuery = new-object System.Collections.Specialized.StringCollectionQuery to find the most recent backup of the DB being restored. $sqlQuery.Add("")#>$ds = $db.ExecuteWithResults($BackupTimeQuery)# Please don't anyone use this as an example, but please do suggest a better way to display multiple fields of a single row returned within Posh.Foreach ($t in $ds.Tables){Foreach ($r in $t.Rows){Foreach ($c in $t.Columns){Write-Host $c.ColumnName "=" $r.Item($c)}}}[/code]Tue, 28 May 2013 11:54:08 GMTShawnTherrienhttp://www.sqlservercentral.com/Forums/Topic1455628-1351-1.aspxWhat better place to solve a nagging Powershell issue than good ol' SQL Server Central :)I'm trying to retrieve disk usage data from servers on my domain. Server list is pulled from a simple text file on the local computer, each server is then queried for disk information, data is saved in a table, data is written to a database table.This was taken from somewhere on the internet and tweaked to work in my environment, and it has worked splendidly until I was asked to incorporate additional servers that had hyphenated names.[code="other"]#define servers to be monitored$server = get-content C:\<path>\serverList.txt#data table to hold resultsFunction out-DataTable { $dt = new-object Data.datatable $First = $true foreach ($item in $input){ $DR = $DT.NewRow() $Item.PsObject.get_properties() | foreach { if ($first) { $Col = new-object Data.DataColumn $Col.ColumnName = $_.Name.ToString() $DT.Columns.Add($Col) } if ($_.value -eq $null) { $DR.Item($_.Name) = "[empty]" } elseif ($_.IsArray) { $DR.Item($_.Name) =[string]::Join($_.value ,";") } else { $DR.Item($_.Name) = $_.value } } $DT.Rows.Add($DR) $First = $false } return @(,($dt))}#function to retrieve disk informationFunction Get-DisksSpace ([string]$Servername, $unit= "GB"){$measure = "1$unit"Get-WmiObject -computername $serverName -query "select SystemName, Name, DriveType, FileSystem, FreeSpace, Capacity, Label from Win32_Volume where DriveType = 2 or DriveType = 3" `| select SystemName ` , Name ` , @{Label="SizeIn$unit";Expression={"{0:n2}" -f($_.Capacity/$measure)}} ` , @{Label="FreeIn$unit";Expression={"{0:n2}" -f($_.freespace/$measure)}} ` , Label}#execute the functionsforeach ($s in $server){#Write what is being retrievedGet-DisksSpace $s#Load into table and database$dataTable = Get-DisksSpace $s | where {$_.name -like "E:\*" -or $_.name -like "C:\*"} | out-DataTable$connectionString = "Data Source=<Server\Instance>; Integrated Security=True;Initial Catalog=<DestinationDB>;"$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString$bulkCopy.DestinationTableName = "<Schema.Table>"$bulkCopy.WriteToServer($dataTable)}[/code]This returns all the information requested except for a system name for hyphenated servers. If you run just the Get-WmiObject cmdlet, two different results are returned.[code="other"]Get-WmiObject -computername computername -query "select SystemName from Win32_Volume where DriveType = 2 or DriveType = 3" [/code]Returns[quote]__GENUS : 2__CLASS : Win32_Volume__SUPERCLASS : __DYNASTY : __RELPATH : __PROPERTY_COUNT : 7__DERIVATION : {}__SERVER : __NAMESPACE : __PATH : SystemName : [b]COMPUTERNAME[/b][/quote][code="other"]Get-WmiObject -computername computer-name -query "select SystemName from Win32_Volume where DriveType = 2 or DriveType = 3" [/code]Returns[quote]__GENUS : 2__CLASS : Win32_Volume__SUPERCLASS : __DYNASTY : __RELPATH : __PROPERTY_COUNT : 1__DERIVATION : {}__SERVER : __NAMESPACE : __PATH : SystemName : [/quote]I've tried surrounding the computer-name with single and double quotes. I've tried escaping the hyphens with backquotes " ` ". Has anyone else ran into this issue before?Wed, 22 May 2013 11:42:53 GMTcalvohttp://www.sqlservercentral.com/Forums/Topic1414803-1351-1.aspxI have SSRS 2008R2 reporting having 3 data sources.Say reportname = \Marketing\Test.rdlDataSource name = \Shared DataSources\Data1 , 2 ,3Now when deploy the report using reports manager upload functionality , after deployment it looses all three connection reference.is there any powershell script to set again correct datasource for this 1 report.Fri, 01 Feb 2013 12:28:07 GMTbangsqlhttp://www.sqlservercentral.com/Forums/Topic1453793-1351-1.aspxThe below script provides the list of trace files available to the location : D:\Trace$fileEntries = [IO.Directory]::GetFiles("D:\Trace\"); foreach($fileName in $fileEntries) { [Console]::WriteLine($fileName); }I want to pass each trace files to the below block to change the trace files compatible to SQL 2008 R2 format.# Enter your filename here$fileName = "D:\Trace\XXXX.trc" # The version information we want to write: 0x0A = 10 = SQLServer 2008[Byte[]] $versionData = 0x0A# The offset of the version information in the file$offset = 390 [System.IO.FileMode] $open = [System.IO.FileMode]::OpenOrCreate$stream = New-Object System.IO.FileStream -ArgumentList $fileName, $open$stream.Seek($offset, [System.IO.SeekOrigin]::Begin);$stream.Write($versionData, 0, $versionData.Length);$stream.Close()Kindly assist....Thu, 16 May 2013 15:04:39 GMTSQL-DBA-01http://www.sqlservercentral.com/Forums/Topic1452724-1351-1.aspxI wanted to gather Active Directory data into SQL Server table to analyze permissions on critical data. I wanted to use a SQL Server Agent Job that would refresh a couple of time per day. I also wanted it to be as clean and portable as possible, so I tried to run everything within the server itself and not rely on external resources which tend to complicate matters. This task seems pretty straight forward, however due to limitations in the OLE DB driver for Microsoft Directory Services and limitations in Powershell it has become quite a chore. The first problem I ran into was trying to bring in "description" from AD using a linked server. The query returned the error: Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.Upon further examination I found that the "description" field in AD is a "multi value" field and the OLE DB provider doesn't know what to do with it so it errors. I then turned to Powershell which has been held up as the future of Microsoft product integration. Though it's a powerful tool, and did exactly what it was supposed to while running in a Command Prompt, once I put it into a SQL Job it threw a syntax error because the job engine could not parse the following line of code: $Command.CommandText = "INSERT INTO Test(Name, Title, Phone, description) Values('$($Name)', '$($Title)', '$($Phone)', '$($description)')"I guess I will have to create an external .PS1 file and run it as an Operating System (CMDExec) file unless someone has a better suggestion.Tue, 14 May 2013 10:52:06 GMTmlemay-957463http://www.sqlservercentral.com/Forums/Topic1450153-1351-1.aspxHi,i'm trying to get the default sql server data and log file path using the below script...[quote]param($ClientName,$DESTINATIONDB)$SqlConnection = New-Object System.Data.SqlClient.SqlConnection$SqlCmd = New-Object System.Data.SqlClient.SqlCommand$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter$DataSet = New-Object System.Data.DataSet$SqlConnection.ConnectionString = "Server = $ClientName; Database = '$DESTINATIONDB'; Integrated Security = True"$SqlCmd.CommandText = "select f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and d.name = 'master'" $SqlCmd.Connection = $SqlConnection$SqlAdapter.SelectCommand = $SqlCmd$SqlAdapter.Fill($DataSet)|out-nullforeach ($Row in $Dataset.Tables[0].Rows){ $dev = $($Row[0]) $dev }[/quote]i'm getting the output like thisC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdfC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldfi would like to replace the name of master in the above path my paramter $destinationDB.Please let me knowthanks in advanceTue, 07 May 2013 07:57:18 GMTRobin35http://www.sqlservercentral.com/Forums/Topic1448608-1351-1.aspxto search stored procedures for server names?we have the list of server names, but not sure if we must script the procedures out and search for server names from the server name list on the file system or is it possible to use the server list and search the procedures traversing each db on a server?thanks a lotdrew(powershell illiterate)Wed, 01 May 2013 14:15:50 GMTdrew.georgopuloshttp://www.sqlservercentral.com/Forums/Topic1448615-1351-1.aspxHi,I'm trying to pull the logical and physical file names from sql server database using powershell and add those values in exisiting xml file...This whole process is for database restore...we don't do direct sql db restore..we use third party tool called Commvault...that is the reason for restore automation...Here's my code....its just a part of it....param([Parameter(Mandatory="True")][String]$ClientName,[Parameter(Mandatory="True")][String]$Sourcedatabase#[Parameter(Mandatory="True")][String]$Destdatabase)$SqlConnection = New-Object System.Data.SqlClient.SqlConnection$SqlCmd = New-Object System.Data.SqlClient.SqlCommand$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter#$DataSet = New-Object System.Data.DataSet$SqlConnection.ConnectionString = "Server = $ClientName; Database = $RestoreSource; Integrated Security = True"$SqlCmd.CommandText = "select d.name dbname,f.name logicalfilename,f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and d.name = '$Sourcedatabase' and f.type = 0 " $SqlCmd.Connection = $SqlConnection$SqlAdapter.SelectCommand = $SqlCmd$SqlAdapter.Fill($DataSet)|out-null$dbs = $DataSet.Tables[0]$dbs $SqlConnection.Close()In the above sql server i'm just trying to pull data file with type = 0..its working fine...if i put type =1 i can get log file info....please let me know how to get data and log file information, such that i can path those information into xml file ...like below<device>|SourceDB|#12!DestinationDB|#12!SourceLogicaldatafilename|#12!Destinationdatafilepath|#12!Sourcedatafilepath</device><device>|SourceDB|#12!DestinationDB|#12!SourceLogicallogfilename|#12!Destinationlogfilepath|#12!Sourcelogfilepath.ld </device>all fields needs to filled accordingly...if we have more than one data file...then it hsould populate n number of device tags....Please help me on this...thanks....let me know if you have any questions...Wed, 01 May 2013 14:43:37 GMTRobin35http://www.sqlservercentral.com/Forums/Topic1446248-1351-1.aspxI'm using OLA's SQL JOBs to do my database maintenance. So far, I really like them; they're very handy, especially when apps create new databases each month (WILDCARD dbnames!).I have to copy files across server instances on occasion; since we're deploying SQL2008r2 I've started using Powershell to do this.I've got a powershell job step that works fine to get bak files from my old servers to new servers.But now I have to copy a backup created by the Ola backup process... it includes $ in the path (as servername$instancename) and at the start of the backup file name.It LOOKS like the $ is confusing Powershell...Is there a control character I can use to allow the "$" into the path/filename, or is there [b]another [/b]way to do this?[code="plain"]#Example: (this is a powershell step in a sql job)# get yesterdays date as YYYYMMDD$dd = (get-date).AddDays(-1).ToString("yyyyMMdd")# here's the original source/target location for the backup file#$from = "\\srvrname\r$\SQL\MSSQL10_50.instname\MSSQL\Backup\srvrname$instname\DBNAME\FULL\srvrname$instname_DBNAME_FULL_" + $dd + "*.BAK"#$to = "\\SA01SQL002\u$\SQL\MSSQL10_50.TEST\MSSQL\Backup\from$instname\"#and here's the error on the copy-item line:# 'An object at the specified path \\srvrname\r$\SQL\MSSQL10_50.instname\MSSQL\Backup\srvrname\DBNAME\FULL does not exist.'# ********# whoops... that should say \srvrname$instname\## that blows up so tried wrapping $from and $to within a double quote... this didn't fly either.$from = """" + ...TheOriginalString... + """"$to = """" + ...TheOriginalString... + """"# the error references this line copy-item $from $to -Force -Recurse[/code]Wed, 24 Apr 2013 15:34:17 GMTmstjeanhttp://www.sqlservercentral.com/Forums/Topic1443064-1351-1.aspxHi,I use the below script to script out FKs in a database:#DECLARE TIMESTAMP FOR THE FILES$timestamp = Get-Date -Format yyyy-MM-dd#SCRIPTSL SQLSERVER:\SQL\'MyServer'\DEFAULT\Databases\'MyDB'\Tables$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions$so.IncludeIfNotExists = 1$so.SchemaQualify = 1$so.SchemaQualifyForeignKeysReferences = 1$so.ScriptSchema = 1dir | foreach {$_.ForeignKeys} | foreach {$_.Script()} > "Z:\MyDB\03_FKs $timestamp .sql"The result I get looks like this:ALTER TABLE [MySchema].[MyTable1] WITH CHECK ADD CONSTRAINT [FK_MyTable1_MyTable2_ID] FOREIGN KEY([ID])REFERENCES [MyTable2] ([ID])ON DELETE CASCADEI am getting an error when using the above script:Msg 1767, Level 16, State 0, Line 1Foreign key 'FK_MyTable1_MyTable2_ID' references invalid table 'MyTable2'.Msg 1750, Level 16, State 0, Line 1Could not create constraint. See previous errors. The reason for this is because MyTable2 belongs to a schema other then 'dbo', so the generated script should look like this:ALTER TABLE [MySchema].[MyTable1] WITH CHECK ADD CONSTRAINT [FK_MyTable1_MyTable2_ID] FOREIGN KEY([ID])REFERENCES [b][MySchema].[/b][MyTable2] ([ID])ON DELETE CASCADEAny ideas?Thanks. Quick ReplyTue, 16 Apr 2013 19:26:25 GMTRoust_mhttp://www.sqlservercentral.com/Forums/Topic1442977-1351-1.aspxHi,I have a project to automate SQL server restore.........use restore db's using third party tool called Simpana, Commvault.....we would like to automate the restore using powershell....i can restore the database using commnd prompt and powershell but to automate its pretty complicated.We usually do restore in Simpana using its GUI...and Simpana has some commands for restore...and when using GUI we have an option to save the restore process as script...it saves as .xml file and .dat file.... To restore the database using powershell we need to use that xml file or we can create a dynamic xml file using that format..i was able to do that....here is the dynamic xml file that i'm generating...i need to pass parameters in to this xml file e.g., db name, destination db name ,source server,dest server Please let me know if anyone have any questions or need more information....$template_Power = "<TMMsg_CreateTaskReq> <taskInfo> <task> <taskFlags> <disabled>false</disabled> </taskFlags> <policyType>DATA_PROTECTION</policyType> <taskType>IMMEDIATE</taskType> <initiatedFrom>COMMANDLINE</initiatedFrom> <alert> <alertName></alertName> </alert> </task> <associations> <backupsetName>defaultBackupSet</backupsetName> <subclientName></subclientName> <clientName></clientName> <appName>SQL Server</appName> <instanceName></instanceName> </associations> <subTasks> <subTask> <subTaskType>RESTORE</subTaskType> <operationType>RESTORE</operationType> </subTask> <options> <restoreOptions> <browseOption> <commCellId>2</commCellId> <backupset> <clientName></clientName> <backupsetName>defaultBackupSet</backupsetName> </backupset> <timeRange/> <noImage>true</noImage> <useExactIndex>false</useExactIndex> <mediaOption> <library/> <mediaAgent/> <drivePool/> <drive/> <copyPrecedence> <copyPrecedenceApplicable>false</copyPrecedenceApplicable> <synchronousCopyPrecedence>0</synchronousCopyPrecedence> <copyPrecedence>0</copyPrecedence> </copyPrecedence> <proxyForSnapClients> <clientName></clientName> </proxyForSnapClients> </mediaOption> <timeZone> <TimeZoneName>(GMT-05:00) Eastern Time (US & Canada)</TimeZoneName> </timeZone> <listMedia>false</listMedia> </browseOption> <destination> <destClient> <clientName></clientName> </destClient> <destinationInstance> <clientName></clientName> <appName>SQL Server</appName> <instanceName></instanceName> </destinationInstance> </destination> <sqlServerRstOption> <dbOnly>false</dbOnly> <overWrite>true</overWrite> <pointOfTimeRst>false</pointOfTimeRst> <sqlRestoreType>DATABASE_RESTORE</sqlRestoreType> <sqlRecoverType>STATE_RECOVER</sqlRecoverType> <stopStartSSA>false</stopStartSSA> <preserveReplicationSettings>false</preserveReplicationSettings> <stopMarkRestore>false</stopMarkRestore> <stopBeforeMarkRestore>false</stopBeforeMarkRestore> <partialRestore>false</partialRestore> <logShippingOnly>false</logShippingOnly> <ffgRestore>false</ffgRestore> <ignoreFullBackup>false</ignoreFullBackup> <vSSBackup>false</vSSBackup> <device>|DBA_Rep2|#12!DBA_Rep2_Test|#12!DBA_Rep|#12!D:\MSSQL\DATA\DBA_Rep2_Test.mdf|#12!D:\MSSQL\DATA\DBA_Rep2.mdf</device> <device>|DBA_Rep2|#12!DBA_Rep2_Test|#12!DBA_Rep_log|#12!L:\MSSQL\LOG\DBA_Rep2_Test_log.ldf|#12!L:\MSSQL\LOG\DBA_Rep2_1.ldf</device> <restoreSource>DBATest</restoreSource> <database>DBATest</database> <timeZone> <TimeZoneName>(GMT-05:00) Eastern Time (US & Canada)</TimeZoneName> </timeZone> <keepDataCapture>false</keepDataCapture> </sqlServerRstOption> <fileOption> <mapFiles/> </fileOption> <commonOptions> <detectRegularExpression>true</detectRegularExpression> <restoreDeviceFilesAsRegularFiles>false</restoreDeviceFilesAsRegularFiles> <restoreSpaceRestrictions>false</restoreSpaceRestrictions> <ignoreNamespaceRequirements>false</ignoreNamespaceRequirements> <skipErrorsAndContinue>false</skipErrorsAndContinue> <onePassRestore>false</onePassRestore> <revert>false</revert> <recoverAllProtectedMails>false</recoverAllProtectedMails> <isFromBrowseBackup>false</isFromBrowseBackup> <clusterDBBackedup>false</clusterDBBackedup> </commonOptions> </restoreOptions> <adminOpts> <updateOption/> </adminOpts> <commonOpts> <startUpOpts> <startInSuspendedState>false</startInSuspendedState> <priority>66</priority> <useDefaultPriority>true</useDefaultPriority> </startUpOpts> <prePostOpts> <preRecoveryCommand></preRecoveryCommand> <postRecoveryCommand></postRecoveryCommand> <impersonation> <!--PrePost Command Impersonation Level--> <level>NO_SELECTION</level> <user> <userName></userName> </user> </impersonation> <runPostWhenFail>false</runPostWhenFail> </prePostOpts> <!--User Description for the job--> <jobDescription></jobDescription> </commonOpts> </options> </subTasks> </taskInfo></TMMsg_CreateTaskReq>"$template_XML > C:\Program Files\Commvault\simpana\Base\template_restXML.xmlTue, 16 Apr 2013 13:36:13 GMTRobin35http://www.sqlservercentral.com/Forums/Topic1362060-1351-1.aspxHi all,I use Powershell to deploy DB scripts. In one script file, I have French accented characters.When I call Invoke-sqlcmd and pass the script to the -inputfile, the French characters get mangled.In the bad old days, the workaround was to save the files as unicode, and sqlcmd would handle them OK.How do I go about this in this brave new world?Thanks!PaulThu, 20 Sep 2012 09:14:49 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1441890-1351-1.aspxSo I am exploring PowerShell, and found a DB with .Databases that I can't see in sys.databases.I ran this following (starting from the Machine level with sqlps):[code="other"] $i = ls $i | %{$_.Databases}[/code]It returns a list of all the DBs, but there is one that says that the state is Normal, Standyby.I don't see it through SSMS, and it isn't in sys.databases.What could cause this and what should I do about that?Fri, 12 Apr 2013 13:33:31 GMTdkschillhttp://www.sqlservercentral.com/Forums/Topic1437570-1351-1.aspxI am attempting to run a sql script with 4 simple INSERT statements. Each of the 4 INSERTS is inserting a regexp as an NVARCHAR string. 2 of them have within the string the combination of "$(" without quotes of course and 2 of them don't.I am holding the script contents in a variable and then executing nonquery, but I keep getting an error for the INSERTS containing the "$(". I know this is a sqlcmd replacement variable combination so i assume something similar is going on here in PoSH.Can anyone provide a better way to do this or provide a workaround?Snippet:FUNCTION runSql () { $dbName = "someDb" $tSql = "path\somesql.sql" [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.SMO") | Out-Null$srv = NEW-OBJECT Microsoft.SqlServer.Management.Smo.Server("someServer")$db = New-Object Microsoft.SqlServer.Management.Smo.Database($srv, $dbName)$db.Create() $sr = New-Object System.IO.StreamReader($tSql) $script = $sr.ReadToEnd() $extype = [Microsoft.SQLServer.Management.Common.ExecutionTypes]::ContinueOnError $db.ExecuteNonQuery($script, $extype)}INSERTS causing issues:INSERT INTO [dbo].[regexes] ([regex]) VALUES (N'%(NS)(.[^\^]{1,12}|\^)\^*(.[^\$]+)\$(.[^\^\$]+|\${1,35})\$*\w*\^(.[^\^]{1,29}|\^)\^*\?;636013(\d+)=\d+=\?#"\s(\w+\s\w+)\s+')INSERT INTO [dbo].[regexes] ([regex]) VALUES (N'%(TX)(.[^\^]{1,12}|\^)\^*(.[^\$]+)\$(.[^\^\$]+|\${1,35})\$*\w*\^(.[^\^]{1,29}|\^)\^*\?;636015(\d+)=\d+\?#"\s(\d+)\s')Mon, 01 Apr 2013 12:35:45 GMThfxDBAhttp://www.sqlservercentral.com/Forums/Topic1435992-1351-1.aspxI have the following powershell script:$html_file_dir = "C:\Scripts"$cvs_file = "C:\Scripts\SpaceFiles\DiskSpace_20130327060003.csv"$loadFile = "$cvs_file"$dbServer = "."$dbName = "ServerSpaceReports"#DBServer ,DBName$constring = "Data Source=$dbServer;Initial Catalog=$dbName;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;"#Data Source=.;Initial Catalog=ServerSpaceReports;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;#"\Package.Variables[User::PlanID].Properties[Value]";"1"$DTexecPath = "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec"#cd $DTexecPath$setPackageVars = "/Set \Package.Variables[""CSVFile""].Value;" + """$loadFIle"""$command = " /F C:\scripts\Package.dtsx $setPackageVars"$commandDTExec.exe $command When I run this I get an error:Option " /F C:\scripts\Package.dtsx /Set \Package.Variables[CSVFile].Value;C:\Scripts\SpaceFiles\DiskSpace_20130327060003.csv" is not valid.If I take the same thing and run it from a command line it runs successfully.I have no idea why this may be failing can any one help me out.Thanks GaryWed, 27 Mar 2013 09:42:19 GMTgary.mazzonehttp://www.sqlservercentral.com/Forums/Topic1432751-1351-1.aspxI found a sql server inventory script that seems to be available on several sites. It actually seems to generate all of the reports but fails on the SqlDataAdapter fill method whether named pipes is turned on or not. If I remote desktop to the server and run it I don't get this error.[b]fill : Exception calling "Fill" with "1" argument(s): "A network-related or instance-specng a connection to SQL Server. The server was not found or was not accessible. Verify thahat SQL Server is configured to allow remote connections. (provider: Named Pipes Providerction to SQL Server)"At C:\powershell\GetServerInfo3.ps1:27 char:9+ $da.fill <<<< ($ds) | Out-Null + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException[/b][url=https://www.simple-talk.com/sql/database-administration/let-powershell-do-an-inventory-of-your-servers/][/url][url=http://colleenmorrow.com/2012/05/31/building-a-sql-server-inventory-part-2/][/url]Tue, 19 Mar 2013 09:52:15 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic1182082-1351-1.aspxI'm in the process of writing a powershell script to inventory my SQL Server environment. In collecting the server information, I'd like to grab the physical hostnames that make up any clustered environments. I know I can use $nodes=get-clusternode -Cluster SERVERNAME |select nameto get the physical servers in the cluster, but if I run that on a non-clustered server, I get an error, obviously. So, can someone recommend a good way to determine whether the SERVERNAME I've connected to is a cluster resource group or a physical stand-alone server?Tue, 27 Sep 2011 14:14:37 GMTColleen M. Morrowhttp://www.sqlservercentral.com/Forums/Topic1426055-1351-1.aspxWhile it would be easy to set the execution policy to unrestricted, or possibly set it to unrestricted in an agent job, do the work, then set back to restricted, I'm wondering what the best practices are.Restricted - No scripts can be run. Windows PowerShell can be used only in interactive mode.AllSigned - Only scripts signed by a trusted publisher can be run.RemoteSigned - Downloaded scripts must be signed by a trusted publisher before they can be run.Unrestricted - No restrictions; all scripts can be run.Sun, 03 Mar 2013 18:26:04 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic1423349-1351-1.aspxI'm trying to learn PowerShell. So I did a [b]Get-Help Get-Printer -examples[/b] and saw that I can issue the command [b]Get-Printer -ComputerName[/b] [i]<computer name>[/i]. So, that's what I tried to do, but it doesn't work in my home network. Is this because I only have a peer-to-peer workgroup at home? Do commands like [b]Get-Printer -ComputerName[/b] [i]<computer name>[/i] only work in Active Directory domains?Sat, 23 Feb 2013 08:46:35 GMTDoctor Who 2http://www.sqlservercentral.com/Forums/Topic1421657-1351-1.aspxHi, I am running a script to collect a count of all errors in the current error log using smo, as below:$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "MYSERVER"$logs = $sqlServer.ReadErrorLog() | where {($_.Text -like "Error*" -or $_.Text -like "*Fail*") -and ($_.Text -notlike "*Found 0 Errors*")};$count = $logs.Count$countand then dumping this into a monitor database.This works fine for most servers, but I have a 2005 cluster that for some reason won't return the count. If I just read the error log into console that works fine, but the .Count method disappears completely from the object. It is like, for this server, the object returned from $sqlServer.ReadErrorLog() is different to the rest of the servers.Any ideas?Thanks for reading.Tue, 19 Feb 2013 07:57:40 GMTSimon D Richardshttp://www.sqlservercentral.com/Forums/Topic1419634-1351-1.aspxI'm calling an command line .exe from within a PoSh script. This is generating a hit Y for Yes / N for No choice. How can I send a Y in my PoSh script?Thanks!Wed, 13 Feb 2013 09:59:54 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1414179-1351-1.aspxI have written tons of Windows Batch scripts and compiled VB6, however not as much VBScript. That said, I seen have entire books about managing Windows with VBScript and I know there are many people out there that use it a lot. PowerShell being the newest kid on the block I am not aware of anything VBScript offers that PowerShell does not. Maybe manipulating Windows GUI components? If I were learning a new scripting language for Windows from the ground up in hopes of having it become my one-stop-shop for all my scripting needs is there a specific reason or use-case where you might recommend VBScript or Windows Batch over PowerShell?Thu, 31 Jan 2013 08:46:20 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1406945-1351-1.aspxHello,I searched for this on here and google and didn't see an exact answer to my question, so sorry if it's been asked and answered before. If I run a query using Invoke-Sqlcmd and put it in a variable: $results = Invoke-Sqlcmd -Query $query -ServerInstance $QueryServer -Database $QueryDB;and I get multiple rows back, I see that $results has a type of System.Array. I can get a count of the number of returned results by using $results.Length.However if I get only 1 row back, the Type is Syste.Data.DataRow and $results.Length is NULLI've gotten around this by creating a new variable called ResultsLen and doing the following:$resultsLen = $results.<field in query>.Length$resultsLen += $results.Lengthbut this doens't really work, as the $results.<field in query>.Length is a count of the number of characters in the returned field. this works for what I need...which is just to run a IF statement to only run some code if there are 1+ records returned in the query...but I'm wondering if there is a better way of doing it?Thanks.Mon, 14 Jan 2013 14:17:34 GMTEric Zierdthttp://www.sqlservercentral.com/Forums/Topic1404624-1351-1.aspxAn invalid reporting option was specified. Only E, W, I, C, D, P, V, and N are allowedImport Data Source from Oracle to SQL Server2008 password Oracle have '[' how solutionthanksWed, 09 Jan 2013 02:49:40 GMTmai0165http://www.sqlservercentral.com/Forums/Topic1385899-1351-1.aspxGurus, I am trying to write a code to send out list of failed jobs in the last 24 hours. I am using out-string to convert my table to string. I am able to get email with list of jobs. But, i would like to see the e-mail in a table format rather than a string format. Here is code. Please help me out for converting my code to send email in HTML format. Thank you in advance. [code="plain"]#Set-ExecutionPolicy RemoteSigned############################################################################ Declare ServerName, DatabaseName and TableName to Get Server List###########################################################################$SourceServerName = 'local'$SourceDatabaseName = 'dbadb'#$SourceTablename = 'LookUp_ServerList_NonProd'############################################################################ Declare Variables for Sending E-mails###########################################################################$ToRecipient = "********@email.com"$From = "*********@email.com"$SMTPServer = "smtp.email.com"$GetDate = get-date -format g############################################################################ Create SqlConnection object and define connection string###########################################################################$SQLCon = New-Object System.Data.SqlClient.SqlConnection$SQLCon.ConnectionString = "Server=$SourceServerName; Database=$SourceDatabaseName; Integrated Security=true"############################################################################ Create SqlCommand object, define command text, and set the connection###########################################################################$SQLCmd = New-Object System.Data.SqlClient.SqlCommand$SQLCmd.CommandText = "SELECT SQLServerInstanceNameFROM [dbo].[LookUp_SQLServerInstanceList_NonProd] AS LUSSILNPINNER JOIN [dbo].[LookUp_ServerList_NonProd] AS LUSLNPON [LUSSILNP].[ServerID] = [LUSLNP].[ServerID]WHERE [SqlPingFlag] = 1"$SQLCmd.Connection = $SQLCon############################################################################ Create SqlDataAdapter object and set the command###########################################################################$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter$SqlDataAdapter.SelectCommand = $SQLCmd############################################################################ Create and fill the DataSet object###########################################################################$DataSet = New-Object System.Data.DataSet$SqlDataAdapter.Fill($DataSet, "SQLServerInstanceName") | Out-Null############################################################################ Close the connection###########################################################################$SQLCon.close()############################################################################ Function for sending E-mails to ********@email.com###########################################################################Function SendEmail{ #param($strTo, $strFrom, $strSubject, $strBody, $smtpServer) param($To, $From, $Subject, $Body, $smtpServer) $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $msg.From = $From $msg.To.Add($To) $msg.Subject = $Subject $msg.IsBodyHtml = 1 $msg.Body = $Body $smtp.Send($msg)}############################################################################ Declare Database and query###########################################################################$SQLDatabaseName = "msdb"$SQLQueryText = "SELECT [S2].[name], [dbo].[agent_datetime]([run_date] , [run_time]) AS RunDateFROM dbo.[sysjobhistory] AS SINNER JOIN dbo.[sysjobs] AS S2ON [S].[job_id] = [S2].[job_id]WHERE [dbo].[agent_datetime]([run_date] , [run_time]) >= GETDATE() - 1 AND ([run_status] IN ( 0 , 3 ) AND [S].[step_id] = 0)"################################################################################################### Iterate through the dataset to Run the query on Remote Server and send Results as E-Mails##################################################################################################foreach ($row in $DataSet.tables["SQLServerInstanceName"].rows){ $DestinationServerName = $row.SQLServerInstanceName #RunSQLQuery -SQLServerName $DestinationserverName -SQLDatabaseName $SQLDBName -SQLQuery $SQLQueryText $SQLConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $DestinationServerName; Database = $SQLDatabaseName; Integrated Security=true" $SqlCommand = New-Object System.Data.SqlClient.SqlCommand $SqlCommand.CommandText = $SQLQueryText $SqlCommand.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCommand $SQLDataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($SQLDataSet) | Out-Null $SqlConnection.Close() $Results = $SQLDataSet.Tables | Format-Table -Auto Name, RunDate | out-string $CountRows = $SQLDataSet.Tables[0].Rows.Count #$Text = @"Following Jobs have Failed:"@ $BodyText = ("$Results") IF($CountRows -ne 0 ) { $DestinationServerName $Subject = "$DestinationServerName - SQL Job Failed - $GetDate" #$Body = $TableResults SendEmail -To $ToRecipient -From $From -Subject $Subject -Body $BodyText -BodyASHTML -Auto Name -smtpServer $SMTPServer }}[/code]Fri, 16 Nov 2012 21:16:12 GMTjvkondapallihttp://www.sqlservercentral.com/Forums/Topic1328482-1351-1.aspxHi All,Please have a look at the below link and help me to get the details (as 4 Columns) into a SQL Server table instead of Mail as I can make a SSR report from the data. SERVER = JOB = LASTRUN = LASTRUNDATE = http://www.sqlservercentral.com/blogs/sqlsandwiches/2012/01/29/find-failed-sql-jobs-with-powershell/RegardsSrikanth Reddy KundurWed, 11 Jul 2012 12:02:38 GMTksrikanth77http://www.sqlservercentral.com/Forums/Topic1393651-1351-1.aspxThis may be a totally off the wall question, but it seems like it should be possible to do. Just can't find any specifics as to how I might accomplish it.I have a situation in that I have the same database (structurally) across multiple SQL Server instances, with different database names. Through SSMS I have a local server group defined that references each unique instance and individual database. This allows me query each database at once with a single SQL statement, regardless of database name.When queries are run through my SSMS environment, I have the option enabled to return the SQL Server instance in the grid output so the instance name is returned with each row. I would like to automate running my query and outputting the results to Excel via Powershell script.What I'm hung up on is that I need to be able (with Powershell) to reference my SSMS local server group as the the data source that the query runs against, and run the query. Has anyone done this? I appreciate any feedback..Thu, 06 Dec 2012 10:53:08 GMTdtibz01http://www.sqlservercentral.com/Forums/Topic1398741-1351-1.aspxGreetings,I have a script that was executing a powershell script from within a batch file with the following syntax:"“powershell.exe -NoLogo -Noninteractive -Command "& \"%~dp0ScriptName.ps1\" %1; exit "Everything was working fine until the OS team patched the server and upgraded Powershell to version 3 (it had version 2). After Powershell was upgraded the script failed with the error:"powershell.exe' is not recognized as an internal or external command, operable program or batch file"For the time being I have "fixed" the issue by executing powershell with an explicit path:"C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe -NoLogo -Noninteractive -Command "& \"%~dp0ScriptName.ps1\" %1; exit"Now, this change may make my script functional again, but I don't understand what exactly is going on. What changed when Powershell was upgraded that made the way I was calling the script invalid? Any information or good links to information would be greatly appreciated.Thanks!KimWed, 19 Dec 2012 19:59:44 GMTkstjacqueshttp://www.sqlservercentral.com/Forums/Topic1386480-1351-1.aspxI have a script that I'm adapting from something I found on the internet where I'm getting database size information and insert that into a table I've created. I've been working with PS for a couple of months, but I'm still a bit green. Below is the section of relevant code - I looked at this Don Jones article [url=http://technet.microsoft.com/en-us/magazine/hh289310.aspx]here[/url] to get an idea how to do this. I'm not sure what the -f is for and he doesn't address it in the article. In any case, the variables have the right data in them at this point in the script (verified with the debugger) but the INSERT command is not working. That is, no data is in the table after the script is completed. No error is thrown, so I'm wanting to use Try...Catch to find out what the problem is. Does anyone have a recommendation as to what to put in the catch in this case? The info I've found on the internet is pretty generic and I don't know what kind of exception to interrogate for a SQL Server command.[code]$cmd.commandtext = "INSERT INTO dbo.db_sizes (Server, DatabaseName, DataFileName, DataSize, DataUsedSpace) VALUES('{0}','{1}','{2}','{3}', '{4})" -f$dbname, $mdfInfo.Name, $mdfInfo.FileName, ($mdfInfo.size / 1000), ($mdfInfo.UsedSpace / 1000)Try { $cmd.executenonquery() }catch { }[/code]Mon, 19 Nov 2012 10:08:05 GMTDel Leehttp://www.sqlservercentral.com/Forums/Topic1392510-1351-1.aspxHiI am trying to run a script that queries the last full backup file for a specific database. Invoke line formated for ease of reading.[code="plain"]# Script is ran on the SQL server (not remotely)# Instance is called sql-instance# database is called sql-dataimport-module SQLPS -disableNameChecking$instanceName = "$($env:computername)\sql-instance"$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName$lastBackupFile = Invoke-Sqlcmd -Query " SELECT TOP 1 msdb.dbo.backupmediafamily.physical_device_name FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' and msdb.dbo.backupset.database_name = 'sql-data' ORDER BY msdb.dbo.backupset.backup_finish_date desc"[/code]The result I get with this is in a table format:[code="plain"]physical_device_name--------------------C:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSQL\Backup\full_sql-data_20121101.bak[/code]Is there any way to just get the result and not the heading stuff?I need just the path and filename for using elsewhere in the script.I tried searching around but only found articles that give the table format and I don't know enouth to trim this down with confidence.Thanks in advanceTue, 04 Dec 2012 08:00:53 GMTDrenlinhttp://www.sqlservercentral.com/Forums/Topic1385788-1351-1.aspxHi. I have a script that looks at the sysjobsteps table to find SSIS script locations and then copies them out to disk for disaster recovery. It works fine but I am making an assumption in the script that all scripts will be found on drive C. This is currently true but it could change without notice. My current script lines are like this:$Server = 'server1'$job ='FILE "C:\Projects\Scripting Restart.dtsx" /CHECKPOINTING OFF /REPORTING E'$URL = $job -replace 'FILE "C:',"\\$Server\C`$" -replace 'dtsx"','dtsx'$URLThe only method I can think of is to use a subscript like this. I haven't made it work yet but I'm sure it's possible.$Drive = $job.Substring(6,1)$DriveIt's getting pretty convoluted. Does anybody have a better way to handle this? I should be able to pass a masking character but I can't figure out how. Thanks in advance for any ideas you can offer.Judy S.Fri, 16 Nov 2012 10:58:06 GMTJudy Scheinukhttp://www.sqlservercentral.com/Forums/Topic1382156-1351-1.aspxI am trying to use a SQL Table to loop through SQL Instances and pass the Instance to a script:$DestinationSQLServer = 'coaidera01\idera'$DestinationDBName = 'CTM_Monitoring'$DestinationQry = "select InstanceName from COA_SQLServerInstanceInfo WHERE InstanceName Like 'coaidera01\idera'"$SQLSvr = get-SqlData -sqlserver $DestinationSQLServer -dbname $DestinationDBName -qry $DestinationQry$SQLSvr | foreach { $myserver = $_$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $myserver;------------------------------------------------------------------------------I get this error: The following exception was thrown when trying to enumerate the collection: "Failed to connect to server System.Data.DataRow.".Any CluesWed, 07 Nov 2012 13:27:13 GMTgeorge.auckland 44813http://www.sqlservercentral.com/Forums/Topic1128582-1351-1.aspxI get the following error when I launch powershell from SSMS, after right-clicking on a remote SQL instance and choosing "Start powershell":[quote]Warning: Could not obtain SQL Server Service information. An attempt to connect to WMI on server1 failed with the following error: access is denied.[/quote]How can I fix this?Mon, 20 Jun 2011 15:11:36 GMTMarios Philippopouloshttp://www.sqlservercentral.com/Forums/Topic1378132-1351-1.aspxDear All,I am very much new to power shell scripting where i need to find out what version of SQL Server is running on it and on which port the DB is running on the remote windows box.Could you pls help us to script this out in powershell.Regards,GangadharaMon, 29 Oct 2012 01:14:59 GMTGangadhara MS http://www.sqlservercentral.com/Forums/Topic1371508-1351-1.aspxHi,I have 2 Array or ArrayList objects, with data like[code="vb"]$Results = New-Object system.Collections.ArrayList$Results.Add("IMP_Trn_Project_Agent_6,User1")$Results.Add("IMP_Trn_Project_Agent_6,User2")$Results.Add("IMP_Trn_Project_Agent_6,User3")$Remove = New-Object system.Collections.ArrayList$Remove.Add("User2")[/code]How can I efficiently remove from the first array all items containing the users in the 2nd array?PaulThu, 11 Oct 2012 07:59:17 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1371080-1351-1.aspxI [strike]want[/strike] need to understand why the code below returnsalpha ----- Y Can someone explain why I don't see alpha-----XYBonus points: if the last 2 lines are$cAlphas = $Objects | select alpha | Get-Unique$cAlphas why do I get alpha-----XThanks![code="VB"]$Objects = @()$Object = New-Object system.object$Object | Add-Member -Type NoteProperty -Name alpha -value "X"$Object | Add-Member -Type NoteProperty -Name Num -value "1"$Objects += $Object$Object = New-Object system.object$Object | Add-Member -Type NoteProperty -Name alpha -value "X"$Object | Add-Member -Type NoteProperty -Name Num -value "2"$Objects += $Object$Object = New-Object system.object$Object | Add-Member -Type NoteProperty -Name alpha -value "Y"$Object | Add-Member -Type NoteProperty -Name Num -value "1"$Objects += $Object$Object = New-Object system.object$Object | Add-Member -Type NoteProperty -Name alpha -value "Y"$Object | Add-Member -Type NoteProperty -Name Num -value "2"$Objects += $Object$cAlphas = $Objects | select alpha $cAlphas | Sort-Object -Unique[/code]Wed, 10 Oct 2012 12:36:59 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1371025-1351-1.aspxIs there a way to list the unique table constraints on a given table using smo/powershell?Or do I have to query the system views?Thanks!Wed, 10 Oct 2012 09:58:02 GMTschleephttp://www.sqlservercentral.com/Forums/Topic1366411-1351-1.aspxHi guys, I am new to powershell, can anyone please help me in undertanding powershell. I googled about powershell. But i dint get any clear idea about it. I would like to know about, for what purpose we are using powershell. And i need to use powershell for deployment. Please help me on this.Mon, 01 Oct 2012 03:19:23 GMTvijayarani87.s