July 7, 2008 at 3:17 am
I have purchased MS SQL Server 2005 Standard Edition unlimited users license and installed it on a core 2 dual processor computer. The license is based on processor socket so a core 2 dual processor is considered one socket thus purchasing one unlimited users license will do.
We are having some problem on data integrity while executing stored procedure on the MS SQL Server. My stored procedure result is not correct at all! We tested the same thing on a single processor computer and result came out to be correct. It seems that MS SQL Server reads 2 processors instead of one processor socket.
Is there any setting we need to configure for core 2 dual processor? Or any other solution?
Please advise. Thanks.
July 7, 2008 at 6:30 am
SQL Server 2005 does not know anything about the number of processors licensed unless you hit an edition limitation (which you have not). It certainly does not return wrong data because of a licensing restriction.
You need to be more specific that the data is all wrong. What is different? What is the procedure doing? How are the execution plans different? Posting the procedures and execution plans and some background information will make it possible to help you.
July 26, 2008 at 11:10 pm
We still have the problem.
The test was pretty straightforward. We used the same stored procedure and execute in a Virtual Machinese (VMware). We can set whether to use one processor or two processors (core 2 dual processor). We have run for at least 3 times to ensure the accuracy of the data. 3 times results are correct from the one processor VMware. As for the two processors, 3 times results are the same with same wrong data. The same wrong data is the same result data on our server with core 2 dual processor.
The issue here is pretty clear. Why SQL Server 2005 Standard Edition has different result for the same stored procedure running on one processor and two processors? Please advise.
Thank you very much.
July 27, 2008 at 9:15 am
You must have differnt data or something else different. I have never even seen or heard of a bug returning different data because of different processor counts.
You should report this on Connect if you are sure this is a bug and post the URL. I'll ping people at MS to look at it.
Please post the DDL and stored proc code or give us some idea of what's wrong
July 28, 2008 at 4:37 am
It would seem as reasonable to assume the problem is VMWare as to assume the problem is the processor settings.
Are you just getting data returned in a different order?
I am still unsure of what you mean by wrong, but there is no bug I have heard of that would actually cause different data to be returned depending on the number of available processors.
July 29, 2008 at 3:29 am
It is not about different order or sorting. The returned results are always the same.
For example the stored procedure should always return the following correct data:
Execute 1st time: AAAAA
Execute 2nd time: AAAAA
Execute 3rd time: AAAAA
But it always return the following wrong data for core 2 dual processor:
Execute 1st time: AABBA
Execute 2nd time: AABBA
Execute 3rd time: AABBA
This wrong data happened on my production server running on core 2 dual processor. So I have tested the SP on my VMware with same SQL Server version and on Win XP Pro SP2. I sat 1 processor for the first setting and got the correct data (always for a few executions). Then I sat 2 processors for the second setting and got the wrong data (always the same wrong data for a few executions). Why SQL Server 2005 returned different value when I different processor setting in VMware? This testing seems to me that the SQL Server result vary when I am running core 2 dual processor. Well the funny thing is that it only happen on one stored procedure. Generally all the SPs are ok.
What is wrong with the SP? Here is the stored procedure:
truncate table wm_stockbalance
update WM_TransHist
set isposted = 0
where VName < '20070701'
USE [ReadyWMS]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[pReadyWMSWM_StockBalanceGenIni]
@pk_AD_User_ID = 1
SELECT 'Return Value' = @return_value
GO
update WM_TransHist
set isposted = 0
where vname = '20070701'
USE [ReadyWMS]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[pReadyWMSWM_StockBalanceGen]
@pk_AD_User_ID = 1
SELECT 'Return Value' = @return_value
GO
July 29, 2008 at 4:51 am
You still have not given us all of the information. I assume the procedure giving you the problem is: [dbo].[pReadyWMSWM_StockBalanceGenIni] Please post the T-SQL for this procedure.
Also, you have posted that it returns AAAAA on one server and AABBA on another. Since it can only return an integer, I assume this is not the value being returned. It's difficult to help if you don't post actual information.
Also, the return value of a stored procedure is intended for error indicators. A successful execution of a stored procedure should always return 0. If you need output you should use output parameters.
July 29, 2008 at 9:50 pm
The returned value I mentioned are just EXAMPLE. The result is in a view of tables. Very hard to show all the fields and values here.
Anyway, here is the stored procedure you requested.
ALTER PROCEDURE [dbo].[pReadyWMSWM_StockBalanceGenIni]
@pk_AD_User_ID numeric(10,0)
AS
DECLARE
@l_count int,
-- TRANSHIST
@pk_WM_TransHist_ID numeric(10,0),
@p_WM_TransHist_ID numeric(10,0),
@p_VName nvarchar(60),
@p_Date datetime,
@p_First_Trans_ID numeric(10,0),
@p_Last_Trans_ID numeric(10,0),
@p_IsPosted bit,
@p_CreateDate datetime,
@p_CreatedBy numeric(10,0),
@p_ModifiedDate datetime,
@p_ModifiedBy numeric(10,0),
@p_IsActive bit,
-- TRANSACTION
@pt_WM_Transaction_ID numeric(10,0),
@pt_AD_Entity_ID numeric(10,0),
@pt_AD_Company_ID numeric(10,0),
@pt_RefTransaction_ID numeric(10,0),
@pt_TransactionDate nvarchar(60),
@pt_TransactionNo nvarchar(60),
@pt_Description nvarchar(254),
@pt_CreateDate datetime,
@pt_CreatedBy numeric(10,0),
@pt_ModifiedDate datetime,
@pt_ModifiedBy numeric(10,0),
@pt_IsActive bit,
@pt_TransType numeric(10,0),
@pt_InvQty char(10),
@pt_InvUOM numeric(10,0),
@pt_BaseQty int,
@pt_BaseUOM numeric(10,0),
@pt_WM_InvLic_ID numeric(10,0),
@pt_WM_Product_ID numeric(10,0),
@pt_LotNo nvarchar(30),
@pt_VendorLot nvarchar(30),
@pt_JobNo nvarchar(30),
@pt_UnitPrice money,
@pt_Cost money,
@pt_Class numeric(10,0),
@pt_FIFODate datetime,
@pt_ExpirationDate datetime,
@pt_WM_TransferLine_ID numeric(10,0),
@pt_WM_ProductionLine_ID numeric(10,0),
@pt_WM_Locator_ID numeric(10,0),
@pt_WM_Warehouse_ID numeric(10,0),
@pt_WM_ReceiptLine_ID numeric(10,0),
@pt_WM_ShipLine_ID numeric(10,0),
@pt_WM_ProductionReturnLine_ID numeric(10,0),
-- Stock Balance
@ps_WM_StockBalance_ID numeric(10,0),
@ps_REF_StockBalance_ID numeric(10,0),
@ps_AD_Entity_ID numeric(10,0),
@ps_AD_Company_ID numeric(10,0),
@ps_VName nvarchar(60),
@ps_WM_Warehouse_ID numeric(10,0),
@ps_CreatedDate datetime,
@ps_CreatedBy numeric(10,0),
@ps_ModifiedDate datetime,
@ps_ModifiedBy numeric(10,0),
@ps_WM_Product_ID numeric(10,0),
@ps_Period numeric(10,0),
@ps_BeginBal_InvQty int,
@ps_BeginBal_BaseQty numeric(18,2),
@ps_OpenBal_InvQty int,
@ps_OpenBal_BaseQty numeric(18,2),
@ps_Receive_InvQty int,
@ps_Receive_BaseQty numeric(18,2),
@ps_Issue_InvQty int,
@ps_Issue_BaseQty numeric(18,2),
@ps_Return_InvQty int,
@ps_Return_BaseQty numeric(18,2),
@ps_Sale_InvQty int,
@ps_Sale_BaseQty numeric(18,2),
@ps_Adj_In_InvQty int,
@ps_Adj_In_BaseQty numeric(18,2),
@ps_Adj_Out_InvQty int,
@ps_Adj_Out_BaseQty numeric(18,2)
BEGIN
-- Get the rowcount first and make sure
-- only one row is returned
DECLARE WM_TransHist_Cursor CURSOR FOR
SELECT [WM_TransHist_ID]
FROM [dbo].[WM_TransHist]
WHERE [IsPosted] = 0
AND CONVERT(CHAR(12),[Date],112) < CONVERT(CHAR(12),GETDATE(),112)
OPEN WM_TransHist_Cursor
FETCH NEXT FROM WM_TransHist_Cursor
INTO @pk_WM_TransHist_ID
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the row from the query. Checksum value will be
-- returned along the row data to support concurrency.
SELECT
@p_WM_TransHist_ID=[WM_TransHist_ID],
@p_VName=[VName],
@p_Date=[Date],
@p_First_Trans_ID=[First_Trans_ID],
@p_Last_Trans_ID=[Last_Trans_ID],
@p_IsPosted =[IsPosted],
@p_CreateDate=[CreateDate],
@p_CreatedBy=[CreatedBy],
@p_ModifiedDate=[ModifiedDate],
@p_ModifiedBy=[ModifiedBy],
@p_IsActive=[IsActive]
FROM [dbo].[WM_TransHist]
WHERE [WM_TransHist_ID] =@pk_WM_TransHist_ID
-- Get distinct information from transaction table
DECLARE DIS_WM_TRANSACTION_Cursor CURSOR
FOR
SELECT DISTINCT
[AD_Entity_ID],
[AD_Company_ID],
[WM_Warehouse_ID],
[WM_Product_ID],
[Class]
FROM WM_TRANSACTION
WHERE [TransactionNo] BETWEEN @p_First_Trans_ID AND @p_Last_Trans_ID
AND [TransactionDate] = @p_VName
ORDER BY [WM_Product_ID]
OPEN DIS_WM_TRANSACTION_Cursor
FETCH NEXT FROM DIS_WM_TRANSACTION_Cursor
INTO
@pt_AD_Entity_ID,
@pt_AD_Company_ID,
@pt_WM_Warehouse_ID,
@pt_WM_Product_ID,
@pt_Class
WHILE @@FETCH_STATUS = 0
BEGIN
-- Prepare information for StockBalance Table
SELECT @l_count = count(*)
FROM [dbo].[WM_StockBalance]
WHERE[AD_Entity_ID] = @pt_AD_Entity_ID
AND[AD_Company_ID] = @pt_AD_Company_ID
AND[WM_Warehouse_ID] = @pt_WM_Warehouse_ID
AND[WM_Product_ID] = @pt_WM_Product_ID
AND[Class]= @pt_Class
IF @l_count = 0
-- Need to Gen New Stock Balance Record for this item with Begin Balance
BEGIN
-- Check if there is a trans type 317
SELECT @ps_BeginBal_InvQty=ISNULL(count(*),0), @ps_BeginBal_BaseQty=ISNULL(sum(BaseQty),0)
FROM WM_TRANSACTION
WHERE[AD_Entity_ID] = @pt_AD_Entity_ID
AND[AD_Company_ID] = @pt_AD_Company_ID
AND[WM_Warehouse_ID] = @pt_WM_Warehouse_ID
AND[WM_Product_ID] = @pt_WM_Product_ID
AND[Class]= @pt_Class
AND [TransType] in (317,314) -- I+
AND[TransactionNo] BETWEEN @p_First_Trans_ID AND @p_Last_Trans_ID
AND[TransactionDate] = @p_VName
SET @ps_REF_StockBalance_ID = NULL
SET @ps_OpenBal_InvQty= @ps_BeginBal_InvQty
SET @ps_OpenBal_BaseQty= @ps_BeginBal_BaseQty
--
INSERT
INTO [dbo].[WM_StockBalance]
(
[REF_StockBalance_ID],
[AD_Entity_ID],
[AD_Company_ID],
[VName],
[WM_Warehouse_ID],
[CreatedDate],
[CreatedBy],
[ModifiedDate],
[ModifiedBy],
[WM_Product_ID],
[Class],
[Period],
[BeginBal_InvQty],
[BeginBal_BaseQty],
[OpenBal_InvQty],
[OpenBal_BaseQty],
[Receive_InvQty],
[Receive_BaseQty],
[Issue_InvQty],
[Issue_BaseQty],
[Return_InvQty],
[Return_BaseQty],
[Sale_InvQty],
[Sale_BaseQty],
[Adj_In_InvQty],
[Adj_In_BaseQty],
[Adj_Out_InvQty],
[Adj_Out_BaseQty]
)
VALUES
(
@ps_REF_StockBalance_ID,
@pt_AD_Entity_ID,
@pt_AD_Company_ID,
@p_VName,
@pt_WM_Warehouse_ID,
current_timestamp,
@pk_AD_User_ID,
current_timestamp,
@pk_AD_User_ID,
@pt_WM_Product_ID,
@pt_Class,
252,
ISNULL(@ps_BeginBal_InvQty,0),
ISNULL(@ps_BeginBal_BaseQty,0),
ISNULL(@ps_OpenBal_InvQty,0),
ISNULL(@ps_OpenBal_BaseQty,0),
ISNULL(@ps_Receive_InvQty,0),
ISNULL(@ps_Receive_BaseQty,0),
ISNULL(@ps_Issue_InvQty,0),
ISNULL(@ps_Issue_BaseQty,0),
ISNULL(@ps_Return_InvQty,0),
ISNULL(@ps_Return_BaseQty,0),
ISNULL(@ps_Sale_InvQty,0),
ISNULL(@ps_Sale_BaseQty,0),
ISNULL(@ps_Adj_In_InvQty,0),
ISNULL(@ps_Adj_In_BaseQty,0),
ISNULL(@ps_Adj_Out_InvQty,0),
ISNULL(@ps_Adj_Out_BaseQty,0)
)
END
ELSE
BEGIN
SELECT @ps_BeginBal_InvQty=ISNULL(count(*),0), @ps_BeginBal_BaseQty=ISNULL(sum(BaseQty),0)
FROM WM_TRANSACTION
WHERE[AD_Entity_ID] = @pt_AD_Entity_ID
AND[AD_Company_ID] = @pt_AD_Company_ID
AND[WM_Warehouse_ID] = @pt_WM_Warehouse_ID
AND[WM_Product_ID] = @pt_WM_Product_ID
AND[Class]= @pt_Class
AND [TransType] in (317,314) -- I+
AND[TransactionNo] BETWEEN @p_First_Trans_ID AND @p_Last_Trans_ID
AND[TransactionDate] = @p_VName
SELECT @ps_WM_StockBalance_ID = [WM_StockBalance_ID]
FROM WM_StockBalance
WHERE[AD_Entity_ID] = @pt_AD_Entity_ID
AND[AD_Company_ID] = @pt_AD_Company_ID
AND[WM_Warehouse_ID] = @pt_WM_Warehouse_ID
AND[WM_Product_ID] = @pt_WM_Product_ID
AND[Class]= @pt_Class
UPDATE WM_StockBalance
SET[BeginBal_InvQty]= [BeginBal_InvQty] + @ps_BeginBal_InvQty,
[BeginBal_BaseQty]= [BeginBal_BaseQty]+ @ps_BeginBal_BaseQty ,
[OpenBal_InvQty]= [OpenBal_InvQty] + @ps_BeginBal_InvQty,
[OpenBal_BaseQty]= [OpenBal_BaseQty] + @ps_BeginBal_BaseQty
WHERE [WM_StockBalance_ID] = @ps_WM_StockBalance_ID
END
FETCH NEXT FROM DIS_WM_TRANSACTION_Cursor
INTO
@pt_AD_Entity_ID,
@pt_AD_Company_ID,
@pt_WM_Warehouse_ID,
@pt_WM_Product_ID,
@pt_Class
END
CLOSE DIS_WM_TRANSACTION_Cursor
DEALLOCATE DIS_WM_TRANSACTION_Cursor
-- UPDATE TRANHIST TO POSTED
UPDATE [dbo].[WM_TransHist]
SET
[IsPosted] = 1,
[ModifiedDate] = CURRENT_TIMESTAMP,
[ModifiedBy] = @pk_AD_User_ID
WHERE [WM_TransHist_ID] = @pk_WM_TransHist_ID
FETCH NEXT FROM WM_TransHist_Cursor
INTO @pk_WM_TransHist_ID
END
CLOSE WM_TransHist_Cursor
DEALLOCATE WM_TransHist_Cursor
END
July 31, 2008 at 7:25 am
Hi,
you design looks a little off, numeric (10,0) instead of int/big int for ID columns are you sure?
ref: http://support.microsoft.com/kb/q198625/ 😉
Hiding under a desk from SSIS Implemenation Work :crazy:
July 31, 2008 at 8:04 am
what is your recommendation for auto-generated numeric ID? will this cause the wrond returned value?
July 31, 2008 at 8:14 am
I would use an integer value based on the expected scale of the data.
programming one rule to follow is;-
never compare floating point numbers by equality as this cannot be guarenteed.
(Due to precision issues of hardware).
--Shaun Tzu's Art of Programming 😀
You have multiple processors and multiple cores doing floating point math.:pinch:
Hiding under a desk from SSIS Implemenation Work :crazy:
August 1, 2008 at 3:11 am
Multiple processor bugs are nothing new to SQL Server, e.g. had them in 7 sp3 and 2000 sp3.
If you think you have a Parallelism problem using MAXDOP 1 option on your queries (use BOL for full syntax if unsure).
This will tell you if it's your design or SQL that is causing the problem.
HTH's
August 4, 2008 at 1:23 am
I will double check on the decimal issue in if statement expression. What is the best practise for comparing date? Any idea?
August 4, 2008 at 1:33 am
Besides, What is the best practise for comparing date? I would like to know whether MAXDOP 1 option will cause process timeout.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply