Currently processed SQL Query.

Is there any way to obtain the SQL query that is currently executed from
within SQL Server?
Thanks.Q1 Is there any way to obtain the SQL query that is currently executed from within SQL Server? Thanks.
A1 Yes several ways, for example, you may execute sp_Who in query analyzer (check the status and cmd columns of the result set); or see / refresh "current activity" in EM, or even use profiler. Example for sp_Who:

exec sp_Who|||That, i exactly want to have is a string variable with the sql query that is
currently executed, in order to use it in a trigger. Is there such a global
variable in SQL Server or can i obtain this from a function or smth? Can
i do this with sp_who? If yes how can i retrieve the cmd column of it's resulting set?

Thank you very much and sorry for not explaining well my question in
my first post?|||Q1 That, i exactly want to have is a string variable with the sql query that is currently executed, in order to use it in a trigger. Is there such a global variable in SQL Server or can i obtain this from a function or smth?
Q2 Can i do this with sp_who? If yes how can i retrieve the cmd column of it's resulting set?
Q3 Thank you very much and sorry for not explaining well my question in my first post?

A1 There is no global variable I know of that holds the currently executing stored procedure / tsql as a whole. The best option for capturing such information in its entirety on the fly is probably to use profiler (it should be possible with trace flags as well, but that would be more involved). Note: It is possible for stored procedures to have large output variables (nvarchar / varchar) that could potentially hold and pass 4000 to 8000 chars to a calling procedure (which may or may not address your needs, but you would in any case have issues with procedures exceeding 8000 chars in length using that approach).

A2 Not with any current release, it is possible to use sp_who to identify the desired currently active connection (and then view the input buffer for that connection, to see the 'currently executing string'), however, that only gives the first 255 char currently in the input buffer.

A3 You are welcome.

Cube Processing Issue

We are trying to process the Cube. First time when we processed the cube it took around 1 and 1/2 hours to do full process. But after some time in the same environment its taking more then 6 hours to process. As such there was no change in the environment. We have 64 bit 2 way itinium processor with 32 GB of ram. The size of the cube is aroung 150 MB. We are using the OLE DB2 provider to connect to database.Any environmental issues that needs to be checked.Any thoughts on this issue.Appriciate your help.

Thnaks,

Really hard to say what is going on.

Many factors could be affecting processing perofrmance.

I would first check the performance of relational database. See what is the rate of DB2 delivering the data. 150 meg of data should take way faster to process for Analysis Server.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

There does not seems to be any issue with Relational Database side .If I ran the same cube from machine its peocessing very fast.Any factor in analysis box that that can count for slow processing .

Analytical box is 64bit 2 way processor with 64 GB ram.

Thnaks,

|||

Try and trace command AS sends to relational database. And then see what rate your 64bit box can get data from relational database

Again, it is had to say without looking at the cube. Try and contact Customer Support Service and have support engineer to look at your situation.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cube Processing Error - Urgent - Thanks

First when I processed cube's (Process Data) using SSIS package I got "Memory error"

"Memory error: Allocation failure : Not enough storage is available to process this command."

I changed "OLAP\ Process \ BufferMemoryLimit" to 30% . I have /3GB setup. and I am using AS 2005 - 32 bit.

When I rerun the package I got olap storage engine error

Errors in the OLAP storage engine: An error occurred while processing the 'Scenario Analysis 2006' partition of the 'Scenario Analysis' measure group for the 'Scenario Analysis' cube from the Risk Reports database.

I don't know what's that, so I run cube process (Full) from Management Studio and got error below while reading data process stoped with following popup message

The trace has been stopped
Unexpected end of the file has occurred. The following elements are not closed: root, return, ExecuteResponse, soap:Body soap: Envelope. Line 13180, position 1. (System.Xml)

Thanks for any help - Ashok

Please check if you're running out of disk space...

Cube Processing Error - Urgent - Thanks

First when I processed cube's (Process Data) using SSIS package I got "Memory error"

"Memory error: Allocation failure : Not enough storage is available to process this command."

I changed "OLAP\ Process \ BufferMemoryLimit" to 30% . I have /3GB setup. and I am using AS 2005 - 32 bit.

When I rerun the package I got olap storage engine error

