September 19, 2003 at 8:13 am
I am trying to create an extract of contact information from a system where the output is placed into a fixed width text file. The output, however, is being truncated to 255 characters in two of three situations. When I run it from DBArtisan and when I run it from my VB interface. When I run this from QueryAnalyzer, after adjusting the output width to handle 1876 characters, it works fine. Any ideas? Here is my SQL (pardon the old style joins):
SELECT TOP 100 CONVERT(CHAR(12), C.CONTACTID) +
CONVERT(CHAR(16), IsNull(C.PREFIX, '')) +
CONVERT(CHAR(32), C.FIRSTNAME) +
CONVERT(CHAR(32), IsNull(C.MIDDLENAME, '')) +
CONVERT(CHAR(32), C.LASTNAME) +
CONVERT(CHAR(16), IsNull(C.SUFFIX, '')) +
CONVERT(CHAR(64), D.SALUTATION) +
CONVERT(CHAR(64), IsNull(C.TITLE, '')) +
CONVERT(CHAR(64), IsNull(CC.JOB_FUNCTION, '')) +
CONVERT(CHAR(64), A.ACCOUNT) +
CONVERT(CHAR(32), IsNull(C.DEPARTMENT, '')) +
CONVERT(CHAR(32), IsNull(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.WORKPHONE, '(', ''), ')', ''), ' ', ''), '-', ''), ',', ''), '+', ''), '.', ''), '[', ''), ']', ''), 'ext', 'x'), 'xx', 'x'), '/', ''), 'x', ' x'), '')) +
CONVERT(CHAR(32), IsNull(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.FAX, '(', ''), ')', ''), ' ', ''), '-', ''), ',', ''), '+', ''), '.', ''), '[', ''), ']', ''), 'ext', 'x'), 'xx', 'x'), '/', ''), 'x', ' x'), '')) +
CONVERT(CHAR(32), IsNull(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.HOMEPHONE, '(', ''), ')', ''), ' ', ''), '-', ''), ',', ''), '+', ''), '.', ''), '[', ''), ']', ''), 'ext', 'x'), 'xx', 'x'), '/', ''), 'x', ' x'), '')) +
CONVERT(CHAR(128), IsNull(C.EMAIL, '')) +
CONVERT(CHAR(64), IsNull(REPLACE(D.ADDRESS2, CHAR(13)+CHAR(10), '; '), '')) +
CONVERT(CHAR(64), IsNull(REPLACE(D.ADDRESS3, CHAR(13)+CHAR(10), '; '), '')) +
CONVERT(CHAR(64), IsNull(REPLACE(D.ADDRESS4, CHAR(13)+CHAR(10), '; '), '')) +
CONVERT(CHAR(32), IsNull(D.CITY, '')) +
CONVERT(CHAR(32), IsNull(D.STATE, '')) +
CONVERT(CHAR(24), IsNull(D.POSTALCODE, '')) +
CONVERT(CHAR(255), REPLACE(IsNull(CC.PRODUCT_INTEREST, ''), ', ', '')) +
CONVERT(CHAR(64), IsNull(CC.SALES_INFLUENCE, '')) +
CONVERT(CHAR(64), IsNull(CC.CATEGORY, '')) +
CONVERT(CHAR(64), IsNull(CC.REASON, '')) +
CONVERT(CHAR(64), IsNull(CC.BD_BUSINESS_FOCUS, '')) +
IsNull(CONVERT(CHAR(8), CONVERT(VARCHAR(20), CC.ISHARES_AUM)), CONVERT(CHAR(8), '0')) +
IsNull(CONVERT(CHAR(8), CONVERT(VARCHAR(20), CC.TOTAL_AUM)), CONVERT(CHAR(8), '0')) +
CONVERT(CHAR(64), C.STATUS) +
CONVERT(CHAR(1), IsNull(CC.DPA_DONOTMAIL, '')) +
CONVERT(CHAR(1), IsNull(CC.DPA_DONOTPHONE, '')) +
CONVERT(CHAR(1), IsNull(CC.DPA_DONOTFAX, '')) +
CONVERT(CHAR(1), IsNull(CC.DPA_DONOTEMAIL, '')) +
CONVERT(CHAR(64), IsNull(CA.SELLING_CHANNEL, '')) +
CONVERT(CHAR(64), IsNull(A.REGION, '')) +
CONVERT(CHAR(64), IsNull(A.TYPE, '')) +
CONVERT(CHAR(64), IsNull(CA.RIA_PLATFORM, '')) +
CONVERT(CHAR(64), UI.USERNAME) +
LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), C.CREATEDATE, 121), ' ', ''), '-', ''), ':', ''), 14) +
CASE
WHEN C.MODIFYDATE > CA.MODIFYDATE AND C.MODIFYDATE > CC.MODIFYDATE AND C.MODIFYDATE > D.MODIFYDATE THEN LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), C.MODIFYDATE, 121), ' ', ''), '-', ''), ':', ''), 14)
WHEN CA.MODIFYDATE > CC.MODIFYDATE AND CA.MODIFYDATE > D.MODIFYDATE THEN LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), CA.MODIFYDATE, 121), ' ', ''), '-', ''), ':', ''), 14)
WHEN CC.MODIFYDATE > D.MODIFYDATE THEN LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), CC.MODIFYDATE, 121), ' ', ''), '-', ''), ':', ''), 14)
ELSE LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), D.MODIFYDATE, 121), ' ', ''), '-', ''), ':', ''), 14)
END +
CASE
WHEN C.CREATEDATE > DATEADD(yy,-1,GETDATE()) THEN 'A'
WHEN C.STATUS = 'To Be Deleted' THEN 'D'
ELSE 'C'
END LINEOUT
FROM ACCOUNT A,
C_ACCOUNT CA,
CONTACT C,
C_CONTACT CC,
ADDRESS D,
USERINFO UI
WHERE (A.SECCODEID LIKE '%01' --US Team
OR A.SECCODEID LIKE '%02') --Global Team
AND CA.ACCOUNTID = A.ACCOUNTID
AND C.ACCOUNTID = A.ACCOUNTID
AND UI.USERID = A.ACCOUNTMANAGERID
AND CC.CONTACTID = C.CONTACTID
AND D.ADDRESSID = C.ADDRESSID
AND (C.MODIFYDATE > DATEADD(yy,-1,GETDATE())
OR CA.MODIFYDATE > DATEADD(yy,-1,GETDATE())
OR CC.MODIFYDATE > DATEADD(yy,-1,GETDATE())
OR D.MODIFYDATE > DATEADD(yy,-1,GETDATE()))
September 19, 2003 at 2:39 pm
There was an ADO bug that truncated text columns if they were not the last columns in the result set. I don't think that's the case here though.
One other ?, can you run this in DTS and have it work? In either case, I'd follow up with Embarkadero since they are your vendor and see if they can solve it. They will likely solve the VB part then.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 19, 2003 at 2:56 pm
Thanks Steve. Good thoughts. I'll follow-up Monday cause I'm outta here!
September 23, 2003 at 6:49 am
Steve,
Again thanks for the help. This did work in DTS, as I pretty much expected. Found the problem to be related to the connection tool used by the product I'm customizing, SalesLogix. The current version uses BDE. The problem with BDE is that it truncates at 255. The next version of SalesLogix gets rid of BDE. Not that that helps me at the moment. Thanks for the assist.
Jeff
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply