Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created a certificate and now I want to remove certificate signature and drop the certificate. When I supply the certificate name directly it is working but when I'm making use of local variables to supply the name its giving me syntax error.

I have tried with different datatypes as well

text, varbinary(max), char(200), varchar(200), nvarchar(200)

None of these datatypes are working.

declare @certificate nvarchar(200) 
set @certificate = 'ITManagerCertificate'

begin transaction

use DBwork
-- remove signature from stored procedure
drop signature from Report_Manager
by certificate @certificate

-- drop certificate
--DROP CERTIFICATE N@certificate 

if @@ERROR > 0
begin 
   rollback transaction
end 
else begin
   commit transaction
end
share|improve this question
2  
I don't think you can use a variable in this situation. –  Mark Sinkinson Jun 9 at 8:36

1 Answer 1

up vote 0 down vote accepted

I don't know if you can pass a paramater in this situation. Please try to create your query in a variable, like:

declare @query as nvarchar(255);
set @query = 'drop signature from Report_Manager by certificate ' + @certificate;

and then execute the query like:

EXECUTE sp_executesql @query
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.