Failed Backup, Property TextHeader is not available for Userdefined Function

Ernst_Smit · April 10, 2018, 7:19am

Hi,

I’m looking for a backup solution for our new accounting software, Sage Pastel Evolution. I can connect to the SQL server and initiate a backup, but the I get this error:

_Failed to backup “TFD Man Live” database with “Full” backup type: The UserDefinedFunction ‘[dbo].[_efnAccBLNC]’ cannot be scripted as its data is not accessible. > Property TextHeader is not available for UserDefinedFunction '[dbo].[efnAccBLNC]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted.

It seems that this specific funtion is protected somehow. Is there any way to prevent this error from occurring?

Thank you.

Ernst_Smit · April 10, 2018, 7:24am

Here is the full detailed error as given by Microsoft SQL Server Management Studio, when trying to access the same entry.

===================================

The UserDefinedFunction ‘[dbo].[_efnAccBLNC]’ cannot be scripted as its data is not accessible. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(Urn[] urns, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.Script(Urn[] urns, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.EnumScript(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.Script(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.ScriptExistingTextObjectToWindow(IManagedConnection mc)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.InvokeTextObject(IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.Invoke()

===================================

Property TextHeader is not available for UserDefinedFunction ‘[dbo].[_efnAccBLNC]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TextHeader&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextProperty(String requestingProperty, ScriptingPreferences sp, Boolean bThrowIfCreating)
at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextHeader(ScriptHeaderType scriptHeaderType)
at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.BuildText(ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextForScript(ScriptingPreferences sp, String[] expectedObjectTypes, Boolean forceCheckNameAndManipulateIfRequired, ScriptHeaderType scriptHeaderType)
at Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptUDF(StringCollection queries, ScriptingPreferences sp, ScriptHeaderType scriptHeaderType)
at Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptAlter(StringCollection alterQuery, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptAlterObjects(List1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(DependencyCollection depList, SqlSmoObject[] objects, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)

Alexander · April 10, 2018, 3:01pm

Hi,

Thank you for the details.

SQLBackupAndFTP uses the same method to perform remote backups as SQL Server Management Studio. After you will be able to perform a remote SQL Server database backup via SQL Server Management Studio, then you can easily do via SQLBackupAndFTP.

If you get any issue during the backup via SQL Server Management Studio we recommend you to contact Microsoft support team.

Ernst_Smit · April 11, 2018, 6:13am

Hi, SQL Management studio works perfectly. It’s only SQL Backup and FTP that gives the error.

However we found another program, that does the backups without errors.

Alexander · April 11, 2018, 10:56am

Hi,

Thanks for the feedback. Could you please install the latest version by the following link https://www.dropbox.com/s/q59zq1sqpvq76y2/SQLBackupAndFTPSetup.exe?dl=0 and check if it resolves the issue?

Also, could you please clarify, do you get the error message, you sent above, from SQLBackupAndFTP or from SQL Server Management Studio?

Sorry for the inconvenience.

Ernst_Smit · April 11, 2018, 11:37am

I will try this version.

This is the log from SQL Backup and FTP:
2018/03/23 08:30 AM Starting job “TFDMLive” - “Full”. Computer “PC-PC”. App v.“11.5.2”
2018/03/23 08:30 AM The backup folder “C:\Windows\TEMP\SYSTEM\Pranas.NET\SQLBackupAndFTP\backup” has “65.956GB” free space. The temporary folder “C:\Windows\TEMP\SYSTEM\Pranas.NET\SQLBackupAndFTP\backup” has “65.956GB” free space
2018/03/23 08:30 AM Backing up “WIN-VKHP194QKI0\SQLEXPRESS_EVO” Remote SQL Server databases
2018/03/23 08:30 AM Getting maximum file size…
2018/03/23 08:30 AM The maximum file size: “”
2018/03/23 08:30 AM Backing up “TFD Man Live” database to .sql file
2018/03/23 08:30 AM Prefetching objects of TFD Man Live database
2018/03/23 08:30 AM Scripting TFD Man Live database
2018/03/23 08:30 AM Scripting users and roles of TFD Man Live database
2018/03/23 08:30 AM Scripting schemas of TFD Man Live database
2018/03/23 08:30 AM Scripting user defined types of TFD Man Live database
2018/03/23 08:30 AM Scripting assemblies of TFD Man Live database
2018/03/23 08:30 AM Scripting user defined aggregates of TFD Man Live database
2018/03/23 08:30 AM Scripting user defined functions and user defined procedures definitions of TFD Man Live database
2018/03/23 08:30 AM Failed to backup “TFD Man Live” database with “Full” backup type: The UserDefinedFunction ‘[dbo].[_efnAccBLNC]’ cannot be scripted as its data is not accessible. > Property TextHeader is not available for UserDefinedFunction ‘[dbo].[_efnAccBLNC]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted.
2018/03/23 08:30 AM Cleaning up backups of “TFDMLive” job
2018/03/23 08:30 AM Job “TFDMLive” finished with “1” errors
2018/03/23 08:30 AM Sending report to ${https://sqlbackupandftp.com/weblog?jobid=0a6c98d3-926b-40a8-a902-f2c8683bdb37|Web Log}

When I try to “modify” the entry “[dbo].[efnAccBLNC]” with MS SQL Server Management Studio v17.4 I get this error:

The UserDefinedFunction ‘[dbo].[_efnAccBLNC]’ cannot be scripted as its data is not accessible. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(Urn[] urns, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.Script(Urn[] urns, SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.EnumScript(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.Script(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.ScriptExistingTextObjectToWindow(IManagedConnection mc)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.InvokeTextObject(IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.Invoke()

===================================

Property TextHeader is not available for UserDefinedFunction ‘[dbo].[_efnAccBLNC]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TextHeader&LinkId=20476

Program Location:

at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextProperty(String requestingProperty, ScriptingPreferences sp, Boolean bThrowIfCreating)
at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextHeader(ScriptHeaderType scriptHeaderType)
at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.BuildText(ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextForScript(ScriptingPreferences sp, String[] expectedObjectTypes, Boolean forceCheckNameAndManipulateIfRequired, ScriptHeaderType scriptHeaderType)
at Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptUDF(StringCollection queries, ScriptingPreferences sp, ScriptHeaderType scriptHeaderType)
at Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptAlter(StringCollection alterQuery, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptAlterObjects(List1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(DependencyCollection depList, SqlSmoObject[] objects, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)

Alexander · April 12, 2018, 2:10pm

Hi,

Thank you for the details.

We have checked your logs and it seems there are not enough privileges for the [dbo].[_efnAccBLNC]. Could you please make sure that there is a VIEW DEFINITION privilege?