10-20-2022 03:23 PM
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.
Solved! Go to Solution.
10-21-2022 04:53 AM
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:
10-20-2022 05:22 PM
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
10-20-2022 06:46 PM
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
10-21-2022 04:53 AM
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:
10-21-2022 09:57 AM
Thank you Jack! Your post solved my issue
10-21-2022 09:56 AM
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