Reset a hanging RDP session using PowerShell Remoting

PS H:\> $cred = Get-StoredCredential -Target “VANLANDEGHEMB” #using PSModule “CredentialManager 2.0” PS H:\> # Create a remote session to the server PS H:\> enter-pssession -ComputerName MyServer -Credential $cred [MyServer]: PS C:\> query session SESSIONNAME USERNAME ID STATE TYPE DEVICE >services 0 Disc console 1 Conn rdp-tcp#27 VANLANDEGHEMB 60 Active rdp-tcp 65536 Listen [MyServer]: PS C:\> […]

Finding SQL Server IP address and TCP port

Lots of blog posts talk about finding the IP address of the SQL Server instance. For most, this is about looking at sys.dm_exec_connections or selecting CONNECTIONPROPERTY(‘local_net_address’). The problem with those techniquest is that they only work from a remote connection via TCP/IP.  Others talk about finding the IP address via xp_cmdshell and parsing the output of ‘ipconfig’.  The same […]

Using “&” (Bitwise AND) operator in SQL Server

A quick example on using the “&” (Bitwise AND) operator in SQL Server.  This can be used to interpret status bits from an application. In this example I will use the status column from master..sysdatabases because it is easily available on SQL server so you don’t need to create any tables to test this example. […]

Programatically create SSRS report snapshot

‘ Created by Bert Van Landeghem 16/01/2015 ‘ How to use: ‘ -i scriptfile ‘ -s https://SSRS_server_url/reportserver ‘ -v REPORT_PATH=”path to report for which to create a snapshot” ‘ ‘ Example command line: ‘ “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\rs.exe” -i C:\test\CreateReportSnapshot.rss -s -v REPORT_PATH=”/Lists/CentralPrint” ‘ ‘ ReportingService2010 Methods (ReportService2010) ‘ [] Public Sub Main() […]

Easy collection of system information from SQL Server in XML

Fairly easy, extensible and readable way to get system information from SQL Server for reporting without having to create a complex system.  The code in the Common Table Expressions (WITH…) are from the SQL Server DMV Diagnostics Queries by Glenn Berry. DROP VIEW server_config GO CREATE VIEW server_config (server_config) AS WITH os_windows_info as ( SELECT […]

Maintenance window boundary calculation

During the implementation of Ola Hallengren’s SQL Server Maintenance Solution, we had a requirement to be able to set different parameters at different times to be able to fit the work in the available maintenance window. For example during the index maintenance part, we would set higher fragmentation thresholds during weekdays than during the weekend, […]