Error ["Unspecified Error"] while inserting data from XML to database using a Stored P

I am gettng the following error message when I try inserting data from a XML to the database (using a Stored Proc). The value of an attribute in the XML tag is very long, and when I try splitting that into multiple strings and insert each one separately to the database, to analyze the issue, I find that all the strings are getting inserted individually without any problem. But when I insert the concatenated form of the string as a whole then it gives me this problem.

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1

The error description is 'Unspecified error'.

Msg 8179, Level 16, State 5, Procedure InsertWinAPIInfo, Line 61

Could not find prepared statement with handle 0.

The statement has been terminated.

Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1

sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

I have tried searching on the net and did not find any possible solution for this. Please help me.You are probably hitting some limit on attribute length of the tag, and this is causing sp_xml_preparedocument to fail.|||You are probably hitting some limit on attribute length of the tag, and this is causing sp_xml_preparedocument to fail. I could not find any documentation as to the maximum length allowed for an attribute but internally I think sp_xml_preparedocument uses msxml parser so you can review docs for msxml parser to see what the limits are.

Error (8626) while inserting record into table with text field and which is the base for i

I have a problem with inserting records into table when an indexed view
is based on it.
Table has text field (without it there is no problem, but I need it).
Here is a sample code:

USE test
GO

CREATE TABLE dbo.aTable (
[id] INT NOT NULL
, [text] TEXT NOT NULL
)
GO

CREATE VIEW dbo.aView
WITH SCHEMABINDING AS
SELECT [id]
, CAST([text] AS VARCHAR(8000)) [text]
FROM dbo.aTable
GO

CREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERT
AS
BEGIN
INSERT INTO aTable
SELECT [id], [text]
FROM inserted
END
GO

Do the insert into aTable (also through aView).

INSERT INTO dbo.aTable VALUES (1, 'a')
INSERT INTO dbo.aView VALUES (2, 'b')

Still do not have any problem. But when I need index on view

CREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])
GO

I get following error while inserting record into aTable:

-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4
-- Only text pointers are allowed in work tables, never text, ntext, or
image columns. The query processor produced a query plan that required
a text, ntext, or image column in a work table.

Does anyone know what causes the error?ing42 (Inga.Korczowska@.gmail.com) writes:
> Do the insert into aTable (also through aView).
> INSERT INTO dbo.aTable VALUES (1, 'a')
> INSERT INTO dbo.aView VALUES (2, 'b')
> Still do not have any problem. But when I need index on view
> CREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])
> GO
> I get following error while inserting record into aTable:
> -- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4
> -- Only text pointers are allowed in work tables, never text, ntext, or
> image columns. The query processor produced a query plan that required
> a text, ntext, or image column in a work table.
> Does anyone know what causes the error?

Did you notice the warning when you created the index:

Warning: The optimizer cannot use the index because the select list of
the view contains a non-aggregate expression.

So the index is not of much use. I guess you have hit a restriction
in SQL Server, which does not report as such in a nice way. When I
run your code in SQL 2005, I get:

Msg 1942, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.aView'. It contains text, ntext,
image or xml columns.

Which is a more resolute message.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx