Archive for the ‘Sql Server 2012’ Category

Interesting Problem Today:

Ran into this issue a few times and every time its a variation of the same headache. So, here some ideas will be documented for posterity.

In general terms, the error looks like this:

Connecting to Subscriber ''
Agent message code 20084. The process could not connect to Subscriber ''
Microsoft SQL Server Native Client 11.0
SQL Server Network Interfaces: The target principal name is incorrect.
Cannot generate SSPI context

The error message seems nebulous and confusing — but for trained eyes it makes perfect sense. For me it took a while to make sense out of it.

There could be several things wrong under the hood, but essentially it says that the target SQL server that is is trying to connect to, does not have a valid SPN with Active Directory.

Meer from Microsoft has documented some information on troubleshooting this issue here. For more details, please read his article, as I’ll over simplify things and address a variation of the problem in this articles (which will be slightly different from his).


From SQLServerA, using UserA, if I’m trying to connect to SqlServerB, sometimes I get this error. Essentially, means SqlServerB does not have a valid SPN.


First, log into the machine that has SqlServerB. Open command prompt with Administrative privileges. Run the command below to see if there is a valid SPN.


SETSPN -L <SQL Server Instance Service Account>


If the output looks like the first image below, then the Sql Server instance does not have a valid SPN. Now its time to generate one.

Output without valid SPN:

Invalid SPN

Invalid SPN

Step 2: Download Kerberos Configuration Manager for SQL Server from here, and start generating one.

Step 3: Open Kerberos Configuration Manager for SQL Server from the same machine that has SqlServerB instance. When you run it, it shows something like this:


Kerberos Tool Output

Kerberos Tool Output

Notice that for Sql Server service, there is not valid SPN or misplaced SPN. So its time to generate one.

Step 4: Hit the “Fix it” button right next to it and generate one. Make sure the user account that is logged into the machine has domain controller permissions.

Step 5: Now run the same command as in Step 1, and the output looks different.

Output with valid SPN:

Valid SPN

Valid SPN


Voila !! Now you are able to connect to SqlServerB from SqlServerA using UserA



Hope this helps,

Read Full Post »

Quick one today:

Surprisingly, this need comes up frequently to list all the Sql Agent jobs in all instances in our lab machines. Sometimes we need to cross check to be sure all environments have the same jobs.

This below query lists out all jobs with their steps:

-- List all Sql Agent Jobs with their steps and other relevant information
	, L.loginname
	, S.database_name
	, J.job_id
	, J.name
	, J.enabled
	, J.description
	, S.step_id
	, S.step_name
	, S.command

FROM dbo.sysjobs_view AS J
INNER JOIN dbo.sysjobsteps AS S
	ON J.job_id = S.job_id
INNER JOIN sys.syslogins AS L
	ON L.sid = J.owner_sid
ORDER BY J.enabled ASC
	, J.originating_server ASC
	, S.database_name ASC
	, J.name ASC
	, S.step_id ASC



Hope this helps,

Read Full Post »

Interesting one today:

Earlier in our lab environment, ran into this interesting error:

2019-06-20 00:18:46.67 Connecting to Distributor ''
2019-06-20 00:18:46.78 The replication agent had encountered an exception.
2019-06-20 00:18:46.78 Source: Replication
2019-06-20 00:18:46.78 Exception Type: 
2019-06-20 00:18:46.78 Exception Message: The snapshot could not be 
generated because the publisher is inactive.
2019-06-20 00:18:46.78 Message Code: 54057
2019-06-20 00:18:46.78

Replication from publisher to subscriber was set up correctly, but when the agent runs, the SQL Agent job stops with this error.


Go to Distributor and run this query to check the status of the publisher instance.

EXEC sp_helpdistpublisher


As we can see, one of the publisher instance is set to Inactive. So now we need to reset it.

Run the script below to change the publisher status at distributor:

EXEC sp_changedistpublisher  @publisher = 'Publisher',  @property = 'active', @value = 'true'

The result looks something like this:


Now  run the previous ‘sp_helpdistpublisher’ again and now the status is active:



Hope this helps,


Read Full Post »

Interesting error today:

When we query over linked server, we run into some restrictions. One such restrictions is this, where we could not query XML columns over LinkedServer connection.

For example, if the remote server table has a XML column in its structure, when we query the remote server for this table, we run this error:

-- Old query that throws this error
FROM LinkedServer01.DatabaseName.dbo.TableName
Error : Msg 9514, Level 16, State 1, Line 1 
Xml data type is not supported in distributed queries. 
Remote object has xml column(s)


Not sure why this limitation exists, but there is a workaround: OPENQUERY

In the past, we’ve covered some key benefits of OPENQUERY and how it allows us to circumvent some limitations of remote-table-value function calls. Today, we’ll see another benefit of OPENQUERY in circumventing the XML limitation of linked servers.

Rather than querying XML directly, we convert XML into NVARCHAR(MAX) and revert it back to XML once the data is on local server.

-- Modified query to circumvent the limitation
FROM OPENQUERY(LinkedServer01,
	FROM DatabaseName.dbo.TableName') AS RemoteQuery

The XML data is retrieved as NVARCHAR(MAX) to the local machine; And immediately converted to XML before displaying.


Hope this helps,

Read Full Post »

Quick one today:

Intellisense is a great feature. Quite often, we run into situations where we don’t see the drop-down showing new tables, columns, objects, etc names. While this is not frequent, it does happen often enough that it causes some good discussions in conference calls.

Problem Definition:

When we make schema changes in the current session, they are not always visible within intellisense immediately (similarly other people making schema changes to the the database schema are not immediately visible in your session either)

As you can imagine, Intellisense has its own cache and it needs to be refreshed to update with recent schema changes.


Two options to fix this:

  1. Ctrl + Shift + R
  2. Edit menu >> Intellisense >> Refresh Local Cache
Hope this helps,


Read Full Post »

Quick  one today:

With regular frequency, there is a need sometimes to change the password of a login. Using SSMS, its an easy step. But when we have a lot of such changes, its gets easier to use T-SQL script.

-- Change Login Password
WITH PASSWORD = 'NewPassword'

For more  info

Hope this helps,

Read Full Post »

Quick one today:

Every time we we build a new cluster in our lab environment, we start with running cluster validation report before starting Sql Server installation. Once in a while, we need to go back to refer older validation reports.

All cluster validation reports are saved, by default, at the location below on the active node where the validation was executed from:




Hope this helps,


Read Full Post »

« Newer Posts - Older Posts »