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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to see & output data > 8192 in...

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

Rate Topic

Display Mode

Topic Options

Author

Message

nimmi.smith-624103

Posted Friday, January 07, 2011 3:35 PM

SSC 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

Elliott Whitlow

Posted Friday, January 07, 2011 3:49 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

nimmi.smith-624103

Posted Friday, January 07, 2011 4:16 PM

SSC 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

Elliott Whitlow

Posted Friday, January 07, 2011 4:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

My SSIS recommendation stands.. It will also help you get the data to disk when its time.

CEWII

Post #1044782

nimmi.smith-624103

Posted Friday, January 07, 2011 4:48 PM

SSC 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

Wildcat

Posted Saturday, January 08, 2011 2:29 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 10:10 PM
Points: 307, Visits: 1,390

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

nimmi.smith-624103

Posted Saturday, January 08, 2011 4:55 PM

SSC 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

Ghanta

Posted Sunday, January 09, 2011 12:06 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 03, 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 »

Permissions