cursor insert with like %

Hi I have a weird problem I want to cursor thru the values in a
temporary table and use the values to do a select statement to insert
into another temporary table...This select statement uses a like clause

something like where...when I take off the insert still nothing comes
back from the select...when I hardcode values it works...I get
results...is there something wrong with appending a +'%' to a value
read from a cursor?

DECLARE @.DEPT VARCHAR(65)
SET @.DEPT = "00201,00203"

DECLARE @.TB_ABSENCES TABLE(DeptOrEmpId VARCHAR(65))
DECLARE @.TB_DEPT TABLE ( V_DEPARTMENT_CODE VARCHAR(128) )

INSERT INTO @.TB_DEPT (V_DEPARTMENT_CODE)
SELECT V_DEPT FROM [ISIS].[dbo].[FU_GET_DEPTS_FROM_STRING](',', @.DEPT)

DECLARE DEPTS CURSOR FAST_FORWARD FOR
SELECT V_DEPARTMENT_CODE+'%' FROM @.TB_DEPT

OPEN DEPTS
FETCH NEXT FROM DEPTS INTO @.DEPT_CODE

WHILE @.@.FETCH_STATUS = 0
BEGIN

--INSERT INTO @.TB_ABSENCES TABLE
SELECT Code from TB_EMPLOYEE_DEPARTMENT T2
WHERE T2.V_HIERARCHY_CODE LIKE @.DEPT_CODE + '%'

FETCH NEXT FROM DEPTS INTO @.DEPT_CODE

END

CLOSE DEPTS
DEALLOCATE DEPTSyurps (yurps@.yahoo.co.uk) writes:
> Hi I have a weird problem I want to cursor thru the values in a
> temporary table and use the values to do a select statement to insert
> into another temporary table...This select statement uses a like clause
> something like where...when I take off the insert still nothing comes
> back from the select...when I hardcode values it works...I get
> results...is there something wrong with appending a +'%' to a value
> read from a cursor?

I would guess that there are trailing spaces. Rewrite as:

DECLARE @.DEPT VARCHAR(65)
SET @.DEPT = "00201,00203"

DECLARE @.TB_ABSENCES TABLE(DeptOrEmpId VARCHAR(65))

INSERT INTO @.TB_ABSENCES TABLE
SELECT Code
from TB_EMPLOYEE_DEPARTMENT T2
JOIN [ISIS].[dbo].[FU_GET_DEPTS_FROM_STRING](',', @.DEPT) D
ON T2.V_HIERARCHY_CODE LIKE rtrim(V_DEPT) + '%'

Yeah, that's right. No cursor. There is no need for it, and it could
be costly in terms of performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Oh gosh !! a couple hours wasted on something that I didn't need
to...

thanks!

Cube won't process in SSIS, but will process thru Mgmt Studio?

Hello,

SSAS newbie here. I have an AS database called "AS_Sales" with numerous cubes, including one called "CP Sales". It has one measure group (also called "CP Sales") that is partitioned by fiscal quarter.

I can right-click the cube in management studio and hit "Process" and it indicates that everything completes successfully. However, I set up an Analysis Services task in SSIS to do the same thing, and I receive errors.

SSIS package consists of two AS tasks -- Refresh Dimensions & Reprocess Cube. The task fails during the dimension reprocessing, with the following error messages:

Information: 0x40016041 at AS_Sales_Cubes_Refresh: The package is attempting to configure from the XML file "E:\SSIS\Config\AS_Sales_Cubes_Refresh.dtsConfig".

SSIS package "AS_Sales_Cubes_Refresh.dtsx" starting.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2001_QTR4' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2003_QTR3' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2005_QTR2' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2007_QTR3' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC1060000 at Rebuild Dimension Structure, Analysis Services Execute DDL Task: OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2005_QTR4' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC1060000 at Rebuild Dimension Structure, Analysis Services Execute DDL Task: OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2006_QTR1' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Task failed: Rebuild Dimension Structure

SSIS package "AS_Sales_Cubes_Refresh.dtsx" finished: Failure.

It only seems to be failing for a handful of the partitions, which makes the problem all that more confusing. Does anyone have any ideas about what I'm doing wrong?

Thanks in advance,

Jamie

if you have defined your attribute relationships as rigid you cannot use refresh for your dimensions. Try full process of each dimension and see what happens. Place the processing(full) of the dimensions before processing the cubes/measure groups.

HTH

Thomas Ivarsson

Cube action calling a stored procedure in AS2000

Hi,

I know a cube action can call a reporting services report thru an action,

I would like to know if it is posible to launch a stored procedure thru an action , if so what would be the best approach

Thank you

U

Yes, it should be possible. What do you want the sproc to do?

Chris

|||

I alredy have the stored procedure, The sproc will make an entry into a scheduler table for with its parameters to run at a later time,

Please let me know what needs to be done to implement this call

Thanks

U

|||

Just to be clear (I've only realised that you're talking about AS2K in this thread, and so not AS2005 sprocs) - you're talking about a SQL Server sproc here, aren't you?

What I would do is create a small ASP.Net app which kicks off this sproc, which you can pass parameters (like a member uniquename or something) to from through the url. You'd then create a url action in your cube to generate the appropriate url based on what the user clicked; after they'd done so they would see a web page open informing them that the entry in the scheduler table had been made successfully.

HTH,

Chris

|||

Not being an ASP.Net expert , is there anyplace where I can find code something similar to this being done

Thanks

U