Linking Data to Visio with SQL Server Stored Procedures

Visio Professional has the ability to link to various data sources, and one of the most popular is SQL Server. The DATA\Link Data to Shapes … function will present a UI to choose from a table or view, but does not provide any stored procedures. Therefore, this article present a VBA macro that demonstrates how a stored procedure can be added as a refreshable data recordset in Visio.

This macro uses the uspGetManagerEmployees procedure from the AdventureWorks sample database ( see ). It expects one integer parameter, and returns a list of employees for a given manager’s business entity id. You will need to enter your own Data Source and Initial Catalog values.

Public Sub AddOrRefreshFromStoredProc()
On Error GoTo errHandler
Dim dds As Visio.DataRecordset  ‘The data recordset
Dim ary() As String         ‘Array to hold the p key columns
Dim SQLConnStr As String    ‘The connection string
Dim SQLCommStr As String    ‘The Command string
Dim datasetName As String   ‘The dataset name
    SQLConnStr = "Provider=SQLOLEDB.1;" & _
        "Integrated Security=SSPI;Persist Security Info=True;" & _
        "Initial Catalog=AdventureWorks2014;" & _
        "Data Source=ARUBA\SQLExpress2014x8;" & _
        "Use Procedure for Prepare=1;"
    SQLCommStr = "EXEC dbo.uspGetManagerEmployees "
Dim busEntity As Variant
    busEntity = InputBox("Which business entity to you want to retrieve for?", SQLCommStr, 2)
    If IsNumeric(busEntity) = False Then
        MsgBox "You must enter an integer", vbCritical, SQLCommStr
        GoTo exitHere
    End If
    SQLCommStr = SQLCommStr & CStr(busEntity)
    ary() = Split("BusinessEntityID", ";")
    datasetName = "uspGetManagerEmployees for " & CStr(busEntity)

    ‘Check if the recordset exists already
    For Each dds In Visio.ActiveDocument.DataRecordsets
        If dds.CommandString = SQLCommStr Then
            GoTo exitHere
        End If
    Set dds = Visio.ActiveDocument.DataRecordsets.Add( _
           SQLConnStr, SQLCommStr, _
           VisDataRecordsetAddOptions.visDataRecordsetDelayQuery, _

    dds.SetPrimaryKey VisPrimaryKeySettings.visKeySingle, ary()

    Visio.ActiveWindow.Windows.ItemFromID( _
        visWinIDExternalData).Visible = True

    Exit Sub
    MsgBox Err.Description
    Resume exitHere
End Sub

The macro will simply ask for the stored procedure parameter to be entered:


The stored procedure will then be executed an a data recordset will be added to the document.


This recordset can be refreshed in the normal manner, or by re-running the macro with the same parameter.


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another 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

Smart graphics for visual people

%d bloggers like this: