Hi,
I have a stored procedure that is creating a temp table to get data from, temp table is required because of some complex logic with different tables involved in data fetch. I am having a probem of no columns listed in data view on design time, while using wizard after fetching data in sqldatasource.
When trying to build a datasource using the stored procedure, After successfuly fetching data in data source and finishing fetch process when wizard try to start report design steps, I get error as
http://screencast.com/t/lG0QjXLZ
Error Message: 'Invalid object name '#temptablename'.'
In order to design report (drag and drop to work properly in the report design), I need the data view to show all the columns return by stored procedure to show the columns currently it does not because it obviously does not understand temp tables.
I don't know but once data is returned by the stored procedure, should not it cache that data for design?
OR else i have to create some real tables for design time support (it will require a lot of overhead work as i have to change so many stored procedures) and once report designing is done and use temp for run-time only?
Regards
I have a stored procedure that is creating a temp table to get data from, temp table is required because of some complex logic with different tables involved in data fetch. I am having a probem of no columns listed in data view on design time, while using wizard after fetching data in sqldatasource.
When trying to build a datasource using the stored procedure, After successfuly fetching data in data source and finishing fetch process when wizard try to start report design steps, I get error as
http://screencast.com/t/lG0QjXLZ
Error Message: 'Invalid object name '#temptablename'.'
In order to design report (drag and drop to work properly in the report design), I need the data view to show all the columns return by stored procedure to show the columns currently it does not because it obviously does not understand temp tables.
I don't know but once data is returned by the stored procedure, should not it cache that data for design?
OR else i have to create some real tables for design time support (it will require a lot of overhead work as i have to change so many stored procedures) and once report designing is done and use temp for run-time only?
Regards
2 Answers, 1 is accepted
0
Accepted
Hello Richard,
Peter
the Telerik team
This seems as a known issue related to Microsoft. Check out the Temporary Table in Stored Proc forum thread and let us know how it goes.
Kind regards,Peter
the Telerik team
1
Richard
Top achievements
Rank 1
Rank 1
answered on 24 Mar 2011, 03:15 PM
Adding
IF 1=0 BEGIN
SET FMTONLY OFF
END
in start of stored procedure did the trick.
Thanks!
IF 1=0 BEGIN
SET FMTONLY OFF
END
in start of stored procedure did the trick.
Thanks!
Matt
commented on 10 Oct 2021, 07:56 PM
Top achievements
Rank 1
Rank 1
Adding the following SQL at the start of the statement seems to be the only way to use temp tables in an sql sata source in the telerik reporting report designer:
IF 1=0 BEGIN
SET FMTONLY OFF
END
Without this, the designer will report an error like: Invalid object name #[name of temp table]
It is a shame that this info can only be found here in the forums, and is not part of the main documentation!
Todor
commented on 13 Oct 2021, 01:34 PM
Telerik team
The SqlDataSource component uses internally the specified ADO.NET provider and this limits the SQL queries to SELECT statements. Therefore, declaring SQL variables, including temp tables in a query is not supported.
Indeed, you may use variables in a Stored Procedure. The workarounds regarding temp tables in Stored Procedures are described in the KB article Configuring Stored Procedure with Temporary Tables.