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 »
$assemblylist = “Microsoft.SqlServer.Management.Common”, “Microsoft.SqlServer.Smo”, “Microsoft.SqlServer.Dmf “, “Microsoft.SqlServer.Instapi “, “Microsoft.SqlServer.SqlWmiManagement “, “Microsoft.SqlServer.ConnectionInfo “, “Microsoft.SqlServer.SmoExtended “, “Microsoft.SqlServer.SqlTDiagM “, “Microsoft.SqlServer.SString “, “Microsoft.SqlServer.Management.RegisteredServers “, “Microsoft.SqlServer.Management.Sdk.Sfc “, “Microsoft.SqlServer.SqlEnum “, “Microsoft.SqlServer.RegSvrEnum “, “Microsoft.SqlServer.WmiEnum “, “Microsoft.SqlServer.ServiceBrokerEnum “, “Microsoft.SqlServer.ConnectionInfoExtended “, “Microsoft.SqlServer.Management.Collector “, “Microsoft.SqlServer.Management.CollectorEnum”, “Microsoft.SqlServer.Management.Dac”, “Microsoft.SqlServer.Management.DacEnum”, “Microsoft.SqlServer.Management.Utility” foreach ($asm in $assemblylist) { $asm = [Reflection.Assembly]::LoadWithPartialName($asm) } $computers = Get-Content -Path C:\scripts\servers.txt #… Read More »