How Do I Done Configuration Dynamically In Web Api And I Am Using Sql Authentication And Got Error

See more:

SQL-Server-2012

The procedure 'sys.sp_dropserver' cannot be executed within a transaction.


and stored procedure is



ALTER PROCEDURE [dbo].[procedure name](@SERVERNAME VARCHAR(50),
@DATABASENAME VARCHAR(50),
@USERNAME VARCHAR(50),
@PASSWORD VARCHAR(50),
@result nvarchar(max) out)
AS
BEGIN

DECLARE @ERROR1 VARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)

BEGIN try
--exec sp_dropserver @SERVERNAME , 'droplogins';

set @SQL = 'SELECT TOP 1 ''SUCCESS'' AS STATUS FROM ['+@SERVERNAME+'].['+@DATABASENAME+'].[dbo].[tblname]'

exec sp_addlinkedserver @SERVERNAME;

exec sp_addlinkedsrvlogin @SERVERNAME, 'FALSE', NULL, @USERNAME, @PASSWORD;

EXEC (@SQL)
set @result='success'
--select 'LINKED'
END try
BEGIN catch
select @ERROR1 = ERROR_MESSAGE();
--PRINT @ERROR1
exec sp_dropserver @SERVERNAME , 'droplogins';
set @result=@ERROR1
END catch


END