Analysis :: Displaying Two Levels From Same Hierarchy In Two Columns
Oct 7, 2015
I want to display Month and year from same time hierarchy in two separate columns.My hierarchy is as below:
Ship Date > Ship Date Hierarchy
Ship Date year
Ship Date Quarter
Ship Date month
The display I am looking for is as below
Ship Date year Ship Date Month
measures
2015 Jan
200
2015 Feb
300
I am using Tail function as I need only the last 13 months, below is my query:
with set [Month] as
{
tail([SHIP DATE].[Ship Date Hierarchy].[SHIP CAL MTH].members, 13)
}
select
{[Measures].[TAG BUID Distinct Count]} on 0,
{[Month]* [SHIP DATE].[Ship Date Hierarchy].[SHIP CAL YEAR]} on 1
from
[GSDR_P4]
This gives me error that The Ship Date Hierarchy hierarchy is used more than once in the Crossjoin function.
I am having a requirement as below. I am having a Emp_Dim dimension table which is having a Manager_Key which is dependent on again on the Emp_Key. Based on the designation I have to create a Hierarchy. I have to create a Hierarchy with 10 levels according to the Designation.
While extracting data I am giving a particular Area manager id in Emp_Key I should get the accumulated data (His and the employees working under him as well). So it's like my MDX query should be like this if I am giving any emp_key then I need all the business done by under him.
My doubt is how I can establish the relations between the different levels in my Hierarchy. I am pretty new to SSAS.
Out of nowhere my derived hierarchies starting showing the following message in the MDS UI
No Level Defined: This Derived Hierarchy is incomplete......
As you can see below the structure is defined and still in tact. This message shows up in by the Explorer & System Admin areas. I'm also able to query the subscription view setup without any issue.
This is with 2012 w/ no CUs. Same setup in running in another environment without issue.
I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -
I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:
"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension.. Parameter name: mdx (MDXQueryGenerator)"
Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).
Here's what my query looks like -
SELECT { [Time].[Month].&[2006-09-01T00:00:00] , [Time].[Month].&[2006-10-01T00:00:00], [Time].[Month].&[2006-11-01T00:00:00], [Time].[Month].&[2006-12-01T00:00:00] } on COLUMNS, { [Measures].[Unique Users], [Measures].[UU Pct 1], [Measures].[UU Pct 2], } ON ROWS FROM [Cube]
I like to get data from a signle table and arranged in hierarchical(hierarchy) order. What will be my sql script to be able to get the desired result shown below? Please include some explanation as too what script is doing..
Table Structure and Sample Data
Id ParentId Name Code DisplayOrder 1 null Group 1 G00001 1 2 null Group 2 G00002 2 3 1 Sub-Group 1 SG0001 1 4 2 Sub-Group 2 SG0002 1 5 3 Sub-Sub-Group 1 SSG001 1 6 null Group 3 G00003 3 7 3 Sub-Sub-Group 2 SSG002 2
Desired Result Id ParentId Level Name ExtendedName DisplayOrder 1 null 1 Group 1 Group 1 1 3 1 2 Sub-Group 1 Group 1 -> Sub-Group 1 1 5 3 3 Sub-Sub-Group 1 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 1 1 7 3 3 Sub-Sub-Group 2 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 2 2 4 2 2 Sub-Group 2 Group 1 -> Sub-Group 2 1 2 null 1 Group 2 Group 2 2 6 null 1 Group 3 Group 3 3
Is there a way - through a DMV or other query - that I can retrieve the collation for a hierarchy using code?I know that I can find out the server, cube, and hierarchy collation using SSMS. But I'm looking to do this in code, in order to compare collations between two cubes' dimensions, and compare member values.
I have a fact table that captures the captures oldest date between a Task date and todays date per Person (so there is only fact per person) like this - a measure is created from the Task Days
PersonID, Dateduekey, Task Days 130 20130809 679
I have a person dimension which has a hierarchy of Department_Name->Team_Name->Person I have created some MDX that gives the MAX Task Day for the hierarchy but when you Person attribute of the hierarchy the code runs really slow.with set a as nonempty(([v Dim Fee Earners].[People Structure].[Person],[Dim Calendar].[Primary Date].[All]),[Measures].[Days KPI]) member measures.a as max(existing(a),[Measures].[Days KPI])
How can I get this running for more efficiently when [v Dim Fee Earners].[People Structure].[Person] is selected? To be fair the true measure [Days KPI] is already calculated at person level so if there is way to use the [Days KPI] when [v Dim Fee Earners].[People Structure].[Person] is selected that would be good
I have a Tabular model with a situation where I want to have three alternate attributehierachies in one dimension.
Dimension FruitAndVegetables (with 4 columns: Id, Name, Fruit and Vegetable) Id Name Fruit Vegetable 1 Apple Apple 2 Onion Onion 3 Banana Banana 4 etc
Now I would like to put Vegetable on rows in a report without getting a blank row (with the sales of all Fruits)..I would like to supress all those Fruit records without adding a separate filter to the report, just let the user pick this Attribute should do the move.
I am using a "Client" dimension that includes a "Holding - Client" hierarchy. I have to make sure, that only the appropriate roles may access appropriate members from this dimension, but I only have the information which role may access which ClientID - I do not have the information which HoldingID should be accessible. Also, "Client" is used as the key column of the dimension with "ClientID" and "HoldingID" as key columns. The hierarchy is strict, no client may belong to multiple holdings.
I cannot seem to find the right MDX for the allowed member set. My MDX expression would need to look like this:
[Client].[Holding - Client].[Client].&[*]&[123]
In this example I want to give access for client &123, no matter the holding, so &1&123 and &2&123 would be allowed.
Any working example of what a ragged hierarchy should look like for the unary and custom rollup calcs to work?
I have a parent-child hierarchy that works as expected but can't manage to get the same with a flattened ragged hierarchy. Parents disappear if any of the children aren't in the fact table (even though the children are set to '~' and the parent has its own custom rollup calc).
Do I need to replicate the unary and measure formula all the way to the end in the same way i do the member name/value (so i can set ignore if same as parent)? Setting unary to null seems to work when the key doesnt exist in the fact table but the default behaviour for null is '+' so i end up getting wrong results if the key does exist!
Repro:
In words
I have an EAV style fact table. The measure name is defined in a 'dim measure' table. Some measures exist in the fact, some are manufactured using Unary ops or custom rollup formulas.
Here's a diagram of the hierarchy
I want to recreate the functionality as a flattened hierarchy (the number of levels is fixed), but can't seem to do it... Here's what I've gotten for the same hierarchy created using the two different approaches
Below are queries to recreate the tables from adventureworksdw2014. The parent child hierarchy is set up the standard way... measureparentkey is parent. set name, unary and formula as expected. Set NonLeafDataHidden for the parent attribute. I've tried various combinations for the ragged hierarchy but none work 100% of the time. I want to have a user defined hierarchy for the performance benefits.
--Fact view CREATE View dbo.FactSales AS select fis.ProductKey , fis.OrderDateKey , 1 AS MeasureKey -- salesAmount , fis.SalesAmount AS MeasureValue
I have a time dimension which has Date, Week, Month and Year. However, the hierarchy will have only Week, Month and Year. It works great for any Sales measure with AggregateFunction as SUM.
I have created a new measure with AggregateFunction = LastNonEmpty. Also in the backend, I have pushed all the inventory data to last date in every month as inventory is always looked on a monthly basis not on a weekly basis. This measure shows correct data for every last week of the month in the hierarchy. However, Months and Years are displayed as zeros.
I have a disconnected utility dimension with the following data and a waterfall->subwaterfall hierarchy. I have ignore if same as parent set on sub water fall. The unary operator is assigned to the relevant level
Opening ~ Opening ~ Price ~ Price ~ Total Composition ~ Composition L1 + Total Composition ~ Composition L2 + Total Composition ~ Composition L3 + Total Composition ~ Composition L4 + Total Composition ~ Composition L5 + Closing ~ Closing ~
I'm using MDX to set values to the members
SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Price]); THIS=Measures.LowestPrice; END SCOPE; SCOPE(Measures.Waterfall,[Dim Waterfall].[Sub Water Fall].&[Composition L1]); THIS=Measures.[Composition L1]; END SCOPE;
[Code] ....
But when i run a simple select query
SELECT {[Measures].[Waterfall]} ON COLUMNS, [Dim Waterfall].[Water Fall].[Water Fall] ON ROWS FROM [GPA]
I only get a value for price. Total Composition is NULL. If I change my query to look at the subwaterfall level then I see values for composition L1->L5, so I know there are values there I was expecting the unary operator to aggregate my composition measures into Total Composition.
I should point out that every measure that's being assigned to my utility dim is calculated with in excess of a dozen or so steps and intermediate calcs, is this causing some kind of solve order issue?
I know that as a workaround i could probably do something like
SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Total Composition]); THIS=SUM([Dim Waterfall].[Hierarchy].[Water Fall].&[Total Composition].Children,Measures.Waterfall); END SCOPE;
But that defeats the purpose of the unary operators
Include children and exclude children in a single hierarchy in parent child dimension in mdx
*12-parent **20-parent - 9-parent --250-child1 --210-child2 --240-child3 aggregation of 12-parent only aggregation of 20-parent only aggregation of 9 with children
I have a parent Child attribute in my dimension. It show the correct ID value in the reports but when I change the value from the NameColumn to be something else it still only shows the ID value. I have an identical case in another dimension and it is working correctly. However this dimension for some reason does not seem to work and I believe they are built the same way I must be missing something. I have tried to show the OrgNodeID and tried to display the OrgNodename but neither displays all it displays is the intdimParentOrgNodeID.My dimension table looks something like this
intdimOrgNodeID int Key (surreget key) intOrgNodeID int (Actual ID) intDimParentOrgNodeID intOrgNodeName
I have a cube with a fact table and 3 dimensions. One of the dimensions is a type 2 and surrogate key is stored in fact table. If i query the database, the dimension attributes display correctly, however the cube is always displaying the latest dimension attribute and not preserving the history.
The measures are correct for the time period displayed, but the dimension attributes always show the latest values.
My problem arises when I want to display First Name and Last Name for both the POCusername and the AssigneeUserName since FirstName and LastName columns exists once in the Personnel table. How can I display the columns twice. Lets say POCFirstName, POCLastName, AssigneeFirstName, and AssigneeLastName. Or this is something that can't be done.
The query below only display the Assignee info. Of course, I need to something else to display the POC info as well...don't where to go from here...
select tblPersonnel.FirstName, tblPersonnel.LastName, tblAsset.AssigneeUserName, tblAsset.POCUserName from tblVendor, tblAsset, tblPersonnel where and tblPersonnel.UserName = tblAsset.AssigneeUserName and tblAsset.POCUserName in(select tblPersonnel.UserName from tblAsset, tblPersonnel where tblPersonnel.UserName= tblAsset.POCUserName)
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.
I created the following simple query between two tables:
SELECT vru.User_Name0, vru.Full_User_Name0, vru.department0, vru.employeeNumber0, vru.title0, vru.manager0, vrs.location0, vrs.Netbios_Name0, vrs.Last_Logon_Timestamp0 FROM v_R_User vru LEFT JOIN v_R_System vrs ON vru.ResourceID = vrs.ResourceID ORDER BY vrs.User_Name0
I don't know how to properly insert a pic, but basically I get output, except the last 3 columns. I've tried every variation of JOIN and I cannot seem to solve this issue.
Ihave 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up).
image 1 is what is currently set up imgur: the simple image sharer top part is what is stored in tables..bottom is more of the result it basically runs this code to get the bottom
DECLARE @id INT; DECLARE @invest nvarchar(50); SET @id = '7633'; SET @invest = ''; SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat FROM orderheader, orderaudits, orderfindings WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%' GROUP BY orderfindings.risk_rating
If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it.
image 2 - the table at the bottom is more what i'm looking for..it combines them both into 1 imgur: the simple image sharer
if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..
I've been working with it...did this [SQL] compare2 - Pastebin.com ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...
Is it possible to display only a certain number of columns in a matrix, say the first 6 and then hide the rest? That is, does the matrix allow to somehow control how many columns can be displayed from a column group and hide the remaining columns (I need this to limit the number of columns a user is able to see so that the matrix width does not get infinitely long).
In other words.....
I need to display the subtotals for all dynamically generated columns but display only first 6 columns. This way I can avoid having to display 50 columns and not have user scroll to so far right and keep the page width within reasonable limits. Hope I have made it clear.
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script: SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added], dbo.cdbAttribInst.ainTVal AS Inudstry FROM dbo.cdbClient LEFT OUTER JOIN dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
I am trying to count the columns of a MDX query but I haven't yet found a solution. Below is the query
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All]},,,INCLUDE_CALC_MEMBERS)}) ON COLUMNS FROM [XCUBE] WHERE ( [TIME].[Year Quarter Month].[Quarter Name].&[Quarter 1, 2015], [CustomerType].[CustomerType].&[Money], [Measures].[Total X] )
With result as
All r k l j g l 7000 1 7 8 5 4 3
The All counts the total of the [Measures].[Total X] and I want to measure the number of columns that presented at the result. Unfortunately my dimension Customers doesn't connect with time dimension...
Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically. My question is how do I get the previous two years dynamically as well. I've tried a few different ways with no luck. As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.
SELECT { [Time].[Time].[Year].&[2013], [Time].[Time].[Year].&[2014], TAIL([Time].[Time].[Year].MEMBERS,1) }ON COLUMNS, NON EMPTY {[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS FROM SALES
I need to show the dimensions of my model like columns in the result. I have this query
with member [Measures].[Customer] as [Customers].[Customer].CURRENTMEMBER.Name member [Measures].[UCs] as [UCs].[UC].CURRENTMEMBER.Name member [Measures].[Order Type] as [Order Types].[Order Type].CURRENTMEMBER.Name member [Measures].[UC Dates] as [UC Dates].[UC Date].CURRENTMEMBER.Name
The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states. Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state.
So far what I've got is:
CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL; SCOPE (Measures.[weighted measure state and above], Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE), Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE), ... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;
This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.
Not sure where the culprit is for this error propping up. I've added a new measure group and Im trying to generate the schema for the resulting fact table, but something isn't right. However, I don't think its my new measure group that's causing the issue, because I removed it and still get this issue when I select Database > Generate Relational Schema
I have just started working the 2047 OLAP and came arcross the Analysis Service Limits. It states that the levels in a cube has a limit of 256 and the leves per dimension is 64. I am confused.
What is the definition of (levels in a cube)! and how are the levels in a cube different from (levels per dimension)
I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.
In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.
I'm using the Bush family in this example, these are the relationships: - George SR --- George JR ------ Jenna ------ Barbara --- Jeb ------ Jeb JR ------ Noelle
These statements will create and populate the tables for the example with the above relationships:
SET NOCOUNT ON DROP TABLE KK_Kids, DD_Dads, GG_Grandpas CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20)) CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20)) CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))
INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR') INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR') INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB') INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' ) INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' ) INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' ) INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )
So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads. I am getting this, which is not what I want:
- George SR --- George JR --- Jeb ------ Jenna ------ Barbara ------ Jeb JR ------ Noelle
SELECT 1 as Tag, NULL as Parent, GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName], GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id], NULL as [DD_Dads!2!DD_DadName], NULL as [DD_Dads!2!DD_Dad_Key!id], NULL as [DD_Dads!2!DD_Grandpa_Key!idref], NULL as [KK_Kids!3!KK_KidName], NULL as [KK_Kids!3!KK_Dad_Key!idref] FROM GG_Grandpas UNION ALL SELECT 2 , 1 , NULL , GG_Grandpa_Key , DD_DadName , DD_Dad_Key , DD_Grandpa_Key , NULL , NULL FROM GG_Grandpas, DD_Dads WHERE GG_Grandpa_Key = DD_Grandpa_Key UNION ALL SELECT 3 , 2 , NULL , GG_Grandpa_Key , NULL , DD_Dad_Key , NULL , KK_KidName , KK_Dad_Key FROM GG_Grandpas, DD_Dads , KK_Kids WHERE GG_Grandpa_Key = DD_Grandpa_Key AND DD_Dad_Key = KK_Dad_Key
FOR XML EXPLICIT
I've tried it all different ways, but no luck so far. Any ideas?
How do I get my data to show starting at the first row instead of skipping down?
Refer to the attachment.
Code: CREATE PROCEDURE [dbo].[uspReportData] -- Add the parameters for the stored procedure here @Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL, @Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,
I am redesigning an application that distributes heldesk tickets to our50 engineers automatically. When the engineer logs into their window astored procedure executes that searches through all open tickets andassigns a predetermined amount of the open tickets to that engineer.Theproblem I am running into is that if 2 or more engineers log in at thesame time the stored procedure will distribute the same set of ticketsmultiple times.Originally this was fixed by "reworking" the way SQL Server handlestransactions. The original developer wrote his code like this:-----DECLARE @RET_STAT INTSELECT 'X' INTO #TEMPBEGIN TRANUPDATE #TEMP SET 'X' = 'Y'SELECT TOP 1 @TICKET_# =TICKET_NUMBER FROM TICKETS WHERE STATUS = 'O'EXEC @RET_STAT = USP_MOVE2QUEUE @TICKET_#, @USERIDIF @RET_STAT <> 0ROLLBACK TRANRETURN @RET_STATENDCOMMIT TRAN-----The UPDATE of the #TEMP table forces the transaction to kick off andlocks the row in table TICKETS until the entire transaction hascompleted.I would like to get rid of the #TEMP table and start using isolationlevels, but I am unsure which isolation level would continue to lockthe selected data and not allow anyone else access. Do I need acombination of isolation level and "WITH (ROWLOCK)"?Additionally, the TICKETS table is used throughout the application andI cannot exclusively lock the entire table just for the distributionprocess. It is VERY high I/O!Thanks for the help.
I am trying to get my head around locking (row, table) and Isolation Levels. We have written a large .NET/SQL application and one day last week we had about two dozen people in our company do some semi "stress/load" testing of the app.
On quite a few occassions, a few of the users would receive the following error:
"Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
We are handling this on two fronts, the app and the database. The error handling in the app is being modified to capture this specific error and to retry the transaction.
However, from the database side, I am trying to find the most affective and efficient change to make regarding locking. I have been doing a lot of reading online and in BOL to get a better grasp of locking, but what I would really like is feedback from the community (forum) and get your thoughts on what changes I should make, if any, on the db side.
