none
parameter resized to varchar 4000 RRS feed

  • Question

  • Upgraded an asp.net app  from vs05 and sql05 to vs2010 and sql08. It uses LinqToSql.

    In vs08 and sql05 everything worked.  Now I get an error.

    A trace shows LINQ is passing this syntax.

    exec sp_executesql N'SELECT [t0].[CourseNumber], [t0].[CourseTitle], [t0].[CourseDescription], [t0].[SponsorOrgName], [t0].[SearchLink]
    FROM [dbo].[tvfSearchCoursesResults](@p0) AS [t0]',N'@p0 varchar(8000)',@p0='"LLW*"'

    Msg 30020, Level 16, State 101, Line 1
    The full-text query parameter for Fulltext Query String is not valid.

     

    If I run the following they both work fine:
    SELECT [t0].[CourseNumber], [t0].[CourseTitle], [t0].[CourseDescription], [t0].[SponsorOrgName], [t0].[SearchLink]
    FROM [dbo].[tvfSearchCoursesResults]('"LLW*"' ) AS [t0]

    SELECT * FROM [VTC].[dbo].[tvfSearchCoursesResults] (
       '"LLW*"')

     

    Here is the table value function

     


    ALTER FUNCTION [dbo].[tvfSearchCoursesResults]
    (   
     @SearchString varchar(255)
    )
    RETURNS TABLE
    AS
    RETURN
    (
      SELECT top 100 percent A.CourseNumber,
             A.CourseTitle,
             A.CourseDescription,
             A.SponsorOrgName,
             C.SearchLink
      FROM Courses A
      INNER JOIN
       CONTAINSTABLE(Courses, *, @SearchString) B
       ON A.CourseNumber = B.[KEY]
      INNER JOIN dbo.CoursesStaticData sd
        ON a.CourseNumber = sd.CourseNumber
      Inner Join dbo.lutRegistrationModes C
       ON sd.RegistrationModeID = C.RegistrationModeID
     WHERE sd.ShowInVTC = 1
     ORDER BY B.RANK DESC

    )

    Is there a problem with the syntax in what LINQ is creating or something?

    If you notice the linq created statement shown in the trace declares the parameter as varchar(8000)

    Where the sproc has it as varchar(255).

    The dbml code behind also has it as 255

            [global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.tvfSearchCoursesResults", IsComposable=true)]
            public IQueryable<tvfSearchCoursesResultsResult> tvfSearchCoursesResults([global::System.Data.Linq.Mapping.ParameterAttribute(Name="SearchString", DbType="VarChar(255)")] string searchString)
            {
                return this.CreateMethodCallQuery<tvfSearchCoursesResultsResult>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), searchString);
            }

     

    Tuesday, July 20, 2010 10:06 PM

Answers

  • Thanks for bringing this into our notice.

    I delved a bit deeper into this and it appears that in L2S v4 we are for some reason, in the current scenario, setting a MAX value on the SqlParameter where the type is varchar. The error is thrown due to the size mismatch as expected by the tbvl function.

    You might want to open an incident with Commercial Technical Support to track this issue further.

     


    Bindesh V, Developer Support
    • Proposed as answer by Ramani Sandeep Wednesday, August 11, 2010 7:15 AM
    • Marked as answer by chuck02323 Thursday, August 12, 2010 1:53 PM
    Wednesday, August 11, 2010 4:59 AM
    Answerer

All replies

  • Hi,

    We are currently looking into your issue and will let you know what we find out.

    Thanks,

    Cathy Miller

    Microsoft Online Community Support

    Monday, July 26, 2010 7:14 PM
    Moderator
  • Any idea on how long it will take to look into this?
    Thursday, July 29, 2010 1:20 PM
  • Can you script out the tables involved and send it to me. I tried reproducing this but we are not hitting this issue from our sample table.

    Please send the script to cts-bindeshv@live.in

    Thanks


    Bindesh V, Developer Support
    Friday, July 30, 2010 2:09 PM
    Answerer
  • Does this problem happen with VS08 against your SQL08 database? It would be worth knowing if either the VS or SQL version change is the contributing factor.

    [)amien

    Friday, July 30, 2010 3:46 PM
    Moderator
  • I scripted the table value function I was using.  See the first post.
    Friday, July 30, 2010 8:14 PM
  • No it was in production for about 6 months with no problems.

     

    I'm pretty sure it was the change to the .Net Framework 4 since that where LinqToSql is or in the SQL client.

     
    Friday, July 30, 2010 8:37 PM
  • The script that you have only has the function code. I would like to get the exact schema of the tables involved so that i can create a L2S project and move it 2010 and see the difference.

     


    Bindesh V, Developer Support
    Tuesday, August 3, 2010 3:41 PM
    Answerer
  • Was emailed to you, functions and tables. cts-bindeshv@live.in
    Tuesday, August 3, 2010 9:40 PM
  • I doubt if it is a dbml or a conversion problem.  I removed the table value function and readded the function.  The dbml code behind is correct. 

     

    The sql server trace shows:

    exec sp_executesql N'SELECT [t0].[CourseNumber], [t0].[CourseTitle], [t0].[CourseDescription], [t0].[SponsorOrgName], [t0].[SearchLink]
    FROM [dbo].[tvfSearchCoursesResults](@p0) AS [t0]',N'@p0 varchar(8000)',@p0='"LLW*"'

    Which shows the wrong sized parameter being passed to sql server.

     

     

    Tuesday, August 3, 2010 9:52 PM
  • Thanks for all your patience on this issue.

    I was unable to execute your scripts due to dependencies. However, I was able to do a repro of my own. Yes the issue is reproducible on VS2010. The searchstring query is 255 chars in sproc but the generated query under profiler shows the size as 8000 chars ..

     

    exec sp_executesql N'SELECT [t0].[KEY] AS [KEY], [t0].[RANK]
    FROM [dbo].[tvfSearchCustomers](@p0) AS [t0]',N'@p0 varchar(8000)',@p0='alfki'
    

    However under VS2008 the generated query looks like this

    exec sp_executesql N'SELECT [t0].[KEY] AS [KEY], [t0].[RANK]
    FROM [dbo].[tvfSearchCustomers](@p0) AS [t0]',N'@p0 varchar(5)',@p0='alfki'
    

     I am not sure if the above difference is the cause of the error but   I get a similar error as reported by you.I am looking into this and update the thread with my findings.

     


    Bindesh V, Developer Support
    Saturday, August 7, 2010 5:14 AM
    Answerer
  • Thanks for bringing this into our notice.

    I delved a bit deeper into this and it appears that in L2S v4 we are for some reason, in the current scenario, setting a MAX value on the SqlParameter where the type is varchar. The error is thrown due to the size mismatch as expected by the tbvl function.

    You might want to open an incident with Commercial Technical Support to track this issue further.

     


    Bindesh V, Developer Support
    • Proposed as answer by Ramani Sandeep Wednesday, August 11, 2010 7:15 AM
    • Marked as answer by chuck02323 Thursday, August 12, 2010 1:53 PM
    Wednesday, August 11, 2010 4:59 AM
    Answerer
  • Can you post the issue on our Connect Feedback site:
    http://connect.microsoft.com/SQLServer/Feedback

    In case you aren't familiar with the process, the Connect site is where you can submit problems that are found with the product or suggestions for improvement, interacting with our product group regarding the issues.  The issues can be voted on by the community as well when submitted there.

    Another option is to open a case for a more in-depth level of support, as Bindesh suggested.

    Please visit the below link to see the various paid support options that are available to better meet your needs.
    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Thanks,

    Cathy Miller

    Microsoft Online Community Support

    Wednesday, August 11, 2010 8:22 PM
    Moderator
  • I upgraded a solution today to VS2010/.Net 4 and I'm now getting this same error. Has a fix or workaround been identified?
    Tuesday, August 24, 2010 5:01 AM
  • I haven't heard of one.

    I put in a bug report.  You may wish to validate or vote on it.

    https://connect.microsoft.com/SQLServer/feedback/details/585759/lint-to-sql-parameter-resized?wa=wsignin1.0

     

    Wednesday, August 25, 2010 1:50 AM