<Long Text> errors on a 'text char(16)' column

Our SQL Server database is being utilized by many Applix/iHelpdesk
forms. On a cloumn with a 'text char(16)' datatype, we recently found
2 <Long Text> errors on a table. The Applix developers said their
application was locked up. My DBArtisan can disply only two records.
The third row showed a <Long Text> error in the Enterprise Manager.
Was my DBArtisan also locked up?
What kinds of Applix data format caused the the <Long Test> error?
How to solve this problem?
TIA.
JeffreyThat's not an error; that's Enterprise Manager not displaying TEXT/NTEXT
properly. Use a better tool (e.g. Query Analyzer) and that problem goes
away.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jeffrey" <cjeffwang@.yahoo.com> wrote in message
news:c61d4674.0411221528.487ebc2@.posting.google.com...
> Our SQL Server database is being utilized by many Applix/iHelpdesk
> forms. On a cloumn with a 'text char(16)' datatype, we recently found
> 2 <Long Text> errors on a table. The Applix developers said their
> application was locked up. My DBArtisan can disply only two records.
> The third row showed a <Long Text> error in the Enterprise Manager.
> Was my DBArtisan also locked up?
> What kinds of Applix data format caused the the <Long Test> error?
> How to solve this problem?
> TIA.
> Jeffrey

<Long Text> errors on a 'text char(16)' column

Our SQL Server database is being utilized by many Applix/iHelpdesk
forms. On a cloumn with a 'text char(16)' datatype, we recently found
2 <Long Text> errors on a table. The Applix developers said their
application was locked up. My DBArtisan can disply only two records.
The third row showed a <Long Text> error in the Enterprise Manager.
Was my DBArtisan also locked up?
What kinds of Applix data format caused the the <Long Test> error?
How to solve this problem?
TIA.
Jeffrey
That's not an error; that's Enterprise Manager not displaying TEXT/NTEXT
properly. Use a better tool (e.g. Query Analyzer) and that problem goes
away.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Jeffrey" <cjeffwang@.yahoo.com> wrote in message
news:c61d4674.0411221528.487ebc2@.posting.google.co m...
> Our SQL Server database is being utilized by many Applix/iHelpdesk
> forms. On a cloumn with a 'text char(16)' datatype, we recently found
> 2 <Long Text> errors on a table. The Applix developers said their
> application was locked up. My DBArtisan can disply only two records.
> The third row showed a <Long Text> error in the Enterprise Manager.
> Was my DBArtisan also locked up?
> What kinds of Applix data format caused the the <Long Test> error?
> How to solve this problem?
> TIA.
> Jeffrey

<Long Text> errors on a 'text char(16)' column

Our SQL Server database is being utilized by many Applix/iHelpdesk
forms. On a cloumn with a 'text char(16)' datatype, we recently found
2 <Long Text> errors on a table. The Applix developers said their
application was locked up. My DBArtisan can disply only two records.
The third row showed a <Long Text> error in the Enterprise Manager.
Was my DBArtisan also locked up?
What kinds of Applix data format caused the the <Long Test> error?
How to solve this problem?
TIA.
Jeffrey
Can you script out this table?
There is no text char(16) data type. There is a text data type and a char
datatype, but no text char(16) data type.
"Jeffrey" <cjeffwang@.yahoo.com> wrote in message
news:c61d4674.0411221515.474c4231@.posting.google.c om...
> Our SQL Server database is being utilized by many Applix/iHelpdesk
> forms. On a cloumn with a 'text char(16)' datatype, we recently found
> 2 <Long Text> errors on a table. The Applix developers said their
> application was locked up. My DBArtisan can disply only two records.
> The third row showed a <Long Text> error in the Enterprise Manager.
> Was my DBArtisan also locked up?
> What kinds of Applix data format caused the the <Long Test> error?
> How to solve this problem?
> TIA.
> Jeffrey

<Long Text> display on 'text' datatype (16-byte pointer) column

Our SQL Server database is being utilized by many Applix/iHelpdesk
devlopers and users.
On a cloumn with a 'text'datatype (16-byte pointer), we recently were
informed by the developers that their application was locked up. In
Enterprise Manager, I found 2 <Long Text> displays on a table. My
DBArtisan can disply only two records. The third row showed a <Long
Text> in the Enterprise Manager.
Was my DBArtisan also locked up?
How to solve this problem from Applix side?
TIA.
Jeffrey
Jeffrey,
Yes, what you have in your table is a column defined with the TEXT datatype
that shows up as a 16-byte textpointer. Most likely the cause is a implied
UPDATE statement is being held on the TEXT data by you application Applix
while your application is open and this is causing blocking or "locking up"
the data from access by other applications. You can use SQL Profiler to
confirm exactly what your application is doing and then alter the code.
Regards,
John
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0411222006.571cfacb@.posting.google.c om...
> Our SQL Server database is being utilized by many Applix/iHelpdesk
> devlopers and users.
> On a cloumn with a 'text'datatype (16-byte pointer), we recently were
> informed by the developers that their application was locked up. In
> Enterprise Manager, I found 2 <Long Text> displays on a table. My
> DBArtisan can disply only two records. The third row showed a <Long
> Text> in the Enterprise Manager.
> Was my DBArtisan also locked up?
> How to solve this problem from Applix side?
> TIA.
> Jeffrey

'concatenating' several columns together for Insert into an XML column

Hi,

I am inserting a row into a Table. One of the columns in this table is of XML datatype.

I am using a Select statement to provide the values that will be inserted into the row. However, I would like to combine some of these values and parse them into XML for insertion into the XML column.

An example might help:

The Table to insert into has the following columns:

Table name: myTable

ID int

Data1 string

Data2 string

Data3 string

DataXml xml

I am inserting into this table using the following:

Code Snippet

insertinto myTable

(Data1, Data2, Data3, DataXml)

select Data1, Data2, Data3,

(select Data4, Data5, Data6)from myOtherTable as inside

where inside.ID = outside.ID forxmlraw)

from myOtherTable as outside

This works but it strikes me as inefficient. There is an additional lookup for each row which seems unnecessary as we are already at that row of the same table.

If it was a simple concatenation, I would do something like 'Data4 + Data5 + Data6', and it seems this is not much different except that instead of concatenation everything is being wrapped in Xml.

Does anyone have any ideas of a better way to do this?

Any help much appreciated.

Holf:

I am definitely not an expert at XML. I too am learning. This is also pretty much the way that I do it. I am also interested in seeing if Martin has a better way of doing this. To me, it looks like you are doing it right.

Kent

|||

Kent,

Thanks for the response. Well, I've checked the query plan generated and I can confirm that there is defintely some nested looping going on. I was wondering if the optimizer would realize what is happening and do some shortcuts.

Given this, I'm thinking it is going to be more efficient to concatenate the XML bits in, e.g. '<row Data4="' + Data4 + '" />' etc.

I don't want to do this because to manipulate XML I'd like to use XML tools, but I'll happily concatenate if it works out to be faster.

Thanks again for the reply. I'm learning XML too, and it does seem as though there's a lot to learn just now!

Holf

|||Be aware that FOR XML will do necessary escaping (e.g. a value like "foo & bar" will be escaped as "foo &amp; bar") while SQL string concatenation will not do any escaping that XML requires.|||Thank you, Martin, I had forgotten about that particular side effect; I have experienced pain from this particular problem a couple of times.|||

Ah yes, that is a very good point.

Well, I may write a function to which I can pass my XML elements and which will respond with some nicely crafted XML. I am considering compiling and importing a .NET assembly specifically for this task, as in .NET there are some nice tools for building up valid XML from raw elements, and these tools take care of escaping invalid characters.

I know this sounds like overkill but I still think it will be more efficient than the subquery approach above. Of course, I will be testing to find out.

Better check that my hosting provider allows upload of .NET assemblies to SQL Server...

Thanks Martin and Kent for your thoughts on this.

|||

Well, I've checked with my hosting provider and they do not allow upload of .NET assemblies. This is not surprising really, given it is a shared database and with the rights to upload assemblies, users could compromise the entire database.

So, I've written a very simple function to escape the necessary characters (of which there are not many from what I read):

Code Snippet

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERFUNCTION [dbo].[fn_EscapeForXml]

(

@.stringToEscape asnvarchar(256)

)

RETURNSnvarchar(256)

AS

BEGIN

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'&','&amp;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'<','&lt;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'>','&gt;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'''','&apos;')

SELECT @.stringToEscape =REPLACE(@.stringToEscape,'"','&quot;')

RETURN(@.stringToEscape)

END

Note that the '&' has to be escaped first, or otherwise the '&'s resulting from the other replace operations are themselves replaced.

When I am concatenating my strings to make an XML document, I pass anything which I know may have invalid characters through this function first.

I have had some XML that I have treated in this way returned to a .NET environment. When the XML is deserialized these characters are resolved as they should be into their original form, so it all seems to be working.