4

I have a stored procedure that when executed in SSMS returns different values than when the same SP is executed in code, even a very simple SP call and dump in Linqpad. We believe this started happening after migrating to a new server running SQL Server 2016 Standard.

The stored procedure uses 3 table variables, one of which is updated using a cursor (not best practice).

Debug Steps taken so far:

  1. The problem does not occur on dev servers restored from nightly backups

  2. I created an identical stored procedure on the same database. The problem did not follow, the new SP returned the same results in SSMS and LINQPAD.

  3. I executed sp_recompile on the stored procedure. This seemed to fix the issue, we then saw the same results in SSMS and LINQPAD. However, this was temporary. We recompiled on Friday, the problem returned today (Tuesday).

  4. Checking sys.dm_exec_procedure_stats, I do not see that the SP plan changed. I also checked the statement plan, it does not seem to have changed either.

Any ideas what I could check next?

Here is the code. First, I will say that this SP is not up to our standards. This SP can and will be re-written to eliminate the cursor, etc.

However, this is the first time in my career that I have ever seen stored procedure results be different in SSMS vs being called from other code. We upgraded to SQL 2016 about 2 weeks ago. This problem pop up shortly after the upgrade.

The column labeled "BoxX" in the output is where we are seeing the differences. This is the column that is updated in the cursor.

One of the purposes of this SP is to to display Box X of Y. (Box 1 of 2, 2 of 2, etc.)

In SSMS the values in BoxX will be 1,2,1,2,3, etc. In LINQPAD they are 1,1,1,1,1

SSMS Results: SSMS RESULTS

LINQPAD Results: enter image description here

CREATE Procedure [dbo].[SP1] as

Begin
  declare @tmpMCCustOrderNo varchar(10), @tmpCompareOrderno varchar(10), @tmpMCPackageID varchar(21), @tmpCurrentMC int
  declare @tmp1 TABlE(OrderNo varchar(10), MCTotCnt int)
  declare @tmp2 TABLE(Orderno varchar(10),PackageID varchar(21), MCBoxX int)

  --Build list of orders with packages in TABLE2
  Insert into @tmp1(OrderNo, MCTotCnt)
  Select    s.CustOrderNo, count(Distinct s.PackageID)
  From  DATABASE1..TABLE1 s
  Where s.CustOrderNo in (Select Distinct CustOrderNo from DATABASE1..TABLE1 where PackageID in (select PackageID from TABLE2))
      and IsNull(s.BoxType, '') <> ''
  Group     By s.CustOrderNo

  Insert into @tmp2(OrderNo,PackageID,MCBoxX)
      Select distinct s.CustOrderNo, s.PackageID, 0
  From DATABASE1..TABLE1 s, @tmp1 l
      Where s.CustOrderNo = l.OrderNo and IsNull(s.BoxType, '') <> ''
  Order By s.CustOrderNo,s.PackageID

  --Cursor to increment counter (@tempCurrentMC)
  DECLARE c_EX CURSOR FOR
  SELECT Orderno,PackageID FROM @tmp2
  OPEN c_EX
  FETCH NEXT FROM c_EX INTO @tmpMCCustOrderNo,@tmpMCPackageID
  SELECT @tmpCompareOrderNo = @tmpMCCustOrderNo
      SELECT @tmpCurrentMC = 0
      WHILE @@FETCH_STATUS = 0
      BEGIN
          IF (@tmpCompareOrderno <> @tmpMCCustOrderNo)
              BEGIN
                  SELECT @tmpCurrentMC = 1
                      SELECT @tmpCompareOrderno = @tmpMCCustOrderNo
              END
              ELSE
              BEGIN
                  SELECT @tmpCurrentMC = @tmpCurrentMC + 1
              END
          UPDATE @tmp2 SET MCBoxX = @tmpCurrentMC WHERE OrderNo = @tmpMCCustOrderNo and PackageID = @tmpMCPackageID
          FETCH NEXT FROM c_EX INTO @tmpMCCustOrderNO, @tmpMCPackageID
      END
      CLOSE c_EX
      DEALLOCATE c_EX

  declare  @tmp3 table(SalesID varchar(20), Shipper Varchar(50), PackageID Varchar(21), ShipToName Varchar(100), UCC128 Varchar(50), Priority int, status varchar(20), 
    LastUpdate datetime, shipdate datetime, pallets int, packages int, mcboxY int)

  --Gather other order information into another table variable
  Insert into @tmp3 (SalesID, Shipper, PackageID, ShipToName, UCC128, Priority, status, LastUpdate, shipdate, pallets, packages, mcboxY)
  Select Distinct i.ax_salesid as SalesID, i.ShipMethod,  m.PackageID, i.ShipToName, m.UCC128, m.Priority Priority, m.MCHQStatus Status, m.LastUpdate, i.ShipDate ShipDate,  
    COUNT(Distinct s.ucc128) Pallets, COUNT(Distinct S.packageid) Packages, T2.MCTotCnt MCBoxY  
  From DATABASE2..TABLE2 m  
    Inner Join DATABASE1..TABLE3 i  on LEFT (m.packageid, 6) = i.CustOrderNo  
    Inner Join DATABASE1..TABLE1 s on S.CustOrderNo = I.CustOrderNo  
    Left Join @tmp1 T2 on T2.OrderNo = S.CustOrderNo    
  Group by i.ax_salesid , i.ShipMethod, m.PackageID, i.ShipToName, m.UCC128, m.Priority, m.MCHQStatus, m.LastUpdate, i.ShipDate , T2.MCTotCnt 
  Order by m.Priority, i.AX_SalesID, m.PackageID 

  --Return Data
  Select MCR.SalesID, MCR.Shipper, MCR.PackageID, MCR.ShipToName, 
    MCR.UCC128, MCR.Priority, MCR.Status, MCR.LastUpdate, MCR.ShipDate, MCR.Pallets, MCR.Packages, MCM.MCBoxX as BoxX, MCR.mcboxY as OfY 
  From @tmp3 MCR
  Inner Join @tmp2 MCM on 
    MCM.PackageID = MCR.PackageID
  Order by MCR.Priority, MCR.SalesID, MCM.MCBoxX, MCR.mcboxY  


END 

And here is how we tested in LINQPAD

void Main()
{


    var testTable = new DataTable();
    SqlConnection con = new SqlConnection("Data Source=server1;Initial Catalog=Database1;Integrated Security=True");
    SqlCommand cmd = new SqlCommand("exec Database1..SP1", con);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    sda.Fill(testTable);
    testTable.Dump(); 

}
  • Different Values. All data is identical except for one column. That one column contains different data (box count per order). – Steve Feb 28 '17 at 18:05
  • Could it be that SET OPTIONS are different between LINQPAD and SSMS? - technet.microsoft.com/en-us/library/ms175088(v=sql.105).aspx – Scott Hodgin Feb 28 '17 at 20:27
  • Thanks for the suggestion, but ANSI_NULLS is set to ON for both connections. – Steve Feb 28 '17 at 23:03
  • Probably not the issue, but are the 2 connections running in the same schema? In the linqpad code you're calling database1..sp1 without specifying the schema – Greg Feb 28 '17 at 23:54
  • 2
    The cursor has no ORDER BY. That might be a reason for different results across executions (the cursor updates this column that shows differences). – ypercubeᵀᴹ Mar 1 '17 at 9:24
1

I agree with what @ypercube is saying about no ORDER BY on the cursor.

Your bullet items 1, 2, 3 all point to possible new execution plans.

This code is the suspicious part:

Insert into @tmp2(OrderNo,PackageID,MCBoxX)
      Select distinct s.CustOrderNo, s.PackageID, 0
  From DATABASE1..TABLE1 s, @tmp1 l
      Where s.CustOrderNo = l.OrderNo and IsNull(s.BoxType, '') <> ''
  Order By s.CustOrderNo,s.PackageID

Apparently, the author of this procedure assumed that a table variable could be loaded using an Order By and the cursor would magically pull the data from that table variable in that order. Check out No Seatbelt – Expecting Order without ORDER BY.

Modify the cursor to include an ORDER BY for Orderno,PackageID and let us know if that solves your problem.

  • Thank You, I skipped right over that and didn't notice the missing order by. I added it to my SP and it is now showing the expected results. I believe this is the answer, but I am still confused as to how I would see different results in SSMS vs C#. What could cause the results to be different in the different methods of calling the SP? – Steve Mar 1 '17 at 14:20
  • 1
    I really think that two different execution plans were generated, possibly due to different SET OPTIONS by the C# and SSMS. I don't really have a way to prove that since I don't have access to your system. I'd be curious as the to execution_count from sys.dm_exec_query_stats when running both the C# and the SSMS version. – Scott Hodgin Mar 1 '17 at 16:28
  • You may get different execution plans for various reasons. You may get results in different order, even from the same connection and the same plan, when you aren't using ORDER BY. It didn't happen only by accident. In a way, you are lucky that this happened and you noticed! – ypercubeᵀᴹ Mar 1 '17 at 23:23
  • Update: to close the loop on this, we added the order by and left the SP in production for a little over 24 hours. The issue never reappeared so I am confident this was the problem. Thank you all for your help. Today we also have rewritten the SP using CTEs instead of table variables and ROWNumber-Over instead of a cursor. Much cleaner. – Steve Mar 2 '17 at 22:58
3

It seems that the cursor's intension is to fill MCBoxX with ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY PackageID). But there is no ORDER BY in the cursor so the the rows of @tmp2 may be processed in a way that each row has different OrderNo than its previous, resulting in all row numbers filled with 1, due to the IF (@tmpCompareOrderno <> @tmpMCCustOrderNo) check.

As Scott Hogdin's answer suggests, you can add an explicit ORDER BY in the cursor.

You could also skip the cursor altogether and provide the row numbers during the insert (and have less and cleaner code):

  Insert into @tmp2
      (OrderNo, PackageID, MCBoxX)
  Select 
      s.CustOrderNo, s.PackageID, 
      RowNumber() Over (Partition By s.CustOrderNo
                        Order By s.PackageID)
  From DATABASE1..TABLE1 s, @tmp1 l
  Where s.CustOrderNo = l.OrderNo and IsNull(s.BoxType, '') <> ''
  Group By s.CustOrderNo, s.PackageID
  Order By s.CustOrderNo, s.PackageID ;
  • I can't be sure but the @tmp1 table seems unnecessary, too. – ypercubeᵀᴹ Mar 1 '17 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.