Extremely Urgent : & character not accepted in parameter in stored procedure

[Shiva]

Dear Telerik Team,

Please consider this as an urgent request so please find some time to address this issue.

We are trying to execute a stored procedure using telerik open access. The stored procedure takes a string parameter. That parameter contains & character which is causing the system to throw following exception:

Telerik.OpenAccess.RT.sql.SQLException: XML parsing: line 1, character 848, illegal name character
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
   at OpenAccessRuntime.Relational.RelationalQueryResult.createFetchResult(Connection conParam, SqlDriver sqlDriver, Boolean scrollableParam, FetchSpec fetchSpec, RelationalCompiledQuery relationalCompiledQuery, RelationalStorageManager storageManager, Object[] paramVals, Int32 fetchSize, Int32 maxRows)

Here is the code snippet:

object[] arrParam = new object[1] { xml };
                SqlParameter parameterCategoryID = new SqlParameter("CategoryDataElementXML", OpenAccessType.Varchar);

                List<SqlParameter> sqlParameters = new List<SqlParameter>();
                sqlParameters.Add(parameterCategoryID);

                var entityDataSource = new MDSDataContext();
                var queryResult = entityDataSource.ExecuteStoredProcedure<object[]>("'SaveCategoryDataElement' ?", sqlParameters, arrParam);

where
xml    : Input string in xml format

To add more information to it, we tried replacing & with & but no help. We still encountered that error.

Can somebody please assist us on this issue?

Best Regards,
Shiva

[Thomas]

Hello Shiva,

I'm sorry, but I cannot reproduce this  exception. Would it be possible to send us the definition of the SaveCategoryDataElement procedure? I'm especially interested in the parameter declaration and the column types that are involved. I guess the exception does not come from the parameter passing in as varchar, but from the conversion in the stored proc to an Xml column.

Regards,
Thomas
the Telerik team

[Shiva]

Dear Thomas,

Thank you for your response. Below is the code of the procedure you have asked for:

CREATE PROCEDURE [dbo].[SaveCategoryDataElement]
(
    @CategoryDataElementXML XML
)
AS
BEGIN

SET NOCOUNT ON;

BEGIN TRY
        BEGIN TRANSACTION
        BEGIN

            --Preparing Handler document for reading XML
            DECLARE @DocDataElement INT, @DocSecurity INT

            EXEC sp_xml_preparedocument @DocDataElement OUTPUT,@CategoryDataElementXML

            CREATE  Table #tmpDataElementTable(
                                        DataElementID       INT,
                                        DataElementName     VARCHAR(50),
                                        DataElementIDNew    INT
                                    )
            CREATE  Table #tmpCategoryTable(
                                        DataElementID       INT,
                                        CategoryID          INT
                                    )

            INSERT INTO #tmpDataElementTable
            SELECT                      DataElementID,
                                        DataElementName,
                                        DataElementID

            FROM OPENXML (@DocDataElement,'/root/DataElement',2)
            WITH(
                                        DataElementID       INT,
                                        DataElementName     VARCHAR(50)
                )


            INSERT INTO #tmpCategoryTable
            SELECT                      DataElementID,
                                        CategoryID

            FROM OPENXML (@DocDataElement,'/root/DataElement/Category',2)
            WITH(
                                        DataElementID       INT,
                                        CategoryID          INT
                )

            INSERT INTO MDSDataElementList (DataElementName, IsActive)
            SELECT DataElementName, 1 FROM #tmpDataElementTable WHERE DataElementID < 0

            UPDATE #tmpDataElementTable SET DataElementIDNew=MDSDataElementList.DataElementID
            FROM MDSDataElementList
            WHERE MDSDataElementList.DataElementName=#tmpDataElementTable.DataElementName AND #tmpDataElementTable.DataElementID < 0

            UPDATE MDSDataElementList SET DataElementName = #tmpDataElementTable.DataElementName
            FROM #tmpDataElementTable WHERE MDSDataElementList.DataElementID = #tmpDataElementTable.DataElementID AND #tmpDataElementTable.DataElementID > 0

            UPDATE #tmpCategoryTable SET DataElementID = #tmpDataElementTable.DataElementIDNew
            FROM #tmpDataElementTable
            WHERE #tmpCategoryTable.DataElementID = #tmpDataElementTable.DataElementID

            DELETE FROM MDSCategoryDataElements WHERE DataElementID IN (SELECT DataElementID FROM #tmpCategoryTable)

            INSERT INTO MDSCategoryDataElements (DataElementID, CategoryID, IsActive)
            SELECT DataElementID, CategoryID, 1 FROM #tmpCategoryTable

            DROP TABLE #tmpDataElementTable
            DROP TABLE #tmpCategoryTable

        END
        COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
        DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity INT
        SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
        RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

END

The parameter passed from the C# code in the stored procedure is varchar but received in stored procedure as XML. Please share your comments.

Warm Regards,
Shiva

[Shiva]

Hi Thomas,

May I please have your comments on this one. This is an urgent issue for us.

Regards,
Shiva

[Ady]

Hello Shiva,

 I was not able to reproduce this error using a similar stored procedure with an XML parameter and called using a varchar parameter (with an & within the XML) . Can you execute the stored procedure using the same parameter value via an external tool like SQL Server Management Studio?
Can you provide me with the sample value that reproduces this error?

All the best,
Ady
the Telerik team