A perpetual catch up game in the world of Azure
Things around the technology world come to a grinding halt around holiday season. But not in the breakneck speed of Azure. Things are getting added at such a rapid pace that even we working with
Microsoft are playing a catch up game, I can imagine how breathless it might be leaving others. But then Azure is a disruptive technology and with it comes some of the added challenges.
My name is Angshuman Nayak and I am with the Azure Support Escalation team. So it started as a case where the developer was getting the following error when trying to edit a Stored Procedure that was written by her a few minutes back from the same management studio.
Edit the SP/Function by using the Object Explorer > Database > Programmability > Stored Procedure > SP> Script Stored Procedure as > Create To > New Query Window
"Operation not supported on version 11.0 SqlAzureDatabase"
===================================
Attempt to retrieve data for object failed for Server 'angshuman7.database.windows.net'. (Microsoft.SqlServer.Smo)
------------------------------
------------------------------
Program Location: at
Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)at
Microsoft.SqlServer.Management.Smo.CreatingObjectDictionary.SmoObjectFromUrn(Urn urn)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)
===================================
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
Program Location: at
Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request) at
Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorData(Request req) at
Microsoft.SqlServer.Management.Smo.Server.GetLCIDCollation(String collationName) at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetComparerFromCollation(String collationName) at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetDbComparer(Boolean inServer) at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitializeStringComparer() at
Microsoft.SqlServer.Management.Smo.AbstractCollectionBase.get_StringComparer() at
Microsoft.SqlServer.Management.Smo.SchemaCollectionBase.InitInnerCollection() at
Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetObjectByKey(ObjectKeyBase key) at
Microsoft.SqlServer.Management.Smo.Server.GetSmoObjectRec(Urn urn)at
Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)
===================================
Operation not supported on version 11.0 SqlAzureDatabase. (Microsoft.SqlServer.SqlEnum)
------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
Program Location: at
Microsoft.SqlServer.Management.Smo.XmlReadDoc.LoadFile(Assembly a, String strFile) at
Microsoft.SqlServer.Management.Smo.SqlObject.LoadInitData(String file,ServerVersion ver, DatabaseEngineType databaseEngineType) at
Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.LoadElement(ObjectLoadInfo oli, ServerVersion ver, DatabaseEngineType databaseEngineType)at
Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.GetElement(ObjectLoadInfo oli, ServerVersion ver, DatabaseEngineType databaseEngineType)at
Microsoft.SqlServer.Management.Sdk.Sfc.ObjectCache.GetAllElements(Urn urn, ServerVersion ver, DatabaseEngineType databaseEngineType, Object ci) at
Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetObjectsFromCache(Urn urn, Object ci) at
Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci) at
Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)at
Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
I verified that the expected things like SQL Server Management Studio was 2012 etc are in place. Also the DAC framework was the latest.
I tried setting the following options on Management Studio
Tools > Options > SQL Server Object Explorer > Scripting
a) Script for database engine type = True but it still failed
b) Include Collation = True but it still failed
It gives error somewhere in the SMO SDK layer so I was sure it will be a long drawn story. So the story goes like this. The error shows up for only SQL Azure Databases with non-default collation i.e for databases which have collation different than SQL_LATIN1_GENERAL. Yes!!! SQL Azure has been supporting various collations for a while and even I was late to be aware of that. The advanced users would know that SQL Server Management Studio’s Object Explorer is developed on the .Net SMO code libraries. The meticulous brains amongst you would have already scoured through the call stack and would have found that the SMO library is written on an SDK framework. So the stack lines which are two sizes larger in the call stack is where the problem lies (yeah it was not a formatting issue). We are in the process of updating the SDK so that SSMS 2012 can handle editing of Stored Procedure from Object Explorer.
In the mean time you can use the following workarounds.
Work Around 1 (For people who believe in the tried and tested)
Open SSMS 2012
a) Connect to the SQL Azure Database
b) In the query pane write sp_helptext spName
c) Then copy the query that appears in the pane below and paste it in the query window on SSMS 2101.
d) This can then be edited and executed. The changes reflect in the SP and you can refresh the object explorer and verify it.
Work Around 2 (For people who love everything new and shiny)
Use the SQL Azure Portal Web frontend and you can edit the SPs there.
I would appreciate a comment incase this blog gave you some insight into the issue, made you smile or did both. So welcome to the world of Azure and be ready to run a perpetually changing track, in here not the meek but the bold will inherit the earth.
Author : Angshuman Nayak (CIE) Azure Escalation Services, Microsoft
Reviewer : Anurag Sharma (CIE) Azure Escalation Services, Microsoft