Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Monday, 29 December 2014

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')

Monday, 29 December 2014 15:51:56 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Friday, 19 December 2014

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:

http://sqlity.net/en/2322/change-password/ 

execute as login = 'my_login'

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

Friday, 19 December 2014 11:07:05 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Wednesday, 17 December 2014

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 !

Wednesday, 17 December 2014 11:12:11 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
DTA - Failed to initialize MSDB dat...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll