If you are working with a large .pst file or a large .ost file in Outlook 2007 you may have come across performance issues as described in KB932086.

Microsoft now has published an update in KB933493 which should fix this problem (including some other) and can be downloaded here.

This weblog is sponsored by FITS.
We support PASS Germany!

If your users have used Excel 2000 and Pivot Table Services targeting SQL Server 2000 as data store, they probably will get the error message "User 'public' does not have permission to run DBCC TRACEON" - even if you have migrated the whole set of security settings.

Regarding to this link the only way would be to grant execute permissions on DBCC, because:
"[...] SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers) [...]"

In SQL Server 2000, non-Sys Admins could execute DBCC Traceon (208) - SQL Server 2005 prevents non-Sys Admins from executing DBCC's. Administrators will not get aware of this effect, because they are normally allowed to start a trace in SQL 2005....
So even IF you have tested the whole bunch of Excel files in the company you would not come across this error.

What does this mean for "real life"?
Just deleting your PTS-Excel files and building new ones?
Then I would suggest Reporting Services... ;-)
Screwing your security settings?
Not exactly....
:-)

As mentioned in the forum post, Excel sends a kind of identifier within its query string, which names the calling application and should look like this in the files you are having troubles with:
"APP=Microsoft® Query;"
But have you tried to change the query string in an Excel file afterwards? It is nearly impossible...

Luckily, you could also use this coding:

Sub PT_Connect_Change()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath AsString, NewPath AsString
Dim rng As Range

ForEach ws In ActiveWorkbook.Sheets
   ForEach pt In ws.PivotTables
      OldPath = pt.PivotCache.Connection
      NewPath =replace(OldPath, "APP=Microsoft® Query;", "")
      pt.PivotCache.Connection = Application.Substitute(pt.PivotCache.Connection, OldPath, NewPath)
      OnErrorResumeNext
      IfErr.Number <> 0 Then
         'you could write an entire log here...
         msgbox ("Error")
      EndIf
      pt.PivotCache.Refresh
   Next pt
Next ws

EndSub

This coding loops through all Pivot Table Services Connections in your open Excel Workbook and cuts of the string "APP=Microsoft® Query;" in the connection string and after that refreshes the connection and the data.

To be able to do this without evoking the above mentioned error message do NOT start refreshing of the queries at the beginning and try to avoid any actions with data extraction / connection. After that, start the VBA Editor, paste the coding and run the Sub.
Maybe you will have to wait some minutes because of the refreshing and depending on the ammount of the data and the numbers of connection strings, but this is neccessary - I encountered problems with the connection string change without doing it.

But you could spend the time in getting a coffee, posting a comment and reading this blog, for example...

This weblog is sponsored by FITS.
We support PASS Germany!

There are some usefull hints in thi s article "Convert VBA Code to Visual Basic When Migrating to Visual Studio 2005 Tools for Office"

This weblog is sponsored by FITS.
We support PASS Germany!