Feeds:
Posts
Comments

Archive for the ‘Sql Server 2016’ Category

Quick one today:

Occasionally, we find a need to retrieve all the IDENTITY columns in all our environments to perform analysis on some architectural tickets. Since there could be many databases across different environments, we need a single script that could retrieve necessary data — so we could point to to a multi-script and BAM !! All data retrieved in one shot !!

--
--  Retrieve IDENTITY columns for all tables in a database
--
SELECT	  	  @@SERVERNAME				AS [ServerName]
		, DB_NAME()				AS [DatabaseName]
		, O.name				AS [TableName]
		, C.name				AS [ColumnName]
		, T.name 				AS [DataType]
		, I.seed_value
		, I.increment_value
		, I.last_value
		, IDENT_CURRENT(O.name) AS [IdentCurrent]
		, I.is_not_for_replication
		, I.is_nullable
		, I.column_id
		, I.user_type_id
FROM sys.objects AS O
INNER JOIN sys.columns AS C
	ON O.object_id = C.object_id
	AND O.is_ms_shipped = 0
INNER JOIN sys.identity_columns AS I
	ON C.name = I.name
	AND C.object_id = I.object_id
	AND I.is_identity = 1
INNER JOIN sys.types AS T
	ON T.user_type_id = I.user_type_id
ORDER BY @@SERVERNAME, DB_NAME(), O.name, C.name
GO

 

Result

Output will look something like this:

IDENTITY_Properties

Hope this helps,
_Sqltimes

 

Advertisements

Read Full Post »

Quick one today:

Occasionally, we also have a need to retrieve all the server roles a login is associated with. Earlier we covered a similar post where covered a script to retrieve all database users & their database role assignments; In this we’ll  cover similar task, but for Server Roles & Logins.

--
-- List all Server roles a login is part of
--
SELECT DB_NAME(),  RP.type_desc AS [Role Type], RP.name, MP.name, MP.type_desc AS [LoginType]
FROM sys.server_role_members AS R
INNER JOIN sys.server_principals AS RP
	ON RP.principal_id = R.role_principal_id
	AND RP.type_desc = 'SERVER_ROLE'
INNER JOIN sys.server_principals AS MP
	ON MP.principal_id = R.member_principal_id
GO

Just like last time, if you run this from multi-script window, we could compare between two different environments.

 

Hope this helps,
_Sqltimes

 

Read Full Post »

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

Example:

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.

Resolution:

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,
_Sqltimes

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
--
SELECT
	  J.originating_server
	, 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
GO

 

 

Hope this helps,
_Sqltimes

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: 
Microsoft.SqlServer.Replication.ReplicationAgentException
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.

Resolution:

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


EXEC sp_helpdistpublisher

PublisherInvalid

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

The result looks something like this:

Publisher_Active

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

PublisherValid

 

Hope this helps,
_Sqltimes

 

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
--
SELECT ID, XMLColumn
FROM LinkedServer01.DatabaseName.dbo.TableName
GO
Error : Msg 9514, Level 16, State 1, Line 1 
Xml data type is not supported in distributed queries. 
Remote object has xml column(s)

Resolution:

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
--
SELECT ID, CONVERT(XML, XMLColumn) AS XMLcolumn
FROM OPENQUERY(LinkedServer01,
	'SELECT ID
		 , CONVERT(NVARCHAR(MAX), XMLColumn) AS XMLColumn
	FROM DatabaseName.dbo.TableName') AS RemoteQuery
GO

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

 

Hope this helps,
_Sqltimes

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.

Resolution

Two options to fix this:

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

 

Read Full Post »

Older Posts »