BMP v9: Issue with a Stored Procedure in a business object

William Tanner

Hi,

 

Does anyone know the correct database permission required for Metastorm to use stored procedures in a Business Object.

Using Metastorm v9 SR2.

 

I have the following scenario.

 

1) Creating a stored procedure in the Metastorm DB, connecting as DBO .... viola the query type business object can execute and show test results.

 

2) Using an existing stored procedure on a remote database via a Connection object. Testing the stored procedure returns nothing back, not even column names as it did in 1)

 

I have executed the stored procedure in SQL Management Studio and it runs perfectly.

I have tested the connection and it connects, and I can write a SQL query that returns results on clicking "Test" --> "Refresh"

 

So it seems stored procedures in a business object need some special permission that's inbetween the execute permission and full dbo access.

 

Ideas?

 

 

 

William Tanner

Issue resolved

 

The issue was the stored procedure on the remote database was inserting into a temporary table before finally selecting out of it once all logic was completed.

 

In order to execute test on this procedure I had to change the procedure to include "SET NOCOUNT ON" (MSSQL), this then allowed me to run tests with test parameters, however the column names still wouldn't pass back into the Variable name table. Meaning I still couldn't Deploy the solution.

 

On testing I found any stored procedure with an insert or update statement before the result set select would have this issue.

 

I'm not a DBA so i'm not sure if there are or SET options available which would have fixed this however, I was able to re-write the stored procedure to no longer use inserts and updates into a temporary table.

 

Hope this helps someone else out there.

 

Cheers,

Bill

Hung_Le

Hi,

 

Were you able to find any way around this?  I'm running into the same issue, curious to see why the variables are not populating for these cases.

 

Thanks,

Hung

Hung_Le

We eventually found a way around this.  Table variables appear to work.  Also make sure that parameter datatypes are correct, otherwise you will see the behaviour where you can test your BO but it doesnt populate the variables names.