May 20, 2009 at 2:55 am
I have a column of varchar(5000) in sqlserver but when i send the data of size more than 4000 characters as parameter to a procedure it is throwing an exception "Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query.".Please help me out
May 20, 2009 at 3:25 am
Are you sure the datatype is varchar and not nvarchar? A nvarchar in SQL 2000 is limited to about 4000 characters.
What's the datatype for the parameter in the stored procedure?
[font="Verdana"]Markus Bohse[/font]
May 20, 2009 at 3:42 am
could you post some code please
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2009 at 3:49 am
kiran.chintala14 (5/20/2009)
I have a column of varchar(5000) in sqlserver but when i send the data of size more than 4000 characters as parameter to a procedure it is throwing an exception "Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query.".Please help me out
As the error message states implicit conversion from ntext to varchar is not allowed.
rewrite your code to do a convert before inserting ntext data into a varchar column.
insert into yourtable (thekey, thevarcharcol)
select TheOriginalKey
, convert(varchar(5000),theNTEXTcolumn)
from TheOriginalTable
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 20, 2009 at 5:33 am
Yes it is varchar(5000) and db is sqlserver2000. Can u tell me the maximum size the varchar can hold. Thanks in advance
May 20, 2009 at 5:36 am
Could you show us your code PLEASE...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2009 at 5:37 am
Thanks Bhose,
The parameter datatype is also varchar(5000) and i am sure of it.Can you tell me the max size the varchar can hold in sqlserver 2000.Thnaks in Advance
Kiran
May 20, 2009 at 5:42 am
ALTER PROCEDURE [dbo].[usp_MessageBoard_InsertMessage]
@Username varchar(100),
@MessageType varchar(50),
@MessageText varchar(5000),
@status bit,
@DateCreated datetime,
@DateModified datetime,
@ModifiedBy varchar(100),
@Results varchar(100) Output
AS
INSERT INTO Messages ( MessageType, MessageText, Status, Username, DateCreated, DateModified, ModifiedBy )
VALUES ( @MessageType, @MessageText, @status, @Username, @DateCreated, @DateModified, @ModifiedBy )
SET @Results = 'Record Added Successfully.'
This is the Storeprocedure where i am facing prob
May 20, 2009 at 5:44 am
The Max lengh for a VARCHAR in 2000 is VARCHAR(8000)
Could you show us the table definition of the Messages table in your proc please
Also is the error in SQL or in you client application?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 20, 2009 at 5:45 am
I cannot see why simply posting the code is an issue ??
The error states you are trying to implicitly convert a NTEXT column !
from Books online SQL2000
varchar[(n)]
Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 20, 2009 at 5:56 am
CREATE TABLE [dbo].[Messages](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[MessageType] [varchar](50) NULL,
[MessageText] [varchar](5000) NULL,
[MessageSubject] [varchar](50) NULL,
[Status] [bit] NULL,
[Username] [varchar](100) NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[ModifiedBy] [varchar](100) NULL,
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
(
[MessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
The above is the table created stmt and below is the error
Msg 257, Level 16, State 2, Procedure Usp_MessageBoard_InsertReply, Line 0
Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query.
May 20, 2009 at 5:58 am
Kiran,
I don't think that the error was raised by the code you posted. The error says that you cannot implicit convert from NTEXT to VARCHAR. The implicit convert from VARCHAR to NTEXT works.
Just try this little sample:
[font="Courier New"]CREATE TABLE TestNText
(
Id INT NOT NULL IDENTITY,
Txt NTEXT,
CONSTRAINT PK_TestNText
PRIMARY KEY CLUSTERED
(Id)
)
GO
CREATE PROCEDURE usp_TestNText
@Txt VARCHAR(8000)
AS
INSERT INTO TestNText
SELECT @Txt
GO
DECLARE @Txt VARCHAR(8000)
SELECT @Txt = REPLICATE('a', 8000)
EXECUTE usp_TestNText @Txt
SELECT
Txt,
DATALENGTH(Txt) / 2
FROM TestNText
DROP PROCEDURE usp_TestNText
GO
DROP TABLE TestNText
[/font]
Greets
Flo
May 20, 2009 at 6:14 am
As Florian already said, I don't think the code you posted is causing the problem.
The procedure as posted works fine on my system.
But just a thought, could it be that there are triggers on the table ?
[font="Verdana"]Markus Bohse[/font]
May 20, 2009 at 6:17 am
I agree with the latter two posts as well.
It also looks like your Error message is being raised from Usp_MessageBoard_InsertReply
However the proc you posted for us is [usp_MessageBoard_InsertMessage].
Please could you post the code for Usp_MessageBoard_InsertReply and as mentioned check the triggers (my feeling would be that the error raised would give the name of the trigger but worth checking)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply