SMISTechs
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.Manage ment.Smo.P ropertyCan notBeRetri evedExcept ion: 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.Manage ment.SqlSc riptPublis h.Generate PublishPag e.worker_D oWork(Obje ct sender, DoWorkEventArgs e) at System.ComponentModel.Back groundWork er.OnDoWor k(DoWorkEv entArgs e) at System.ComponentModel.Back groundWork er.WorkerT hreadStart (Object argument)
Preparing dbo.mnp_datetotextymd
Microsoft.SqlServer.Manage
ASKER
The text for object 'mnp_datetotextymd' is encrypted.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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!
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
--
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!
ASKER
Thanks for your assistance. The encrypted object prompted to move the database by generating a dump instead. It just seemed an easier option.
What sp_helptext 'mnp_datetotextymd' returns?