Sql server Error Message : Msg 402, Level 16, State 1, Line 45 The data types nvarchar and varbinary are incompatible in the add operator

Quick one today:

Recently, ran into this error, about an error with implicit conversion.

Msg 402, Level 16, State 1, Line 45
The data types nvarchar and varbinary are incompatible in the add operator.

This is the query:

--
-- Generate script to CREATE/migrate Sql logins
--
SELECT
		  'CREATE LOGIN [' + L.name
		+ '] WITH PASSWORD = ' + L.password_hash + ' HASHED, sid = ' + L.sid
		+ ', CHECK_EXPIRATION = '
		+ CASE WHEN L.is_expiration_checked = 0 THEN 'OFF, ' ELSE 'ON, ' END
		+ 'CHECK_POLICY = '
		+ CASE WHEN L.is_policy_checked = 0 THEN ' OFF, ' ELSE 'ON, ' END
		+ 'DEFAULT_DATABASE = [' + L.default_database_name + '], '
		+ 'DEFAULT_LANGUAGE = [' + L.default_language_name + ']'
FROM sys.sql_logins AS L
WHERE type = 'S' -- SqlLogin
ORDER BY L.name ASC
GO

On the surface it seems fairly easy to fix, right? Just find the VARBINARY literal in the query and make the conversion EXPLICIT using a CONVERT() function.

In this case, it is ‘L.password_hash‘ & ‘L.sid‘.

As indicated in BoL, password_hash is a VARBINARY(256) dataype and sid is a VARBINARY(85). To concatenate VARBINARY values (represented in hex), with the rest of the query, written in VARCHAR, we need to use an an undocument function called: fn_varbintohexstr. 

Here our goal is to keep the hex representation intact (visual); and just convert the binary to VARCHAR, so they could be concatenated with the rest of the SQL script (written in VARCHAR). We do not want to convert the contents into VARCHAR, just the format to VARCHAR.

--
-- Generate script to CREATE/migrate Sql logins
--
SELECT
		  'CREATE LOGIN [' + L.name
		+ '] WITH PASSWORD = ' + master.sys.fn_varbintohexstr(L.password_hash) + ' HASHED, sid = ' + master.sys.fn_varbintohexstr(L.sid)
		+ ', CHECK_EXPIRATION = '
		+ CASE WHEN L.is_expiration_checked = 0 THEN 'OFF, ' ELSE 'ON, ' END
		+ 'CHECK_POLICY = '
		+ CASE WHEN L.is_policy_checked = 0 THEN ' OFF, ' ELSE 'ON, ' END
		+ 'DEFAULT_DATABASE = [' + L.default_database_name + '], '
		+ 'DEFAULT_LANGUAGE = [' + L.default_language_name + ']'
FROM sys.sql_logins AS L
WHERE type = 'S' -- SqlLogin
ORDER BY L.name ASC
GO

With this function, we are able to convert the contents of VARBINARY variable/column (represented in hexadecimal) to VARCHAR (again represented in hex), so we could generate a proper SQL script.

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Error Message : Procedure sp_replmonitorsubscriptionpendingcmds, Line 233 An INSERT EXEC statement cannot be nested

Problem

While gathering replication backlog details, ran into this interesting error. The goal was to run the sp_replmonitorsubscriptionpendingcmds stored procedure and store the output in a table. As we’ve seen in a recent post, redirecting output of a stored procedure execution into a table is possible; But in this case, it throws an error saying that is not allowed.

INSERT INTO #DC1_Repl_Backlog
EXEC  sp_replmonitorsubscriptionpendingcmds
		  @publisher	= 'InstanceName'
		, @publisher_db	= 'DBName'
		, @publication	= 'Publication'
		, @subscriber	= 'Subscriber'
		, @subscriber_db= 'DBName2'
		, @subscription_type = '0'
GO

Msg 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 233
An INSERT EXEC statement cannot be nested.

(0 row(s) affected)

