Hello if this product can quickly add support for stored procedures I will push and have justification for full developer license at "Bank Of America".

Thanks in advance,

Matthew

 

Matthew,

Thanks for your post. We are still reviewing the best way to offer a solution for supporting stored procedures. A few things...

First... there is a major reason (which we discovered after release) as to why Dynamic Views cannot offer stored procedure support but rather basic SQL Query support. I will try and explain in a short summary and long, sorry if this gets boring.

Part of this has to do with the fact that we only select the top 'x' records and the fact that all of the search fields are filtered directly in the query itself and not at the code level. Our research has indicated that when the filter is added at the query level the performance is increased considerable. In order to do this was have to perform steps such as creating a temp table, only selecting the top records specified within the module config, etc...

Here are a few directions we can go with this.

1. If you are running SQL Server 2008 then we might be able to tell you a stored procedure you can modify within the application that should be able to offer more direct support. Right now the module only requires SQL Server 2005 and I believe SQL Server 2008 has the ability to more easily do a direct 'SELECT INTO TEMP TABLE' from a stored procedure.

2. We are working on another alternative for this but still reviewing based on item #3 below. The alternative is that we do an option in SQL Server called setting "SET FMTONLY ON" and then off. This gets us the column names and data types with no data, then we can build a unique temp table. The problem now is that since each module instance query or procedure is unique we don't know what columns are needed. If we do this more easily figure this out.

3. We are reviewing another alternative that is kind of holding back development on item #2. Basically this alternative is to filter both the count and the actual search filter at the code level. As I mentioned this isn't preferred because of performance however this also limits the module with other types of databases's. Right now if you have an 'External Connection' this won't work with Oracle because oracle doesn't process stored procedures and the 'TOP' x records the same etc... Oracle does not use Top X syntax. To do the same thing in Oracle, you have to: Select * from tablename where rownum < x etc... So, we might have to add an alternative option.

So... We are reviewing and working on the best way to handle this. If you need something handled outside of our standard patch or release schedule we can also handle this via our Premium Support services at http://www.datasprings.com/Premium-Support.

Thanks,

-Chad

FYI - This is the option we are reviewing related to SQL Server 2008 where we might be able to handle this in the future as long as the module required SQL Server 2008.

http://sqldev.wordpress.com/2008/05...procedure/


-Chad

Matthew,

Hi. I wanted to follow up on this thread. We have found a good way to support this in the next release of Dynamic Views. We are working on a new method when selecting SQL Query so that you can choose between 'Fixed' (as it works now) where the top records are taken and the row filter is included in the query etc... and also 'Free Flow'. The 'Free Flow' method would allow you to basically use tokens in your queries directly and therefore it wouldn't require you to use SQL Server or use a stored procedure etc...

Thanks,

Chad

So does this mean we can not use an SP with Dynamic Views? or can we execute a stored procedure with the free flow option?

Hello,

You can easily use a stored procedure in Dynamic Views by calling your stored procedure in the Custom SQL Query section or in Dynamic Views 2.0 you can execute a query(or stored procedure) based on if a hyperlink or image is clicked.

This is a new enhancement "Actions".

If you have any questions please let us know.

Thanks,

Ryan

Is there an example of configuring the External Connection setting? The description says "Enter the name of the connection string" -- do you mean enter the content of the connections string, or put the connection string in web.config and reference it here by name?

So, does the latest version of DF (3.4?) support the use of stored procedures within the SQL Query when using the Custom SQL Query option?  I have been trying this with a couple of straightforward queries and when I click the "Generate Column Names" link, no columns are created and I notice the following javascript error:

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB6.6; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET CLR 3.0.30618; .NET CLR 3.5.21022; MDDC; Media Center PC 5.0; SLCC1; OfficeLivePatch.1.3; MS-RTC LM 8; OfficeLiveConnector.1.5; .NET4.0C; .NET4.0E)
Timestamp: Fri, 14 Jan 2011 22:16:11 UTC

Message: Sys.WebForms.PageRequestManagerServerErrorException: Incorrect syntax near '0'.
Line: 5
Char: 62099
Code: 0
URI: http://localhost:2009/ScriptResourc...t=5c2f384e

Thanks!
Brian

Hello bcorngtr,

I was speaking on the behalf of the newly added enhancement Actions.

Dynamic Forms can positively use stored procedures, functions, CTE's, etc...

Dynamic Forms is very useful when it comes to using SQL.

If you have any questions please let me know.

Thanks,

Ryan

Hello,

I am hoping to use a stored procedure in Dyn Views 2.1 but cannot tell from this thread whether that is supported. Is it?

I was hoping to use the filter options as parameters.

~Steve

Hi. Well I know with the very latest release of Dynamic Views it fully supports SQL stored procedures now... Not sure about 2.1 or not. Is there a "Free Form" option in the 2.1 interface? If so, that should be used when needing to use stored procedures.

-Chad

Yes, there is a free form but the stored procedure has parameters. Can I use dyn views having the search criteria of the user be parameters on the stored procedure instead of filtering the data after all the data is returned?

Was the last question answered? If so what was the result? Can Store Procedures be called that uses parameters? Can the parameters be entered by the person runnning the report?