Client Safety Option
-
Can't refresh the excel sheet and keep getting following error:
"Client Safety option do not allow pass through statements to be issued to the data source"
Can anyone, please help me to resolve this problem...I'm not sure if this is the right forum to ask this question.
Thanx.
Muneeb.
-
I had a quick look via Internet and couldn't see any similar reference so I'm guessing you might have an Excel Add-In or an XLA file in your Excel 'startup" folder...maybe this is the source of that message.
If you are using Office the startup folder usually resides in C:\Program Files\Microsoft Office\Office\XLStart - it can be configured to be elswhere by the Excel/Tools/Options/General settings.
DB
The systems fine with no users loggged in. Can we keep it that way ?br> -
You don't say what version of Excel you are running, but if it is Office xp then excel is set to stop macros running by default when it is installed. This stops a lot of interconnection to Excel and might also be stopping your pass-through statements.
To change the security in Excel:
Start up your Excel spreadsheet. Go to menu item Tools-Options-Security
Click on the 'macro security' button and select 'Low' security.
Hope this helps
Peter Tillotson
-
I am experiencing the same problem here as well and it seems to be turning off the macro doesn't help either.
Anyone has a clue?
-
Based on the (potentially flawed) assumption that you've got an olap connection/data in your spreadsheet, the safety option/s can be set in the connection string used for the worksheet (stored in a *.oqy, usually found in one of your personal folders). Look up the 'Safety Options Property' or alternatively the 'PivotTable Service Properties' in BOL.
Steve.
-
Thank you. I will try your recommendations
-
Well how about that ? You live and learn hey ? (Go Aussie)
The systems fine with no users loggged in. Can we keep it that way ?br> -
I am also getting the "Client Safety Option" message when trying to refresh an OLAP cube via Excel. Incidently, this has only occurred since the installation of SQL SP3a.
As suggested above, I have tried to set the "Safety Options Property" in the oqy file, but with no success. The clause I added is:
DBPROP_MSMD_SAFETY_OPTIONS=DBPROPVAL_MSMD_SAFETY_OPTIONS_ALLOW_ALL
Can anyone confirm that this is correct, or whether anything else needs to be done?
thanks in anticipation!
Adrian.
-
Can't honestly say what the values should be, but I would have thought the property name used should be 'Safety Options', so included in the conxn string like 'safety options=<value>;', however msdn and bol aren't being too helpful at resolving the names, you could take a punt that its an enumeration, and try for 1 or 2 for 'Allow all'(it's either zero based or 1 based).
Just a thing to check on the side, have you installed sp3/sp3a on both the server *and* the client machine? You can do this from the SP cd/install dir, look for the PTS (pivot table service) install, you may be able to get away with PTSLite.
hope it works out
Steve.
-
Thanks for your comments. I have tried every combination I can think of with still no luck.
According to MSDN, when using OLEDB the property ID should be used, which is why I used "DBPROP_MSMD_SAFETY_OPTIONS". This is borne out when I use the property name "Safety Options" as I get an error when loading the query.
I have also loaded the PTSLITE and PTSFULL from the SP3a CD and this has made no difference (I suspect they were already installed).
Any other thoughts would be appreciated.
-
I had similar problems on Excel 2K when refreshing a query which created a local cube. The initial query worked fine but if you refreshed the data then I got the same error message. I've also got the same message when created the initial query on Excel 2003.
BUT...........
Adding Safety Options=1 in the connection string like this
Connection=Provider=MSOLAP; Safety Options=1; Initial Catalog=[OCWCube];
in the oqy file allows the cube to be refreshed. I will check this out for Excel 2003 and post my findings later.
Still having problems with Excel 2K. It appears that this setting only applies once and the next time you refresh the error returns so you have to close Excel and re-open.
-
I shall be intrigued to hear your results. I am also using Excel 2003, and setting the Safety Options as you described has made no difference. I do not have Excel 2002 so cannot make any comparisons and cannot confirm that my testing is valid.
I am beginning to wonder if this is a bug in Excel 2003 - so your findings will be very interesting.
-
Adrian, obscure question time, you havent by any chance got IBM Office Connect installed on your machine? If you have, you could go to http://www-306.ibm.com/software/data/informix/pubs/library/notes/relnotes/relnotes-ocae4.html and try the suggested fix.
Steve.
-
No office connect. Saw that link as well, thanks anyway.
The results are variable.
For both 2K and 2K3 for NEW queries they work fine but as soon as you refresh you get the problem so you have to come out and rebuild or re-execute the query as though it's a first time query. It appears that once executed Excel overrides the safety options and then you have problems. I would now agree that this is a bug in the office products which has been brought about by changes in SP3/3Aa when this behaviour changed.
But....... If you use Microsoft Query you can process what the hell you like and create your offline cube which can be opened in Excel. This is in fact what Excel does behind the scenes for you. Clrealy Microsoft have let a whole load of inconsistencies creep in here (again.. sigh!).
All I can suggest is play with combinations of actions and determine what works and what doesn't as I stil get varying results even from 2 different 2K3/XP machines.
Very frustrating....
-
SUCCESS! I have a solution!
I posted an incident report to Microsoft and they actually came up with a response ... and it works!
Just add the following key to the registry:
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\11.0\Excel\Options
Value Name: OLAPUDFSecurity
Data Type: REG_DWORD
Value: 1
This is quite conclusive. If you set the value to 0, then message re-appears.
Thanks for all your comments - they certainly helped to narrow down the cause and enable me to post a succinct report to Microsoft!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply