Specified cast is not valid. (Microsoft.SqlServer.Smo) - degradation of v11

Question

I have Sql management studio 2014 SP1 and im working with my Azure SQL Databases.

Previously all my databases and servers were on Sql Server 11.0 and thing were working fine, until a couple of days ago; 1 database on a server started throwing the specified cast is not valid error when you try to use management studio to Script table as..... and today all (3) of my databases on (different) servers are throwing that error 

Earlier today I upgraded 1 of the servers to the latest azure sql database 12.0.2000.8 (it took a while since it can take up to 24 hours); and can verify that the same script table as... or any management studio function that will generate scripts, like Generate Scripts task does not throw this error.

The issue with v11 started happening a few days ago and is now on all my v11 databases; management studio will not generate scripts.,  wanted to submit this as a bug., but there is no easier way to do so.

screenshots below.

error dump:

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

Specified cast is not valid. (Microsoft.SqlServer.Smo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Table.Microsoft.SqlServer.Management.Smo.IPropertyDataDispatch.SetPropertyValue(Int32 index, Object value)
   at Microsoft.SqlServer.Management.Smo.PropertyDispatcher.SetValue(Int32 index, Object value)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.SetValue(Int32 index, Object value)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AddObjectPropsFromDataReader(IDataReader reader, Boolean skipIfDirty, Int32 startColIdx, Int32 endColIdx)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetPropertyObject(Int32 index)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropertyOptional(String propName)
   at Microsoft.SqlServer.Management.Smo.Column.EmbedDefaultConstraints()
   at Microsoft.SqlServer.Management.Smo.Column.GetPropagateInfo(PropagateAction action)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropagateInfoForDiscovery(PropagateAction action)
   at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.GetScriptableChildren(List`1 propInfoList, PropagateAction propagateAction)
   at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.SfcChildrenDiscovery(HashSet`1 discoveredUrns)
   at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.Discover(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.Discover(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ScriptGenerator.ScriptCreate(SqlTextWriter sqlwriter, Server server, Urn[] urns, SqlScriptOptions options)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ScriptNodeActionContext.Script(SqlTextWriter writer)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.SqlScriptMenu.OnScriptItemClick(Object sender, EventArgs e)

Answer 1

Hi Zxed,

It may be a known behavior with sql azure v11. If migrating to V12 is helping, please move to V12. Alternatively, I would recommend you to open a technical support ticket where you can get your database/scripts checked by the experts and then he/she will be able to file a bug accordingly.

Regards,

Mekh.

Answer 2

Hey Zxed,

If you could share your schema and the version of SSMS (under help>about) we might be able to help you a bit more.

Agree with Mekh. if upgrading to v12 solves your problems, we would recommend upgrading to v12 as there are numerous improvements over v11 including new capabilities not available in v11 as well as performance improvements.

To learn more, you can check this article out:
http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-whats-new/

let us know if this helps

Thanks,

Answer 3

I had done a few test restores on v12 and had made several changes to get the test db working a few weeks ago - thats the only reason I was able to click upgrade to v12 and have the issue get resolved. Others who may run into the same might not be that fortunate. I've upgraded all my database to v12 and things are almost as good as having sql on vm. The error I quoted happened in sql management studio 2014, 2014 cu6 and even 2014 sp1. It also was reproducible with visual studio 2013. The point was "something in the last week or two broke functionality in v11", maybe it was a cu, or a hot fix, or something - I have a go fe path, others might not. Creating a tech bug request with MS requires someone to pay for a subscription.... So it ends up here instead. Thanks for chiming in guys...

Answer 4

I'm also running into this exact issue with V11 and SQL Server Management Studio -- table "Generate Create Script" fails with "Specified cast is not valid. (Microsoft.SqlServer.Smo)". This just started happening recently with V11 and doesn't manifest with V12. I'm running SSMS with latest hotifxes (CU6 I believe). I don't have the option of an immediate move to V12.

Microsoft SQL Server Management Studio 12.0.2480.0
Microsoft Analysis Services Client Tools 12.0.2480.0
Microsoft Data Access Components (MDAC) 6.3.9600.16384
Microsoft MSXML 3.0 6.0 
Microsoft Internet Explorer 9.11.9600.17728
Microsoft .NET Framework 4.0.30319.34014
Operating System 6.3.9600

Answer 5

Exactly my point & thanks for reporting the same issue I faced

Mekh & Tiger; The question isn't answered and thus in my opinion its inappropriate to mark it as answered - but you are the moderators so do what you feel is right.

There is a bug in v11 that did not exist before, someone should report it as a service degradation; someone who has a subscription to support....

Answer 6

Hi Kimberly and Zxed,

We will try to repro this problem but to help us speed this process up, can you share the schema of the table/database of the table you're trying to script? you can email this to me at:

vinsonyu[at]Microsoft[dot]com

Answer 7

I noticed that I did not clarify in my original post. trying to do this on ANY table, view, spx gives the same error. Create a table with just 1 field; then try and generate the script for that table, you get the error.

example

create table temp_test
(id int null)

then refresh, find the table, right click "script table as", create to: any option (new window, clip, etc)., wait a few seconds, get error.

SQL Server 11.0.9230.82

here is a quick screencast

http://screencast-o-matic.com/watch/cofYblfceB

Answer 8

Hi Kimberly and Zxed,

We are aware of this issue and will be working on a fix soon.

Meanwhile here are 2 mitigations you can do to use the "Script as..." capabilities:

1. Upgrade to v12 (there are also other numerous benefits for going to v12 as well)

for more information go here:
http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-plan-prepare-upgrade/

2. Use SQL Server Data Tools on Visual Studio 2013 (free).
I was able to use the "script as..." against v11 Azure SQL Databases here.

for download and more information go here:
https://www.visualstudio.com/en-us/products/visual-studio-community-vs.aspx

Thank You,
Vin

Answer 9

Hi,

This is a bug in SQL 2014 SP1. So, you can create scripts by using another version of SQL Server until MS provides its solution\hotfix. I faced the same problem with SQL Server 2014 (SP1) and then I created script through SQL Server 2012 without any problem. I hope, it will even work well with other lower versions of SQL Server. 

Cheers,

MIqbal

Answer 10

I uninstalled SP1 update and it works again.

Answer 11

This was reported in April 2015 and you said there would be a fix soon.  It is now March of 2016, and still no fix?  How soon is soon?  I really need this functionality.  Please help me solve this problem.  Thank you