Feeds:
Posts
Comments

Posts Tagged ‘Sql Server Error Message’

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
Advertisements

Read Full Post »

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 »