This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Downloaded from http://blogs.msdn.com/b/saponsqlserver | |
EXEC('IF OBJECT_ID (''sp_hexadecimal'') IS NOT NULL DROP PROCEDURE sp_hexadecimal') | |
EXEC('CREATE PROCEDURE sp_hexadecimal | |
@binvalue varbinary(256), | |
@hexvalue varchar (514) OUTPUT | |
AS | |
DECLARE @charvalue varchar (514) | |
DECLARE @i int | |
DECLARE @length int |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$source = "sqlserver" | |
$destination = "sqlcluster" | |
$smo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | |
$smoext = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | |
$sourceserver = New-Object Microsoft.SqlServer.Management.Smo.Server $source | |
$destserver = New-Object Microsoft.SqlServer.Management.Smo.Server $destination | |
foreach ($sourcelogin in $sourceserver.logins) { |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
.SYNOPSIS | |
Restores SQL Server databases from the backup directory structure created by Ola Hallengren's database maintenance scripts. | |
.DESCRIPTION | |
Many SQL Server database administrators use Ola Hallengren's SQL Server Maintenance Solution which can be found at http://ola.hallengren.com | |
Hallengren uses a predictable backup structure which made it relatively easy to create a script that can restore an entire SQL Server database instance, down to the master database (next version), to a new server. This script is intended to be used in the event that the originating SQL Server becomes unavailable, thus rendering my other SQL restore script (http://goo.gl/QmfQ6s) ineffective. | |
.PARAMETER ServerName | |
Required. The SQL Server to which you will be restoring the databases. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1. Took note at the exact version of SQL Server, down to the build number | |
2. Made backups of all databases, including the system databases | |
3. Stopped the SQL server role within the Failover Cluster Manager | |
4. Once the disks went offline within Windows, took a storage-level snapshot of the shared disks. I named this snap “ent version”. | |
5. Copied the system databases, and their logs (mdfs and ldfs) to backup directories just in case the snapshot didn’t work (one can never be too safe) | |
6. Used VMware to snapshot both Windows nodes (alternatively, you can shut them down and snap them at the storage level) | |
7. Uninstalled SQL Server from both nodes using the Remove Node from Cluster option. This will delete the system dbs, but not the user dbs. | |
8. Deleted templog.ldf from my system directory (it was the only one that the uninstall didn’t get rid of) | |
9. Reinstalled SQL Server Standard exactly as Enterprise was before on both nodes – down to the IP, service account, name, directory structure, filestream share etc. | |
10 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE countries ( | |
code char(2) PRIMARY KEY NOT NULL, | |
name_en nvarchar(255), | |
name_fr nvarchar(255), | |
) | |
INSERT INTO countries VALUES ('AD','Andorra','Andorre'),('AE','United Arab Emirates','Émirats arabes unis'),('AF','Afghanistan','Afghanistan'),('AG','Antigua and Barbuda','Antigua-et-Barbuda'),('AI','Anguilla','Anguilla'),('AL','Albania','Albanie'),('AM','Armenia','Arménie'),('AO','Angola','Angola'),('AQ','Antarctica','Antarctique'),('AR','Argentina','Argentine'),('AS','American Samoa','Samoa américaine'),('AT','Austria','Autriche'),('AU','Australia','Australie'),('AW','Aruba','Aruba'),('AX','Åland Islands','Îles d''Åland'),('AZ','Azerbaijan','Azerbaïdjan'),('BA','Bosnia and Herzegovina','Bosnie-Herzégovine'),('BB','Barbados','Barbade'),('BD','Bangladesh','Bangladesh'),('BE','Belgium','Belgique'),('BF','Burkina Faso','Burkina Faso'),('BG','Bulgaria','Bulgarie'),('BH','Bahrain','Bahreïn'),('BI','Burundi','Burundi'),('BJ','Benin','Bénin'),('BL','Saint Barthélemy','Saint-Barthélemy'),('BM','Bermud |
We can make this file beautiful and searchable if this error is corrected: No commas found in this CSV file in line 0.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$csvfile = "C:\perf\bigperf.csv" | |
$csvfile = "C:\perf\tab.csv" | |
$csvdelimiter = "`t" # `t for tab | |
$firstrowcolumnnames = $false | |
Write-Output "Script started..." | |
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.VisualBasic") | |
$elapsed = [System.Diagnostics.Stopwatch]::StartNew() | |
$csv = New-Object Microsoft.VisualBasic.FileIO.TextFieldParser($csvfile) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Create Datatable | |
$dt = New-Object System.Data.Datatable "Music" | |
[void]$dt.Columns.Add("Artist") | |
[void]$dt.Columns.Add("Genre") | |
[void]$dt.Columns.Add("Album") | |
[void]$dt.Columns.Add("ReleaseYear") | |
# Add data | |
[void]$dt.Rows.Add("Poison","Glam Metal","Open Up and Say... Ahh!",1988) | |
[void]$dt.Rows.Add("Cinderella","Night Songs","Flesh & Blood",1986) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
if ($PSScriptRoot) { $location = $PSScriptRoot } else { $location = (Get-Location).Path } | |
$dll = "$location\System.Data.SqlServerCe.dll" | |
# look into SQLiteDataAdapter | |
if ($GetDll) { | |
$wc = New-Object System.Net.WebClient | |
$url = "https://netnerds.net/System.Data.SqlServerCe.dll" | |
$wc.DownloadFile($url,$dll) | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$GetDll = $true | |
if ($PSScriptRoot) { $location = $PSScriptRoot } else { $location = (Get-Location).Path } | |
$dll = "$location\System.Data.SQLite.dll" | |
if ($GetDll) { | |
$wc = New-Object System.Net.WebClient | |
$url = "https://netnerds.net/System.Data.SQLite.dll" | |
$wc.DownloadFile($url,$dll) | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
.SYNOPSIS | |
Fans of (Linux/UNIX) GNU findutils' locate will appreciate Invoke-Locate, which provides similar functionality. "locate" and "updatedb" aliases are automatically created. | |
.DESCRIPTION | |
This script was made in the spirit of GNU locate. While the name of this script is Invoke-Locate, it actually creates two persistent aliases: locate and updatedb. A fresh index is automatically created every 6 hours, updatedb can be used force a refresh. Indexes generally takes less than three minutes. Performing the actual locate takes about 300 milliseconds. Invoke-Locate supports both case-sensitive, and case-insensitive searches, and is case-insensitive by default. | |
locate queries a user-specific SQLite database prepared by updatedb (Task Scheduler) and writes file names matching the pattern to standard output, one per line. Since the back-end is SQL, SQL "LIKE" syntax can be used for the search pattern (ie % and _). Asterisks are automatically translated to % for people who are used to searching with |
OlderNewer