July 14, 2017 at 8:21 am
Getting the error while trying to drop the db from other node.
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1 [Batch Start Line 418]
Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.
declare @linkedserverName sysname= 'ServerXXX', @DBname sysname = 'Dbname'
DECLARE @dml nvarchar(max) = N''
SELECT @dml += 'SELECT name FROM ' + QUOTENAME(@linkedserverName) +
'.[master].[sys].databases WHERE name = ''' + @DBname + ''' AND state_desc != ''ONLINE'''
SELECT @dml
--exec(@dml)
Declare @retVal Int;
EXEC sp_executesql @dml, '@retVal int output', @retVal output
IF @retVal > 0
BEGIN
Print 'DB present in other node'
declare @cmd_6 nvarchar(1000);
set @cmd_6 = 'DROP DATABASE ' + QUOTENAME(@DBname);
Set @cmd_6 = QUOTENAME(@linkedserverName) + '.master..sp_executesql N''' + @cmd_6 + '''';
SELECT @cmd_6
exec sp_executesql @cmd_6
END
else
Print 'DB does not present in other node'
Thanks.
July 14, 2017 at 8:23 am
Put an N before the second parameter of sp_executesql.
John
July 14, 2017 at 8:31 am
Not sure why the output results are coming as NULL. Any advise?
declare @linkedserverName sysname= 'server', @DBname sysname = 'db'
DECLARE @dml nvarchar(max) = N''
SELECT @dml += 'SELECT name FROM ' + QUOTENAME(@linkedserverName) +
'.[master].[sys].databases WHERE name = ''' + @DBname + ''' AND state_desc != ''ONLINE'''
--SELECT @dml = STUFF(@dml, 1, 10, '')
SELECT @dml
--exec(@dml)
Declare @retVal varchar(1000)
--Put up the N
EXEC sp_executesql @dml, N'@retVal nvarchar(1000) output', @retVal output
SELECT @retVal
IF @retVal > 0
BEGIN
Print 'DB present in other node'
declare @cmd_6 nvarchar(1000);
set @cmd_6 = 'DROP DATABASE ' + QUOTENAME(@DBname);
Set @cmd_6 = QUOTENAME(@linkedserverName) + '.master..sp_executesql N''' + @cmd_6 + '''';
SELECT @cmd_6
exec sp_executesql @cmd_6
END
else
Print 'DB does not present in other node'
Thanks.
July 14, 2017 at 9:02 am
You mean from SELECT @retval? It's because you have no @retval in your code. I would remove those second and third parameters - they don't serve any useful purpose.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply