5/2/2024 5:29:46 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
ADO and Excel
Posted: 08 Dec 07 4:09 PM
|
I have a recorset with one of the fileds as a memo (converted to string I have fields up to 1600 or so).
The Recordset is returned with no error from the DB. When I try to export it to Excel using
WkSheet.CopyFromRecordset MyRS
I get "unknown error".
I do not have special characters in my memo field and it is working correctly if selected rows has smaller memo fields.
Anyone had tis problem? Any ideas/workaround?????
BTW I have SLX 7.01 and MS 2003
|
|
|
|
Re: ADO and Excel
Posted: 10 Dec 07 8:55 AM
|
Excel can't handle columns more then 256, character strings more than 256, and special characters In the string fields often give it fits (Tabs mean next column for example).
Been that way for a long time (I remember 16,000 row limit....64 column limit....) |
|
|
|
Re: ADO and Excel
Posted: 10 Dec 07 9:35 AM
|
I tested the same thing using SSRS and then export to Excel with no problem(using SQL provider). I can export as many and as long fields as I want.
BUT if I user SLX provider I run into problems.
I want to use the following query and I don't know why SSRS(or me) cannot handle it.
SELECT t.ticketID,
e.SERIAL_NUMBER,
esn.PART_ID,
ct.RETURNED_SERIAL,
t.COMPLETEDDATE As DateClosed,
t.RECEIVEDDATE AS DateOpened,
a.ACCOUNT,
t.ALTERNATEKEYSUFFIX AS TicketNo,
t.ISSUE,
ct.SECONDARY_ISSUE,
e.PRODUCT_LINE,
p.TEXT as TicketStatus,
ct.Shots_Actual AS ShotCount,
tp.notes
FROM TICKET t
INNER JOIN C_TICKET ct ON t.TICKETID = ct.TICKETID
INNER JOIN PICKLIST p ON t.STATUSCODE = p.ITEMID
LEFT JOIN ACCOUNT a ON t.ACCOUNTID = a.ACCOUNTID
LEFT OUTER JOIN TICKETPROBLEM tp ON t.TICKETID = tp.TICKETID
LEFT OUTER JOIN ESI_EQUIPMENT e ON ct.EQUIPMENT_ID= e.ESI_EQUIPMENTID
LEFT OUTER JOIN EII_SOFSN esn ON ct.RETURNED_SERIAL = esn.SERIAL_NUMBER
WHERE t.RECEIVEDDATE BETWEEN ? AND ?
AND e.PRODUCT_LINE = ISNULL(?, e.PRODUCT_LINE)
AND p.TEXT = ISNULL(?, p.TEXT)
AND e.Serial_number = '11-0885'
ORDER BY t.ALTERNATEKEYSUFFIX
Parameter3 and 4 (ProductLine and Text) are optional, so I want to be able to pass NULL values.
If I run the same query in SLX Admin, with the parameters hardcoded works fine. I I do the same thing in SSRS and I pass the same parameters, I get an empty Recordset.
What you guys do when you need to export large fields? |
|
|
|
Re: ADO and Excel
Posted: 10 Dec 07 6:42 PM
|
Use Access?
256 (column IV) is the maximum number of columns, 65536 is max rows and 32,767 is max characters in a cell (in Excel 2003).
Maybe you could use SQL Server Profiler to find out what SQL the SSIS job is actually sending and work from there?
Phil |
|
|
|
Re: ADO and Excel
Posted: 11 Dec 07 7:05 AM
|
I have 14 columns and around 500 rows. I have 1 BLOB field wich could be very large and it is causing the problem. The recordset it is returned correctly, it is not failing on the DB part. It is failing on CopyFromRecordset method.
Anyway,I solved the problem (temporary) for now. I am using SQL Server Reporting Services (SSRS) and export to Excel on the fly. As an interface I have a small application in .NET. I am curious what code behind is used when export to Excel in SSRS. Anyone knows? |
|
|
| |
|
Re: ADO and Excel
Posted: 11 Dec 07 11:20 PM
|
I love the workaround:
To work around this issue, edit the script so that no cells in the array contain a character string that holds more than 911 characters.
|
|
- who would ever have thought of that? |
|
|
|
Re: ADO and Excel
Posted: 12 Dec 07 1:32 AM
|
Nobody ever accused Microsoft of lacking a sense of humor!
Well, actually, I guess some people have... Okay, lots of people have... Come to think of it, practically everyone has accused Microsoft of lacking a sense of humor at some point or other...
I suppose one might try outputting XML spreadsheets instead? |
|
|
|
Re: ADO and Excel
Posted: 12 Dec 07 8:49 AM
|
911, huh ? What a nice round number...
Or maybe is just 9 - 1- 1?
! |
|
|
|
Re: ADO and Excel
Posted: 13 Dec 07 9:58 AM
|
Wait, are you accusing Microsoft of being involved in a 9/11 conspiracy???   |
|
|
|
Re: ADO and Excel
Posted: 14 Dec 07 10:29 AM
|
Well, now that you mention it, my guess would be that it's not a conspiracy, but rather the work of just one very clever person. And yes, the fact that it's 911 characters, and not some other proximate number like 999 or 1024, *does* suggest to me that it isn't a coincidence.
Think about it: It's 2001, and here's this guy sitting in a cubicle at Microsoft's campus in Redmond, assigned to the Excel programming team. Unfortunately, Excel is boring as all get-out, and he can't get any actual work done anyway because Microsoft's change management process is easily the most frustrating on the planet. So he splits his time between working on his resume, and surfing news sites and blogs, where all he sees is an endless stream of material about terrorists, homeland security, and suicide bombers. Meanwhile, he wants to do something to memorialize the 9/11 victims, but what can he do, just sitting in that little cubicle?
Whoever introduced that bug was brilliant, in my opinion - and I'm saying this even though I spent the better part of two days figuring it out myself, and ended up having to buy a $100 component to export Excel files just to get around that limitation. And who knows - maybe the person tried it with 32K characters, but maybe it blew up at anything over, say, 1024... Though personally I doubt that; the OLE interface could probably handle all 32K characters with no trouble. Regardless, the person wanted people to notice, and people have, right? The trick worked beautifully. The fact that people are still encountering this a full 6 years later is a testament to how clever the person was. |
|
|
|
Re: ADO and Excel
Posted: 14 Dec 07 12:10 PM
|
LOL - loved the line "Unfortunately, Excel is boring as all get-out" - some accountants might take offense. Oh, I get it, that was your point wasn't it?
Ted |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|