cursor in Stored Procedures

any one Explain me the details about Cursors in Stored Procedures and is there any other way to call views in stored Procedure

Quote:

Originally Posted by hariharanmca

any one Explain me the details about Cursors in Stored Procedures and is there any other way to call views in stored Procedure

Its very Urgent, If i simply write select * from vw_viewName then its giving Error like

Error:
=================================
Server: Msg 8624, Level 16, State 16, Procedure sp_Add_Remove_ItemQtyForNonChargeOrder , Line 18
Internal SQL Server error.

Line 18 is select * from vw_viewName|||See problem description here.

Curious DTSWizard behavior

Can someone try and explain to me how a sql query can run fine and return data, yet when I try to run it in dtswizard, while the "preview" view shows the data, actually running it returns zero rows?

This is on SQL2005 express and since I can't get dtsrun or dtexec to work, I'm using auto-it to simulate my actually stepping through the process. Very kludgy, but "when all you've got is a hammer..."Very difficult to debug a DTS issue without looking over your shoulder. More details on the query might help. Are you getting any error messages? Does the target object exist under different ownerships?|||I can post the query here if that would help, I just find it odd that the preview shows me data while the actual process does not. If I didn't make myself clear, I'm just running the dtswizard to create an Excel file and not actually saving the dts package since I have no way of running the package.|||I can't promise that posting the query will help, but I can tell you that you will not get a lot of responses on this forum unless you do post the query.|||Fair enough. Here it is...

use Hayesonline

SELECT
o.name
, Coalesce(Sum(CASE WHEN 1 = d THEN 1 END), 0) AS [01] -- Show explicit zero in first column
, Sum(CASE WHEN 2 = d THEN 1 END) AS [02]
, Sum(CASE WHEN 3 = d THEN 1 END) AS [03]
, Sum(CASE WHEN 4 = d THEN 1 END) AS [04]
, Sum(CASE WHEN 5 = d THEN 1 END) AS [05]
, Sum(CASE WHEN 6 = d THEN 1 END) AS [06]
, Sum(CASE WHEN 7 = d THEN 1 END) AS [07]
, Sum(CASE WHEN 8 = d THEN 1 END) AS [08]
, Sum(CASE WHEN 9 = d THEN 1 END) AS [09]
, Sum(CASE WHEN 10 = d THEN 1 END) AS [10]
, Sum(CASE WHEN 11 = d THEN 1 END) AS [11]
, Sum(CASE WHEN 12 = d THEN 1 END) AS [12]
, Sum(CASE WHEN 13 = d THEN 1 END) AS [13]
, Sum(CASE WHEN 14 = d THEN 1 END) AS [14]
, Sum(CASE WHEN 15 = d THEN 1 END) AS [15]
, Sum(CASE WHEN 16 = d THEN 1 END) AS [16]
, Sum(CASE WHEN 17 = d THEN 1 END) AS [17]
, Sum(CASE WHEN 18 = d THEN 1 END) AS [18]
, Sum(CASE WHEN 19 = d THEN 1 END) AS [19]
, Sum(CASE WHEN 20 = d THEN 1 END) AS [20]
, Sum(CASE WHEN 21 = d THEN 1 END) AS [21]
, Sum(CASE WHEN 22 = d THEN 1 END) AS [22]
, Sum(CASE WHEN 23 = d THEN 1 END) AS [23]
, Sum(CASE WHEN 24 = d THEN 1 END) AS [24]
, Sum(CASE WHEN 25 = d THEN 1 END) AS [25]
, Sum(CASE WHEN 26 = d THEN 1 END) AS [26]
, Sum(CASE WHEN 27 = d THEN 1 END) AS [27]
, Sum(CASE WHEN 28 = d THEN 1 END) AS [28]
, Sum(CASE WHEN 29 = d THEN 1 END) AS [29]
, Sum(CASE WHEN 30 = d THEN 1 END) AS [30]
, Sum(CASE WHEN 31 = d THEN 1 END) AS [31]
FROM dbo.Offices AS o
LEFT JOIN (SELECT a.officeID
, DatePart(d, o.DateCompleted) AS d
FROM dbo.Orders AS o
JOIN dbo.Appraisers AS a
ON (o.AppraiserID = a.AppraiserID)
WHERE Convert(CHAR(8), GetDate(), 121) + '01' <= o.dateCompleted
AND o.DateCompleted < DateAdd(month, 1, Convert(CHAR(8), GetDate(), 121) + '01')
AND 1 = o.StatusID) AS z
ON (z.officeID = o.officeID)
GROUP BY o.name
ORDER BY o.name ASC|||Are there any other tables named Offices, Orders, or Appraisers, under ownerhips other than DBO?

What is the datatype of the Orders.dateCompleted column? Is it datetime or is it varchar?|||DBO owns everything in the system.

orders.datecompleted is of type datetime.

cube processing error - perhaps someone can explain

I have one fact table that is also a dimension table + one other dimension table.

When I deployed the cube I was getting an key attribute missing error.

I went back and did a "Process Full" on the fact diminsion and then the cube would

deploy without the error.

Should a fact dimension always be set to process full?

thanks

What could have happen here is:

You've processed your dimension earlier, and then you got some more data inserted into your table. Processing of cube will figure out that dimension is already processed and will only start processing of partitions. The partition processing will see new members that came with new data and will complain during processing.

You dont have to use ProcessFull for your dimensions to keep them in sync. You can run ProcessUpdate for dimensions. But if processing of your dimensions and the cube doesnt take long, I would say do the ProcessFull.

Here is whitepaper that gives bit more details about processing: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

Hope that helps.

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

|||

I went in and changed the process options for the dimension to 'process full'

however it always defaults back to 'process update'

is there a way to lock it on process full?

|||

Click on the "script" button in your processing dialog to get XMLA processing command generated.

You can send same XMLA command to process your dimension and not to use ProcessingDialog.

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