Linking SQL database on Azure to Visio

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.

Create Azure Sql database

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.

Azure Sql database properties

Of course, I need to setup a username and password in order to create any tables, and to access any data.

 

Azure Sql database login

I created three columns in my simple table which I named Floors.

Sql database Create tables

Then I entered some data for the ground and first floor (or if you are American first and second floor Smile ).

Sql Database Add data

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.

Visio Link Data to Shapes Select Sql database

The Data Connection Wizard then allowed me to select the database on that server, and the table within it.

 

Visio Link Data to Shapes Select table

I set the properties of the data connection. 

Visio Link Data to Shapes Authentication settings

I could then save this Office Data Connection (odc) file for future use.

 

Visio Link Data to Shapes Save password in file

Finally, the table appeared in my External Data window in Visio, and so I could use it to connect rows of data to shapes.

 

Visio External Data

 

Conclusion

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 )

Posted in Azure, Visio 2013. Tags: , , . 1 Comment »

One Response to “Linking SQL database on Azure to Visio”

  1. Linking SQL database on Azure to Visio via SharePoint Online | bVisual Says:

    […] last post was entitled Linking SQL database on Azure to Visio (see https://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 […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

davecra.wordpress.com/

Microsoft Office Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: