SQL not being accepted
Last Post 09-06-2013 04:55 AM by Ryan Bakerink. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
UnioppUser is Offline
Posts:4
Avatar

--
09-04-2013 12:55 AM

    I have a complicated SQL query that is not being accepted as an SQL driven Default Value and I can't find out why. When I click the Update and Exit it disappears. Any ideas would be much appreciated.

    SELECT DATENAME(MONTH,DATEADD(HOUR,14,GETDATE()))
    + '-' +
    CAST((CASE WHEN DATEDIFF(YEAR,DATEADD(HOUR,14,CONVERT(DATETIME, UserProfile.PropertyValue,101)),CONVERT(DATETIME,'04/01/' + CAST(YEAR(GETDATE()) AS VARCHAR(10)),101)) <= 0
    THEN 0
    WHEN DATEDIFF(YEAR,DATEADD(HOUR,14,CONVERT(DATETIME, UserProfile.PropertyValue,101)),CONVERT(DATETIME,'04/01/' + CAST(YEAR(GETDATE()) AS VARCHAR(10)),101)) >= 18
    THEN 18
    ELSE DATEDIFF(YEAR,DATEADD(HOUR,14,CONVERT(DATETIME, UserProfile.PropertyValue,101)),CONVERT(DATETIME,'04/01/' + CAST(YEAR(GETDATE()) AS VARCHAR(10)),101)) END) AS VARCHAR(10)) AS DefaultValue
    FROM UserProfile
    WHERE UserID = @UserID
    AND   PropertyDefinitionID = 215

    Ryan BakerinkUser is Offline
    river guide
    river guide
    Posts:1900
    Avatar

    --
    09-04-2013 07:19 AM
    Hello,

    This sounds like a truncation issue. Meaning that the SQL Default column accepts a certain amount of characters and your query is exceeding that limit.

    To lift this limit you must do 3 things.

    1.) Modify the @Defaultvalue parameter in this stored procedure to use nvarchar(max):
    DynamicRegistration_AddDynamicQuestion

    2.) Modify the @Defaultvalue parameter in this stored procedure to use nvarchar(max):
    DynamicRegistration_UpdateDynamicQuestion

    3.) Modify the Defaultvalue Column in this SQL Table to have a datatype/size limit of nvarchar(max):
    DynamicRegistration_Question

    After making all of these modifications, you'll then need to go to Host -> Host Settings and restart the application. Then try to paste your query into the Dynamic Registration SQL Default section for your Question and it should save.

    Let me know if you have any questions.

    Thanks,

    Ryan
    UnioppUser is Offline
    Posts:4
    Avatar

    --
    09-04-2013 03:55 PM
    Thank you for your reply.

    I'm sorry but I don't understand where to modify the three items you mentioned. Where are they stored?

    Thank you.
    UnioppUser is Offline
    Posts:4
    Avatar

    --
    09-04-2013 05:37 PM
    After a few hours of searching GOOGLE, I now understand that the items needing modifications are stored procedures in my database.
    I still have no idea of the actual code to use and where to actually use it so I would appreciate it if you could give me the code (for each of the items you said need modifying) and tell me where to paste it.
    Thank you.
    Ryan BakerinkUser is Offline
    river guide
    river guide
    Posts:1900
    Avatar

    --
    09-05-2013 05:17 AM
    Hello Uniopp,

    Unfortunately I won't be able to spend the time to put together these instructions.

    Can you provide HOST access to your site? This will be necessary if we're to make this change for you. Please forward Host Credentials and the URL to your site to dnnsupport@datasprings.com. Can you please provide a subject addressing me? This will make it easier to locate.

    Please let me know if you have any questions.

    Thanks,

    Ryan
    UnioppUser is Offline
    Posts:4
    Avatar

    --
    09-05-2013 03:45 PM
    Ok. Now I have access to my database and found the three items you were talking about. I understand what you were suggesting much better now.
    The first two were already set at nvarchar(max) but the third one wasn't.
    After making the change on the Defaultvalue Column to nvarchar(max) and restarting the application my problem was solved.
    Thank you very much for your help.
    Ryan BakerinkUser is Offline
    river guide
    river guide
    Posts:1900
    Avatar

    --
    09-06-2013 04:55 AM
    Glad to hear that you've resolved this.

    Let us know if you ever have any questions.

    Thanks,

    Ryan



    ---
  • film izle
  • 720 izle
  • film
  • sinema izle
  • film makinesi
  • T�rk�e dublaj film
  • film izle
  • film izle
  • baglan film izle
  • sinema izle
  • 1080 film izle
  • film mercegi