March 15, 2009 at 9:02 pm
Hello,
When I run the following SQL procedure I get the this error:
"Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query."
How can I use CONVERT in this procedure.
CREATE PROCEDURE sprocBlogEntryUpdateSingleItem
@id int,
@title nvarchar(200),
@body nvarchar(4000),
@categoryId int,
@datePublished datetime,
@PostedBy varchar(20),
@searchkeywords varchar(200),
@blogsummary varchar(300)
AS
UPDATE
BlogEntry
SET
Title = @title,
Body = @body,
CategoryId = @categoryId,
DatePublished = @datePublished,
PostedBy = @PostedBy,
searchkeywords = @searchkeywords,
blogsummary = @blogsummary
WHERE
Id = @id
GO
March 15, 2009 at 9:09 pm
Stranger. Is the body field ntext?
March 15, 2009 at 9:23 pm
yes, body field is nvarchar as it needs to accept unicode characters.
March 15, 2009 at 9:25 pm
Hello,
Can you post an example of the SP execution that fails? Also can you provide the BlogEntry table’s definition?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
March 15, 2009 at 9:37 pm
It's strange since the proc shows nvarchar. ntext conversions are what make this error.
Are you sure this is the correct definition? Did this come out of VCS or the server?
March 15, 2009 at 9:39 pm
Hi,
Here is the table schema..
Id -> int
title -> nvarchar (200)
body -> nvarchar (4000)
categoryID -> int
datePublished -> datetime
postedby -> varchar (20)
searchkeywords -> varchar (200)
blogsummary -> varchar (300)
The execution error I am getting is..
"Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query."
This comes as a pop window..
March 15, 2009 at 9:53 pm
From the error I suspect it's something to do with what you're trying to write to the last 3 fields.
My guess is the last field, [blogsummary]. What are you trying to write to that field? Is it a subset of the [body] field?
March 15, 2009 at 9:59 pm
Hello again,
When you say a “pop up window” does that mean you get this error when you call it from an application? (.Net?) If you execute the SP from an SSMS Query Window does it work? If so, then it would most likely be the Data Types in your application that are causing the issue.
Regards,
John Marsh
PS: I was really asking for the Create Table Script for BlogEntry.
www.sql.lu
SQL Server Luxembourg User Group
March 16, 2009 at 6:15 am
Is BlogEntry by chance a view? Does the underlying table have a different datatype than what you have presented here?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2009 at 7:20 am
I don't see an answer directly.
does the error come when you run the code to create the sp, or when you EXEC the sp?
Sounds like the problem is when you issue the
EXEC sprocBlogEntryUpdateSingleItem ...
If this is true, make sure that none of your passed values are ntext
(explicitly CONVERT them to nvarchar(4000) before EXEC the sp)
HTH
-Chris C.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply