Unresolved external reference on BacPac upload

Question

Hi All - I receive the following error when trying to upload / import a bacpac, and although it shows complete essentially nothing has been transferred.

 

Import started: 6/5/2011 5:32:09 PM
Connecting...
Connection Open.
PrepareSystemTables: Pending Preparing DAC metadata in the SQL Server instance 'oup9oabzwz'
PrepareSystemTables: Failure Preparing DAC metadata in the SQL Server instance 'oup9oabzwz' Microsof
t.SqlServer.Management.SqlParser.MetadataSerialization.DeserializationException: Unresolved external
 reference: '/Database[RoasterWerks]/Schema[dbo]/ScalarValuedFunction[fn_diagramobjects]'.
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.XmlMetadataDeserializer.Externa
lReference.StrIdExternalReference.get_MetadataObject()
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.XmlMetadataDeserializer.Deseria
lizationDataProvider.GetExternalObjectById(Int32 id)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.DeserializationModel.GetObjectB
yId[T](Int32 id)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.Internals.DatabasePermission.Mi
crosoft.SqlServer.Management.SqlParser.Metadata.IDatabasePermission.get_TargetObject()
   at Microsoft.SqlServer.Management.DacSerialization.PermissionsUtil.MergePermissions(IDatabasePrin
cipal userDefinedPrincipal, IDatabasePrincipal systemPrincipal, MergeType type)
   at Microsoft.SqlServer.Management.DacSerialization.DacSerializer.MergedDatabasePrincipal.get_Perm
issions()
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.SerializationVisitor.UserSerial
izationInfo.GetObjectData[TId](ISerializationHandler`1 serializer, IUser obj)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.SerializationVisitor.UserSerial
izationInfo.GetObjectData[TId](ISerializationHandler`1 serializer, IMetadataObject obj)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.MetadataSerializerCore`1.GetMet
adataObjectData(IMetadataObject obj)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.XmlMetadataSerializer.Serialize
(IDatabaseObject dbObject)
   at Microsoft.SqlServer.Management.DacSerialization.DacSerializer.Serialize(IServer server, Stream
 outputLogicalStream, Stream outputPhysicalStream, String databaseName)
   at Microsoft.SqlServer.Management.Dac.DacType.Save(DacType dacType, Stream stream)
   at Microsoft.SqlServer.Management.Dac.DacInstance.SetPackage()
   at Microsoft.SqlServer.Management.Dac.DacInstance.get_TypeStream()
   at Microsoft.SqlServer.Management.Dac.PrepareDacDeploymentStep.PreparePayloads(DatabaseDeployment
Properties deploymentProperties)
   at Microsoft.SqlServer.Management.Dac.PrepareDacDeploymentStep.Execute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.NonTransaction
alStep.DoExecute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.Execute()
DAC Exception: Microsoft.SqlServer.Management.Dac.DacException: Unable to import bacpac. See inner e
xception for more details. ---> Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.Deser
ializationException: Unresolved external reference: '/Database[RoasterWerks]/Schema[dbo]/ScalarValue
dFunction[fn_diagramobjects]'.
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.XmlMetadataDeserializer.Externa
lReference.StrIdExternalReference.get_MetadataObject()
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.XmlMetadataDeserializer.Deseria
lizationDataProvider.GetExternalObjectById(Int32 id)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.DeserializationModel.GetObjectB
yId[T](Int32 id)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.Internals.DatabasePermission.Mi
crosoft.SqlServer.Management.SqlParser.Metadata.IDatabasePermission.get_TargetObject()
   at Microsoft.SqlServer.Management.DacSerialization.PermissionsUtil.MergePermissions(IDatabasePrin
cipal userDefinedPrincipal, IDatabasePrincipal systemPrincipal, MergeType type)
   at Microsoft.SqlServer.Management.DacSerialization.DacSerializer.MergedDatabasePrincipal.get_Perm
issions()
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.SerializationVisitor.UserSerial
izationInfo.GetObjectData[TId](ISerializationHandler`1 serializer, IUser obj)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.SerializationVisitor.UserSerial
izationInfo.GetObjectData[TId](ISerializationHandler`1 serializer, IMetadataObject obj)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.MetadataSerializerCore`1.GetMet
adataObjectData(IMetadataObject obj)
   at Microsoft.SqlServer.Management.SqlParser.MetadataSerialization.XmlMetadataSerializer.Serialize
(IDatabaseObject dbObject)
   at Microsoft.SqlServer.Management.DacSerialization.DacSerializer.Serialize(IServer server, Stream
 outputLogicalStream, Stream outputPhysicalStream, String databaseName)
   at Microsoft.SqlServer.Management.Dac.DacType.Save(DacType dacType, Stream stream)
   at Microsoft.SqlServer.Management.Dac.DacInstance.SetPackage()
   at Microsoft.SqlServer.Management.Dac.DacInstance.get_TypeStream()
   at Microsoft.SqlServer.Management.Dac.PrepareDacDeploymentStep.PreparePayloads(DatabaseDeployment
Properties deploymentProperties)
   at Microsoft.SqlServer.Management.Dac.PrepareDacDeploymentStep.Execute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.NonTransaction
alStep.DoExecute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.Execute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ExecuteActionSteps(IEnumerable`1
 managedActionSteps)
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.Go()
   at Microsoft.SqlServer.Management.Dac.DacStore.Import(Stream stream, DatabaseDeploymentProperties
 deploymentProperties, Boolean skipPolicyValidation)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Dac.DacStore.Import(Stream stream, DatabaseDeploymentProperties
 deploymentProperties, Boolean skipPolicyValidation)
   at Microsoft.SqlServer.Management.Dac.DacStore.Import(DatabaseDeploymentProperties deploymentProp
erties, String path, Boolean skipPolicyValidation)
   at DacImportExportCli.Program.ImportAction()
Import Complete.  Total time: 00:00:04.8482254

---

Mark

Answer 1

Hi Mark,

The error is due to referencing the function  fn_diagramobjects in a SP or Function in the database RoasterWerks.

FYI This is a system function generated automatically when creating Database Diagrams. When Extracting Data Tier Applications using SQL Server Management Studio 2008 R2, this function will be excluded automatically.

Can you please brief whether you created this manually by scripting it from other database ? Please also explain how you generated the DacPac whether using SQL Server 2008 R2 Management Studio or DACPACCLI tool ?

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, let us know by clicking "Vote as Helpful" and Propose as Answer

Answer 2

Hello Arurnraj,

I created the export by using the command line tool.  To be honest, I don't believe I ever created a diagram but perhaps I'm mistaken.  I did inspect the DB with SSMS and there was a diagram0.

 

I didn't realize a bacpac could be created by SSMS, I thought we were limited to schema only through the GUI.  Is that not true?   

 

HTH and thanks very much,

 

Mark

---

Mark

Answer 3

Hi Mark,

Yes, you can generate DACPAC from SQL Server Management Studio 2008 R2, when you generate using that, it normally doesn't include the system related objects.

By any chance, did you created or copied this function manually from other database ?

Also please post the parameters you used from Command line to generate DACPAC.

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, let us know by clicking "Vote as Helpful" and Propose as Answer

Answer 4

Hello Arunraj,

 

My understanding is Dacpac is no data while Bacpac is with data.  I'm looking to include the data and I don't see that available within SSMS.

Export command: DacImportExportCli -S .\SQLEXPRESS -E -B -D RoasterWerks -F C:\Users\Mark\Desktop\DacImportExportCli\RoasterWerks.bacpac -X

Import command: DacImportExportCli -S somewhere.database.windows.net -U user -P password -R -D RoasterWerks -F RoasterWerks.bacpac -I -EDITION web -SIZE 1 -N

This function was not copied.

---

Mark

Answer 5

Hi Mark,

BACPAC is the new format and extension of DACPAC. The main difference is it includes data along with schema.

Currently SQL Server Management Studio 2008 R2, doesn't support BACPAC format, it supports DACPAC format only.

The next CTP release of SQL Server Denali will support this in Management Studio, For more information about DAC 2.0 API, please visit http://sqldacexamples.codeplex.com

To resolve your issue, Script your source database and then search for any SP or function which is referencing fn_diagramobjects and drop them and then generate a new bacpac and try importing it.

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, let us know by clicking "Vote as Helpful" and Propose as Answer

Answer 6

Hey Mark,

 

Others have struggled with this as well.  Even if you remove the diagrams, a number of artifacts are left behind.  I believe that this is the full set:

 

DROP PROCEDURE dbo.sp_alterdiagram

DROP PROCEDURE dbo.sp_creatediagram

DROP PROCEDURE dbo.sp_dropdiagram

DROP PROCEDURE dbo.sp_helpdiagramdefinition

DROP PROCEDURE dbo.sp_renamediagram

DROP PROCEDURE dbo.sp_upgraddiagrams

DROP PROCEDURE dbo.sp_helpdiagrams

DROP FUNCTION dbo.fn_diagramobjects

DROP TABLE dbo.sysdiagrams

 

Hope that helps.

Answer 7

Hi Adrian,

Thank you - we get much further.  Unfortunately it still bails.

 

Import started: 6/8/2011 6:34:00 AM
Connecting...
Connection Open.
PrepareSystemTables: Pending Preparing DAC metadata in the SQL Server instance 'oup9oabzwz'
PrepareSystemTables: Success Preparing DAC metadata in the SQL Server instance 'oup9oabzwz'
CreateDatabase: Pending Creating database 'RoasterWerks'
CreateDatabase: Success Creating database 'RoasterWerks'
ScriptDACObjects: Pending Preparing deployment script
ScriptDACObjects: Success Preparing deployment script
CreateLogin: Pending Creating login 'NT AUTHORITY\NETWORK SERVICE'
CreateLogin: Warning Creating login 'NT AUTHORITY\NETWORK SERVICE'
CreateLogin: Pending Creating login 'OFFICE\Mark'
CreateLogin: Warning Creating login 'OFFICE\Mark'
CreateDatabaseObjects: Pending Creating schema objects in database 'RoasterWerks'
CreateDatabaseObjects: Failure Creating schema objects in database 'RoasterWerks' Microsoft.SqlServe
r.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL
statement or batch. ---> System.Data.SqlClient.SqlException: Keyword or statement option 'context_in
fo' is not supported in this version of SQL Server.
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action,
Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, Exec
utionTypes executionType)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, Exec
utionTypes executionType)
   at Microsoft.SqlServer.Management.Dac.ConnectionManager.SqlAzureConnectionManager.ExecutionContex
t.ExecuteNonQuery(String sqlCommand)
   at Microsoft.SqlServer.Management.Dac.SqlScriptExecutionAction.Execute()
   at Microsoft.SqlServer.Management.Dac.SqlScriptExecutionAction.Execute(IExecutionContext executio
nContext, TransactionalActionManager transactionalActionManager, IEnumerable`1 actionGroups, ScriptT
arget scriptTarget, Boolean rollbackOnFailure, Boolean sendEvents)
   at Microsoft.SqlServer.Management.Dac.CreateDatabaseObjectsStep.Execute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.TransactionalS
tep.DoExecute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.Execute()
CreateDatabase: RollbackPending Creating database 'RoasterWerks'
CreateDatabase: RollbackSuccess Creating database 'RoasterWerks'
DAC Exception: Microsoft.SqlServer.Management.Dac.DacException: Unable to import bacpac. See inner e
xception for more details. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An
exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.Sql
Exception: Keyword or statement option 'context_info' is not supported in this version of SQL Server
.
   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action,
Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, Exec
utionTypes executionType)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, Exec
utionTypes executionType)
   at Microsoft.SqlServer.Management.Dac.ConnectionManager.SqlAzureConnectionManager.ExecutionContex
t.ExecuteNonQuery(String sqlCommand)
   at Microsoft.SqlServer.Management.Dac.SqlScriptExecutionAction.Execute()
   at Microsoft.SqlServer.Management.Dac.SqlScriptExecutionAction.Execute(IExecutionContext executio
nContext, TransactionalActionManager transactionalActionManager, IEnumerable`1 actionGroups, ScriptT
arget scriptTarget, Boolean rollbackOnFailure, Boolean sendEvents)
   at Microsoft.SqlServer.Management.Dac.CreateDatabaseObjectsStep.Execute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.TransactionalS
tep.DoExecute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ManagedActionStep.Execute()
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.ExecuteActionSteps(IEnumerable`1
 managedActionSteps)
   at Microsoft.SqlServer.Management.Dac.TransactionalActionManager.Go()
   at Microsoft.SqlServer.Management.Dac.DacStore.Import(Stream stream, DatabaseDeploymentProperties
 deploymentProperties, Boolean skipPolicyValidation)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Dac.DacStore.Import(Stream stream, DatabaseDeploymentProperties
 deploymentProperties, Boolean skipPolicyValidation)
   at Microsoft.SqlServer.Management.Dac.DacStore.Import(DatabaseDeploymentProperties deploymentProp
erties, String path, Boolean skipPolicyValidation)
   at DacImportExportCli.Program.ImportAction()
Import Complete.  Total time: 00:01:15.3940809

 

The Login creation failures I am familiar with and normally just remove them from the import script when I create one.  The 'context_in
fo' issue is germaine to the cli and doesn't exhibit with the script.

---

Mark

Answer 8

Hi Mark,

I think one of your SP uses SET CONTEXT_INFO Statement, which is not supported by SQL Azure yet.

I also see that you are using Windows Logins (NT AUTHORITY\NETWORK SERVICE, OFFICE\Mark) in your database, you need to remove them as well. SQL Azure doesn't support Windows Authentication.

I suggest you to use SQL Azure Migration Wizard (http://sqlazuremw.codeplex.com/ ) and do an analysis, so that it gives you the complete report of unsupported objects, so that you can fix them before using DACPACCLI to create the bacpac file.

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, let us know by clicking "Vote as Helpful" and Propose as Answer

Answer 9

I've continued to drop / make changes as outlined here.  I've run the changes through the wizard and no issues are shown.  The Cli, however, continues to fail time and again and is unfortunately not ready for prime-time.  At this point I'll circle back around after more effort has been devoted to it.

 

---

Mark

Answer 10

There is an update out today for the CLI Tool.  Please give it a try and let us know how it fairs.  We have a team working on the DAC all up, but the CLI Tool is just a sample.  You can compile it yourself if you wanted.

The DAC is still being updated, and I expect a refresh of the binaries pretty soon.