Microsoft Visio has many data sources available for Link Data to Shapes, and one of those is SQL Server. Microsoft Azure is increasingly becoming a low-cost, and always available, choice for hosting all sorts of data, and applications, so I thought I would document how easy it is to setup a SQL database on Azure for use with Visio.
Creating the SQL database on Azure
First, you need register for an Azure subscription, and then create a SQL database on a server in a suitable location.
In my case, I have created a database called VisioFM on a server in North Europe, and I have highlighted the server name and port that I will use later in Visio.
Of course, I need to setup a username and password in order to create any tables, and to access any data.
I created three columns in my simple table which I named Floors.
Then I entered some data for the ground and first floor (or if you are American first and second floor ).
So, there it is, a simple database with one table, no views, and no stored procedures.
Linking the SQL database on Azure to a Visio document
I then create a new Visio document, selected Link Data to Shapes. and then chose a Microsoft SQL Server database.
I entered the Azure SQL server name and port from above, and entered the user name and password.
The Data Connection Wizard then allowed me to select the database on that server, and the table within it.
I set the properties of the data connection.
I could then save this Office Data Connection (odc) file for future use.
Finally, the table appeared in my External Data window in Visio, and so I could use it to connect rows of data to shapes.
This scenario is useful when you want to link to a remotely available SQL database. You can setup multiple logins to the tables, views or stored procedures, with different levels of permissions.
Tables and views are available to select from the data connection wizard, but links to stored procedures have to be coded.
The data is refreshable in Visio, however, if you are hosting your Visio diagrams in SharePoint, and want them to be refreshable within the Visio Web Access control, then you will want to create the Azure SQL database as an External Type using Business Connectivity Services (see http://www.pointbeyond.com/2012/12/31/surfacing-data-from-sql-azure-in-sharepoint-2013-online-office-365 )
Can you link more than one data table in Visio 2016 Pro? I can add multiple tables using Custom Import, but it seems like only one is active for pulling objects into a diagram.
You can link one row from any number of External Data recordsets to any Visio shape. So, a shape can be linked to many recordsets, but only one row in each of them. They can have different key fields too.
Miles Thomas says
Can Visio link to SharePoint lists?
David Parker says
Certainly you can!
Using SharePoint Links and Hyperlinks in Visio
Using SharePoint Choice Columns with Visio Services