Operation is not allowed when the object is closed.

[former_member191614]

I have had a problem with drill through not updating and I found that the .dqy file was pointing to the wrong server. My old server was SQL Server 2008 R2 and my current server is SQL Server 2012. I made the change in the server name but now I get "Operation is not allowed when the object is closed." error.

I am attaching a screen shot of the error and the text of the new .dqy and stored procedure executed in the .dqy.

The only change I made is switching the server parameter from bpc-sql1\bpcprod to epm-sql1-qa

DrillThroughError.txt.zip

[JohnL · Accepted solution]

Hi Bill,

The connection string listed at this website should work:

SQL Server Native Client 11.0 ODBC Driver Connection Strings - ConnectionStrings.com

This dqy file works in a copy of environmentshell with SQL 2012:

XLODBC

1

Driver={SQL Server Native Client 11.0};Server=BPC10SQL12;Database=EnvShell_Copy;Trusted_Connection=yes;

SELECT CATEGORY, ENTITY, ACCOUNT,TIMEID FROM tblFACtwbPlanning

CATEGORY, ENTITY, ACCOUNT,TIMEID

Also does the SP_Drill_Revenue stored procedure exist on the new server?

Thanks!

[former_member191614 · Accepted solution]

Hi John,

I changed the code as you suggested but still get the same error, however when I run the .dqy in Excel I get the attached error.

[former_member191614 · Accepted solution]

I was able to change the CLR integration on the server and the drill through isn't getting any error messages anymore. My problem now is I'm getting "no data to display" message but if I query the table there is data. I executed the drill_revenue.dqy file in excel and I got the headings but still no data. Suggestions?

[former_member191614]

John,

Thank you for the help, the syntax change and the enabling of CLR integration looks like it solved the problem.

[JohnL]

Thanks Bill.

Is there anything else you had to do?

-John

[former_member191614]

I think I'm go for now. Thanks again

[former_member191614]

I had to research the Internet to find out how to enable the CLR integration and I ran the code in the sp_configure stored procedure of the system master database on the server. That seems to have solved the issue. Thanks again.

Bill