Has anyone been able to successfully install the Exchange reporting pack
without also having Visual Studio .Net installed? I've tried but receive the
following error: "Object Reference not set to an instance of object" and the
install fails.I have the same problem and would really like to find out if it is possible
to install without VS.Net. I've tried installing not only the Exchange
reporting pack but also the Financial which is what I'm really interested in
now.
"Tony G" wrote:
> Has anyone been able to successfully install the Exchange reporting pack
> without also having Visual Studio .Net installed? I've tried but receive the
> following error: "Object Reference not set to an instance of object" and the
> install fails.
Showing posts with label install. Show all posts
Showing posts with label install. Show all posts
Monday, March 19, 2012
Exchange Pack for Reporting Services
Is there any way to get data from a real install of Exchange into the
database for the Exchange Pack for Reporting Services, or is it just to look
at pretty reports that mean nothing to us?
Am I missing something here?
MichaelThe goal of the report packs is to show reports one might write using
reporting services. It is not necessarily to ship solutions that monitor
the specified application. This particular report pack contains a reference
to a tool from SSW for a product they support for extracting data about
exchange. This could be a start. Alternatively, you can use an ETL tool
such as DTS to do so, though I'm sure tools such as SSW Exchange Extraction
Service provide additional analytics.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
<michael@.civis.com> wrote in message
news:OUTh16MBFHA.1392@.tk2msftngp13.phx.gbl...
> Is there any way to get data from a real install of Exchange into the
> database for the Exchange Pack for Reporting Services, or is it just to
> look
> at pretty reports that mean nothing to us?
> Am I missing something here?
> Michael
>
database for the Exchange Pack for Reporting Services, or is it just to look
at pretty reports that mean nothing to us?
Am I missing something here?
MichaelThe goal of the report packs is to show reports one might write using
reporting services. It is not necessarily to ship solutions that monitor
the specified application. This particular report pack contains a reference
to a tool from SSW for a product they support for extracting data about
exchange. This could be a start. Alternatively, you can use an ETL tool
such as DTS to do so, though I'm sure tools such as SSW Exchange Extraction
Service provide additional analytics.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
<michael@.civis.com> wrote in message
news:OUTh16MBFHA.1392@.tk2msftngp13.phx.gbl...
> Is there any way to get data from a real install of Exchange into the
> database for the Exchange Pack for Reporting Services, or is it just to
> look
> at pretty reports that mean nothing to us?
> Am I missing something here?
> Michael
>
Friday, March 9, 2012
EXCEPTION_ACCESS_VIOLATION when installing June CTP
Hi - I'm trying to install June CTP on Win2k3 SP1.
Setup failed when trying to start services for configuration.
In log files I found the following
Computer type is AT/AT COMPATIBLE.
Bios Version is INSYDE - 1
Insyde Software MobilePRO BIOS Version 4.00.01
Current time is 11:26:54 08/30/05.
1 Intel x86 level 15, 3 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.
Setup failed when trying to start services for configuration.
In log files I found the following
Computer type is AT/AT COMPATIBLE.
Bios Version is INSYDE - 1
Insyde Software MobilePRO BIOS Version 4.00.01
Current time is 11:26:54 08/30/05.
1 Intel x86 level 15, 3 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.
Memory
MemoryLoad = 57%
Total Physical = 959 MB
Available Physical = 408 MB
Total Page File = 2329 MB
Available Page File = 1812 MB
Total Virtual = 2047 MB
Available Virtual = 1014 MB
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\SQLDump0001.txt
SqlDumpExceptionHandler: Process 5180 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server i
s terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/30/05 11:26:54 spid 0
*
*
* Exception Address = 77BD8944 (strncmp + 00000014)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00330072
*
* MODULE BASE END SIZE
* sqlservr 00400000 01DDEFFF 019df000
* Invalid Address 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* ADVAPI32 77F50000 77FEBFFF 0009c000
* RPCRT4 77C50000 77CEEFFF 0009f000
* CRYPT32 761B0000 76242FFF 00093000
* MSASN1 76190000 761A1FFF 00012000
* msvcrt 77BA0000 77BF9FFF 0005a000
* USER32 77380000 77411FFF 00092000
* GDI32 77C00000 77C47FFF 00048000
* MSVCP80 7C420000 7C4A4FFF 00085000
* MSVCR80 7C370000 7C408FFF 00099000
* MSWSOCK 71B20000 71B60FFF 00041000
* WS2_32 71C00000 71C16FFF 00017000
* WS2HELP 71BF0000 71BF7FFF 00008000
* NETAPI32 71C40000 71C97FFF 00058000
* opends60 41060000 41065FFF 00006000
* Secur32 76F50000 76F62FFF 00013000
* SHLWAPI 77DA0000 77DF1FFF 00052000
* USERENV 76920000 769E3FFF 000c4000
* psapi 76B70000 76B7AFFF 0000b000
* instapi 02310000 02318FFF 00009000
* sqlevn70 41070000 411ECFFF 0017d000
* SQLOS 02350000 02354FFF 00005000
* rsaenh 68000000 6802EFFF 0002f000
* AUTHZ 76C40000 76C53FFF 00014000
* MSCOREE 78800000 7883FFFF 00040000
* ole32 77670000 777A3FFF 00134000
* msv1_0 76C90000 76CB6FFF 00027000
* iphlpapi 76CF0000 76D09FFF 0001a000
* kerberos 71CA0000 71CF7FFF 00058000
* cryptdll 766E0000 766EBFFF 0000c000
* schannel 76750000 76776FFF 00027000
* security 71F60000 71F63FFF 00004000
* VERSION 77B90000 77B97FFF 00008000
* dssenh 68100000 68123FFF 00024000
* hnetcfg 5F270000 5F2C8FFF 00059000
* wshtcpip 71AE0000 71AE7FFF 00008000
* DNSAPI 76ED0000 76EF8FFF 00029000
* winrnr 76F70000 76F76FFF 00007000
* WLDAP32 76F10000 76F3DFFF 0002e000
* rasadhlp 76F80000 76F84FFF 00005000
* ntdsapi 766F0000 76704FFF 00015000
* dbghelp 3FA40000 3FB49FFF 0010a000
*
* Edi: 00330072:
* Esi: 00330072:
* Eax: 00000000:
* Ebx: 00000008:
* Ecx: 00000008:
* Edx: 01020001: EC83EC8B 78816608 53008002 820F5756 000000A3 8510508D
* Eip: 77BD8944: D9F7AEF2 FE8BCB03 F30C758B FF468AA6 473AC933 740577FF
* Ebp: 3F6EF3A8: 3F6EF3E4 766F4FF6 00330072 766F4F84 00000008 0194A7FC
* SegCs: 0000001B:
* EFlags: 00010246: 00730075 00650074 005C0072 006C0063 00730075 00650074
* Esp: 3F6EF39C: 3F6EF410 77BD8930 766F4F84 3F6EF3E4 766F4FF6 00330072
* SegSs: 00000023:
* *******************************************************************************
* -
* Short Stack Dump
Any help will be appreciated.The AV is happening in the SQL Server engine. I've moved your post to the SQL DB Engine forum. Maybe someone here can help you.
Dan|||Alexandr,
Are you still getting this error? Can you start the service manually?
From the fragment of the output you have provided there is no way to tell what is wrong.
We either need a full dump file (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\log\SQLDump0001.txt) or paste Short Stack Dump.
Regards,
Boris.|||Thanks for reply Boris.
I have reinstalled SQL Server on a brand new OS and it works fine now.
With the previous installation - i cannot start service manually - it gives the same error. I've tried to install SQL Express - and it gives the same error again.
I tried to start SQL Server even not as a service but as a console app - and it gives that error.
That may be happend because I already has installed SQL 2000 and MSDE on my PC.
Regards,
Alexandr.|||Alexandr,
An instance of SQL Server 2005 should coexist just fine with SQL Server 2000. I don't think that is the reason for the AV you were getting, although this is just a guess.
Since you have reinstalled the OS, I wonder if you have kept the dump file(s) that were created when the server generated the AV.
If yes, could you paste short stack dump here and we can take a further look.
Thank you,
Boris.|||Boris,
SQLServer 2005 works fine with SQL 2000 installed on the same box.
I haven't kept the stack dump or log files.
Thanks,
Alexandr.
Friday, February 17, 2012
Excel Pivot Table for AS2005
I try to use the Excel Pivot Table to retrieve the data from AS2005 cube data. I have install the Microsoft OLE DB Provider for Analsysis Services 9.0", but I get the following error message: Insitialzaiton of the data source failed. However, when I use the SQL Server Management Studio, I can browse the cube data. As a result, I know I have permission the access the cube data. Why the Excel Pivot Table is not working for me?
This schenario is pretty common and should be working. There got to be something wrong with components installed.
Try applying the latest version of AS OLEDB provider from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.|||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!!!!
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
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?
|||Excuse me, I have got the same problem. Where do you go to change the "DatabaseName" to [DatabaseName] in Excel?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.
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 herehttp://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
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.
Excel Pivot Table
When I use Excel 2002 Sp3 to connect to the AS2000. I can select multiple value in the Page field. But when I install the OLAP AS 9.0 provider to connect to the AS2005. The multiple value selection is disapper. How can I fix this?There is a 'select multiple items' checkbox in the bottom area of the dropdown box.
If this is not visible - I'd almost think that your Excel platform at that specific time is Excel 2000.
Try recreating your pivot table from the as2005 cube|||I am using Excel 2002. It is funny sometime the muti value check box appear, sometime it disappear. I also try to reinstall the Excel, but it doesn't help|||
Did you upgrade MSXML to 6.0 as well as upgrading the AS Provider ?
Must say I don't get this behaviour in 2003 having added both the upgrades
|||Yes MSXML 6.0 Parser and MS OLAP Provide For AS 9.0 have been installed in my computer. Now I find the tempoary solution. If I run another application such as IE6 to overlap the Excel, then I can see the multi value check box. It is really @.$%...?|||It's good that you're getting somewhere!
I remember that in some cases, I've had to have MDAC 2.8 installed if it wasn't already present. You shouldn't be getting that far in excel w/o it, but it's worth a try.
http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c
Subscribe to:
Posts (Atom)