Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to see & output data > 8192 in varchar(max) Expand / Collapse
Author
Message
Posted Friday, January 7, 2011 3:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 10:16 AM
Points: 32, Visits: 163
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]
Post #1044763
Posted Friday, January 7, 2011 3:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #1044767
Posted Friday, January 7, 2011 4:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 10:16 AM
Points: 32, Visits: 163
I have not reached the point of writing to disk yet. Trying to format my query to pull all the data elements.
Post #1044780
Posted Friday, January 7, 2011 4:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
My SSIS recommendation stands.. It will also help you get the data to disk when its time.

CEWII
Post #1044782
Posted Friday, January 7, 2011 4:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 10:16 AM
Points: 32, Visits: 163
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?
Post #1044798
Posted Saturday, January 8, 2011 2:29 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:26 PM
Points: 405, Visits: 1,431
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.)
Post #1044923
Posted Saturday, January 8, 2011 4:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 10:16 AM
Points: 32, Visits: 163
Wildcat: thx for the response. Can u give me a hint as to what print function should I search for?
Post #1044929
Posted Sunday, January 9, 2011 12:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 3, 2012 11:53 AM
Points: 231, 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()...
Post #1044948
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse