Post reply

Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query.

  • May 6, 2009 at 6:49 am

    #204424

    Ok, I'm confused ...

    I have a parameter in my report which passes a multi value VARCHAR value to a procedure. The parameter argument is a VARCHAR(4000). It works just fine, and has been working fine. I found a case (based on date range) to as where I'm getting the error "Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query." After tracing the activity, I found that SSRS is sending all my values to the proc with in unicode ('N). As soon as I remove it, works like a champ.

    The thing is though, the source data, is VARCHAR, so why is it barking at me saying I'm trying to convert ntext to varchar? If anything, shouldn't it be trying to convert it to nvarchar from varchar?

    So my question is, can I remove this from SSRS? If not, any suggestions? I've never seen such a thing ... I simply changed the parameter on the procedure from varchar to nvarchar ... but again, I'd like to know more about this and if I have any better options.

    Thanks

  • May 11, 2009 at 8:38 am

    #992166

    Any thoughts on this? I really need to use VARCHAR as I need greater than a 4000 limit ...

    Thanks

  • May 11, 2009 at 11:02 am

    #992324

    So after reviewing this again, I think SSRS is converting the data source to ntext because it's so large, and then obviously when passing into the procedure, it blows up.

    Even though the source column is VARCHAR in my dataset, I tried adding a cast AS VARCHAR, and I'm still having the same error.

    Obviously If I was SQL2005/8 I could use (MAX), but for now I'm stuck. Any thoughts at all would be most appreciated.

  • June 22, 2010 at 4:43 am

    #1183213

    I was facing the same problem. But I came with a solution. I dont know whether it is correct or not.

    create procedure sp_test

    @STR varchar(8000)

    changed to

    create procedure sp_test

    @STR ntext

    as

    declare @str1 varchar(8000)

    set str1= conver(varchar(8000), @STR)

    blah blah

  • June 22, 2010 at 6:37 am

    #1183275

    Amol, I ended up doing the same thing to resolve this. Never found any other resolution.

    Thanks for the feedback.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply