We have numerous applications using SQL Login accounts . Simple powershell scripts saves time for rotating passwords for SQL Logins.
We have numerous applications using SQL Login accounts . Simple powershell scripts saves time for rotating passwords for SQL Logins.
Backup-Zip-Copy-Unzip-Restore SQL Server Databases: I am sure we all try to bring databases from One Environment to Another Environment whether its to accomplish testing, debugging or maintaining the parity between production and lab. I used to spend little bit of time…
Copied from here. http://blogs.technet.com/b/sqlthoughts/archive/2008/10/03/out-sql-powershell-function-export-pipeline-to-a-new-sql-server-table.aspx http://tools.perceptus.ca/text-wiz.php http://dnhlmssql.blogspot.com/2013/05/from-excel-to-insert-into-another.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 |
############################################################################## ## ## out-sql.ps1 ## ## by Alexey Yeltsov, Microsoft Corp. ## Raju Venkataraman Added some fixes for Table creation and Inserts ## Export pipeline contents into a new SQL table ## ## Parameters: ## $SqlServer - SQL Server ## $Database - Database name ## $Table - Table name ## $DropExisting - Drop $Table if it already exists and recreate it ## (default $false) ## $RowId - Add identity column named $RowId and make it a primary key. ## (default "RowID". Can pass $null if identity is not needed) ## ## ## Examples: ## ## #First, load the function ## . .\out-sql.ps1 ## ## #Export processes to table Process in database Scratch on local sql server ## get-process | out-sql -SqlServer . -database Scratch -table Process -dropexisting $true ## ## #Export volume details from 4 servers into a table ## @("Server1","Server2","Server3","Server4") ` ## | % {$Server = $_ ; Get-WMIObject Win32_Volume -computer $Server } ` ## | Select-Object ` ## SystemName, ` ## Name, ` ## @{Name="CapacityGb";Expression={[math]::truncate($_.Capacity / 1Gb)}}, ` ## @{Name="FreeGb";Expression={[math]::truncate($_.FreeSpace / 1Gb)}} ` ## | out-sql -sqlserver . -database Scratch -table DiskVolume -dropexisting $true ## ## ## ############################################################################## $DebugPreference = "Continue" function Out-Sql($SqlServer=$null,$Database=$null,$Table=$null,$DropExisting=$false,$RowId="RowID") { begin { $Line = 0 [string]$CreateTable = "" if(-not $SqlServer) { throw 'Out-Sql expects $SqlServer parameter' } if(-not $Database) { throw 'Out-Sql expects $Database parameter' } if(-not $Table) { throw 'Out-Sql expects $Table parameter' } if($DropExisting) { write-debug "Note: If the table exists, it WILL be dropped and re-created."} $SqlConnectionString = " Provider=sqloledb;" + " Data Source=$SqlServer;" + " Initial Catalog=$Database;" + " Integrated Security=SSPI;" write-debug "Will open connection to SQL server ""$SqlServer"" and will populate table ""$Table.""" write-debug "Connection string: `n$SqlConnectionString" $SqlConnection = New-Object System.Data.OleDb.OleDbConnection $SqlConnectionString $SqlCommand = New-Object System.Data.OleDb.OleDbCommand "",$SqlConnection $SqlConnection.Open() } process { $Line ++ $Properties = $_.PSObject.Properties if (-not $Properties) { throw "Out-Sql expects object to be passed on the pipeline. The object must have .PSObject.Properties collection." } #if we're at the first line, initialize the table if ($Line -eq 1) { #initialize SQL connection and create table if($DropExisting) { $CreateTable += "IF OBJECT_ID('$Table') IS NOT NULL DROP TABLE $Table;`n"} $CreateTable +="CREATE TABLE $Table ( `n" $col = 0 if ($RowId) { $col++; $CreateTable +="$RowId INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED `n" } foreach($Property in $Properties) { $col++; if ($col -gt 1) { $CreateTable +="," } # In below, why not use "if ($Property.Value -is [datetime])"? # Because access can be denied to the value, but Property.TypeNameOfValue would still be accessible! if ($Property.TypeNameOfValue -eq "System.DateTime") { $CreateTable +="["+"$($Property.Name)"+"]"+" DATETIME NULL `n" } else { $CreateTable +="["+"$($Property.Name)"+"]"+" NVARCHAR(MAX) NULL `n" } } $CreateTable +=")" write-debug "Will execute SQL to create table: `n$CreateTable" $SqlCommand.CommandText = $CreateTable $rows = $SqlCommand.ExecuteNonQuery() } #Prepare SQL insert statement and execute it $InsertStatement = "INSERT $Table VALUES(" $col = 0 foreach($Property in $Properties) { $col++; if ($col -gt 1) { $InsertStatement += "," } #In the INSERT statement, do speacial tratment for Nulls, Dates and XML. Other special cases can be added as needed. if (-not $Property.Value) { $InsertStatement += "null `n" } elseif ($Property.Value -is [datetime]) { $InsertStatement += "'" + $Property.Value.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'`n" } elseif ($Property.Value -is [System.Xml.XmlNode] -or $Property.Value -is [System.Xml.XmlElement]) { $InsertStatement += "'" + ([string]$($Property.Value.Get_OuterXml())).Replace("'","''") + "'`n" } else { $InsertStatement += "'" + ([string]$($Property.Value)).Replace("'","''") + "'`n" } } $InsertStatement +=")" write-debug "Running insert statement: `n $InsertStatement" $SqlCommand.CommandText = $InsertStatement $rows = $SqlCommand.ExecuteNonQuery() } end { write-debug "closing SQL connection..." $SqlConnection.Close() } } function Import-Excel([string]$FilePath, [string]$SheetName = "") { $csvFile = Join-Path $env:temp ("{0}.csv" -f (Get-Item -path $FilePath).BaseName) if (Test-Path -path $csvFile) { Remove-Item -path $csvFile } # convert Excel file to CSV file $xlCSVType = 6 # SEE: http://msdn.microsoft.com/en-us/library/bb241279.aspx $excelObject = New-Object -ComObject Excel.Application $excelObject.Visible = $false $workbookObject = $excelObject.Workbooks.Open($FilePath) SetActiveSheet $workbookObject $SheetName | Out-Null $workbookObject.SaveAs($csvFile,$xlCSVType) $workbookObject.Saved = $true $workbookObject.Close() # cleanup [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbookObject) | Out-Null $excelObject.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObject) | Out-Null [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() # now import and return the data Import-Csv -path $csvFile } function FindSheet([Object]$workbook, [string]$name) { $sheetNumber = 0 for ($i=1; $i -le $workbook.Sheets.Count; $i++) { if ($name -eq $workbook.Sheets.Item($i).Name) { $sheetNumber = $i; break } } return $sheetNumber } function SetActiveSheet([Object]$workbook, [string]$name) { if (!$name) { return } $sheetNumber = FindSheet $workbook $name if ($sheetNumber -gt 0) { $workbook.Worksheets.Item($sheetNumber).Activate() } return ($sheetNumber -gt 0) } $ExcelSheetContent = Import-Excel -FilePath "C:\Dev\svn\database\Projects\NewMexico\DBSetup\Data\Excel.xlsx" -SheetName "Sheet1" Write-Host $ExcelSheetContent $ExcelSheetContent |Select-Object |out-sql -SqlServer "DEV\INS1" -Database "ETLSTage" -Table "TargetTable" -DropExisting "True" |
Run SQL Scripts using PowerShell We all have the need to run SQL Scripts against Multiple Servers on Daily Basis. Sometimes, Its cumbersome to manually run the scripts against the server using Management Studio. This powershell Script comes handy to…