Are board can connect Store Procedure SQL?
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
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').
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)
Okey So Board can connect SP with parameter which done by manual query use EXEC, but it must "Static" ones.
Thanks Björn Reuber.
Hi Björn Reuber,
Is there a special syntax i have to use in order to get this to work? I tried
to no avail. The error reported is
I can run the procedure successfully in SSMS#
you need to add also the db name (exec DBNAME.SCHEMA.SP)
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
did you used the same SQL user in SSMS and in BOARD? Cause board is getting this error message directly from MSSQL
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
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
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.
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.
I followed your instructions. Howerver everytime I execute the datareader, the procedure is not executed.
This is what I did:
Could you help me with this?
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.
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.
Retrieving data ...