January 7, 2011 at 3:35 pm
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]
January 7, 2011 at 3:49 pm
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
January 7, 2011 at 4:16 pm
I have not reached the point of writing to disk yet. Trying to format my query to pull all the data elements.
January 7, 2011 at 4:18 pm
My SSIS recommendation stands.. It will also help you get the data to disk when its time.
CEWII
January 7, 2011 at 4:48 pm
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?
January 8, 2011 at 2:29 pm
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.)
January 8, 2011 at 4:55 pm
Wildcat: thx for the response. Can u give me a hint as to what print function should I search for?
January 9, 2011 at 12:06 am
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()...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply