The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog



No Ads ever, except search!
Thursday, February 22, 2024 Login

Blog posts for the month of December,2014.
query sysprocesses fails for read_only availability group12/29/2014 3:51:56 PM

We have a process that queries sysprocesses (or sys.processes) and tracks some information.

Recently it began failing on a read_only node of an always-on availability group.  We have several other servers configured the exact same way and it does not happen.  We are unable to determine why this server has this behavior, no other errors or issues have been found.  We did find a cumbersome work-around, listed below.  SQL Server 2012, 10.0.5500.

select * from sysprocesses

select * from sys.sysprocesses

Msg 978, Level 14, State 1, Line 1

The target database ('Some_database') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

-- now run from linked server, which has a loop back to the server itself with applicationintent=readonly

/* this code will drop and create the linked server

IF EXISTS (select * from master..sysservers where srvname = 'LOCALSERVER') EXEC master.dbo.sp_dropserver @server=N'LOCALSERVER', @droplogins='droplogins'

EXEC master.dbo.sp_addlinkedserver @server = N'LOCALSERVER', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=@@servername, @provstr=N'APPLICATIONINTENT=READONLY'


select * from openquery (LOCALSERVER, 'select * from sysprocesses')

Cows in the Barn11/20/2014 5:25:54 PM

Cows in the Barn, modified.








SQL Install interupted by Windows Update12/17/2014 11:12:11 AM

What you don't want to see when your install SQL Server in a clustered environment !

This happened because the server was missing .Net Framework 3.5 service pack 1, so I flipped over to the "features" and added this feature, re-ran the "check prerequisites" and completed the install, or so I thought.

But then suddenly the windows update kicked in, as it detected that there were existing approved patches for this new feature, and immediately pushed those down.

The key here is to quickly flip over to services and stop the windows update service, of course this assumes your group policy allows you to stop that service.

Which will finish first SQL Server install of adding a node to a cluster or the remaining time to reboot !

Alter Login fails12/19/2014 11:07:05 AM

SQL Server, Alter Login fails, must specify the old_password and "Execute As Login = 'user'.

Absolutely confusing for users and seems dumb, maybe I need to read more to understand why.

Good write up here: 

execute as login = 'my_login'

ALTER LOGIN my_login WITH PASSWORD = 'new password' OLD_PASSWORD = 'old password';

Blog Home