Errors in the OLAP storage engine: An error occurred while processing the 'Scenario Analysis 2006' partition of the 'Scenario Analysis' measure group for the 'Scenario Analysis' cube from the Risk Reports database.

I don't know what's that, so I run cube process (Full) from Management Studio and got error below while reading data process stoped with following popup message

The trace has been stopped
Unexpected end of the file has occurred. The following elements are not closed: root, return, ExecuteResponse, soap:Body soap: Envelope. Line 13180, position 1. (System.Xml)

Thanks for any help - Ashok

Please check if you're running out of disk space...

Cube processed time always shows current time.

I have an issue with the "processed" time that shows on the analysis
manager. Every time I open Analysis Services and click on my database, one
of my cubes shows the current time for being "processed" under metadata.
Not all of my cubes shows this time. My database and cube have the same
name (don't know if this means anything, but my other cubes have different
names.
Any help?
Eric
Message posted via http://www.webservertalk.comYou might get better help over in microsoft.public.sqlserver.olap or
microsoft.public.sqlserver.datawarehouse
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Eric Wiltrout via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:55c55310d5784032b6089b6f75c89123@.SQ
webservertalk.com...
> I have an issue with the "processed" time that shows on the analysis
> manager. Every time I open Analysis Services and click on my database,
one
> of my cubes shows the current time for being "processed" under metadata.
> Not all of my cubes shows this time. My database and cube have the same
> name (don't know if this means anything, but my other cubes have different
> names.
> Any help?
> Eric
> --
> Message posted via http://www.webservertalk.com

Cube not showing up in Metadata Browser

I have a cube that is processed, available, browsable, etc., but that does not appear in the list of available cubes in the following scenarios:

Open a new MDX Query window in the Managment Studio: the Metadata browser displays the error message "Error loading Metadata: No cubes were found.", Yet I can still write a query and execute it against the cube. Use excel and try connecting to the cube using the "Data", "Import External Data", "New Database Query", "Olap Cubes","<New Data Source>", create a new "MS OLE DB Provider for Analysis Services 9.0" data source pointing to the server/database, and no cube names show up in the cube drop down. Proclarity 6.1: same problem: cube shows as not processed or unavailable and I can't connect to it.

The cube works great with the Excel Cube Analysis Add-in, so I'm suspecting it is one of the new features of AS 2005 that is causing the problem. I am using Many-to-Many dimension relationships in the cube, and mutiple measure groups. Other than that, the cube is pretty straight-forward.

Any ideas on what would make this happen? I've tried deleting the cube from the server and completely redeploying the project, changing the cube name to not have a space in the name and a few other simple things.

Regards,

Clayton

This has to fall into the "bonehead" category.

The cube's "Visible" property was set to "False". Not sure how this happened, but for the record, the solution is to set the "Visible" property to "True".

|||I don't know about "bonehead." I spent 4 hours with this exact same problem. Not until I found this post did I think to view the cubes XML and change <visible>false</visble> to <visible>true</visible>. I mean, is there even a way to change this property though the designer? I just about went crazy. Thanks.
|||

hello,

yes, if you open your project in BIDS, and open cube designer, look in the properties panel - it should show the cube and one of the properties listed in there is Visible (in the Advanced category).

hope this helps,

|||I did the same thing. I think what we all have accidentally done was select a dimension that we wanted to hide then went to the properties window to set the Visible property. What I just noticed is that if you click on on one of the dimensions while on the Cube Structure tab the properties window does not change to the dimension and the Visible property on the cube gets set to false accidentally. At least that how I ended up doing it. I hope this helps someone else realize how it happened to them.

Cube not showing up in Metadata Browser

I have a cube that is processed, available, browsable, etc., but that does not appear in the list of available cubes in the following scenarios:

Open a new MDX Query window in the Managment Studio: the Metadata browser displays the error message "Error loading Metadata: No cubes were found.", Yet I can still write a query and execute it against the cube.

Use excel and try connecting to the cube using the "Data", "Import External Data", "New Database Query", "Olap Cubes","<New Data Source>", create a new "MS OLE DB Provider for Analysis Services 9.0" data source pointing to the server/database, and no cube names show up in the cube drop down.

Proclarity 6.1: same problem: cube shows as not processed or unavailable and I can't connect to it.

The cube works great with the Excel Cube Analysis Add-in, so I'm suspecting it is one of the new features of AS 2005 that is causing the problem. I am using Many-to-Many dimension relationships in the cube, and mutiple measure groups. Other than that, the cube is pretty straight-forward.

Any ideas on what would make this happen? I've tried deleting the cube from the server and completely redeploying the project, changing the cube name to not have a space in the name and a few other simple things.

Regards,

Clayton

This has to fall into the "bonehead" category.

The cube's "Visible" property was set to "False". Not sure how this happened, but for the record, the solution is to set the "Visible" property to "True".

|||I don't know about "bonehead." I spent 4 hours with this exact same problem. Not until I found this post did I think to view the cubes XML and change <visible>false</visble> to <visible>true</visible>. I mean, is there even a way to change this property though the designer? I just about went crazy. Thanks.|||

hello,

yes, if you open your project in BIDS, and open cube designer, look in the properties panel - it should show the cube and one of the properties listed in there is Visible (in the Advanced category).

hope this helps,

|||I did the same thing. I think what we all have accidentally done was select a dimension that we wanted to hide then went to the properties window to set the Visible property. What I just noticed is that if you click on on one of the dimensions while on the Cube Structure tab the properties window does not change to the dimension and the Visible property on the cube gets set to false accidentally. At least that how I ended up doing it. I hope this helps someone else realize how it happened to them.

Cube not showing up in Metadata Browser

I have a cube that is processed, available, browsable, etc., but that does not appear in the list of available cubes in the following scenarios:

Open a new MDX Query window in the Managment Studio: the Metadata browser displays the error message "Error loading Metadata: No cubes were found.", Yet I can still write a query and execute it against the cube.

Use excel and try connecting to the cube using the "Data", "Import External Data", "New Database Query", "Olap Cubes","<New Data Source>", create a new "MS OLE DB Provider for Analysis Services 9.0" data source pointing to the server/database, and no cube names show up in the cube drop down.

Proclarity 6.1: same problem: cube shows as not processed or unavailable and I can't connect to it.

The cube works great with the Excel Cube Analysis Add-in, so I'm suspecting it is one of the new features of AS 2005 that is causing the problem. I am using Many-to-Many dimension relationships in the cube, and mutiple measure groups. Other than that, the cube is pretty straight-forward.

Any ideas on what would make this happen? I've tried deleting the cube from the server and completely redeploying the project, changing the cube name to not have a space in the name and a few other simple things.

Regards,

Clayton

This has to fall into the "bonehead" category.

The cube's "Visible" property was set to "False". Not sure how this happened, but for the record, the solution is to set the "Visible" property to "True".

|||I don't know about "bonehead." I spent 4 hours with this exact same problem. Not until I found this post did I think to view the cubes XML and change <visible>false</visble> to <visible>true</visible>. I mean, is there even a way to change this property though the designer? I just about went crazy. Thanks.|||

hello,

yes, if you open your project in BIDS, and open cube designer, look in the properties panel - it should show the cube and one of the properties listed in there is Visible (in the Advanced category).

hope this helps,

|||I did the same thing. I think what we all have accidentally done was select a dimension that we wanted to hide then went to the properties window to set the Visible property. What I just noticed is that if you click on on one of the dimensions while on the Cube Structure tab the properties window does not change to the dimension and the Visible property on the cube gets set to false accidentally. At least that how I ended up doing it. I hope this helps someone else realize how it happened to them.

Cube always shows the current time under "Processed".

I have an issue in Analysis Manager with regard to the "last time the cube
was processed". When I open Analysis Manager, connect to my Server, and
click on my database. I click on metadata and see under "Cubes" that one of
my cubes always shows the current time under "Processed".
I have three other cubes that show the correct time as to when they were
processed. My database and the cube in question have the same name...I
don't know if this is an issue.
Can anybody help?
Eric
Message posted via http://www.droptable.com"My database and the cube in question have the same name...I don't know
if this is an issue"
In a test AS 2000 database, I created a copy of an existing cube, giving
it the same name as the database. The "last processed time" of this cube
is correctly reported, and does not change with the current time. So
this doesn't seem to be an issue in all cases (may be in your case?).
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!