November 30, 2011 at 8:44 am
I have just encountered the weirdest thing I have ever seen in SQL Server, ever. on my local WS, with SQL 2008 R2 Dev edition, 10.50.1600.1.
In a data load process I loaded the staging tables and got an error regarding a key violation when copying from the Staging table to the Prod tables. When I began looking into it I looked at my stored proc, which is supposed to look for the 2 staging tables and truncate them if they exist, otherwise it should create them, with keys, indexes and constraints. This is standard process I use in many ETL processes.
In this case though, even though my procedure can tell the table is there, it will not truncate it. It does seem to delete some of the records, because the record count gets smaller after I run the procedure.
What is even weirder is that the Identity(1,1) column on this table doesn't change values. I have a 476 value in the first record selected from the table when I did Select top 1000 rows from it. After I ran the procedure, to truncate or create the table, the number of total records dropped but when I selected Top 1000 rows again the beginning value in the Identity column was still 476 although the data in the other columns changed.
I keep running this over and over, looking at the same thing happening over and over, until the number of records in the table got down to 1900 rows and would not drop any further even with multiple runs of the procedure.
About to pull my hair out, I restarted SQL Server and Agent. Now the stored procedure acts right and the tables cleared of all records the first time it was run.
I ran the process to populate the tables again then ran the stored proc again. It ran fine and cleared the tables as it was supposed to.
Also, I find that I no longer get a key violation from my package, even though I have made no changes to the package or supporting procedures. The only thing I did was restart SQL Server.
Of course, I wonder what was going on, and if anyone else has seen this before. This behavior causes me to wonder if it could happen someplace else, like on a Prod server, where it will be a much bigger deal.
Here's the proc:
ALTER Procedure [dbo].[uspPALoadClearSTGTables]
As
Begin
/* Staging Tables for PA load.
This procedure truncates the tables if they exist and creates them if they do not.
Version 1.0, 11-11-2011
*/
If Exists(Select 1 From Sys.objects where TYPE = 'U' And name = 'PAHeaderSTG')
Truncate Table PAHeaderSTG
Else
Begin
Create Table PAHeaderSTG(
HeaderID Int Identity(1,1) Not Null,
PAKey Int Not Null,
JobKey Int Not Null,
InsertDate DateTime,
RawData VarChar(1000),
[ICN] AS (CONVERT([varchar](13),substring([RawData],(1),(13)),(0))),
[PrescriberQualifier] AS (CONVERT([varchar](2),substring([RawData],(14),(2)),(0))),
[PrescriberID] AS (CONVERT([varchar](15),substring([RawData],(16),(15)),(0))),
[QuanLimitRuleID] AS (CONVERT([varchar](9),substring([RawData],(31),(9)),(0))),
[QuanApproved] AS (CONVERT([varchar](15),substring([RawData],(40),(15)),(0))),
[QuanBeginDate] AS (CONVERT([varchar](8),substring([RawData],(55),(8)),(0))),
[QuanEndDate] AS (CONVERT([varchar](8),substring([RawData],(63),(8)),(0))),
[DaysLimitRuleID] AS (CONVERT([varchar](9),substring([RawData],(71),(9)),(0))),
[DaysApproved] AS (CONVERT([varchar](3),substring([RawData],(80),(3)),(0))),
[DaysBeginDate] AS (CONVERT([varchar](8),substring([RawData],(83),(8)),(0))),
[DaysEndDate] AS (CONVERT([varchar](8),substring([RawData],(91),(8)),(0))),
[AgeLimitRuleID] AS (CONVERT([varchar](9),substring([RawData],(99),(9)),(0))),
[MinAge] AS (CONVERT([varchar](3),substring([RawData],(108),(3)),(0))),
[MaxAge] AS (CONVERT([varchar](3),substring([RawData],(111),(3)),(0))),
[AgeBegineDate] AS (CONVERT([varchar](8),substring([RawData],(114),(8)),(0))),
[AgeEndDate] AS (CONVERT([varchar](8),substring([RawData],(122),(8)),(0))),
[OverflowInd] AS (CONVERT([varchar](1),substring([RawData],(130),(1)),(0)))
)
Alter Table [PAHeaderSTG]
Add Constraint [PAHeaderSTGInsertDefault] DEFAULT GetDate() FOR InsertDate
Create Clustered Index idxPAHeaderPAKey On PAHeaderSTG
(
PAKey Asc
) On [Primary]
Create NonClustered Index idxPAHeaderICN On PAHeaderSTG
(
ICN Asc
) On [Primary]
Create NonClustered Index idxPAHeaderJobKey On PAHeaderSTG
(
JobKey Asc
) On [Primary]
End
If Exists(Select 1 From Sys.objects where TYPE = 'U' And name = 'PAOverridesSTG')
Truncate Table PAOverridesSTG
Else
Begin
Create Table PAOverridesSTG(
OverrideID Int Identity(1,1) Not Null,
PAKey Int Not Null,
JobKey Int Not Null,
InsertDate DateTime,
RawData VarChar(130),
RecNum Int Not Null,
[NcpdpCode] AS (CONVERT([varchar](4),substring([RawData],(1),(4)),(0))),
[MmaCode] AS (CONVERT([varchar](9),substring([RawData],(5),(9)),(0))),
[BeginDate] AS (CONVERT([varchar](8),substring([RawData],(14),(8)),(0))),
[EndDate] AS (CONVERT([varchar](8),substring([RawData],(22),(8)),(0)))
)
Alter Table [PAOverridesSTG]
Add Constraint [PAOverridesSTGInsertDefault] DEFAULT GetDate() FOR InsertDate
Create Clustered Index idxPAOverridesPAKey On PAOverridesSTG
(
PAKey Asc,
RecNum Asc
) On [Primary]
Create NonClustered Index idxPAOverridesJobKey On PAOverridesSTG
(
JobKey Asc
) On [Primary]
End
End
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
November 30, 2011 at 9:05 am
How are you executing this stored procedure? i.e. a job or manually? What happens if you run the commands individually in SSMS outside of the sp? It sounds like a permissions issue...
Jared
Jared
CE - Microsoft
November 30, 2011 at 9:07 am
Also, I believe doing SELECT TOP 1000 will not necessarily give you the first identity value. You have to have an ORDER BY to guarantee your result set.
Jared
Jared
CE - Microsoft
December 1, 2011 at 7:05 am
The first time around the proc was executed from an SSIS package, but when I started researching it I was executing from a query window.
Normally, if you select Top X from a table, and run it over and over, you get the same values in the same order, whatever that order by be, again and again, regardless of the order they are returned since SQL doesn't purposely randomize the results if you do not request a specific sort order. I understand that SQL server may not return results in the order I want, but it does return the results in the same order on repeated runs of the same query. In this case I did not care about the order, since the table was supposed to be empty.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
December 1, 2011 at 8:00 am
Well... This is very strange indeed, but probably has some sort of explanation. Is it possible that some other query or sp was deleting rows or putting a lock on the table that would prevent you from truncating it? Of course, I would expect the sp to wait for that lock when run. Is it possible that the machine running dev edition was extremely bogged down with something else and could not keep up? I'm just shooting in the dark, but there HAS to be some sort of explanation. Keep us posted if it occurs again 🙂
Jared
Jared
CE - Microsoft
December 1, 2011 at 4:13 pm
Did the TRUNCATE TABLE actually work? There are, of course, certain restrictions to using TRUNCATE.
Did something else in the batch fail and cause a rollback? Unlike Oracle, a TRUNCATE in SQL Server can be rolled back.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 5, 2011 at 7:27 am
That's the curious thing. The truncate table seems to have selectively deleted records, as if I had issued a Delete statement with criteria. I have been using Truncate since the 90s have have never seen behavior like that.
Now I wish I hadn't restarted SQL, so I could investigate more, but I had to get a project done the other day, so I restarted to fix it and posted about it after I got my project going.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
December 5, 2011 at 7:45 am
Interesting. TRUNCATE doesn't fire triggers, so I wouldn't think a trigger would be causing the partial DELETEs.
Is this the actual table that is being TRUNCATEd, or somehow is it a view that is actually being TRUNCATEd?(?)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 6, 2011 at 11:11 am
There is no schema specified, is it possible you had multiple copies of the same table, owned by different schemas? Seems unlikely, but funny things can happen if you have users whose default schema is not dbo.
December 6, 2011 at 12:14 pm
Excellent point!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 6, 2011 at 1:20 pm
No, there are no schema issues here. Everything is in DBO. I do wish I could replicate the issue so we could try different things to figure out what happened.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply