Unable to expand or display column nodes of SQL Azure database connected in SQL Server Management Studio 2014

Sometimes when you are working on a SQL Azure database using SQL Server Management Studio, you might encounter a strange issue of not able to expand the tables to list down the column nodes or display the column nodes. I am using SQL Server 2014 and I had been facing this issue for a long time.

The error might say any of the following:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

unknown property IsMemoryOptimized (Microsoft.SqlServer.Management.Sdk.Sfc) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

Finally I was able to solve this issue by updating my SQL Server 2014 with Service Pack 1 downloaded from the following link https://www.microsoft.com/en-us/download/details.aspx?id=46694

Hope this will provide a resolution for someone who is encountering the same issue.

Generate SQL Azure DB Schema Diagram

There was a necessity for me to generate a Schema diagram having all the DB objects for my SQL Azure database. Unfortunately while you connect to your SQL Azure database through SQL Management Studio, you will not find the option to generate DB diagram like the one you get from by connecting to Local SQL Server Database

In order to accomplish that I had downloaded SQL data Migration Wizard from https://sqlazuremw.codeplex.com/ and did a migration from SQL Azure too Local SQL database. I did find another tool in order to generate Schema diagrams DBForge Studio for SQL Server but you need to pay for this. You can also generate the schema diagram using Entity Framework through Visual Studio but I don’t want to take that path.

Step by step I went ahead using the Migration tool to generate SQL objects from SQL Azure to my local database. Also this tool is really helpful when you want to migrate the schema object from local database to SQL Azure.

My first step is to run SQLAzureMW (Migration Tool) that I had downloaded from the above link. I had selected the Migrate option and also Target Server as my Local SQL Server.

image

Next step is to connect to the source whose schema migration is required.

image

Next step is to select the object or objects whose schema is required for migration purpose.

image

The next screen will show the summary and along with that the SQL Script creation required for migration purpose will be started.

image

Once the SQL Script generation is complete you can either save the script for your pre deployment steps or you can continue with next level of migration.

image

While you click Next, you will get the window where you need to select the target SQL Server which might be a your local SQL server or another SQL Azure Database. Since I need to create my database schema diagram, I went on selection for my local SQL Server and my database where I need to migrate the SQL Azure table objects

image

The above step will execute the SQL Scripts generated from SQL Azure database against my local SQL server database.

All good to go. My database have all the tables I wanted and I am ready to generate database diagram. Godspeed…

Awesome tool to play with your code

I came across this tool called http://codebeautify.org and I can say that is simply awesome. There was a requirement for me to convert XML to JSON and vice versa and came across this tool and I would say it just made my life simpler. Even you can play with nodes of JSON by adding, removing the nodes specific to your test or requirement. This is handy especially if you are trying to Mock objects or HTTP response in JSON format. Try it as it gives you more than just what you need for your programming.

image

image

image

Database Project build fails in Visual Studio 2013

Recently I had experienced a strange issue while building a database project in Visual Studio 2013. Most of the times we require a database project so that we can manipulate the queries and DB objects through Visual Studio. Hence we go for a database project by selecting the available project template as shown below.

image

Once you have selected the project, you would like to build the project. And suddenly you might encounter issues like below.

image

No worries. There is a resolution for that too.

Microsoft provide a repairing Data Tool which you can download from the following link http://go.microsoft.com/fwlink/?linkid=393521&clcid=0x409. Installing the tool should resolve the issue.

In case you still have the issue just go ahead and update SQL Server database using Extensions and Utilities from Tool menu of Visual Studio.

image

This has eventually resolved my issue after my system got restarted on completion of above installation process.

Hope it helps…..

Owin.IAppBuilder does not contain definition for UseWebApi

At some point of time you might had encountered issues with Owin.IAppBuilder giving error in Startup class which states

‘Owin.IAppBuilder’ does not contain a definition for ‘UseWebApi’ and no extension method ‘UseWebApi’ accepting a first argument of type ‘Owin.IAppBuilder’ could be found (are you missing a using directive or an assembly reference?)

This basically happens when try to add route configuration to the OWIN pipeline while using ASP.NET Web API2 OWIN. You might try updating the OWIN package from 3.0.0.0 to 3.0.1.0 using the NuGet command update-package Microsoft.Owin but that will not resolve the issue.

Solution is you need to install Microsoft ASP.NET Web API2 OWIN Self Host which will resolve the issue. You can use the NuGet command Install-Package Microsoft.AspNet.WebApi.OwinSelfHost

image

Publishing NuGet Package using NuGet Packager Extension

In one of my last post http://idevexplorer.com/2015/06/create-and-publish-nuget-package-in-windows-azure/ I have explained how we can create a NuGet Package on every build and deploy it to NuGet server in Azure.

In this post I would like to emphasize on how we can use the NuGet Package Extension in Visual Studio and publish NuGet packages to the server.

First of all we need to install the extension from the following url https://visualstudiogallery.msdn.microsoft.com/daf5c6db-386b-4994-bdd7-b6cd52f11b72. Once you have installed the extension for Visual Studio you can get the project template to create NuGet Package as shown below.

image

Once you add the NuGet Packager project to your solution, you can find the project hierarchy as shown below.

image

Now all you need to do is configure the NuGet project in order to take the libraries that you would like to publish. Open the NuGet.config file and provide the server url where the package is going to get published. Add the ApiKey which will validate the publishing of the library.

image

Copy your libraries that you would like to publish in the lib folder of the NuGet project.

image

Rename package.nuspec file to a name that you would like to distinguish for and update the project url in the nuspec file pointing to the publishing server

image

Change the description and other parameters as you want. You can even provide the dependencies to get installed or validate during the installation of the package

Now all we need to do is build the Package project. You might encounter some issues during building the package if you have renamed the nuspec file. Open NuGetPackage.ps1 file and change the following statements to point to correct NuSpec name

image

Build it again. Now you will not see any error. When you are building the NuGet project in Debug mode it will create the package *.nupkg file locally. In order to deploy it to the server through build process, you need to build it in Release mode.

Now if you go to the NuGet feed url of your server you can view the package over there.

image

You can even view through NuGet package manager the package installed.

image

If you want to delete the package from the server then you need to use the following command in Command Prompt.

nuget delete <Package Name> <Version> <API Key> –Source <NuGet Server Url>

image

That’s it you are good to go.

Create and Publish NuGet Package in Windows Azure

Recently I came up with a situation where I need to re-use few libraries in many projects. One way to do this is to refer the latest library in every projects of your existing or any other solutions which I am not comfortable with at all. The other way to avoid this traditional approach of directly adding references of your library to the project is to use NuGet Packager which I like the most. Well we might all know that having the library located in a common repository of NuGet Server makes more sense in order to re-use it multiple places. Also you can keep multiple versions of the library and use it as per client requirement. If client requires the latest package or a specific old versioned package, its relatively a lot easier to refer the library through NuGet Package Manager or Console.

Well keeping all these in mind, my situation was that I need to deploy this library in a NuGet Server that can be accessible by resources globally. I also know I do not have a global server that can be used. Hence the only option I have is to deploy the package in Windows Azure as I have a subscription on it and see how it works.

Create an Empty Web Application

First I started creating an empty Web Project from VS2013. While creating the website, it will also provide me an option to select the Web Host where the site would be hosted. I had selected that option so that my Web Host repository gets created.

Once an empty Website is created use the package manager console to add NuGet.Server library to the web project in order to provide the facility to create your web server.

PM> Install-Package NuGet.Server

This will add few libraries and folders in your empty web project as shown below.

image1

 

Open the web.config file and validate that you have requireApiKey value set to True and have added an apiKey so that no unauthorized user can have permission to create the package and publish it to repository.

image_3

 

Run the solution pressing F5 and you would see the following web page in your explorer.

image_4

 

In order to push any package to the server you need to run the following command from Command Prompt.

nuget push {package file} –s http://localhost:61342/ {apikey}

Well our agenda is to push the package to Azure web app instead of localhost. Hence our next step is to configure our Web Publishing activity to create the NuGet Web Server in Azure.

 

Update the settings for publishing to your Windows Azure repository

While creating the web project we have selected the Web Host location where the files need to be deployed. In this step you need to update the parameters for publishing and creating the NuGet Web Server in Azure. Select the settings option of Web Publish Activity to update the parameters.

image_5

 

 

Remember to select Microsoft Azure Websites from Publish Target section.

image_6

 

Once you have selected the Target location, you need to validate the connection to the Target location for the Web Publishing method.

image_7

 

Next step is to publish it. Make sure that you are publishing the website in Release Mode and also no databases are required.

image_8

 

Once the web site is published in your Azure account, you can review the website using the Url created after the publishing activity is complete.

 

image_9

 

You can even verify from your Azure account that the web server has been created through http://manage.windowsazure.com

image_10

 

Well we are all set to go for publishing our packages to the web server.

 

Publish your package to the web server

Select the Project you want to publish as a NuGet package to your NuGet Server.

Use the package manager console to add CreateNewNuGetPackageFromProjectAfterEachBuild library to the project in order to create the NuGet package *.nupkg file on every build.

PM> Install-Package CreateNewNuGetPackageFromProjectAfterEachBuild

The following files will get generated in your project.

image_11

 

Now right click on the Project and Build it. This will create your NuGet Package.

image_12

 

Once your package is generated, copy the package and place it in a folder where NuGet.exe resides. NuGet.exe will help to publish the package to the new NuGet Server that has been created in your Azure account.

You can download NuGet.exe from the following location http://nuget.codeplex.com/releases/view/121838

Now open your Command Prompt and run the following command nuget push {package file} –s http://{Azure NuGet Web Server address}/ {apikey}

image_13

 

Your package is now deployed in NuGet Web Server.

 

Apply your package to a project pointing to NuGet Web Server hosted in Azure

Next step is to consume your NuGet Package from your newly created NuGet Server location.

Add you package source in NuGet Package Manager interface.

image_14

 

Now you can install the package using Manage NuGet Packages option while right-clicking on the project where you want the package to get installed.

image_15

 

That’s it you are good to go with NuGet package….