November 5, 2007 at 3:49 am
Hello everyone, i m using SQL Server 2000, i need help, here is my SP which i used to insert bulk records from my application:
CREATE PROCEDURE UpdateWTSSubscriptionPrefrences
@MemberID int,
@xmlWTSPrefrences nText
AS
--Declare output handle
DECLARE @xmlHandleWTS int
--PREPARE XML DOC
exec sp_xml_preparedocument @xmlHandleWTS OUTPUT,@xmlWTSPrefrences
--INSERT THE WTS Prefrences READING XML DOC
BEGIN
--Delete Old Listings
DELETE from MembersAlertSubscriptions where MemberID=@MemberID and ModuleType=1
--if (Len(@xmlWTSPrefrences)>0)
--BEGIN
Insert Into MembersAlertSubscriptions
SELECT MemberID,CategoryID,ModuleType
FROM OPENXML (@xmlHandleWTS, '/NewDataSet/Table1',1)
WITH(MemberID int, CategoryID int, ModuleType int)
--END
END
--RELEASE Handle
EXEC sp_xml_removedocument @xmlHandleWTS
GO
now i want to make check on the vaiable @xmlWTSPrefrences (its contain the string returned by the application)i want to
calculate the length of the string comes in that variable and if length is greater than zero then the Insert stmt is working otherwise
print msg.
Now when i used this for testing :
declare @myVar ntext
set @myVar = '123456'
if (Len(@myVar)>0)
print 'Length is not zero'
else
print 'length is zero'
it gives me following msg: "The text, ntext, and image data types are invalid for local variables."
plz tell me how to find the length of the ntext datatype variable.
Thanx in Advance.
November 5, 2007 at 4:08 am
use DataLength function, for more information see SQL BOL.
try like this:
if datalength (@xmlWTSPrefrences) <> 0
begin
-- do something
end
else
begin
-- do something
end
🙂
November 5, 2007 at 4:48 am
This is very simple error.
Just as the text suggests, you can't declare local ntext variables.
N 56°04'39.16"
E 12°55'05.25"
December 3, 2007 at 4:45 am
As the message suggests, you cannot create local variables of text, ntext or image data types.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply