Cursor Fetch Problem

I'm using a cursor to get a rowset and then update that rowset with certain new values. So I have a basic while loop to check until @.@.fetch_status is not zero. However, this means that it tries to fetch one time a row that does not exist before it exists the loop. This gives out an error message that the query had an error in it. I know it's not an error, but is there any way way to avoid that? Is there a way to peek ahead to see if I'm currently fetching the last row and exit before trying to fetch another one? Maybe there is a way to see how many rows were returned when the cursor was opened? This is a dynamic cursor so @.@.CURSOR_ROWS won't work as far as I know.

Thank you.What was the exact error?
For more information on cursors refer to books online.|||How about using the dreaded GOTO

WHILE 1 = 1

BEGIN

FETCH NEXT..............

IF (@.@.FETCH_STATUS <> 0) GOTO CloseCursor

......update rowset.......

END

CloseCursor:

CLOSE .........
DEALLOCATE ..........

GWsql

Current Process

Hi
I've been told that I can use the following SQL expressions to find out the
current SQL being executed under a certain SPID, but for some reason I
cannot get this to work.
Is there something wrong, in what I have written?
--*************************************
DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
SELECT * FROM ::fn_get_sql(@.Handle)
--*************************************
Kind Regards
Ricky
(WIN2K/SQL2K-SP4)It should work if the specified SPId is still running.
Try this
DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = @.@.SPID
SELECT * FROM ::fn_get_sql(@.Handle)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ricky" <ricky@.ricky.com> wrote in message
news:O08GR61mGHA.4212@.TK2MSFTNGP04.phx.gbl...
> Hi
> I've been told that I can use the following SQL expressions to find out
> the
> current SQL being executed under a certain SPID, but for some reason I
> cannot get this to work.
> Is there something wrong, in what I have written?
> --*************************************
> DECLARE @.Handle binary(20)
> SELECT @.Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
> SELECT * FROM ::fn_get_sql(@.Handle)
> --*************************************
> Kind Regards
> Ricky
> (WIN2K/SQL2K-SP4)
>

Currency Format

The $ must appears only on certain total levels and has to be in the same position in the column regardless of the value for financial reporting. In other words, I don't want the $ to float next the most significant digit in the value. See the example below from Excel. Using an IIF statement I can concatenate the $ to the string when it is a certain group level. However, I have tried many various formatting strings but can't find one that results in a fixed number of characters so that the $ will always appears in the same position in the column.

Anyone have a way to do this?

General Government

222

3.45%

$ 405,590,654

1.98%

Public Buildings

194

3.01%

375,453,314

1.83%

Other Facilities

22

0.34%

21,324,140

0.1%

Property Acquisition

6

0.09%

8,813,200

0.04%

Grand Total

6,439

100.0%

$ 20,507,977,298

100.0%

I did get this to work by adding a column to the grouping. That somehow causes the heading size to increase (?) and takes much more time than coding a format code in an expression (I have many reports to do).

Can you try something like this in your field's expression:

="$" & Space(30 - len(Fields!Total.Value)) & Fields!Total.Value

The 30 in my example would have to be higher than the maximum number of digits in your Total field. This will put spaces between the $ and the actual number you are showing, but only enough to make the $ symbols align.

Hope this helps.

Jarret

|||

This is close, but the proportional font keeps the $ from lining up.

I am not a VB programmer so I have a little trouble finding the appropriate string and format functions.

Thanks for your reply.

|||

Could you change to a fixed-width font so that it would line up?

Or, you could add a column to the left of the field that has the $ symbol in it, but only for the group header and footer. This way, it would always line up.

Jarret

|||

I did the add a column and it causes the group headers to grow and be ugly. I am hoping for a solution where I can code something in the expression. If not, I may have someone code me a little VB routine to use.

Thanks.

Currency Conversion

I am using the cube intelligence wizard to add currency conversions. I have defined everything properly. At a certain moment, a query is launched against my Oracle backend. This is the query:

SELECT "Reporting Currency".*

FROM

(

SELECT "CCDM_IL"."CCDM_IL_CRRN_T"."CURRENCY_CODE","CCDM_IL"."CCDM_IL_CRRN_T"."CURRENCY_NAME"

FROM "CCDM_IL"."CCDM_IL_CRRN_T"

WHERE [CURRENCY_NAME] IN ('Euro') UNION

SELECT

DISTINCT

'Local' "Local",'Local' "Local 2"

FROM "CCDM_IL"."CCDM_IL_CRRN_T"

)

"Reporting Currency"

Now, this query returns the error "ORA-00936: Missing Expression". This error is caused by the square brackets around currency_name in the where clause. Once I remove these the query runs fine.

How can I make this query work?

Thanks

Joos

By the look of it this is coming from a named query in the DSV. Check if the wizard has actually inserted the square brackets into the named query in the DSV. If so, you should be able to edit the definition to remove the square brackets.|||

The query is generated by the BI wizard itself. In my DSV, I do not have a named query for the currency dimension. It is a real table...

The currency dimension has one attribute for which the key is set to CCDM_IL_CRRN_T.CURRENCY_CODE and the name is set to CCDM_IL_CRRN_T.CURRENCY_NAME.

|||Can you double check? Right click on the object in the DSV and tell me if the third menu item you see says either "Edit Named Query..." or "Replace Table with...". I would not normally ask, but the reason I am is becuase the BI wizard actually generates objects in your DSV and cubes, but it does not actually send any queries to the relational source.|||

For the table CCDM_IL_CRRN_T, it definitely says "Replace Table".

Joos

|||

Have you been able to isolate if this error is coming from a dimension or from a partition? (by processing objects associated with currencies one at a time)

Try going into SSMS, right clicking on the database and generating an alter script. This will generate a heap of xmla, search through this for the table name in square brackets or some other identifiable portion of this problem query. You can then either run the alter script and then import the database back into BIDS or hopefully this will help identify where exactly the problem query is coming from.

|||

In the script, the square brackets do not occur. Extracts where currency_name occurs:

<NameColumn>

<DataType>WChar</DataType>

<DataSize>255</DataSize>

<Source xsi:type="ColumnBinding">

<TableID>CCDM_CCDM_IL_CRRN_T</TableID>

<ColumnID>CURRENCY_NAME</ColumnID>

</Source>

</NameColumn>

<xs:element name="CURRENCY_NAME" msprop:FriendlyName="CURRENCY_NAME" mspropbColumnName="CURRENCY_NAME" minOccurs="0">

<xsimpleType>

<xs:restriction base="xstring">

<xs:maxLength value="255" />

</xs:restriction>

</xsimpleType>

</xs:element>

Joos

Cube Roles - How to restrict access to certain members

I have a dimension called Account that has five levels. I'm trying to define a cube role to deny access to accounts in the lowest level that starts with the letters GL. I tried applying a filter in the dimension data tab in the role designer, however, this only seems to create a static rule. The account structure gets updated all the time and I want to restrict access to all accounts that start with the letters GL now and in the future.

When I created a rule in another dimension using a top-level static member, a denied member set was created in the advanced tab. I think I need to create something similar for the Account dimension, but I just can't get the syntax of MDX. I tried the BOL samples but can't find anything since I want to filter only part of the member name.

Try typing something like the following expression for DeniedSet:

Filter(Account.Levels(4), Account.Name >= 'GL' AND Account.Name < 'GM')

|||

Thanks, but that didn't work. In addition, I tried Filter([Account].Members, Left([Account].CurrentMember.Name, 2) = "GL") but that didn't work either. I'm wondering if there are too many members to filter at the bottom level.

Oddly, I was able to solve part of the problem by Enabling Visual Total in my other dimensions. I can filter GL type accounts in my other dimensions because there is only one fixed GL member in those dimensions. Since GL accounts only match up to the GL members in the other dimensions and since those GL members have been filtered, GL Accounts are not displayed in the cube result.

I will still try to find a solution, although I can restrict GL data from being displayed in the cube, the GL accounts still show up in the drop down. It's a good thing that when the user selects GL accounts, nothing is displayed.