Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cckelly
Starting Member
9 Posts |
 Posted - 2008-03-18 : 19:41:06
|
 I need to export data from a table in a Microsoft SQL Server 2000 database, and create a tab-delimited text file. I'm actually pulling several columns from "the table"; but, I'm having difficulty with ONE of the columns--so, I won't bother listing the other columns.In Enterprise Manager, "the column" is defined as follows:Name: narrative DataType: textSize: 16Nulls: (checked)Default: none specifiedInitially (before I started working here) the table lived in a SQL7 environment. And there was no restriction on the size of the output column. Whoever wrote the query for the export used the following SQL code (part of SELECT clause) to pull that column:CAST (tableName.Narrative AS varchar(255)) AS NARRATIVESince then, the server has been upgraded to SQL2000. And, now there is a restriction on the size of the output column--a maximum of 254 characters. In my mind, I thought I could just wrap a left-254 function around what was being used in the past.I started playing with various functions to determing the length of my output field. From the results I get, it became obvious to me that I don't understand what's going on behind the scenes with this column. I've tried CAST, LEN, DATALENGTH and probably a couple of others.I've tried to find out how SQL stores this data internally, but, I can't find anything that fully explains how a "size 16" column can hold all this character data. I need to create a text field of <= 254 characters. How should I do it? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-18 : 21:49:36
|
| Why does it have to be converted at all? Just export the column without any functions.If that doesn't work, then try changing 255 to 254, no need to wrap things.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
cckelly
Starting Member
9 Posts |
Posted - 2008-03-19 : 13:55:42
|
| Somehow, this "size 16" column holds thousands of characters.So, I have to send the leftmost 254 characters. Because I don't understand what SQL is doing and how it is able to store so many characters in a column that appears to be so small, I don't know how to manipulate it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-20 : 01:24:06
|
| Well text data type can hold quite a bit of data and not just 16 characters. If you'd like more info on it, check out text data type in SQL Server Books Online.If you only want to show the first 254 characters, then you can use the SUBSTRING or LEFT functions. Check them out in BOL. If you need additional help, then please provide your query and more detailed information on what you'd like it to output.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
help..data types and text fields