March 28, 2007 at 8:40 am
First off hi, secondly, I hope this is an appropriate thread for this query.
Right, my problem...I'm trying to run a query in MS SQL Query Analyser.
The SQL db I am looking at is the backend of a network interrogation tool.
I'm asking SQL QA to return all the data from a single table. Simple! The problem is that the data in one of the fields/columns is a rather massive text string. (The data is a list of all .exe's and installation paths on a machine). No matter what I do I can’t seem to export the whole string. Most I’ve managed is the first 8,000ish characters.
I thought that the best way to approach this was to get SQL QA to split the field into multiple fields as it returns the results of the query. Which I can do for x number of characters. But then I have to put those fields back together somewhere.
What I want to do is get SQL QA to split this field so that it starts to populate another column every time it sees an certain sting of characters (in this case "C:\")
Anyone able to point me in the right direction?
Thank you,
Rob (SQL novice)
p.s the query I’m using at the moment is
USE CAAMDB
Select * FROM APUNKNOW
March 28, 2007 at 9:20 am
Honestly for fast access you might want to attach with Access to review that field or Use SQL Reporting Services Deisgner in Visual Studio if RS is available. Otherwise look at READTEXT in SQL BOL.
March 28, 2007 at 11:10 am
Yes, I've had that problem as well. My fix, though probably not optimal is to execute the query in isql and redirect the output to a file. In order to get all the data you must use specific switches for isql.
Here's an example:
isql -Usa -Ppassword -Sserver -Q"select long_text_column from table" -x<some_large_number_big_enough_to_cover_your_data> -o<output_file_name>
Carlos
March 29, 2007 at 6:34 am
If you must do it in QA, then do a SELECT SUBSTRING(col1,1,1000), SUBSTRING(col1,1001,2000), . . . FROM table1 and you should be able to see all of the data in the table for col1.
April 3, 2007 at 1:55 pm
You say the data is in a massive text string....but what data type is the column? According to the BOL, the maximum Bytes per row is 8,060 and you say you are only getting 8,000ish characters. If the datatype is char/varchar that might be the problem.
-SQLBill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply