Enterprise manager export truncates columns

[roblasch]

I am having a terrible time with enterprise manager. I am trying to export a table into a flat file and some records are being truncated. I cannot find any reason for this to be happening. I have tried everything I can think of, but to no avail. It seems to be related to the length of the entire record. It happens in the middle of words so I don't think it is caused by hidden or control characters, although I don't know enough about language types, etc. to be sure. It is not a problem to export the data to another sql table though. Does anyone know of some quirk with em that would cause this or can someone explain how to insert records into a flat file using query analyzer. Thanks.

 

[ralphwiggum]

In query analyzer you can export the results to a text file by going to the menubar and selecting Query-->Results to File

If you wan to set up the properties for the output file goto Tools-->Options-->results tab

 

[roblasch]

That is very helpfull ... thanks. But there is still a problem. Maybe it will provide a clue to the cause. When I run the query to get the data the results in the grid of query analyzer (or the file if I write to a file, or the text if I show the results in text) are truncated. But when I run the same query with an insert in front of it (inserting it into a table), the results are not truncated. Any ideas?

 

[ralphwiggum]

I've never seen anything like that before...Is there anything special about this data? Is it really large text or some strange characters, etc?

 

[roblasch]

No. It might have come from an old program at some point. Maybe on a mac. But if I edit the text by removing a block of text and typing replacement text into enterprise manager, the same thing will happen and the new text will be truncated. I did have a similar problem a while ago and someone said that enterprise manager did strange things like this sometimes and it had to do with the size of some columns. This seems to be related to column size, but I don't know how, or how to fix it or work around it.

 

[tlbroadbent]

Query Analyzer has a setting that can limit the number of characters per column that are returned in the display, whether grid or text. Open the Tools | Options menu. Click on the Results tab. Check the value of the Maximum characters per column setting. It can be set to a max of 8192.

I don't often use Enterprise Manager to view or manipulate data. I find it too buggy.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[ecobb]

I had the same thing happen to me on an Import several months ago. thread962-633369

I never found out what happend, so I'm interested in seeing where this thread leads. Hopefully, we can come up with some answers!



Hope This Helps!

Ecobb
- I hate computers!

 

[roblasch]

Thanks. That is usefull information. However, when I use query analyzer to write to a text file, the format is wrong. I don't know if there is a way to make adjustments so that it is written to a tab diliminated file or not. Do you know of any similar settings in enterprise manager or dts that could be having the same effect? I have found a workaround that I will use for now. If I export firt to an excel file, then to a text file, the records seem to all be o.k. That adds even more confusion to the subject. I find this quite baffling and would appreciate more info if anyone has any. Thanks again.

 

[ralphwiggum]

You may want to try using a DTS package instead, I believe you have more control over the format. Also, it's easier to perform multiple times once it's setup.