Sirs / Madams


        I am new to MSSQL and was wondering about data conflicts with Views
in a Multi-User System.  By that I mean if I have five users execute the
same report against a database at the same time but with different criteria
how does MSSQL prevent data corruption between them.  Does each user get
their own workspace?

        Any help would be appreciated.


Mark

I'm not sure I understand the question. Queries do not block each other
so there is no conflict or corruption. Or maybe you are asking the same
question that was answered in this thread:
http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_frm/thread/5c3f93002f0284b0/de491e8f68c9b7e8

David

        Here is what I mean.

        User 1 runs Report50 with the following parameters - StoreID =
131995 and Department = 3
        User 2 runs Report50 with the following parameters - StoreID =
127223 and Department = 7
        User 3 runs Report50 with the following parameters - StoreID =
295478 and Department = 1
        User 4 runs Report50 with the following parameters - StoreID =
359991 and Department = 5
        User 5 runs Report50 with the following parameters - StoreID =
478325 and Department = 4

        Below is the beginning portion of the Query that is used by Report50

        How do I prevent the data from being mixed in the view - should I be
using Temp Tables instead ?

Mark


<---------------------------------------------------------------------------
------------------------------------>

Create View T0 as

Select StoreID,
       VendorNumber,
       ItemNumber,
       PLUNumber

From   Item

Where  StoreID = 151995
  and  DepartmentCode = (1)

GO

/* -------------------------------------------------------------------------
-- */

Create View T1A as

Select T1A.StoreID,
       T1A.VendorNumber,
       T1A.ItemNumber,
       T1A.PLUNumber,
       Min(LandedCost)                             AS  MinLandedCost,
       Min(FeeOperating + FeeService + FeeFreight) AS  LowFees,
       Max(LandedCost)                             AS  MaxLandedCost,
       Max(FeeOperating + FeeService + FeeFreight) AS  HighFees,
       Max(IDate)                                  AS  LastInvoiceDate,
       Sum(PurchaseQty)                            AS  TotalPurchaseQty,
       Sum(PurchaseQty * CasePack * SRP)           AS  ExtendedRetail,
       Count(*)                                    AS  NumOfInv

From Invoice as T1A, T0

Where    T1A.StoreID      = T0.StoreID
  and    T1A.VendorNumber = T0.VendorNumber
  and    T1A.ItemNumber   = T0.ItemNumber
  and    T1A.PLUNumber    = T0.PLUNumber

Group By T1A.StoreID,
         T1A.VendorNumber,
         T1A.ItemNumber,
         T1A.PLUNumber

GO




http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_frm/thread/5c3f93002f0284b0/de491e8f68c9b7e8
Show quoteHide quote

But creating a view doesn't run a report. In order to run the report
the user needs to execute a QUERY. So what you need to do is execute a
query based on the parameters the user supplies. Create a stored
procedure that accepts the user's input from your application:

CREATE PROCEDURE usp_item_select_by_store_department
(@store_id INTEGER, @department_code INTEGER)
AS

SELECT ...
FROM Invoice
WHERE  StoreID = @store_id
  AND  DepartmentCode = @department_code
...

RETURN

All user access to the database should usually be done through stored
procedures.

David

        How would you convert the following code to a Stored Procedure?

        Would I Create the Views and then run the Query against the Last
View in the chain?

Mark Moss


<---------------------------------------------------------------------------
------------------------------------------------------->

Create View T0 as

Select StoreID,
       VendorNumber,
       ItemNumber,
       PLUNumber

From   Item

Where  StoreID = 151995
  and  DepartmentCode = (1)

GO

/* -------------------------------------------------------------------------
-- */

Create View T1A as

Select T1A.StoreID,
       T1A.VendorNumber,
       T1A.ItemNumber,
       T1A.PLUNumber,
       Min(LandedCost)                             AS  MinLandedCost,
       Min(FeeOperating + FeeService + FeeFreight) AS  LowFees,
       Max(LandedCost)                             AS  MaxLandedCost,
       Max(FeeOperating + FeeService + FeeFreight) AS  HighFees,
       Max(IDate)                                  AS  LastInvoiceDate,
       Sum(PurchaseQty)                            AS  TotalPurchaseQty,
       Sum(PurchaseQty * CasePack * SRP)           AS  ExtendedRetail,
       Count(*)                                    AS  NumOfInv

From Invoice as T1A, T0

Where    T1A.StoreID      = T0.StoreID
  and    T1A.VendorNumber = T0.VendorNumber
  and    T1A.ItemNumber   = T0.ItemNumber
  and    T1A.PLUNumber    = T0.PLUNumber

Group By T1A.StoreID,
         T1A.VendorNumber,
         T1A.ItemNumber,
         T1A.PLUNumber

GO

/* -------------------------------------------------------------------------
-- */

Create View T1 as

Select *,
       ( MaxLandedCost - MinLandedCost)                        AS
DiffCentsCost,
       ( HighFees      - LowFees)                              AS
DiffCentsFees,
       ((MaxLandedCost - MinLandedCost) / ISNULL(NULLIF(MinLandedCost,
0.00), 1.00 )) * 100 AS DiffPercentCost,
       ((HighFees      - LowFees)       / ISNULL(NULLIF(LowFees,
0.00), 1.00 )) * 100 AS DiffPercentFees

From T1A

GO

/* -------------------------------------------------------------------------
-- */

Create View T2 as

Select   StoreID,
         VendorNumber,
         ItemNumber,
         PLUNumber,
         Brand,
         [Description],
         BaseCasePack,
         SizeQuantity,
         AvgWeeklyMovement,
         InventoryTurns,
         ( ( CurrentMargin / ( CASE ( CurrentMargin ) WHEN 100 THEN 1 ELSE
( 100 - CurrentMargin ) END ) ) * InventoryTurns ) AS ROII

From (
        Select  T2.StoreID,
                T2.VendorNumber,
                T2.ItemNumber,
                T2.PLUNumber,
                I3.Brand,
                I3.[Description],
                I3.BaseCasePack,
                I3.SizeQuantity,
                T2.AvgWeeklyMovement,
                ( ( T2.AvgWeeklyMovement * 52 ) / ( CASE ( I3.BaseCasePack )
WHEN 0 THEN 1 ELSE I3.BaseCasePack END ) ) AS InventoryTurns,
                I3.CurrentMargin,
                I3.CurrentPrice

        from    Item AS I3, (
                                Select  StoreID,
                                        VendorNumber,
                                        ItemNumber,
                                        PLUNumber,
                                        ( sum( POSMovement ) /
ISNULL(NULLIF( ( ( DATEDIFF( day, min(TDate), max(TDate) ) + 1 ) / 7 ),
0.00), 1.00 ) ) AS AvgWeeklyMovement

                                From    Movement

                                Where   StoreID  = 151995
                                  and   TDate   >= DATEADD( day, - ( 3 *
365 ), GetDate() )  --  '- ( 7 * 26 )'
                                  and   TDate   <= GetDate()

                                        Group By StoreID,
                                        VendorNumber,
                                        ItemNumber,
                                        PLUNumber
                            ) AS T2

Where    I3.StoreID      = T2.StoreID
  and    I3.VendorNumber = T2.VendorNumber
  and    I3.ItemNumber   = T2.ItemNumber
  and    I3.PLUNumber    = T2.PLUNumber
) AS T3

GO

/* -------------------------------------------------------------------------
-- */

Select   T1.StoreID,
         T1.VendorNumber,
         T1.ItemNumber,
         T1.PLUNumber,
         T2.Brand,
         T2.[Description],
         T2.BaseCasePack,
         T2.SizeQuantity,
         T2.AvgWeeklyMovement,
         T2.InventoryTurns,
         T2.ROII,
         T1.MinLandedCost,
         T1.LowFees,
         T1.MaxLandedCost,
         T1.HighFees,
         T1.LastInvoiceDate,
         T1.TotalPurchaseQty,
         T1.ExtendedRetail,
         T1.NumOfInv,
         T1.DiffCentsCost,
         T1.DiffCentsFees,
         T1.DiffPercentCost,
         T1.DiffPercentFees

From T1 T1, T2 T2

Where    T2.StoreID      = T1.StoreID
  and    T2.VendorNumber = T1.VendorNumber
  and    T2.ItemNumber   = T1.ItemNumber
  and    T2.PLUNumber    = T1.PLUNumber

Order By T1.DiffPercentCost

GO

/* -------------------------------------------------------------------------
-- */


<---------------------------------------------------------------------------
------------------------------------------------------->






Show quoteHide quote

Yes. As I explained in the link I posted earlier, views are usually
created by the developer at design-time and are not changed or
parameterized at runtime. Users interact with the database through
parameterized stored procedures. That's the standard design pattern
anyway.

Each connection has it's own process id and resultset. The only time
conflicts (not actual corruption) occcurs is when one connection tries to
query data currently being updated / inserted / deleted in another
connection's uncommitted transaction.

Show quoteHide quote

case and order by problem?
Trouble with SP
How can I do this without a cursor??
Somewhat complicated GROUP behavior that has me stumped
sort by calculated value