Blog

When using the “Deploy to Azure Database” option in Microsoft SQL Management Studio to move a database to Azure, you may sometimes come across the following error:

Error SQL71562: Error validating element [dbo].[f]: Synonym: [dbo].[f] has an unresolved reference to object [server].[database_name].[table_name]. External references are not supported when creating a package from this platform.

These type of errors are generated as you cannot setup a linked server in Azure and queries using four-part [server].[database].[schema].[table] references are not supported. I’ve come across a SQL71562 error in the past, but this one was different. Generally, the error details are a lot more helpful and relates to stored procedures or views where a table path contains the database name:

Error SQL71562: Procedure: [dbo].[store_procedure_name] has an unresolved reference to object [database_name].[dbo].[table_name]

Easy enough to resolve. The error I was getting this time threw me as it didn’t point me to any object in the database to where the conflict resides and would require me to look through all possible database objects. This would be easy enough to do manually on a small database, but not a large database consisting of over 50 stored procedures and 30 views. Thankfully, SQL to the rescue...

To search across all stored procedures and views, you can use the LIKE operator to search against the database’s offending system objects based on the details you can gather from the error message:

-- Stored Procedures
SELECT OBJECT_NAME(object_id),
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%[database_name]%'

-- Views
SELECT OBJECT_NAME(object_id),
       OBJECT_DEFINITION(object_id)
FROM sys.views
WHERE OBJECT_DEFINITION(object_id) LIKE '%[database_name]%'

Earlier this week a post I wrote for C# Corner was published. It was about an alternative to use the very well known SQL Server "IN" condition when working with many values. I discuss storing a list of values you would normally pass directly into your "IN" condition for querying to a User Defined Data Type.

There will probably be a very small number of cases where additional steps I write in the post will need to be carried out. Afterall, SQL Server has a very large limit on the number of values the “IN” condition can handle, based on the length of instruction (max 65k).

Check it out here: http://www.c-sharpcorner.com/blogs/alternative-to-sql-in-condition-when-working-with-many-values

Whilst trying to import a .bacpac database exported from an SQL Azure platform onto Microsoft SQL Server 2012, I received the following error:

Internal Error. The internal target platform type SqlAzureDatabaseSchemaProvider does not support schema file version '2.5'. (File: C:\Users\Surinder\Downloads\SurinderAzureDB.bacpac) (Microsoft.Data.Tools.Schema.Sql)

Now it seems many people online have been encountering this horrible error and majority of my fellow bloggers have suggested that installing the SQL Server Data Tools – December 2012 Update for VS 2010 or 2012 will resolve this issue. Unfortunately installing this alone did not help me. I found out that I missed one key part of my SQL Server installation: Service Pack 2!

Can't believe this one small oversight caused me such a headache.

You can download Microsoft SQL Server 2012 SP2 from the following link: https://www.microsoft.com/en-gb/download/details.aspx?id=43340.

I don’t generally have a problem importing an Excel spread sheet into one of my SQL Server tables. But today would end my run of Excel importing perfection.

I experienced an problem where all rows that only contained numbers were ending up as NULL in my table after import, which I thought was strange since the Excel spread sheet did not contain empty cells. It contained a mixture of data formats: text and numbers.

I decided to format all rows in my spread sheet to text and try another re-import. No change.

After much experimentation, the solution was to copy all columns and paste them into Notepad in order to remove all formatting inherited from Excel. I then re-copied all my data from Notepad back into my spread sheet and carried out another import. Lo and behold it worked!

I don’t understand why I had this problem. It could have been due to the fact the spread sheet contained cells of different data formats and causing confusing through the import process.