Excel pivot table

Question

Hi,

I have several pivot tables in Excel that access data to a SQL 2000. We install SQL 2005, we change the ODBC from the 2000 server to the 2005 server. Now when we try to run the pivot tables I've got the following message:

"User 'public' does not have permission to run DBCC TRACEON"

Any idea on how to fix this problem?

Thanks,

Arty

Answer 1

Try determining who is attempting to execute DBCC TRACEON; this command requires sysadmin privileges and cannot be executed by public. I'm not sure why a pivot table would attempt to execute a DBCC TRACEON command.

I suggest to connect SQL Server Profiler to SQL Server and audit the "Audit DBCC Event "in the Security Audit Category as well as the events from the Errors and Warnings category. The DBCC Event should tell you what is the exact command that fails (it should give you the argument to TRACEON). You could try the same thing on the 2000 server to verify if the same DBCC TRACEON execution occurs. Let us know what you find.

Thanks
Laurentiu

Answer 2

When I trace with the SQL Profiler as Laurentiu suggested in both SQL 2000 and 2005, the statement that run is: dbcc traceon(208). I have no clue what the 208 means. The difference between the SQL 2000 and 2005 is that in the 2000 I have no errors and the pivot tables runs ok, however in the 2005 I have the error previously mentioned.

Arty

Answer 3

I found something about this error. DBCC TRACEON(208) means: "SET QUOTED IDENTIFIER ON". What I did is the following.

1. Go to the ODBC connection and uncheck "Use ANSI qouted identifiers".
2. Go to the Excel pivot table and change from "Database Name".dbo.TableName to [Database Name].dbo.TableName. I've got an error here explaining that Microsoft Query can not have a visual representation of the query (?).
3. Run the query.

Results:
Still have the dbcc traceon(208) running. However if I create a new Excel pivot table the dbcc command is not generated.

My question now is: What can I do to avoid the pivot tables to generate that dbcc command in the pivot tables I have already (about 300 of them)?

Arty

Answer 4

Could you also look in Profiler and see what are the differences between the contexts that are executing the DBCC TRACEON command on SQL Server 2000 and SQL Server 2005. Make sure to select 'Show all columns' for the trace, so that you can see all information available for the DBCC event. I'm interested in columns like NTUserName, LoginName, DBUserName, SessionLoginName, which provide information on who is executing the command.

Thanks
Laurentiu 

Answer 5

I am experiencing the exact problem with MS Query. The problem only occurs with non-Sys Admin users. The difference is that with SQL Server 2000, non-Sys Admins could execute DBCC Traceon (208). SQL Server 2005 prevents non-Sys Admins from executing DBCC's. Is there a work around. Is there a way to grant execute permissions on a DBCC?

 

Answer 6

Looks like a change in SQL 2005.

Our SQL Drivers send dbcc traceon(208) to server if client is MS Query for backwards compatibility reasons (turns on support for old quoted identifiers).

SQL 2000 allows this, SQL 2005 requires you to be sysadmin.

I can't see any other way to work around this.

Answer 7

"I found something about this error. DBCC TRACEON(208) means: "SET QUOTED IDENTIFIER ON". What I did is the following.

1. Go to the ODBC connection and uncheck "Use ANSI qouted identifiers"."

This answer solves the problem if you just add to the first line that the uncheck should be done via the ODBC administrator, when using Excel ODBC admin this option is never shown.

regards,

Hobbes

Answer 8

I have a similar problem when running Excel 2000 SR1 -SP3. I create an ODBC connection to the SQL 2005 database and try extract data using an MS-Query(r) to sheet1$. The user has Dbowner role on the database but not Sysadmin on the server. This fails with a "DBCC TRACEON" error. As soon as I add the user to Sysadmin Fixed server role the problem goes away. But then the Network security Audit team are extremely unhappy.  I have tried removing the "ANSI Quoted Identifiers" and Such options but this still will not work unless i am either sysadmin or use Office 2003 Excel

Is there any way, besides upgrading to Office 2003 to solve this problem?

Answer 9

Arty Arochita wrote:

I found something about this error. DBCC TRACEON(208) means: "SET QUOTED IDENTIFIER ON". What I did is the following. 1. Go to the ODBC connection and uncheck "Use ANSI qouted identifiers". 2. Go to the Excel pivot table and change from "Database Name".dbo.TableName to [Database Name].dbo.TableName. I've got an error here explaining that Microsoft Query can not have a visual representation of the query (?). 3. Run the query.

Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?

I cannot find it.
Is there a way to change the connection in an excel Pivot?
I mean, all my pivot points to the Production DB Server.
I want to point them to Datawarehouse server, where there are "offline" copy of all databases wothout having to recreate all pivot.

TIA
IgorB

Answer 10

updade your Excel to Excel XP. it will connect to SQL2005.

 

Answer 11

This is a backward compatibility issue.

 

I found the solution at

http://www.fits-consulting.de/blog/PermaLink,guid,10259e27-75b2-4800-9b0b-0b526f556c10.aspx

 

Worked perfectly

 

Answer 12

The code provided here
http://www.fits-consulting.de/blog/PermaLink,guid,10259e27-75b2-4800-9b0b-0b526f556c10.aspx
doesn't work for me.
I solved modifying each pivot and deleting from the connection mask the APP=Microsoft Query.

This was a bit annoying but it works

Answer 13

Hello Igor,

knowing that the code helped many users - would you like to share your Excel Workbook for examining the circumstances?

have you copy&pasted the code like described in the article?

cheers,
markus

Answer 14

Yes, copy and paste the code in Tools -> Macro -> Visual Basic Editor.
I received an error... but now I have no errors ...
will try to repro and post results...

I used Excel 97 but now have tryed on an Excel 2000... may be the problems is because of the old excel version.

Answer 15

No way to repro... I can say it was my mistake: the code works perfectly on Excel 97, 2000 or 2003.

I did a lot of work that I could have avoided...

I find the script useful also to change the connection string to point to a different DB or to change the autentication method.

Answer 16

I had this problem too, running Excel 2000 and SQL Server Express 2005.  Just deleteing the words "Microsoft Query" from the "Application Name" in the connection Options fixed the problem.

Answer 17

Actually, I'm wrong. Deleting it doesn't work (it defaults back to where it started), but if you put "test" or something else then it works.

Answer 18

Steve,

thats actually the way the coding works - but if you would like to do it on an Excel file with 20 or more pivot tables - just do it...

But I would think that this is a boring work...

cheers,
Markus

Answer 19

If you instal the "SQL Native Client for SQL Server 2005" and produces the connection ODBC about the Native client, everything functions again without big changes in Excel. :-))

 

Answer 20

I'm facing this problem again, this time with a Vista machine: the Excel Pivot that used to work on XP SP2 machines, now gives an error "the server cannot be located".
I am using Windows Vista, with SQL server native client and Excel 2000 (no way to upgrade, but I will test if a more recent version will work).

The strange thing is that if I create an access database and link a SQL server table (the same on which the excel pivot is created), I can see all the data.

It seems an Excel related problem, but may be someone here already solved it...

Answer 21

I solved by myself...
It is a problem with the windows firewall: opening a table from access goes directly on the sql server port (in my case 1838). Updating an excel pivot seem to do a little more complex path, and needs an UDP port to be opened on the firewall.

Answer 22

Arty,

I have found a simple way to fix it.
- Start your ODBC setup and double-click, or click on the 'configure' button for the 'broken' ODBC connection.
- If you normally have windows authentication, change your ODBC entry to use standard login. Don't worry about the login, as you won't actually use it. You will change it back to windows authentication later on.
- Start or switch to Excel, and pick Data / Get External Data / New Database Query.
- Pick the ODBC data source desired. You will get the SQL server login box, as you chose standard login earlier.
- Click on the Options button, and change the Application Name entry to ANYTHING but what is there now. This is just an identifier that SQL displays, but, except for advanced setups, is not used. Check with your DBA if unsure.
- If you use windows authentication, now is the time to pick it. Otherwise, fill in your usual login information for SQL.
- Click OK, and you should be in business.

What I have found, is that if the SQL server sees the string 'Microsoft® Query' in the application name value passed to it, the server tries to turn on the DBCC TRACEON, which is a sysadmin function only in SQL 2005. If it sees anything else, it does not make the attempt.

Hope this helps!!!!

Answer 23

This problem is related to the upgrade to SQL 2005 and the changes to 'public' permissions, in that they cannot run DBCC TRACEON(208) to enable Quoted Identifiers.

A simple way to fix the problem in an Excel document:
- for each of the affected queries, right click and select "Refresh Query"
- this will cause a series of error messages ('public', 'us_english', 'db_name', etc)
- next, it will bring up the ODBC selection pop-up
- on the ODBC selection pop-up, pick the correct connection and click OK
- ** IMPORTANT ** on the next screen, expand the "Advanced Options >>" (or just "Options >>") and remove "Microsoft @Query" from the Application Name.
- hit okay and then "Refresh" the query again.
--Save the document


got this from some url - works a treat