Problem with SP and TEXT Column Type

[ChrisQuick]

I have a table in SQL Server 7. One of the fields is a type Text so we can store strings larger than 8000 characters.

We are using a stored procedure to insert new records:

The SP:
CREATE PROCEDURE dbo.ENS_InsertNewScenarioWithMap
(
@UserID int,
@NewScenarioName varchar(50),
@NewXMLRequest varchar(8000),
@NewXMLMapRequest text = "",
@MapXCoord varchar(25) = '',
@MapYCoord varchar(25) = ''
)
AS

Set NoCount On

Begin transaction

If Len(@MapXCoord ) > 0
/* We have a center point for the selection. Circular selection */
INSERT INTO ENS_Scenarios
(ENS_Users_ID, Name, XMLRequest, CircleXCoord, CircleYCoord, XMLMapRequest)

VALUES
( @UserID, @NewScenarioName, @NewXMLRequest, @MapXCoord, @MapYCoord, @NewXMLMapRequest)

Else
/* No Center Point, might be rectangle selection */
INSERT INTO ENS_Scenarios
(ENS_Users_ID, Name, XMLRequest, XMLMaprequest)

VALUES
( @UserID, @NewScenarioName, @NewXMLRequest, @NewXMLMapRequest)


COMMIT TRANSACTION

When we pass the SP a value greater than 8000 characters for the @NewXMLRequest it blows up with the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

[Microsoft][ODBC SQL Server Driver]String data, right truncation

/addScenario.asp, line 168

The Procedure is being called through ADO in an ASP page.

The *relevant* code is:

Set objADOCommand = Server.CreateObject("adodb.Command&quot
With objADOCommand
.ActiveConnection = objADOConn
.CommandText = "ENS_InsertNewScenarioWithMap"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue,0)
.Parameters.Append .CreateParameter("@UserId", adInteger,adParamInput,,intUserId)
.Parameters.Append .CreateParameter("@NewScenarioName", adVarChar,adParamInput,50,strScenarioName)
.Parameters.Append .CreateParameter("@NewXMLRequest", adVarChar,adParamInput,8000,strAXLRequest)
.Parameters.Append .CreateParameter("@NewXMLMapRequest", adVarWChar,adParamInput,1073741823,strMapRequest)
.Parameters.Append .CreateParameter("@MapXCoord", adVarChar,adParamInput,25,null)
.Parameters.Append .CreateParameter("@MapYCoord", adVarChar,adParamInput,25,null)
If LCase(strSaveRequest) = "no" Then
.Parameters.Append .CreateParameter("@SaveTheRequest", adBoolean ,adParamInput,1,1)
Else
.Parameters.Append .CreateParameter("@SaveTheRequest", adBoolean ,adParamInput,1,0)
End If
.Execute <---- This is Line 168
End With cquick@callingpost.com
Geographic Information System (GIS), ASP, some Oracle

 

[desi2002]

declare @error int output
check
if len(NewXMLRequest) > 8000
set @error=1
return error

else
your SP code


Hope it helps you.

 

[ChrisQuick]

In theory, we should be able to pass a string larger than 8000 characters for the parameter @NewXMLMapRequest. cquick@callingpost.com
Geographic Information System (GIS), ASP, some Oracle