update data with stored procedure

building semantic layer
1

Hello!
I’m using Bo 6.5.1 and Sql Server.

I need to call a stored procedure before run my report for fill a table.

Well I have made some test with SP, and they work all fine… with only one exception… they can not write any data inside the DB!!! :nonod:

For example:

If my procedure I made:


Select * from MyTable
where bla bla bla....

it work fine

but if I write


Update MyTable
Set MyTotal = N
Where bla bla bla

Select * from MyTable
where bla bla bla....

I will receive an error : “Exception: CS, Unable to run the sql execute.”

My Sp, of course, is more complex than this, but I have made a little test with these command and I receive the same error.

What can I do? Somebody may help me?

thanks in advance

Enrico

orcabigia (BOB member since 2005-09-06)

2

Did you check the sql server owner you use through BO stored proc has required rights to udate / insert values in your database ?

In the other hand, BO is a dedicated reporting tool and it is very strange to use it to make update :smiley:

bernard timbal :fr: (BOB member since 2003-05-26)

3

Hi
Thank’s for your reply. :smiley:

Yes, my user have all access right.

Perhaps it’s stange use BO for update, but I need to calculate some value with complex formulas (stored into a db table) before running my report. (is it really so stange? :crazy_face: ).

Thanks

Enrico

orcabigia (BOB member since 2005-09-06)

4

then, if it is not a user issue, maybe BusinessObjects has an internal system to avoid query other than “Select” for security reasons, including stored procedures but I am not sure about it.

I suggest you to ask techsupport about this question

Regards !

bernard timbal :fr: (BOB member since 2003-05-26)

5

I’ve not done it, but perhaps this past discussion might give you some insights.

(I found it by doing a Search for keywords stored procedure select

Anita Craig :us: (BOB member since 2002-06-17)

6

I found a discussion here about 6.5 restricting stored procedures to just do select – and it sounds like some hotfix for SP 2 might reverse that restriction.

Anita Craig :us: (BOB member since 2002-06-17)

7

Hi
Many many thanks Anita!
I had already found the past discussion and, reading them, all seam work! :smiley: (or my english is too bad! :oops: )
Your second post confirm my fear.
I have tried another solution: using a VBA script for call my SP with update.

Example:


Private Sub Document_BeforeRefresh(Cancel As Boolean)
Dim a As ADODB.Connection
Set a = New ADODB.Connection

a.ConnectionString = "Provider=SQLOLEDB.1;Password=MyPassword;Persist Security Info=True;User ID=MyUserName;Initial Catalog=MyDataBase;Data Source=MyServerSQL"

a.Open
a.Execute "MyStoredProcedure"
a.Close


End Sub

There are two little defects about this:

  • I have not found the event for call my procedure after the prompt and before the start of the select. So, for use some prompt, I must declare the form for manage all my request defined in the prompt.
  • I must disable the control check about the macro for BO.

I’m not sure that it’s the best solution, but, it seam work.

again thanks

Enrico

orcabigia (BOB member since 2005-09-06)

8

If I were in your shoes, I’d file a case to see if you can get a Hotfix from tech support that does what the hotfix in SP2 does.

Anita Craig :us: (BOB member since 2002-06-17)