My last post was entitled Linking SQL database on Azure to Visio (see http://blog.bvisual.net/2014/09/11/linking-sql-database-on-azure-to-visio/), but that was only half the solution I wanted to present because I was having issues with linking my SQL database on Azure to my SharePoint Online. So, I enlisted my friend and colleague, Nick Ajderian, who looked into it and wrote the following blog article : http://softwaremechanik.wordpress.com/2014/09/24/connecting-microsoft-azure-to-sharepoint/ . Well, I still failed, until I realised a subtle error that I was making …
In my previous article, I showed how to connect directly to the SQL database on Azure by using the Data Connection Wizard, where I entered the Server name with the “, 1433” suffix in order to address the port. Well, when I kept on entering the same server name in the SQL Server Connection dialog for the new connection in the Data Source Explorer for a External Content Type in SharePoint Designer, everything was looking fine because I could expand the connection to view the tables and create the operations. I could even create the External List okay, but I could not view any records in it, and kept getting a connection error. I was getting very frustrated until I dropped the “, 1433”, then everything worked!
Here is my Floors table from my SQL database on Azure in SharePoint Online:
So, now I can select this list, as a Microsoft SharePoint Foundation List, using Link Data to Shapes in Visio:
It appears with the “ (External)” ending in the Data Selector dialog:
So, now the SQL database table on Azure is available in Visio, through SharePoint Online:
This is good because I can now have refreshable Visio diagrams, linked to SQL database on Azure, displayed in SharePoint web pages!
Of course, I could have used SQL views, or SharePoint views, and I could even have linked SQL stored procedures as External Content Types.
All that is left is to build a proper solution … and to thank my friend Nick, aka Software Mechanik ( http://softwaremechanik.wordpress.com/ ), for his assistance.