January 31, 2012 at 2:05 pm
I have a stored procedure that queries a view. I have observed lately (only on occasion) that the same parameters will produce different result sets.
The underlying data are historical orders tables, and I have been limiting my queries to a three week period of 2011. At first I suspected some odd behavior in SSRS, but running the queries in SSMS gave similar results.
There appear to be about 6 rows (out of ~3500) that are the culprits. They are characterized by sharing a particular value in one field. This field is not used in the sproc, or in the view in any way, so it may be a red herring.
The stored procedure looks like:
select a.*
FROM vwPortalOrdersWithDetails_NotShipped a
INNER JOIN dbo.fnT_SplitVarchar(@Brand,',','0') aa ON aa.Item = REPLACE(a.[Brand],'''','')
INNER JOIN dbo.fnT_SplitVarchar(@Package,',','0') ab ON ab.Item = a.Package
INNER JOIN dbo.fnT_SplitVarchar(@Company,',','0') ac ON ac.Item = a.Custnmbr
INNER JOIN dbo.fnT_SplitVarchar(@Status,',','0') ad ON a.Status = ad.Item
--uncommented the next line
INNER JOIN dbo.fnT_SplitVarchar(@ShipmentStatus,',','0') ae ON a.ShipmentStatusDetail = ae.Item
WHERE
(@dtBegin IS NULL OR a.Scheduleddate BETWEEN @dtBegin AND @dtEnd)
The function fnT_SplitVarchar is a table valued function that splits a comma delimited string and returns a table. The input parameter for that function is VARCHAR(8000), and the longest parameter is ~5000 characters.
The view 'vwPortalOrdersWithDetails_NotShipped' queries reporting tables.
Any advice on how to troubleshoot this would be very much appreciated. thx. D Lewis
January 31, 2012 at 2:31 pm
You didn't really give anybody much to go on here. You select from a view and join to a function multiple times. My guess is whatever is causing this is in your function. Without ddl for the tables and the function and data that will recreate it I don't think there is much anybody can do to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2012 at 2:40 pm
At least the function definition would be needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2012 at 2:58 pm
Yes, I really didn't know where to begin.
I don't know if supplying data will really help, because this behavior is not predictable.
But, I can post some if it is requested.
I cannot see how the function would affect this, as I was calling the sproc with the same set of parameters.
Here is the function definition:
ALTER FUNCTION [dbo].[fnT_SplitVarchar]
(
@STR VARCHAR(8000)
, @delimiter CHAR(1)
, @allowmultiple BIT
)
RETURNS @myTable table (Item varchar(500))
AS
BEGIN
SET @STR=REPLACE(@str,'''','')
DECLARE @frommarker INT,
@tomarker INT,
@strlen INT,
@item VARCHAR(500)
SELECT @frommarker=1, @tomarker=1
WHILE (@tomarker > 0)
BEGIN
SET @tomarker = CHARINDEX (@delimiter, @STR, @frommarker)
IF @tomarker > 0
SET @strlen = @tomarker - @frommarker
ELSE
SET @strlen = LEN(@str) - @frommarker + 1
SET @item = ltrim(rtrim(SUBSTRING (@str, @frommarker, @strlen)))
IF @allowmultiple=1
INSERT INTO @myTable VALUES(@item)
ELSE
IF NOT EXISTS(SELECT NULL FROM @myTable WHERE Item = @item)
INSERT INTO @myTable VALUES(@item)
SET @frommarker = @tomarker + 1
END
RETURN
END
Here is the view definition:
SELECT DISTINCT o.[order_id] AS ORDERID
,o.[dist_customer_number] AS CUSTNMBR
,o.[dist_name] AS Company
,o.[purchase_order_number] AS PO
,o.[requested_ship_date] AS Shipdate
,'ScheduledDate'=CASE
WHEN o.shipment_Scheduled_date IS NULL THEN o.Requested_ship_date
ELSE o.shipment_Scheduled_date END
,o.[created_on_date] AS Orderdate
,CASE LEN(o.[carrier_name]) WHEN 0 THEN 'N/A' ELSE o.[carrier_name] END AS Carrier
, s.status_text AS [Status]
,od.[product_name] AS Brand
,od.[product_id] AS PRODUCTID
,od.[package_type_id] AS PACKAGEID
,od.[package_size] AS Package
,od.[quantity] AS quantity
,o.[order_note] AS DetailNotes
,o.pallets AS Pallets
,o.[weight] AS [SumWeight]
,o.[weight] AS [Weight]
,o.[dist_address_state] AS Stateshort
,CASE o.[dist_customer_number] WHEN '101CRGEN000' THEN 'Region 50' ELSE dd.Regionshort END AS Regionshort
,od.[product_group]
,od.[package_class]
,'No Shipment' AS ShipmentStatus
,od.product_brand AS product_brand
,CASE o.shipment_Status_id
WHEN 1 THEN 'New'
WHEN 2 THEN 'Scheduled'
WHEN 3 THEN 'Confirmed'
WHEN 4 THEN 'Pending'
WHEN 5 THEN 'Canceled'
WHEN 6 THEN 'Shipped'
ELSE 'Unknown'
END AS ShipmentStatusDetail
,ISNULL(o.shipment_Status_id,0) AS Status_id
,ISNULL(o.Shipment_note,'') AS ShipmentComments--was shipment.shipment_note
,od.pallet_units
,o.order_gp_status
,o.order_gp_status_id
,CASE WHEN od.product_item_sku IS NULL THEN od.[product_name] ELSE od.product_item_sku END AS product_item_sku
,ISNULL(od.label_sku,'N/A') AS label_sku
,od.order_detail_id
,dd.country
,ISNULL(o.shipment_sequence,0) as shipment_sequence
,o.shipment_order_count
FROM [snbCustomerPortal].[dbo].[rpt_order] o
JOIN [snbCustomerPortal].[dbo].[rpt_order_detail] od ON o.order_id=od.order_id
LEFT OUTER JOIN dbo.order_status AS s ON s.status_id = o.status_id
LEFT OUTER JOIN Depmanager.dbo.dimDistributors dd
ON o.[dist_customer_number] = dd.custnmbr
WHERE o.Order_id NOT IN
(SELECT order_id FROM dbo.Shipment_orders so
JOIN dbo.Shipment s ON so.Shipment_id=s.Shipment_id
)
UNION ALL
SELECT DISTINCT o.[order_id] AS ORDERID
,o.[dist_customer_number] AS CUSTNMBR
,o.[dist_name] AS Company
,o.[purchase_order_number] AS PO
,o.[requested_ship_date] AS Shipdate
,'ScheduledDate'=CASE
WHEN o.shipment_Scheduled_date IS NULL THEN o.Requested_ship_date
ELSE o.shipment_Scheduled_date END
,o.[created_on_date] AS Orderdate
,CASE LEN(o.[carrier_name]) WHEN 0 THEN 'N/A' ELSE o.[carrier_name] END AS Carrier
, o.order_status AS [Status]
,od.[product_name] AS Brand
,od.[product_id] AS PRODUCTID
,od.[package_type_id] AS PACKAGEID
,od.[package_size] AS Package
,od.[quantity] AS quantity
,o.[order_note] AS DetailNotes
,o.pallets AS Pallets
,o.[weight] AS [SumWeight]
,o.[weight] AS [Weight]
,o.[dist_address_state] AS Stateshort
,CASE o.[dist_customer_number] WHEN '101CRGEN000' THEN 'Region 50' ELSE dd.Regionshort END AS Regionshort
,od.[product_group]
,od.[package_class]
,'Shipment' AS ShipmentStatus
,od.product_brand AS product_brand
,CASE o.shipment_Status_id
WHEN 1 THEN 'New'
WHEN 2 THEN 'Scheduled'
WHEN 3 THEN 'Confirmed'
WHEN 4 THEN 'Pending'
WHEN 5 THEN 'Canceled'
WHEN 6 THEN 'Shipped'
ELSE 'Unknown'
END AS ShipmentStatusDetail
,ISNULL(o.shipment_Status_id,0) AS Status_id
,ISNULL(o.shipment_note,'') AS ShipmentComments--was shipment.shipment_note
,od.pallet_units
,o.order_gp_status
,o.order_gp_status_id
,CASE WHEN od.product_item_sku IS NULL THEN od.[product_name] ELSE od.product_item_sku END AS product_item_sku
,ISNULL(od.label_sku,'N/A') AS label_sku
,od.order_detail_id
,dd.country
,ISNULL(o.shipment_sequence,0) as shipment_sequence
,o.shipment_order_count
FROM [snbCustomerPortal].[dbo].[rpt_order] o
JOIN [snbCustomerPortal].[dbo].[rpt_order_detail] od ON o.order_id=od.order_id
LEFT OUTER JOIN dbo.order_status AS s ON s.status_id = o.status_id
LEFT OUTER JOIN Depmanager.dbo.dimDistributors dd
ON o.[dist_customer_number] = dd.custnmbr
WHERE o.shipment_Status_id >0
And here are the ddl for the tables in the view:
CREATE TABLE [dbo].[rpt_order](
[order_id] [int] NOT NULL,
[created_by_userid] [int] NOT NULL,
[created_on_date] [smalldatetime] NOT NULL,
[updated_by_userid] [int] NOT NULL,
[updated_on_date] [datetime] NOT NULL,
[purchase_order_number] [varchar](50) NULL,
[requested_ship_date] [smalldatetime] NULL,
[carrier_name] [varchar](150) NULL,
[dist_group_id] [bigint] NOT NULL,
[dist_customer_number] [varchar](50) NULL,
[dist_name] [varchar](100) NOT NULL,
[dist_address_street] [varchar](100) NULL,
[dist_address_city] [varchar](50) NULL,
[dist_address_state] [varchar](2) NULL,
[dist_address_zip] [varchar](10) NULL,
[dist_phone] [varchar](20) NULL,
[dist_fax] [varchar](20) NULL,
[name_first] [varchar](50) NULL,
[name_last] [varchar](50) NULL,
[status_id] [int] NOT NULL,
[order_status_id] [int] NULL,
[order_status] [varchar](50) NULL,
[order_gp_status_id] [int] NULL,
[order_gp_status] [varchar](50) NULL,
[shipment_status_id] [int] NULL,
[shipment_status] [varchar](50) NULL,
[shipment_scheduled_date] [smalldatetime] NULL,
[shipment_scheduled_time] [smalldatetime] NULL,
[shipment_scheduled_datetime] [smalldatetime] NULL,
[shipment_note] [varchar](500) NULL,
[shipment_status_note] [varchar](500) NULL,
[weight] [decimal](9, 2) NULL,
[pallets] [decimal](9, 2) NULL,
[flags] [int] NOT NULL,
[status_note] [varchar](500) NULL,
[order_note] [varchar](500) NULL,
[group_id] [int] NULL,
[comment] [varchar](500) NULL,
[shipment_sequence] [int] NULL,
[shipment_order_count] [int] NULL,
[shipment_id] [int] NULL,
CONSTRAINT [PK_rpt_order] PRIMARY KEY CLUSTERED
(
[order_id] ASC
CREATE TABLE [dbo].[rpt_order_detail](
[order_detail_id] [bigint] NOT NULL,
[order_id] [int] NOT NULL,
[line_num] [int] NOT NULL,
[product_id] [int] NOT NULL,
[package_type_id] [int] NOT NULL,
[quantity] [int] NOT NULL,
[product_name] [varchar](100) NOT NULL,
[product_brand] [varchar](50) NULL,
[ship_date] [smalldatetime] NULL,
[end_date] [smalldatetime] NULL,
[product_group] [varchar](50) NOT NULL,
[package_size] [varchar](255) NOT NULL,
[package_weight] [int] NOT NULL,
[pallet_units] [int] NOT NULL,
[package_class] [varchar](20) NOT NULL,
[product_item_sku] [varchar](31) NULL,
[label_sku] [varchar](3) NULL,
[varchar](500) NULL,
CONSTRAINT [PK_rpt_order_detail] PRIMARY KEY CLUSTERED
(
[order_detail_id] ASC
) ON [PRIMARY]
CREATE TABLE [dbo].[order_status](
[status_id] [int] IDENTITY(1,1) NOT NULL,
[status_text] [varchar](100) NOT NULL,
[status_desc] [varchar](500) NULL,
[image_path_small] [varchar](150) NULL,
[image_path_large] [varchar](150) NULL,
[status_class] [varchar](50) NULL,
CONSTRAINT [PK_order_status] PRIMARY KEY CLUSTERED
(
[status_id] ASC
Thanks.
January 31, 2012 at 3:40 pm
Thanks for the additional information above David. So, some clarifications if you'll be so kind, and hopefully some ideas on what we can look for in regards to troubleshooting.
DavidL (1/31/2012)
I have a stored procedure that queries a view. I have observed lately (only on occasion) that the same parameters will produce different result sets.The underlying data are historical orders tables, and I have been limiting my queries to a three week period of 2011. At first I suspected some odd behavior in SSRS, but running the queries in SSMS gave similar results.
I'd like to confirm that the code below is the only thing in the proc besides the declaration header. In particular, do you have a particular SET TRANSACTION ISOLATION LEVEL or anything else set against this?
select a.*
FROM vwPortalOrdersWithDetails_NotShipped a
INNER JOIN dbo.fnT_SplitVarchar(@Brand,',','0') aa ON aa.Item = REPLACE(a.[Brand],'''','')
INNER JOIN dbo.fnT_SplitVarchar(@Package,',','0') ab ON ab.Item = a.Package
INNER JOIN dbo.fnT_SplitVarchar(@Company,',','0') ac ON ac.Item = a.Custnmbr
INNER JOIN dbo.fnT_SplitVarchar(@Status,',','0') ad ON a.Status = ad.Item
--uncommented the next line
INNER JOIN dbo.fnT_SplitVarchar(@ShipmentStatus,',','0') ae ON a.ShipmentStatusDetail = ae.Item
WHERE
(@dtBegin IS NULL OR a.Scheduleddate BETWEEN @dtBegin AND @dtEnd)
The function fnT_SplitVarchar is a table valued function that splits a comma delimited string and returns a table. The input parameter for that function is VARCHAR(8000), and the longest parameter is ~5000 characters.
Looks like a simple splitter, but with one caveat. You've got an 'allow multiples' or not choice here. It's set to 0 in your proc. Do your parameters ever come with multiple entries here? Are they ever in a different order during your testing?
When you've tested this with the same parameters, does the exact same run in the same window return different results when you hit F5, or must you do anything different? Even opening another connection window?
Is there any activity in these 'reporting tables', or are they static warehouse tables?
Also, those red herring fields you mentioned, they might be important. Notice your view has a select distinct. If you're finding these rows because of a count of records, their differences might be the cause of getting the extra few rows. Not knowing the exact cause of their difference or how they're working, though...
With answers to that we can probably help you drill in deeper. An example of the problem rows vs. correct rows might help too, but my first guess is you have hidden activity on your system hiding under the hood where the DISTINCT component in the view is reacting with a different number of rows to the output, but because you're not showing the differing field(s), you're not seeing the underlying cause.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2012 at 4:03 pm
Craig (and others reading):
Here is the stored procedure text. It is very slightly different what I first posted, but not in essence. There are no SET... statements of any kind.
ALTER PROCEDURE [dbo].[usp_PortalOrders]
@brand-2 VARCHAR(8000)=NULL
,@Package VARCHAR(8000)=NULL
,@Company VARCHAR(8000)=NULL
,@dtBegin DATETIME=NULL
,@dtEnd DATETIME=NULL
,@Sort VARCHAR(20)=null
,@direction VARCHAR(10)='ASC'
,@status VARCHAR(200)=NULL
,@shipmentstatus VARCHAR(200)=NULL
,@UserName VARCHAR(50)=NULL
,@ReportName VARCHAR(50)=NULL
AS
INSERT INTO [DepManager].[dbo].[tblParamTracker]
([Sproc]
,[ParamName]
,[ParamValue]
,[ReportName]
,[UserName])
(SELECT 'portal.usp_PortalOrders','@shipmentstatus',@shipmentstatus,@ReportName,@UserName
UNION ALL
SELECT 'portal.usp_PortalOrders','@status',@status,@ReportName,@UserName
UNION ALL
SELECT 'portal.usp_PortalOrders','@Brand',@Brand,@ReportName,@UserName
UNION ALL
SELECT 'portal.usp_PortalOrders','@Package',@Package,@ReportName,@UserName
UNION ALL
SELECT 'portal.usp_PortalOrders','@Company',@Company,@ReportName,@UserName
UNION ALL
SELECT 'portal.usp_PortalOrders','@dtBegin',CONVERT(VARCHAR(100),@dtBegin),@ReportName,@UserName
UNION ALL
SELECT 'portal.usp_PortalOrders','@dtEnd',CONVERT(VARCHAR(100),@dtEnd),@ReportName,@UserName
)
SET @sort=@sort + ' ' + @direction
SELECT DISTINCT a.[ORDERID]
,a.[CUSTNMBR]
,a.[Company]
,a.[PO]
,a.[Shipdate]
,a.[Scheduleddate]
,a.[Orderdate]
,a.[Carrier]
,a.[Status]
,REPLACE(a.[Brand],'''','') AS Brand
,a.[PRODUCTID]
,a.[PACKAGEID]
,a.[Package]
,CONVERT(INT,a.quantity) AS Quantity
,ISNULL(a.DetailNotes,'None') AS DetailNotes
,a.RegionShort
,a.StateShort
,a.Pallets
,a.SumWeight AS Weight
,a.product_group
,a.package_Class
,a.shipmentStatus
,a.product_brand
,a.ShipmentStatusDetail
,a.status_id
,a.ShipmentComments
,DENSE_RANK() OVER ( ORDER BY a.[Packageid]) AS sorting
,CASE WHEN a.[PACKAGEID]<8 THEN a.[Packageid] ELSE a.[packageid]-1 END AS pk
,a.order_detail_id
,a.shipment_sequence
,a.shipment_order_count
,a.order_gp_status
FROM vwPortalOrdersWithDetails_NotShipped a
INNER JOIN dbo.fnT_SplitVarchar(@Brand,',','0') aa ON aa.Item = REPLACE(a.[Brand],'''','')
INNER JOIN dbo.fnT_SplitVarchar(@Package,',','0') ab ON ab.Item = a.Package
INNER JOIN dbo.fnT_SplitVarchar(@Company,',','0') ac ON ac.Item = a.Custnmbr
INNER JOIN dbo.fnT_SplitVarchar(@Status,',','0') ad ON a.Status = ad.Item
INNER JOIN dbo.fnT_SplitVarchar(@ShipmentStatus,',','0') ae ON a.ShipmentStatusDetail = ae.Item
WHERE
(@dtBegin IS NULL OR a.Scheduleddate BETWEEN @dtBegin AND @dtEnd)
There are no duplicates in the parameters, and while I don't know for sure I suspect that they are delivered in the same order each time.
As regards
When you've tested this with the same parameters, does the exact same run in the same window return different results when you hit F5, or must you do anything different? Even opening another connection window?
I cannot recall exactly, but I think different results occurred in both scenarios. Sorry I can't be definite on that... I have so many bleeding windows open by now...
The reporting tables are static warehouse tables. There is no possibility that the time period I am querying will have changes (these are records from 2011).
The 'red herring' fields are order_gp_status and order_gp_status_id. In most rows those fields are empty, in some they have a 'import successful' and the handful of rows that sometimes are missing have the value 'import failed'. There are no other rows in the record set that have that value in those fields. It is a simple varchar column, and is referenced nowhere in the view, sproc, or functions... For that reason I feel they are likely a red herring.
Also, in case it is unclear, the greater rowcount is the correct result. I consider that when the sproc misses the rows (that happen to have) 'import failed' in column order_gp_status it has 'erred'.
With answers to that we can probably help you drill in deeper. An example of the problem rows vs. correct rows might help too, but my first guess is you have hidden activity on your system hiding under the hood where the DISTINCT component in the view is reacting with a different number of rows to the output, but because you're not showing the differing field(s), you're not seeing the underlying cause.
Unfortunately I don't follow that... Thanks. D Lewis
January 31, 2012 at 4:31 pm
DavidL (1/31/2012)
Craig (and others reading):Here is the stored procedure text. It is very slightly different what I first posted, but not in essence. There are no SET... statements of any kind.
Thanks, that helps for future reference. I don't see anything in there directly that will affect results, taken alongside the following:
The reporting tables are static warehouse tables. There is no possibility that the time period I am querying will have changes (these are records from 2011).
The 'red herring' fields are order_gp_status and order_gp_status_id. In most rows those fields are empty, in some they have a 'import successful' and the handful of rows that sometimes are missing have the value 'import failed'. There are no other rows in the record set that have that value in those fields. It is a simple varchar column, and is referenced nowhere in the view, sproc, or functions...
Agreed my concern about the DISTINCT is now unwarranted. Hadn't realized it was skipped in the view as well.
Also, in case it is unclear, the greater rowcount is the correct result. I consider that when the sproc misses the rows (that happen to have) 'import failed' in column order_gp_status it has 'erred'.
That's interesting. Not sure if it's important, but interesting. Safe to assume no other 'import failed' items are falling out of the proc?
Unfortunately I don't follow that... Thanks. D Lewis
Won't matter if your data is static and you're sure of it.
Which means another possibility. Corruption. Run DBCC CHECKDB on this database. Do NOT use any of the repair options, just run it and see what errors (if any) it returns. Either backup/restore it to another server or I'd recommend doing it during a maintenance window. It takes a DB snapshot to lower concurrency issues but it can still be intensive. I don't know how large the DB is to give you an estimate of time.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2012 at 7:09 am
I put the UDF through some simple unit-testing and it does appear to be deterministic, so I don't think that's the issue. (Had to confirm that, of course.)
In the original post, you mentioned 6 rows that "appear to be the culprits" and mention some data differences in them. Can you add to that? Samples of rows that seem to be working correctly vs the rows that appear to be potentially problematic might help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2012 at 9:39 am
Attached is a spreadsheet with two rows.
The yellow highlighted row is an example of a row that is missing. In the range of records I am looking at there are 6 rows that only occasionally go missing, and the only commonality between those 6 rows is the column 'order_gp_status' which has the value 'gp import failed'.
One of the parameters for the stored procedure is 'Customer', and it does not happen that entire customers drop out of the incomplete record set. In other words, those 6 missing rows are for 3 different customers, but there are other rows for those customers that do successfully come through. From what I can see, the only unique feature of the missing rows is 'gp import failed'.
February 1, 2012 at 9:40 am
Also, I forgot to mention that DBCC CHECKDB is run nightly and has not shown any issues. D Lewis
February 1, 2012 at 10:56 am
Not enough for me to see a real pattern to it.
I think I'd need access to the actual server and database to really determine what's going on. I'd test concurrency, locking issues, check default isolation levels, connection isolation levels, run traces to see what's going on when it reads correctly vs incorrectly, and possibly other tests as well.
I'm assuming this isn't just a matter of rows being in a different order than expected. There are less/more rows different times, right?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2012 at 11:30 am
I'm assuming this isn't just a matter of rows being in a different order than expected. There are less/more rows different times, right?
Correct. The example I sent with two rows showed one that shows up 'all' the time, and an example of one that showed up 'most' of the time.
We don't have a dba, so I am stuck trying to figure this out.
I set up a trace to try and see what happens when the stored procedure is run -- I thought that if I could see how it unpacked the query, functions, view, etc. I might gain some insights. However I couldn't find any appropriate events that captured that. If you can recommend some I would appreciate it. TSQL statement compile etc. didn't seem to show anything other than 'EXEC usp_xyz @a='b', @b-2='c' etc. etc.
THanks for you help. D Lewis
February 1, 2012 at 1:57 pm
I doubt that this is related but it sounds similar enough that I figure I may as well share. We had an issue where the statistics were out of date for a table that did not change a whole lot and that in certain circumstances it would cause literally different data (that did not even exist in our system) to appears in the result set. The fix ended up coming along with SQL 2008 SP1.
Unlikely that this resolves your problem, but since I did not specifically see the version of SQL you were running under I figured it was worth throwing out there.
February 1, 2012 at 2:38 pm
I doubt that this is related but it sounds similar enough that I figure I may as well share. We had an issue where the statistics were out of date for a table that did not change a whole lot and that in certain circumstances it would cause literally different data (that did not even exist in our system) to appears in the result set. The fix ended up coming along with SQL 2008 SP1.
Knowledge Base Article.
Unlikely that this resolves your problem, but since I did not specifically see the version of SQL you were running under I figured it was worth throwing out there.
Very interesting, and I thank you for that. I will inquire to see what service pack we are running. It is SQL2008.
February 1, 2012 at 2:43 pm
Actually I got it wrong, it is Microsoft SQL Server 2005 - 9.00.5292.00 (X64) Apr 13 2011 15:43:31
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply