Client safety options in Excel

[lanez]

we use odbc to link out office apps on PCs to our order processing suite on an IBM Rs6000. Everything worked fine until we upgraded to Office 2003. Now the pivot tables we use in Excel give the following error message:
"Client Safety Options do not allow pass-through statements to be issued to the data source." There does not appear to be any reference to "client safety options" in any office or microsoft documentation - tried technet, ms website & various other forums. The only reference I found about it refered to "Office Connect" but we don't use Office Connect.

Can anyone help?
lanez

 

[Mike555]

I wonder if this is Active Directory-related. When you upgraded to Office 2003, was anything else upgraded at the same time?

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[lanez]

Thanks for the interest Mike. No other software or applications were changed at the time, only Office was upgraded. btw the OS is XP with all fixes.

 

[lanez]

Found the problem.
Not Office at all, but the latest XP hotfix. Took out the hotfix using system resotre and hey presto! everythings working again!

 

[tr0s]

This what helped me to resolve similar issue (found in newsgroups):

From: "Bas Kersten [MSFT]" (bask@online.microsoft.com)
Subject: RE: Client Safety Options do not allow pass through statements to be issued to the data s
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-10-20 01:21:04 PST

Hi Andrea,

Excel security is controlled by a registry value, in the
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\<Office Version>\Excel\Options
registry key.

You should be able to enable passthrough functionality by adding the values
below to the HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\<Office
Version>\Excel\Options registry key.

Value Name OLAPUDFSecurity
Data Type REG_DWORD
Value 1

The value 1 allows any DLL to run (note using this setting security is low
and any user defined function is allowed to pass through, which may present
a security risk).

The value 2 only allows DLLs marked as safe for scripting to be executed.
The value 3 does not allow any UDF to run.

The default value (if not present) is 2.

HTH,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."

And the next one is a workaround which also worked in my case:

From: Hugh V (hvaughan@$jacana.com)
Subject: Client safety options do not allow pass through statements to be issued ...
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.office.developer.web.components
Date: 2003-10-06 15:48:04 PST

If anyone is interested, I resolved this by changing the
form of the SELECT statement in the source clause of my
INSERT INTO statement.

The SELECT was in the form:

select field1, field2, ... from table

I changed it to:

select T0.field1, T0.field2, ... from table as T0

This completely resolved this issue for my case. I
believe that Pivottable Service was unable to parse the
first form of the statement, making the use of OPTIONS
ATTEMPT_ANALYSIS ineffective, and causing it to revert to
PASTHROUGH mode. Therefore it became subject to the new
security measure implemented in SQL Server 2000 SP3a and
installed with Office 2003.

If you actually want or need to use PASSTHROUGH mode,
then I'm afraid I can't help - I still don't know how you
can override the "client safety options" to permit this
with local cubes.

I hope this information is of some help to somebody.