IF anyone has ALTERNATIVE suggestions, please post comments or replies. The "solution" documented here might not be the correct one.


How do I get past this error message when using SQL Anywhere 11 with Excel 12.0 on Windows 7? The code works on Windows XP, and on other V11 databases on Windows 7... so I am guessing it is some kind of environmental / permissions problem.

Could not execute statement.
Unable to connect to server 'excel_master_property_list':
[Microsoft][ODBC Excel Driver] System resource exceeded.
SQLCODE=-656, ODBC 3 State="HY000"
Line 1, column 1
CREATE EXISTING TABLE proxy_master_property_list
   AT 'excel_master_property_list;C:/projects/foxhound/010b_rroad_master_property_list_v1101.xlsx;;master$'

asked 11 Feb '11, 19:54

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

edited 13 Feb '11, 12:14


Of course I STFW beforehand, and 99% of the suggestions assumed that "System resource exceeded" had something to do with exceeding system resources.

But no, it has something to do with permissions. At several points in my search I was led to this page

http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/05aaccd6-2a38-4784-b310-99e34b8d1a35

but the advice seemed so off-topic that it made no sense... no more sense than waving a dead chicken over the keyboard...

...which of course means it was the right advice. I just had to reach a sufficiently elevated level of frustration.


Here are the Dead Chicken steps I took to solve the problem (slightly modified from the steps listed on that page):

http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/05aaccd6-2a38-4784-b310-99e34b8d1a35

1. Open the DCOM Configuration utility by going to Start - 
   Run and type dcomcnfg

2. Expand Component Services/Computers/My Computer

3. Right click on My Computer and select Properties

4. On the Default Properties tab, check the following:

a. Make sure that “Enable Distributed COM on this computer” 
      is checked

b. Make sure that Default Authentication Level = Connect

c. Make sure that Default Impersonation Level = Identify or 
      Impersonate

5. On the COM Security tab, check the following:

a. In the Access Permissions section, click the Edit Limits 
      and Edit Default buttons

b. Under each button, make sure Allow was set for everything.

c. In the Launch and Activation Permissions section, click the 
      Edit Limits and Edit Default buttons

d. Under each button, make sure Allow was set for everything.

6. On the Default Protocols tab, check the following:

a. Check to verify that Connection-oriented TCP/IP exists in 
      the DCOM Protocols

Everything was already OK, except for 5b and 5d where I had to check Allow for some choices. After a reboot, the CREATE EXISTING TABLE worked on both V11 databases, not just one of them.

Interesting aside: Previously, if I used a Windows XP computer to create a folder on the Windows 7 computer, and then in that folder did dbinit/dbeng11/dbisql to create a brand new V11 database, the CREATE EXISTING TABLE would work on that database, but not another database in a folder created locally.

Anyway, this cost the better part of a day, hence the long discussion.


After a few runs, the symptom returned, so I returned to this page...

http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/05aaccd6-2a38-4784-b310-99e34b8d1a35

...to perform Step 7, which eluded me before because the instructions didn't match the Windows 7 GUI; here are the updated instructions:

7. Expand Component Services - Computers - My Computer - 
   Distributed Transaction Coordinator

Right click on Local DTC and select Properties

On the Security tab:
      Check Network DTC Access
      Check Allow Remote Clients
      Check Allow Inbound
      Check Allow Outputnd
      Check No Authentication Required
      Check Enable XA Transactions

Then reboot.

It is possible that "reboot" is the real solution... if the symptom returns, that's what I will try right away.

Perhaps the dead chicken has passed its Best Before date :)

permanent link

answered 11 Feb '11, 20:45

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

edited 11 Feb '11, 21:49

Just as a remark, not as an answer to your particular question:

I remember I to had to activate the MSDTC to enable the linked server facility from MS SQL 2000 to SQL Anywhere (both with ASA 8 and SA 10/11). Otherwise, I got error messages with "failing distributed transactions". Needless to say, I had not explicitly tried to use distributed transactions as the ASA/SA access from MS SQL was simply readonly.

IIRC, the error only occured when MS SQL and ASA/SA ran on different boxes, and in case the ASA/SA server was running on Win XP, I had to enable several permissions comparable to what you have tried.

My resume was that after setting several options it simply worked (I have documented them but have them not available at the moment) but I felt (and feel) far from understanding the background...

Doing proxy access from ASA/SA to MS SQL was (and is) so much easier...

permanent link

answered 12 Feb '11, 18:32

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

I agree that having to fiddle around with MSDTC settings makes little sense when dealing with local Excel proxy tables... which is why it's called a "dead chicken" solution... and it still might not BE the actual solution, "reboot" might be. It won't be the only software requiring a reboot before every use, Norton Ghost on XP is another, at least on my setup.

(13 Feb '11, 12:12) Breck Carter

It looks like "none of the above" is the actual solution... the symptom has returned, and reboot does not help.

(13 Feb '11, 16:37) Breck Carter

Well... one reboot did not help, but the second reboot did. Plus (in memory of Thomas Edison) here's another technique that did not work: right mouse - Run as administrator on the command file that invokes the errant SQL.

(13 Feb '11, 17:06) Breck Carter

@Breck: Time to refine your legendary blog article (http://sqlanywhere.blogspot.com/2010/02/try-rebooting.html) to "Try another rebooting"?

(14 Feb '11, 08:10) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×63

question asked: 11 Feb '11, 19:54

question was seen: 13,574 times

last updated: 13 Feb '11, 12:14