Store Procedure

Previous Member

Hi All,

 

Are board can connect Store Procedure SQL?

 

Thanks.

Björn Reuber

Hi,

 

yes, from an SQL DataReader you can execute Stored procedures. For this you have to switch to manual query and type the stored Procedure using EXEC

 

Regards

Björn

Previous Member

Hi,

 

 

If in SP i have script to create temp table and some parameter, are board still work?

because my report basically shown if user input parameter like ('day','location').

Thanks.

Björn Reuber

Hi,

 

sure, you can use parameter, but this must be "static" ones. So you can't overhand some values from BOARD.

But you can write data to a rolap cube (text cube) and use this data wihtin your SP (but I dont understand, why you want to create new tables inside a SP)

 

Regards

Björn

Previous Member

Hi,

 

Okey So Board can connect SP with parameter which done by manual query use EXEC, but it must "Static" ones.

Thanks Björn Reuber.

Peter van Bennekom

Hi Björn Reuber,

 

Is there a special syntax i have to use in order to get this to work? I tried

 

EXEC dbo.<procName>

 

to no avail. The error reported is 

I can run the procedure successfully in SSMS#

Guido Zamperini

Hi Peter, 

you need to add also the db name (exec DBNAME.SCHEMA.SP)

Peter van Bennekom

thanks Guido but that does not make any sense - I select the connection which specifies the database already.

 

I tried it anyway and it failed with the following error

 

EXEC <dbname>.dbo.<procname>

 

Björn Reuber

Hi,

 

did you used the same SQL user in SSMS and in BOARD? Cause board is getting this error message directly from MSSQL

 

Regards

Björn

Peter van Bennekom

We had a permission issue in the DB and once that was fixed it still did not run.

 

After talks to one of my colleagues it was pointed out that browse did not work - it did run in the data reader screen (this baffles the mind - should be the same right?)

sql procedure stored procedure

Fabio Donatellis

HI Peter van Bennekom

When you configure the datareader to read data from a store procedure, it is not possible to have a preview of the data.

This is because the Browser function performs a select top of the query statement.

in these cases, the best approach is:
   - create a dr with a dummy table, for mapping the fields
   - create a store procedure that returns a data set, with the same order of columns
   - set the sql statement in manual query tab
   - run the datareader

 

Regards

Fabio

Peter van Bennekom

Thanks Fabio Donatellis

 

In your best approach i do not see where the stored procedure is called or executed..

 

I do understand that the browse button returns the top 300 (i think). Could a message be displayed (ok/cancel) indicating all rows would be returned, so the developer would have the choice to run it irregardless or choose not to? Just a suggestion. This is not the only case where the browse button fails - it can also fail when the statement is a bit more complicated than a regular straight select from one table - example: select from two tables where both tables have a column with the same names.

 

Cheers,

Peter

Fabio Donatellis

Hi Peter van Bennekom

the store procedure is called in the manual query tab. 

 

Yes there are several cases in which the browser doesn't work. This happens because the limit is applied on the database side and each database uses a different language / syntax to do it.

 

Thus, when you run the browser, a generic query is created with a limit, in which your query is nested. Very complex queries may not support nested queries with limitation on the number of records.

 

Regards

Fabio

 

Previous Member

Hi,

I followed your instructions. Howerver everytime I execute the datareader, the procedure is not executed. 

This is what I did:

1. create a stored procedure, tested it in Management studio and it works correctly.
2. generate a dummy cube in Board
3. The procedure will return data in the same format as the dummy cube
4. In the manual I entered: exec BOARD_DATA.dbo.load_ChartOfAccounts (so database name.schema.procedure)
5. Everytime I run it I can see in the log exec Board_data.dbo.load_chartofAccount but the reader does not execute the procedure

Could you help me with this?

 

Thanks

 

RJ

Peter van Bennekom

Hi Robert-Jan van Kuppeveld, do you connect to SSMS with the same user as BOARD is using to connect to the database? It may be a permissions issue. You may also want to turn on the SQL Profiler to see what exactly is being send from BOARD to SQLServer. Hope this helps.

Previous Member

I found the issue

 

I am calling a stored procedure, in the procedure I am importing data from a linked server. The user that I use in the ODBC connection has sufficient rights on the SQL server on which the procedure is running but does not have rights on the linked server.