With the available information, right now, a clear & coherent explanation fo this behavior is not available from my end. But my guess is this; The code inside this stored procedure must be using a similar INSERT INTO #table EXEC sp_xyz, hence the error “INSERT EXEC statement cannot be nested”

Resolution

OPENROWSET helps in getting around this. See the sample code below:

--
--
--
IF OBJECT_ID('tempdb..#DC1_Repl_Backlog') IS NOT NULL
	DROP TABLE #DC1_Repl_Backlog

CREATE TABLE #DC1_Repl_Backlog (
	  pendingcmdcount	BIGINT
	, estimatedprocesstime	BIGINT
)

INSERT #DC1_Repl_Backlog (pendingcmdcount, estimatedprocesstime)
SELECT *
FROM OPENROWSET('SQLOLEDB',
		'Server=InstanceName;Trusted_Connection=yes;',
		'EXEC Ditribution.dbo.sp_replmonitorsubscriptionpendingcmds
					  @publisher = ''PublisherInstance''
					, @publisher_db	= ''DBName''
					, @publication = ''Publication''
					, @subscriber = ''Subscriber''
					, @subscriber_db = ''DBName2''
					, @subscription_type = ''0'''
		) 

SELECT *
FROM #DC1_Repl_Backlog
GO

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server: How to increase Replication Transaction Retention & History Retention

Quick one today:

In replication, the transactions that come into the system are stored in Distribution database first; From there they are replicated to each Subscriber. The data stays there for several hours. This is determined by a ‘Transaction Retention‘ setting on the Distributor.

Similarly, as the data is replicated, log entries are made in the Distributor database. These entries also have a retention policy that could be set using ‘History Retention‘ setting.

Go to Distribution instance, Replication >> Right click >> Distributor Properties

Distributor Retention Properties

Under general tab, we see these settings. Change them as needed to achieve longer/shorter retention of both data & log.

Retention Policy

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : How to SELECT BOTTOM to records (vs TOP 10 records) maintaining the same order in records

Quick one today:

Every few days, we run into a situation, where just adding an ORDER BY clause does not solve the problem of retrieving records in a particular fashion.

ORDER BY is beneficial, if you want to order the records and retrieve them in a defined pattern. Adding TOP 10 (TOP 100, etc) to it makes it easy to query the ones you want. But our situation we needed to order the records in ascending order, but we only want the bottom 10 records.

Drum roll !! Enter the all purpose CTE to rescue!!

--
-- Create a temp table and store records in some random order
--
CREATE TABLE #OrderTest (
	  ID	INT			NOT NULL
	, Name	VARCHAR(10)	NOT NULL
	, Flag	TINYINT
)
GO

INSERT INTO #OrderTest (ID, Name, Flag)
VALUES (2, 'Test Name1', 1)
	, (4, 'TestName2', 0)
	, (1, 'TestName3', 7)
	, (3, 'TestName4', 2)
	, (9, 'TestName5', 5)
	, (8, 'TestName6', 1)
	, (5, 'TestName7', 3)
	, (6, 'TestName8', 0)
GO

SELECT * FROM #OrderTest
GO

Initial order of records (Insert order)

Now, when we query TOP 10 using ORDER BY ID ASC, we’ll get records in the expected ascending order of the ID column. But the requirement is to retrieve BOTTOM 5 records, with the same ascending order on ID column. Like TOP 10 clause, there is no BOTTOM 10 clause. See the image below for clearer understanding:

Records we need to query

So we use CTE to circumvent that.

Usually ORDER BY clause is not allowed in CTE’s; Except when TOP clause is used.

--
-- Use CTE to pick the records we want
--
; WITH Bottom5 (ID, Name)
AS
	(
		SELECT TOP 5 ID, Name
		FROM #OrderTest
		ORDER BY ID DESC
	)
SELECT *
FROM Bottom5
ORDER BY ID ASC
GO

Desired Result

Hope this helps,
_Sqltimes

Read Full Post »