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 https://msftdbprodsamples.codeplex.com/releases ). 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
            dds.Refresh
            GoTo exitHere
        End If
    Next
   
    Set dds = Visio.ActiveDocument.DataRecordsets.Add( _
           SQLConnStr, SQLCommStr, _
           VisDataRecordsetAddOptions.visDataRecordsetDelayQuery, _
           datasetName)

    dds.SetPrimaryKey VisPrimaryKeySettings.visKeySingle, ary()
    dds.Refresh

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

exitHere:
    Exit Sub
errHandler:
    MsgBox Err.Description
    Resume exitHere
End Sub

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

image

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

image

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:

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: