April 29, 2006 at 1:20 pm
I have a problem with using a parameter with ntext type in a stored procedure.
I have a .NET function that takes two parameters.One is the name of the stored procedure that updates the table,and the second is of type NameValueCollection that holds the stored procedure's parameters and their values.The task of this function is to invoke the stored procedure.In my code I fill a variable of type NameValueCollection with my stored procedure's parameters that one of them is to be assigned to the ntext field in the table.In my ASP.NET page user can enter text of any kind(even copy from word).I previously didnt use this stored procedure technic , I mean I constructed the update query in my ASP.NET code rather than using stored procedure.And that way I had no problem and It worked fine.But in this case I dont know why when the size of text exceeds a limit all text becomes question marks('?').What's wrong?> Help me please thanks.
May 2, 2006 at 5:43 am
Please refer to BOL for ntext datatype usage, you need to use READTEXT,WRITETEXT,UPDATETEXT when the size of the text crosses some limit.
Prasad Bhogadi
www.inforaise.com
May 6, 2006 at 9:50 pm
Can you plz post code?
May 8, 2006 at 12:45 pm
I think that limit is about 4030 characters for NVarChar, then you move to NText and have to start using READTEXT,WRITETEXT,UPDATETEXT. NText will give you 2GB bytes for the field.
May 9, 2006 at 9:37 am
Problem is with stored procedure's parameter size limit.Because when I make query in my code there is no problem but when I use stored procedure problem happens;Tell me about stored procedure's parameter size limit. How can I deal with it.
May 9, 2006 at 9:56 am
As far as I know, an NVarChar is going to be limited to about 4000 unicode characters. Is your data/field going to be larger than that? If so, you will need to use the NText data type instead of NVarChar.
How big is your data going to be?
What type have you declared the stored procedure parameter as?
What does your code look like to use it?
May 10, 2006 at 1:52 am
Infact in SQL Server 2005 we have NVARCHAR(MAX) datatype that has a maximum storage size of 2^31-1 bytes
Prasad Bhogadi
www.inforaise.com
May 12, 2006 at 12:53 am
I have used param types on stored procs such as image & text - ntext is no different... That's why if you post code we can perhaps see what you are doing wrong from the .NET side of things...
So could you plz post both your .NET code and your stored proc code? Thanx
January 6, 2010 at 9:12 am
I am having a similar problem. I am using SQL 2008. I have a stored procedure that I am passing 2 parameters into. One parameter is an integer and the other is a string. I have the string defined as NTEXT. When I call this procedure from my VB code, I am not getting the expected results (if string passed in is 250 characters or less, I get the correct results) because for some reason my NTEXT field was not read in its entirety. Can someone help me see what I am doing wrong?
CREATE PROCEDURE [dbo].[GCI_PM_FileSubTypeGetBy_FileTypeAndContracts]
@FileTypeID INT,
@Contracts ntext
AS
EXEC('SELECT DISTINCT
GCI_PM_FileSubType.ID, GCI_PM_FileSubType.SubTypeName, GCI_PM_FileSubType.FileTypeID, GCI_PM_FileSubType.CreateDate,
GCI_PM_FileSubType.CreateUserID, GCI_PM_FileSubType.ModifyDate, GCI_PM_FileSubType.ModifyUserID,
CreateUsers.FirstName + '' '' + CreateUsers.LastName AS CreateUser, ModifyUsers.FirstName + '' '' + ModifyUsers.LastName AS ModifyUser,
GCI_PM_FileType.FileTypeName
FROM GCI_PM_FileSubType WITH (NOLOCK) INNER JOIN
GCI_PM_FileSubTypeXContract ON GCI_PM_FileSubType.ID = GCI_PM_FileSubTypeXContract.FileSubTypeID LEFT OUTER JOIN
GCI_PM_FileType WITH (NOLOCK) ON GCI_PM_FileSubType.FileTypeID = GCI_PM_FileType.ID LEFT OUTER JOIN
Users AS ModifyUsers WITH (NOLOCK) ON GCI_PM_FileSubType.ModifyUserID = ModifyUsers.UserID LEFT OUTER JOIN
Users AS CreateUsers WITH (NOLOCK) ON GCI_PM_FileSubType.CreateUserID = CreateUsers.UserID
WHERE (GCI_PM_FileSubType.ID IN
(SELECT FileSubTypeID
FROM GCI_PM_FileSubTypeXContract AS GCI_PM_FileSubTypeXContract_1
WHERE (GCI_PM_FileSubTypeXContract.ContractID IN (' + @Contracts + ')))) AND (GCI_PM_FileSubType.FileTypeID = ' + @FileTypeID + ')')
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply