|
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$' |
|
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 :) |
|
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... 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. It looks like "none of the above" is the actual solution... the symptom has returned, and reboot does not help. 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. @Breck: Time to refine your legendary blog article (http://sqlanywhere.blogspot.com/2010/02/try-rebooting.html) to "Try another rebooting"? |
