Excel pivot table

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

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

|||

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

|||

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

|||

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

|||

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?

|||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.

|||

"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

|||

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?

|||

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|||

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

|||

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

|||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|||

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

|||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.