Forum Discussion

Oscar's avatar
Oscar
Contributor
3 years ago
Solved

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 ar...
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:

     

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