Forum Discussion

Oscar's avatar
Oscar
Contributor
3 years ago

Execute Stored Procedure on External Database

Hello-

We created a connection to an external database and I can see it just fine in our Dev application. under Data Source > External Database Connection in Dashboard Data Adapters. The issue we are having is, we are not able to write and execute a query against this External Database. Instead, we are only able to execute a stored procedure but the SQL Query field will not allow us to do something like:

execute dbo.usp_GLReport

Has anyone run into a similar issue? Any ideas are greatly appreciated.

Data Management
Import
Workflow
  • JackLacava's avatar
    JackLacava
    3 years ago

    It should be basically equivalent, but just in case it makes any difference: you can try executing the command from a rule instead. For example, as a Dashboard DataSet:

    [...]
Case Is = DashboardDataSetFunctionType.GetDataSet
	If args.DataSetName.XFEqualsIgnoreCase("MyDS") Then
		Using dbConnApp As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, "OneStream BI Blend")
			Dim sqlQ As String = "Select 1"
			Dim dt As DataTable = brapi.Database.ExecuteSql(dbConnApp, sqlQ, False)
			Return dt
		End Using
	End If
[...]

    With a Data Adapter to run it:

     

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    3 years ago

    It should be basically equivalent, but just in case it makes any difference: you can try executing the command from a rule instead. For example, as a Dashboard DataSet:

    [...]
Case Is = DashboardDataSetFunctionType.GetDataSet
	If args.DataSetName.XFEqualsIgnoreCase("MyDS") Then
		Using dbConnApp As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, "OneStream BI Blend")
			Dim sqlQ As String = "Select 1"
			Dim dt As DataTable = brapi.Database.ExecuteSql(dbConnApp, sqlQ, False)
			Return dt
		End Using
	End If
[...]

    With a Data Adapter to run it:

     

    • Oscar's avatar
      Oscar
      Contributor
      3 years ago

      Thank you Jack! Your post solved my issue

  • bilbrandon's avatar
    bilbrandon
    New Contributor III
    3 years ago

    Hi, Oscar.

    Are you getting an error? If so, please attach it to this thread.

    Two thoughts: 1) Does the user have EXECUTE permission on the dbo.usp_GLReport stored procedure?  2) Are you entering the command as shown? I think the correct syntax would be EXEC dbo.usp_GLReport

     

    Thanks,
    Bil

    • DanielWillis's avatar
      DanielWillis
      Valued Contributor
      3 years ago

      I think EXEC and EXECUTE are synonymous although I use 'exec' myself. Confirming I can execute a stored proc from a BR or a data adapter and have results returned. As bilbrandon said we really need an indication of what is happening when you try (error message). Do you need to specify a schema perhaps?

      e.g., execute schemaname.dbo.usp_GLReport

  • Oscar's avatar
    Oscar
    Contributor
    3 years ago

    Thank you all for the feedback. My issue was connecting with the database:

     

    Using dbConn As DbConnInfo = BRApi.Database.CreateCustomExternalDbConnInfo(si,"DbProviderType.OLEDB","ExternalDB")

     

    Fixed after using function:

    CreateExternalDbConnInfo