Sql assembly not working after changes

Transact-SQL
1

Hi,
Query is related to stored procedure with an assembly calling in it.The assembly is used to create a xml file.

It is a running procedure on live.
Made a very minor change in the dateadd function of sp and executed the alter sp Again.

Now the assembly part is not working after making changes.
Do we need to do any additional process to work the assembly as before?
Please advise

2

By assembly I assume you mean a CLR assembly? Did you make any changes to the CLR code? If yes, did you ALTER ASSEMBLY and include the new compiled .Net DLL?

Beyond, that, we'd need to see the DATEADD code you modified and examples of what is "not working", either example data (before and after the change) or output/error messages.

3

It is clr assembly.
No changes to assembly part.
The Change was made to one of the select query where clause for dateadd function.
That part is fine.

The error thrown is from the raise error custom message given based on return value of the assembly part. -true or false

4

The stored proc may have been signed with a certificate which would have been removed when you ALTERed it. Maybe

  1. have a look to see how the assembly is signed.
  2. look for certificate users in the DB.
  3. restore the db and see if the original SP is signed.

Third party providers often sign SPs to see if they have been tampered with. Altering the SP might have put you in breach of licensing conditions.

1 Like