Link to home
Start Free TrialLog in
Avatar of SMISTechs
SMISTechsFlag for United States of America

asked on

SQL Server Generate Scripts Fails

Migrating dbs to new server and assumed that generating scripts for the entire DB would be simple, however on each of the 3 databases, the generating scripts fails as follows:
Preparing dbo.mnp_datetotextymd

Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException: Property TextHeader is not available for UserDefinedFunction '[dbo].[mnp_datetotextymd]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Is that an extended procedure?
What sp_helptext 'mnp_datetotextymd' returns?
Avatar of SMISTechs

ASKER

The text for object 'mnp_datetotextymd' is encrypted.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi SMISTechs,

This object - [dbo].[mnp_datetotextymd] is encrypted. So when anything is encrypted their definition is NULL. So when you tried to create the generate script it fails.

--

select object_id , definition , case when definition is null then 'Encrypted' else 'not encrypted' end IsEncrypted
from sys.sql_modules

--

Open in new window


So in this case we have to first decrypt it and get the code and then generate the scripts.  For getting the script you can use DAC connection or a third party tool.
 
Please follow this document to get the script for the encrypted object.

https://sqljunkieshare.com/2012/03/07/decrypting-encrypted-stored-procedures-views-functions-in-sql-server-20052008-r2/

Hope it helps!
Thanks for your assistance. The encrypted object prompted to move the database by generating a dump instead. It just seemed an easier option.