N
Nathan Holmes
I have a spreadsheet that pulls data from a SQL Server 2000 database using a
stored procedure call (via ADODB.Command.Execute) and the
Range.CopyFromRecordset method. In previous versions of Excel (97 and 2000),
my code works fine. However, we're upgrading to Excel 2003 and this seems to
have problems with any field longer than 911 characters.
One of the fields I pull back is VARCHAR(1000), and in a few cases this is
fully used. If in the stored procedure I wrap this field with Left( ... ,
911) then it works fine, but if I omit the Left() call or use 912 or any
larger number, I get a run time error -2147467259 - "Method CopyFromRecordset
of object Range failed".
Has anyone else encountered this behaviour? Is it a recognised bug with
Excel? (I can't find reference to it in the knowledge base.) Is there a post
SP1 hot-fix or patch that fixes this? For information, the exact build of
Excel that I'm working to is 11.6355.6360.
I've found a previous reported instance on the following URL, but the
discussion petered out before anyone came up with a solution/recommendation.
<http://www.xtremevbtalk.com/showthread.php?mode=hybrid&t=231384>
In the short term I'll leave the Left() in the SP to get people working
again, but it'd be nice to show full-length fields if there's a
fix/workaround...
I'd be very grateful for any assistance or further information.
Yours,
Nathan Holmes.
PS: 911 seems like an odd number to fall over on, unless a bored developer
at MS was having a joke at the expense of the [American] emergency services!
stored procedure call (via ADODB.Command.Execute) and the
Range.CopyFromRecordset method. In previous versions of Excel (97 and 2000),
my code works fine. However, we're upgrading to Excel 2003 and this seems to
have problems with any field longer than 911 characters.
One of the fields I pull back is VARCHAR(1000), and in a few cases this is
fully used. If in the stored procedure I wrap this field with Left( ... ,
911) then it works fine, but if I omit the Left() call or use 912 or any
larger number, I get a run time error -2147467259 - "Method CopyFromRecordset
of object Range failed".
Has anyone else encountered this behaviour? Is it a recognised bug with
Excel? (I can't find reference to it in the knowledge base.) Is there a post
SP1 hot-fix or patch that fixes this? For information, the exact build of
Excel that I'm working to is 11.6355.6360.
I've found a previous reported instance on the following URL, but the
discussion petered out before anyone came up with a solution/recommendation.
<http://www.xtremevbtalk.com/showthread.php?mode=hybrid&t=231384>
In the short term I'll leave the Left() in the SP to get people working
again, but it'd be nice to show full-length fields if there's a
fix/workaround...
I'd be very grateful for any assistance or further information.
Yours,
Nathan Holmes.
PS: 911 seems like an odd number to fall over on, unless a bored developer
at MS was having a joke at the expense of the [American] emergency services!