February 11, 2009 at 8:49 am
I am having trouble with an OpenQuery extract from an different data source (Cache). The data pull is a large text file (max length > 50,000 charachters.)
The process limits to 8,000 charachters. In trouble shooting this I created a sample table extracting the data in seven potential increments of 8,000 charachters each. I then created a new column with Varchar(max) and updated the new column by concatenating the first two (total of 16,000 charachters.)
This process still limited the total size to 8,000 charachters. Therefore, I think the issue is based in SQ Server rather than the OpenQuery. Code for update and results are as follows:
update Vista5.dbo.TIU_Document_ReportText
Set RT1 = ReportText+ReportText1
--
Select max(len(RT1)) as RT1,
max(len(ReportText)) as ReportText,
max(len(ReportText1)) as ReportText1
From Vista5.dbo.TIU_Document_ReportText
Results are :
RT1:8000 ReportText: 8000 ReportText1: 8000
Any advice would be appreciated.
February 11, 2009 at 9:16 am
The LEN function does not work on MAX data types. Use DATALENGTH instead to find the length of the string. Note that, DATALENGTH returns the bytes required to store the string not the length of the string.
--Ramesh
February 11, 2009 at 9:29 am
max length of openquery strings is 8k
http://msdn.microsoft.com/en-us/library/ms188427.aspx
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 11, 2009 at 12:44 pm
1. I may not have explained correctly, but the max does seem to work on another table with a definition of Varchar(max). Below is a sample code to find the maximum length of the field I used (with an outdated load program).
Select max(len(REPORT_TEXT_2)) from vista.MDE.TIU_DOCUMENT_8925
Returned: 46042
2. While it is correct that an QpenQuery command string is limited to 8K, my problem is with the data element (Report_Text) not the command string.
As I indicated, I do not believe the problem (current issue) is in the OpenQuery code since I can not concatenate the multiple text fields once they are successfully loaded to SQL Server in separate pieces.
It seems like one can define a field as varchar(max) but can not load it.
The former process that works (somehow) loads the data into a table by defining the field type as "text". I can not define a field as "text" and do not know how the former process does this.
At any rate, thank you for your comments. Sooner or later I will find the key.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply