Execute Stored Procedure on External Database

[Oscar]

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.

[bilbrandon]

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]

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

[JackLacava · Accepted solution]

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]

Thank you Jack! Your post solved my issue

[Oscar]

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