Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to see & output data > 8192 in varchar(max)


How to see & output data > 8192 in varchar(max)

Author
Message
nimmi.smith-624103
nimmi.smith-624103
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 172
I have a situation where I need to provide a fixed format flat file to a client. The problem is there are several large fields that need to be outputted. When I run my query and output as text, I can only see 8192 characters. What I did was create a tmp table with one field txt varchar(max). Then I inserted into my tmp table the data from my select statement. I cannot see beyond 8192 characters. I tried:

select SUBSTRING(txt,1,8192) from tmp--this works
select SUBSTRING(txt,8193,8192) from tmp--this does not work

Question-How do people what is in the table beyond the 8192 character?
How to output this to a flat file?

create table tmp (txt varchar(max) )
go
insert tmp
select
REPLICATE(' ',60)/*batch_id*/
+ left(broker + REPLICATE(' ', 25),25)
+ REPLICATE(' ',20)/*Investor Loan no*/
+ right(replicate('0',20) + convert(varchar(20),convert(bigint,[loan no] )),20)/*right pad loanno for 20*/
+ left('Selene' + REPLICATE(' ', 25),25)
+ left('Selene' + REPLICATE(' ', 25),25)/*investorname*/
+ left(ltrim(rtrim([LO First Name])) + ' ' + ltrim(rtrim([LO Last Name])) + REPLICATE(' ', 25),25)
+ left(ltrim(rtrim([First Name]))+ REPLICATE(' ', 50),50)
+ left(ltrim(rtrim([Last Name]))+ REPLICATE(' ', 50),50)
+ left(ltrim(rtrim([Subject City]))+ REPLICATE(' ', 50),50)
+ left(ltrim(rtrim([state]))+ REPLICATE(' ', 10),10)
+ ISNULL(CONVERT(varchar(10),[Dt Submit],101),'')
+ left(ltrim(rtrim([CURRENT status]))+ REPLICATE(' ', 10),100)/*overall_result*/
+ REPLICATE(' ',200)/* vendor's overall results*/
+ REPLICATE(' ',100)/* status_reason*/
+ REPLICATE(' ',10)/* REFER_TO_SERVICER_DATE*/
+ REPLICATE(' ',1000)/* REFER_TO_SERVICER_COMMENTS*/
+ REPLICATE(' ',10)/* RECONSIDERATION_DATE*/
+ REPLICATE(' ',300)/* INELIGIBLE_REASON*/
+ ISNULL(CONVERT(varchar(10),[Dt Submit],101),'') /*LAST_CONTACT_DATE*/
+ REPLICATE(' ',25)/* last_contact_note*/
+ left(ISNULL(CONVERT(varchar(10),[Dt Into UW],101),'')+ REPLICATE(' ',20),20)/*DISCLOSURE_SENT_DATE*/
+ REPLICATE(' ',20)/* PROCESSING_ON_HOLD_DATE*/
+ left(ISNULL(CONVERT(varchar(10),[Dt Approved],101),'')+ REPLICATE(' ',20),20)/*DISCLOSURE_SENT_DATE*/
+ left(ISNULL(CONVERT(varchar(10),[Dt Suspended],101),'')+ REPLICATE(' ',20),20)/*SUSPENDED_DATE*/
+ left(ISNULL(CONVERT(varchar(10),[Dt declined],101),'')+ REPLICATE(' ',20),20)/*DENIED_DATE*/
+ left(ISNULL(CONVERT(varchar(10),[Dt Canceled],101),'')+ REPLICATE(' ',20),20)/*WITHDRAWN_CANCELLED_DATE*/
+ REPLICATE(' ',10)/* STIPULATIONS_CLEARED_DATE*/
+ REPLICATE(' ',10)/* CLEAR_TO_CLOSE_DATE*/
+ left(ISNULL(CONVERT(varchar(10),[Dt Locked],101),'')+ REPLICATE(' ',20),20)/*rate_lock_date*/
+ left(ISNULL(CONVERT(varchar(10),[Dt Lock Expires],101),'')+ REPLICATE(' ',20),20)/*RATE_LOCK_EXPIRATION_DATE*/
+ left(ISNULL(CONVERT(varchar(10),[Note Dt],101),'')+ REPLICATE(' ',20),20)/*NOTE_DATE*/
+ left(ISNULL(CONVERT(varchar(10),[Dt Funded],101),'')+ REPLICATE(' ',20),20)/*FUNDED_DATE*/
+ REPLICATE(' ',1600)/* INVESTOR_SUMMARY_NOTES*/
+ left('INVESTOR_STIPULATIONS' + REPLICATE(' ',800),800)/* INVESTOR_STIPULATIONS*/
+ left('CONCESSION_NOTES' +REPLICATE(' ',1000),1000) /* CONCESSION_NOTES*/
+ REPLICATE(' ',10)/* LO_CONCESSION_REQUESTED_DATE*/
+ REPLICATE(' ',10)/* CONCESSION_REQUEST_DATE*/
+ REPLICATE(' ',10)/* CONCESSION_RESPONSE_DATE*/
+ REPLICATE(' ',25)/* CONCESSION_RESPONSE*/
+ left('CONCESSION_RESPONSE_NOTES' +REPLICATE(' ',1500),1500)/* CONCESSION_RESPONSE_NOTES*/
+ left(ISNULL(CONVERT(varchar(10),[Next Due Dt],101),'')+ REPLICATE(' ',20),20)/*NEXT_PAYMENT_DUE_DATE*/
+ REPLICATE(' ',50)/* FIRST_LIEN_HOLDER*/
+ REPLICATE('0',28)/* FIRST_LIEN_PAYOFF_AMT*/
+ REPLICATE(' ',50)/* SECOND_LIEN_HOLDER*/
+ REPLICATE('0',28)/* SECOND_LIEN_PAYOFF_AMT*/
+ CAST(FICO AS CHAR(3)) /*FICO*/
+ 'END'
from dbo.[T_Master ALL20110105]
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7088 Visits: 5314
I would probably do this with an SSIS package using a script task in the data-flow to format and breakup the text. It would also probably perform better.

How are you getting the file written to disk?

CEWII
nimmi.smith-624103
nimmi.smith-624103
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 172
I have not reached the point of writing to disk yet. Trying to format my query to pull all the data elements.
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7088 Visits: 5314
My SSIS recommendation stands.. It will also help you get the data to disk when its time.

CEWII
nimmi.smith-624103
nimmi.smith-624103
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 172
I went to SSIS. In the OLE DB Source I inputted my query as shown in the original post. Then I selected the Flat file destination. The package ran with no errors. When I looked at the output file in a text editor I am not seeing the data that should have shown up after the 4000th character or so. I do see in the AdvancedEditor for Flat file Destination, under input columns the length is set to 4000 and it is grayed out-so I can not modify this. I suspect this is where my problem is. Any ideas how to correct this?
Wildcat
Wildcat
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 1444
To print out varchar length over 8000, you have to write your own PRINT function. There is one on this site. You can search. (And Microsoft will develop one in later version, I guess.)
nimmi.smith-624103
nimmi.smith-624103
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 172
Wildcat: thx for the response. Can u give me a hint as to what print function should I search for?
Ghanta
Ghanta
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 698
Just to read that SELECT I guess instead of putting in one row insert each line into different row and then you can read it... or try not sure though using REVERSE() and SUBSTRING()...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search