Lesson Learned #282: Troubleshooting the element cannot be deployed as the script body is encrypted

[Guest Jose_Manuel_Jurado]

This time our customer was running into the following error message when attempting to perform an export to a bacpac file "One or more unsupported elements were found in the schema used as part of a data package. Error SQL71564: Error validating element [dbo].[usp_GetData]: The element [dbo].[usp_GetData] cannot be deployed as the script body is encrypted."

 

 

 

 

 

 

Checking this situation, we reviewed the definition of this stored procedure usp_GetData and we found in its definition:

 

 

 

 

 

CREATE OR ALTER PROCEDURE usp_GetData

WITH ENCRYPTION

as

begin

select 1

End

 

 

 

 

 

So, this points to that the stored procedure is with encryption enabled and in order to export the schema to the bacpac we need to decrypt the objects before the bacpac file would be successfully created.

 

 

 

Running the command to decrypt the stored procedure code our customer was able to export the data.

 

 

 

 

 

alter PROCEDURE usp_GetData

as

begin

select 1

End

 

 

 

 

 

 

 

 

Enjoy!

 

Continue reading...