Column size question from newbie

[mtoney]

I've learned just enough sql to do this.

I'm trying to export information from an oracle database into sql using the following--
select label,
local_creation_time,
text_part,
opc_act_messages.severity,
opc_act_messages.message_group
from opc_nodes,
opc_act_messages,
opc_msg_text
where opc_nodes.node_id=opc_act_messages.node_id and
opc_act_messages.message_number=opc_msg_text.message_number

it works fine except the text_part column seems to be too big to fit into a sql column so it truncates the message into 2 rows. Which of course kills any chance I've got of making an accurate report.

Any suggestions and thanks in advance!

 

[rac2]

So clever of Microsoft to take the name of a language, SQL, as a name for one of their products, SQL Server.

The answer may lie in the datatypes of the column, text_part, in the Oracle database and in the SQL Server database. Possibly the datatype in the Oracle database is TEXT, or BLOB, or some such; but the datatype in the SQL Server database is VARCHAR. This would lead to truncating the imported data. If this is the cause of the problem, then the solution is to specify the appropriate datatype in the SQL Server database, which would be TEXT for binary large objects (BLOBs).

 

[harebrain]

It's not necessarily a column-size limit, but a row-size limit. In SQL Server, the data in a row cannot exceed the page size, which is 8192 bytes minus 152 bytes overhead, leaving you 8040 bytes.

SQL Server will not prevent you from exceeding this limit when you create a table, nor will it generate an error when you blow the limit in use: data simply drops off the end of the row.

This is particularly insidious where many columns are defined as VARCHAR, as the problem is difficult to spot and diagnose.