Excel Import: odbc

Showing posts with label odbc. Show all posts

Wednesday, March 7, 2012

EXCEPTION_ACCESS_VIOLATION

I am using SQL Server 7 and the following query giving
exception access violation error.
Error is : ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 14 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
Could you please suggest what could be wrong with the
Query.
It is working fine if I sub divide the Derived expression
to intermediate temporary tables.
SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
BUDGET_DATE,
(((D1.WB_AVG * ISNULL
(ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
@.MONTH_DAYS AS WB_EXPENSE
INTO #WB_MTD
FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
CHG_OFF_1,
COALESCE(D.REGION,L.REGION) AS REGION,
@.BUDGET_DATE AS BUDGET_DATE,
(((ISNULL(L.PRE_LOANS,0) - ISNULL
(D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
(L.CUR_LOANS,0)))/2) AS WB_AVG
FROM (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
FROM DEPOSITS_CUBE_V
WHERE BUSINESS_DATE = @.BUDGET_DATE AND
CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
FULL OUTER JOIN (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
FROM LOANS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
ON D.BUDGET_DATE = L.BUDGET_DATE AND
D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
(RATE_LAST_USED,0))/100) AS ANN_INTEREST
FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY REGION) AS R
ON D1.REGION = R.REGION
Thanks in advalceThere types of errors are typically bugs in SQL Server. Assuming you are current on service pack and
have searched KB already, I suggest you open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Girija Ponnaganti" <anonymous@.discussions.microsoft.com> wrote in message
news:006701c3ad6a$a59dd9b0$a401280a@.phx.gbl...
> I am using SQL Server 7 and the following query giving
> exception access violation error.
> Error is : ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 14 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> Could you please suggest what could be wrong with the
> Query.
> It is working fine if I sub divide the Derived expression
> to intermediate temporary tables.
> SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
> BUDGET_DATE,
> (((D1.WB_AVG * ISNULL
> (ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
> @.MONTH_DAYS AS WB_EXPENSE
> INTO #WB_MTD
> FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
> CHG_OFF_1,
> COALESCE(D.REGION,L.REGION) AS REGION,
> @.BUDGET_DATE AS BUDGET_DATE,
> (((ISNULL(L.PRE_LOANS,0) - ISNULL
> (D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
> (L.CUR_LOANS,0)))/2) AS WB_AVG
> FROM (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
> FROM DEPOSITS_CUBE_V
> WHERE BUSINESS_DATE = @.BUDGET_DATE AND
> CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
> FULL OUTER JOIN (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
> FROM LOANS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
> ON D.BUDGET_DATE = L.BUDGET_DATE AND
> D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
> JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
> TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
> (RATE_LAST_USED,0))/100) AS ANN_INTEREST
> FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY REGION) AS R
> ON D1.REGION = R.REGION
>
> Thanks in advalce

Friday, February 24, 2012

Excel: terrible performance

Running SQLS7 on an NT4 server at 500MHz, with 768MB RAM (256MB
dedicated to SQLS). Using ODBC to get data into an Excel spreadsheet on
a desktop machine. SQL database is about 3GB; spreadsheet comes to about
14MB. Data from 3 tables is being used, related by a field common to all
3; data for one month out of 4 years' total data being extracted.
Performance is extremely slow, 15 minutes or more after selecting Edit
Query on the spreadsheet before the MS Query window comes up, etc.
Performance was terrible on 300MHz/Win98/Office 97 workstation; it is
still unusable on 2600MHz/WinXP/Office 2000. When performing the Edit
Query, the windows Task Manager shows workstation CPU usage to be 100%
steady. The server machine is not doing anything else; the Task Manager
shows CPU usage during SQL processing to be moderate.
Is this poor performance to be expected with the amount of data and
spreadsheet size? Is there an interface that will give better
performance than ODBC? Will some other software perform better than
Excel?
Best wishes,
Michael Salem
Hi
Have you thought about using DTS to write the file to a share?
You should also check that you don't have logging enabled on the ODBC
connection.
If you execute the SQL in Query analyser you may be able to improve the
performance using the query plan.
John
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1b43a289db966af898968e@.msnews.microsoft.c om...
> Running SQLS7 on an NT4 server at 500MHz, with 768MB RAM (256MB
> dedicated to SQLS). Using ODBC to get data into an Excel spreadsheet on
> a desktop machine. SQL database is about 3GB; spreadsheet comes to about
> 14MB. Data from 3 tables is being used, related by a field common to all
> 3; data for one month out of 4 years' total data being extracted.
> Performance is extremely slow, 15 minutes or more after selecting Edit
> Query on the spreadsheet before the MS Query window comes up, etc.
> Performance was terrible on 300MHz/Win98/Office 97 workstation; it is
> still unusable on 2600MHz/WinXP/Office 2000. When performing the Edit
> Query, the windows Task Manager shows workstation CPU usage to be 100%
> steady. The server machine is not doing anything else; the Task Manager
> shows CPU usage during SQL processing to be moderate.
> Is this poor performance to be expected with the amount of data and
> spreadsheet size? Is there an interface that will give better
> performance than ODBC? Will some other software perform better than
> Excel?
> Best wishes,
> --
> Michael Salem
|||John Bell responded to my question on slow Excel/ODBC/SQL with 3GB SQLS
& 14MB Excel files -- many thanks.

> Have you thought about using DTS to write the file to a share?
> You should also check that you don't have logging enabled on the ODBC
> connection.
> If you execute the SQL in Query analyser you may be able to improve the
> performance using the query plan.
Thanks for these suggestions, I will follow up. I wouldn't expect Query
Analyzer to help, as it is a very simple query, but I will try it.
Reading between the lines it would appear that you're not totally
surprised by the slowness, so it is probably better to seek a more
efficient way of doing the analysis needed than to tweak the present
setup.
I've since learned that a very similar setup on the same hardware but
with a much smaller database works at an acceptable speed.
Best wishes,
michael Salem
|||Hi
Check out DTS as this is a more common way of doing it. See Books online and
http://www.sqldts.com/default.aspx for information regarding how to use
this.
John
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.c om...
> John Bell responded to my question on slow Excel/ODBC/SQL with 3GB SQLS
> & 14MB Excel files -- many thanks.
>
> Thanks for these suggestions, I will follow up. I wouldn't expect Query
> Analyzer to help, as it is a very simple query, but I will try it.
> Reading between the lines it would appear that you're not totally
> surprised by the slowness, so it is probably better to seek a more
> efficient way of doing the analysis needed than to tweak the present
> setup.
> I've since learned that a very similar setup on the same hardware but
> with a much smaller database works at an acceptable speed.
> Best wishes,
> --
> michael Salem
|||I asked about slowness getting data from SQL to Excel via ODBC; John
Bell made some excellent suggestions, for which many thanks. I append
the most recent message in full for reference, as it was a few days ago.
I was focussing on getting data out of a database which was somebody
else's responsibility; I didn't want to tread on toes. Anyway, after a
bit of analysis I added an index to the database anyway; this made a
dramatic difference. If I had realised that this was the problem, I
would have done it long ago.
Thanks again,
Michael Salem
John Bell wrote:
> Hi
> Check out DTS as this is a more common way of doing it. See Books online and
> http://www.sqldts.com/default.aspx for information regarding how to use
> this.
> John
> "Michael Salem" <msnews@.ms3.org.uk> wrote in message
> news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.c om...
>
>
|||I asked about slowness getting data from SQL to Excel via ODBC; John
Bell made some excellent suggestions, for which many thanks. I append
the most recent message in full for reference, as it was a few days ago.
I was focussing on getting data out of a database which was somebody
else's responsibility; I didn't want to tread on toes. Anyway, after a
bit of analysis I added an index to the database anyway; this made a
dramatic difference. If I had realised that this was the problem, I
would have done it long ago.
Thanks again,
Michael Salem
John Bell wrote:
> Hi
> Check out DTS as this is a more common way of doing it. See Books online and
> http://www.sqldts.com/default.aspx for information regarding how to use
> this.
> John
> "Michael Salem" <msnews@.ms3.org.uk> wrote in message
> news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.c om...
>
>

Excel: terrible performance

Running SQLS7 on an NT4 server at 500MHz, with 768MB RAM (256MB
dedicated to SQLS). Using ODBC to get data into an Excel spreadsheet on
a desktop machine. SQL database is about 3GB; spreadsheet comes to about
14MB. Data from 3 tables is being used, related by a field common to all
3; data for one month out of 4 years' total data being extracted.
Performance is extremely slow, 15 minutes or more after selecting Edit
Query on the spreadsheet before the MS Query window comes up, etc.
Performance was terrible on 300MHz/Win98/Office 97 workstation; it is
still unusable on 2600MHz/WinXP/Office 2000. When performing the Edit
Query, the windows Task Manager shows workstation CPU usage to be 100%
steady. The server machine is not doing anything else; the Task Manager
shows CPU usage during SQL processing to be moderate.
Is this poor performance to be expected with the amount of data and
spreadsheet size? Is there an interface that will give better
performance than ODBC? Will some other software perform better than
Excel?
Best wishes,
--
Michael SalemHi
Have you thought about using DTS to write the file to a share?
You should also check that you don't have logging enabled on the ODBC
connection.
If you execute the SQL in Query analyser you may be able to improve the
performance using the query plan.
John
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1b43a289db966af898968e@.msnews.microsoft.com...
> Running SQLS7 on an NT4 server at 500MHz, with 768MB RAM (256MB
> dedicated to SQLS). Using ODBC to get data into an Excel spreadsheet on
> a desktop machine. SQL database is about 3GB; spreadsheet comes to about
> 14MB. Data from 3 tables is being used, related by a field common to all
> 3; data for one month out of 4 years' total data being extracted.
> Performance is extremely slow, 15 minutes or more after selecting Edit
> Query on the spreadsheet before the MS Query window comes up, etc.
> Performance was terrible on 300MHz/Win98/Office 97 workstation; it is
> still unusable on 2600MHz/WinXP/Office 2000. When performing the Edit
> Query, the windows Task Manager shows workstation CPU usage to be 100%
> steady. The server machine is not doing anything else; the Task Manager
> shows CPU usage during SQL processing to be moderate.
> Is this poor performance to be expected with the amount of data and
> spreadsheet size? Is there an interface that will give better
> performance than ODBC? Will some other software perform better than
> Excel?
> Best wishes,
> --
> Michael Salem|||John Bell responded to my question on slow Excel/ODBC/SQL with 3GB SQLS
& 14MB Excel files -- many thanks.

> Have you thought about using DTS to write the file to a share?
> You should also check that you don't have logging enabled on the ODBC
> connection.
> If you execute the SQL in Query analyser you may be able to improve the
> performance using the query plan.
Thanks for these suggestions, I will follow up. I wouldn't expect Query
Analyzer to help, as it is a very simple query, but I will try it.
Reading between the lines it would appear that you're not totally
surprised by the slowness, so it is probably better to seek a more
efficient way of doing the analysis needed than to tweak the present
setup.
I've since learned that a very similar setup on the same hardware but
with a much smaller database works at an acceptable speed.
Best wishes,
--
michael Salem|||Hi
Check out DTS as this is a more common way of doing it. See Books online and
http://www.sqldts.com/default.aspx for information regarding how to use
this.
John
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.com...
> John Bell responded to my question on slow Excel/ODBC/SQL with 3GB SQLS
> & 14MB Excel files -- many thanks.
>
> Thanks for these suggestions, I will follow up. I wouldn't expect Query
> Analyzer to help, as it is a very simple query, but I will try it.
> Reading between the lines it would appear that you're not totally
> surprised by the slowness, so it is probably better to seek a more
> efficient way of doing the analysis needed than to tweak the present
> setup.
> I've since learned that a very similar setup on the same hardware but
> with a much smaller database works at an acceptable speed.
> Best wishes,
> --
> michael Salem|||I asked about slowness getting data from SQL to Excel via ODBC; John
Bell made some excellent suggestions, for which many thanks. I append
the most recent message in full for reference, as it was a few days ago.
I was focussing on getting data out of a database which was somebody
else's responsibility; I didn't want to tread on toes. Anyway, after a
bit of analysis I added an index to the database anyway; this made a
dramatic difference. If I had realised that this was the problem, I
would have done it long ago.
Thanks again,
--
Michael Salem
John Bell wrote:
> Hi
> Check out DTS as this is a more common way of doing it. See Books online a
nd
> http://www.sqldts.com/default.aspx for information regarding how to use
> this.
> John
> "Michael Salem" <msnews@.ms3.org.uk> wrote in message
> news:MPG.1b44d4de9dc5ffee98968f@.msnews.microsoft.com...
>
>

Friday, February 17, 2012

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

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Excel pivot table

Hi,

Any idea on how to fix this problem?

Thanks,

Arty

Thanks
Laurentiu

|||

Arty

|||

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

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

|||

Thanks
Laurentiu

|||

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

|||

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?

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

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.

Wednesday, February 15, 2012

Excel ODBC Problem

Hello,

I created an excel sheet I want to link to, to get data from. But now I'm at a loss as to how to actually make this sheet work in Crystal. Help!

I know I have to set up a new ODBC connection for the spreadsheet I created, which I did.

When I select the spreadsheet as a new connection, it comes up with a blank table (no fields).

What did I do wrong?create a new connection in crystal using
access/excel DAO|||This link may help

http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=c2001842&sliceId=&dialogID=3630043&stateId=1%200%203628127

Rashmi

Wednesday, March 7, 2012

EXCEPTION_ACCESS_VIOLATION

Friday, February 24, 2012

Excel: terrible performance

Excel: terrible performance

Friday, February 17, 2012

Excel pivot table

Excel pivot table

Excel pivot table

Excel pivot table

Excel pivot table

Excel pivot table

Excel pivot table

Excel pivot table

Excel pivot table

Excel pivot table

Excel pivot table

Excel pivot table

Wednesday, February 15, 2012

Excel ODBC Problem

Excel Import

Blog Archive

About Me