NebRef
river guide
Posts:91
 |
| 09-24-2013 01:16 PM |
|
I have written a very long custom SQL Query to generate a table in Dynamic View incorporating multiple joins and select statements. The query works fine when I test it in Host > SQL. However, it has trouble saving in Dynamic Views and, if do get it to finally save, I can't go back in and edit my query at all.
Is there a space limit on the Custom SQL Query field for Dynamic Views? If so, how can I change this.
Thank you.
Jim
|
|
|
|
|
Chad Nash
Posts:5260
|
| 09-24-2013 05:12 PM |
|
Hi Jim - There might be a character limit of 2000 characters. I will need to check the code, I believe this is because the module uses the ModuleSettings table. I can give you a query you can run to set the column width of that column to 4000 or if you are able to change this to be a stored procedure instead of a query directly that might help out as well (where you pass in stored procedure parameters etc...)
Let me know what you think.
-Chad
|
|
|
|
|
NebRef
river guide
Posts:91
|
| 09-27-2013 11:59 AM |
|
Chad:
Unfortunately, I don't know how to create and use stored procedures yet so will have to learn how to do that. In the meantime, could you provide me with the query to increase the character limit and see how that works? Thank you.
Jim |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 09-30-2013 05:00 AM |
|
Hello Jim,
You'll need to modify the size of the SettingValue column in the ModuleSettings table.
Try the following query under Host -> SQL:
Alter table ModuleSettings
Alter Column SettingValue NVARCHAR(MAX)
After executing this query go to Host -> Host Settings and restart the application.
Let me know if this fixes the issue for you. If this doesn't, there may be 1 additional step.
Thanks,
Ryan |
|
|
|
|
NebRef
river guide
Posts:91
|
| 09-30-2013 05:40 AM |
|
Ryan:
Thanks for the suggestion. I did run the query and it ran successfully. I restarted the application but no success. I can save my long query once but if I try to go back in, modify it, and save it again, it hangs and doesn't save the modifications.
I checked, my query is about 7,500 characters not counting the spaces.
Thanks,
Jim |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 09-30-2013 05:54 AM |
|
Hello Jim,
In this case there's a couple more steps to complete this.
1.) Go to Host -> SQL and execute this statement:
sp_helptext "UpdateModuleSetting"
1a.) You should see the contents of this stored procedure. Copy/paste into Notepad or SQL Server Management Studio.
1b.) You're going to need to modify the Parameter in this stored procedure that collects the SettingValue. Should look like this "@SettingValue nvarchar(xxx)".
Change the "xxx" to "MAX".
1c.) Then change the CREATE statement(which is the first word in the stored procedure) to ALTER. So there's only 2 small modifications you're making.
A.) Size of the Parameter for SettingValue
B.) Changing CREATE to ALTER.
After making these changes, please copy/paste this modified stored procedure into Host -> SQL and execute. After executing go to Host -> Host Settings and restart the application.
You may want to perform the same steps for the AddModuleSetting stored procedure, this way anytime you initially create a Dynamic View(which is an Add not an Update), it'll work.
Please let me know if you have any questions.
Thanks,
Ryan |
|
|
|
|
NebRef
river guide
Posts:91
|
| 09-30-2013 12:59 PM |
|
Ryan:
I did as suggested. Both "UpdateModuleSetting" and "AddModuleSetting" were already set to "MAX" for @SettingValue but did as recommended anyway.
Still won't save properly. Any other suggestions?
Jim |
|
|
|
|
NebRef
river guide
Posts:91
|
| 09-30-2013 05:59 PM |
|
Ryan:
I was able to create a stored procedure in SQL Server Management Studio that uses two variables - @club and @startdate. I am able to execute this stored procedure in Server Management Studio successfully.
When I had Dynamic Views set up to run this query as a custom SQL query, I passed these two variables to this page from a hyperlink and the first few lines of my custom SQL query were as follows:
Declare @club as nvarchar(50)
Set @club='$(club)'
Declare @startdate as datetime
Set @startdate='$(startdate)'
I do not know how to execute a stored procedure in Dynamic Views using the pass-through options that I have used successfully before. Any help?
Thanks, Ryan.
Jim
|
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 10-02-2013 06:36 AM |
|
Hello Jim,
Do you have Dynamic View Search filters on columns named club and startdate?
If not, these two tokens( '$(club)' and '$(startdate)' ) will not render. Also with stored procedures you'll need to enable allow free form. By enabling this option it disables all Search Filters. So you'll have to manually create the filters in your WHERE clause, in your Stored Procedure or raw SQL.
Please let me know if you have any questions.
Thanks,
Ryan
|
|
|
|
|