Skip to main content
O
Oscar
Contributor
October 20, 2022
Solved

Execute Stored Procedure on External Database

  • October 20, 2022
  • 3 replies
  • 0 views

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.

Best answer by JackLacava

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_0-1666342288468.png

 

3 replies

B
bilbrandon
Contributor
October 20, 2022

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

    D
    DanielWillis
    Contributor
    October 20, 2022

    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

    J
    OneStream Employee
    JackLacavaOneStream EmployeeAnswer
    OneStream Employee
    October 21, 2022

    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_0-1666342288468.png

     

      O
      OscarAuthor
      Contributor
      October 21, 2022

      Thank you Jack! Your post solved my issue

      O
      OscarAuthor
      Contributor
      October 21, 2022

      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

       

       

      Terms & ConditionsAccessibility statement