SQL Server / MySQL / Linked Server / An unexpected NULL value was returned for column

Posted by in MySQL, SQL Server, T-SQL on Nov 22nd, 2017 | 2 comments
SQL Server / MySQL / Linked Server / An unexpected NULL value was returned for column
Error: Msg 7342, Level 16, State 1, Line 8 An unexpected NULL value was returned for column “[MSDASQL].column” from OLE DB provider “MSDASQL” for linked server “LinkedMySQL”. This column cannot be NULL At the same time when I started writing this blog I also became active on MSDN forum, where I am trying to help SQL users to resolve theirs database issues. I just wanted to share with you one interesting issue appeared today (Msg 7342, Level 16, State 1, Line 8, An unexpected NULL value was returned for...
read more

SQL Server / Extended Events / Finding Deadlocks

Posted by in SQL Monitoring, SQL Server, SQL Tips and Tricks on Nov 20th, 2017 | 0 comments
SQL Server / Extended Events / Finding Deadlocks
Recently we experienced deadlocks on one of our staging servers, so I wanted to capture those deadlocks using Extended Events. This how I did it. First of all you need location where you gonna keep your XEL file, then we can create Extended Event session Using SSMS Creating new Extended Event session   Selecting Events you want to capture, in this case xml_deadlock_report   Save the event data to an XEL file   Using T-SQL CREATE EVENT SESSION [Deadlock_Report] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET...
read more

SQL Server / Monitoring / SSMS Activity Monitor and Page Splits per Second on TempDB

Posted by in SQL Monitoring, SQL Server on Nov 18th, 2017 | 0 comments
SQL Server / Monitoring / SSMS Activity Monitor and Page Splits per Second on TempDB
On one of our older instances I noticed unusually high number of page splits per second, nothing spectacular, average around 20+ (normally below 5) just something unusual for that period of time.   Same period of time day before   I was curious and wanted to investigate what is causing this additional page splits. I did not noticed any performance issues. As well I checked the indexes of all user databases and there where no additional page splits. SQL Server provides a DMV to track page splits sys.dm_db_index_operationl_stats. You...
read more

Powershell / Use PowerShell to Backup all user databases to Azure Storage

Posted by in Azure, Powershell, SQL Server on Nov 17th, 2017 | 0 comments
Powershell / Use PowerShell to Backup all user databases to Azure Storage
Script Download: The script with usage example is available for download from https://gallery.technet.microsoft.com/Use-PowerShell-to-Backup-3bb0a397 Summary: Backup all user database from specified SQL Server instance to the buffer location, and then copy from the buffer location to the azure storage, and create log file with results. Description: This PowerShell script can be invoked remotely from another PC trough the command line, with PowerShell or executed remotely through task scheduler adding parameters – instance name, backup...
read more

SQL Server / Login failed for user ‘DOMAIN\MACHINENAME$’. [SQLSTATE 28000]

Posted by in SQL Server on Nov 14th, 2017 | 0 comments
SQL Server / Login failed for user ‘DOMAIN\MACHINENAME$’. [SQLSTATE 28000]
Multi Server Administration MSX/TSX. When I have multiple instances with same or similar jobs I usualy use Multi Server Administration to create the job just once and then add target servers for that job. I just wanted to point out some errors you can face during Multi Server Administration implementation. Message [298] SQLServer Error: 18456, Login failed for user ‘DOMAIN\MACHINENAME$’. [SQLSTATE 28000] What does this mean. Your SQL Server Agent probably using NETWORK SERVICE credential for log on account (default NT...
read more

Powershell / Providers / Get-ChildItem : Invalid Path

Posted by in Powershell on Nov 13th, 2017 | 0 comments
Powershell / Providers / Get-ChildItem : Invalid Path
Using UNC paths when working with PowerShell providers. There are errors you can encounter using UNC paths if your current provider is different then filesystem. PowerShell will just use the current one. We can use the Get-PSProvider cmdlet to get full list of the PSProviders available on our system. My current one is filesystem.   So lets test this. I will use one simple PowerShell script to delete all sub-folders and files from network folder. $FolderNetworkPath="\\10.0.0.102\test\" Write-Output ((Get-Date -Format g) + " - Delete Files...
read more

Powershell / Credentials / Connect to SQL Server with PowerShell

Posted by in Powershell, SQL Server on Nov 10th, 2017 | 2 comments
Powershell / Credentials / Connect to SQL Server with PowerShell
How to login using SQL Server Management Object (SMO)   For windows authentication #################################################################### ####################### Windows Authentication ##################### #################################################################### [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-Null $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "InstanceName" $s.ConnectionContext.LoginSecure=$true $s.Databases | select name, size, status...
read more