Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query. Expand / Collapse
Author
Message
Posted Wednesday, May 6, 2009 6:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:54 AM
Points: 1,171, Visits: 1,995
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


---
SQLSlayer
Making SQL do what we want it to do.

Post #711010
Posted Monday, May 11, 2009 8:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:54 AM
Points: 1,171, Visits: 1,995
Any thoughts on this? I really need to use VARCHAR as I need greater than a 4000 limit ...

Thanks


---
SQLSlayer
Making SQL do what we want it to do.

Post #714176
Posted Monday, May 11, 2009 11:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:54 AM
Points: 1,171, Visits: 1,995
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.


---
SQLSlayer
Making SQL do what we want it to do.

Post #714348
Posted Tuesday, June 22, 2010 4:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 22, 2010 4:40 AM
Points: 1, Visits: 0
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
Post #940907
Posted Tuesday, June 22, 2010 6:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:54 AM
Points: 1,171, Visits: 1,995
Amol, I ended up doing the same thing to resolve this. Never found any other resolution.

Thanks for the feedback.


---
SQLSlayer
Making SQL do what we want it to do.

Post #940979
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse