This is the basic query text to use whenever you need to loop through a result set in SQL Server using TSQL. In this example we are getting every database name and printing it out. /* Declare variable that will hold a single result */ DECLARE @DBName varchar(50) /* Declare the cursor and “load” the… Read More »
Do transactions apply in dynamic SQL, in SQL Server? In a word, yes. For proof, run the following chunks of code: No Transactions You can see the third row having a value of “5”, showing that it was updated CREATE TABLE #T( ID INT IDENTITY(1,1) ,COL1 VARCHAR(100) ,COL2 VARCHAR(100) ); INSERT INTO #T (COL1,COL2) VALUES(‘1′,’2’);… Read More »
Source: http://sqlserverplanet.com/dmvs/missing-indexes-dmv SELECT mid.statement ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure ,OBJECT_NAME(mid.Object_id) ,’CREATE INDEX [idx_’ + LEFT(PARSENAME(mid.statement, 1), 32) + ‘_’ + CONVERT(VARCHAR, mig.index_group_handle) + ‘_’ + CONVERT(VARCHAR, mid.index_handle) + ‘]’ + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL(mid.equality_columns, ”) + CASE WHEN mid.equality_columns IS NOT NULL AND… Read More »
—- Allow advanced options to be changed. –EXEC sp_configure ‘show advanced options’, 1 –GO —- Update the cuently configured value for advanced options. –RECONFIGURE –GO —- Enable xp_cmdshell –EXEC sp_configure ‘xp_cmdshell’, 1 –GO —- Update the cuently configured value for xp_cmdshell –RECONFIGURE –GO —- Disallow further advanced options to be changed. –EXEC sp_configure ‘show advanced… Read More »
The basic syntax for sending a query to SQL Server using PowerShell. sqlcmd -S SERVER_NAME -d master -Q "select count(1) from sys.objects"
Show Date/Time Of Last Boot Get-WmiObject -class Win32_OperatingSystem | Select-Object __SERVER,@{label='LastBootUpTime';expression={$_.ConvertToDateTime($_.LastBootUpTime)}}
If PowerShell is not allowing you to run scripts then you might need to set the execution policy. Set-ExecutionPolicy RemoteSigned https://technet.microsoft.com/en-us/library/ee176961.aspx
cls import-module failoverclusters Import-Module -Name SQLPS -DisableNameChecking $servers = “comma”,”separated”,”server”,”list” foreach($server in $servers) { try { $virtual_name = $server; $instances = ”; try{ $virtual_name = ((get-clusterresource -Cluster $server -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | get-clusterparameter VirtualServerName -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Select Value -ErrorAction SilentlyContinue -WarningAction SilentlyContinue)[0].Value ) } catch{} $server = $virtual_name; $path = “SQLServer:\SQL\”… Read More »
Source: https://gist.github.com/mbrownnycnyc/9913361 This is a very fast way to ping a server using PowerShell. # with reference to http://theadminguy.com/2009/04/30/portscan-with-powershell/ 2 function fastping{ 3 [CmdletBinding()] 4 param( 5 [String]$computername = “127.0.0.1”, 6 [int]$delay = 100 7 ) 8 9 $ping = new-object System.Net.NetworkInformation.Ping 10 # see http://msdn.microsoft.com/en-us/library/system.net.networkinformation.ipstatus%28v=vs.110%29.aspx 11 try { 12 if ($ping.send($computername,$delay).status -ne “Success”) {… Read More »