Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

temp tables work oddly Expand / Collapse
Author
Message
Posted Saturday, July 28, 2012 4:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:26 PM
Points: 2,929, Visits: 2,947
Dear All,

I have one doubt that I want to clear out by help of your thinking and reasoning.

A stored procedure (SP) was running for ages on SQL 2005. Its aim is to fill a table DDB. All the tables used in SP are in same database. The collations of all tables including collations for theirs columns and the database collation are identical. The collation of the server instance is also identical. The SP usually inserts about 1 million records on daily basis.
Some days ago the SP began failing with error message “…PK constraint violation … for table DDB …”.
All the FK relations are checked and there are no any problems. No triggers , no constraints on the table. Only one primary key.

ANSI NULLs = True
Quoted identifier = True
Collation = SQL_Latin1_General_CP1_CI_AS
There are no any special settings on the database or on the server. It happened on a local machine as well as on the production.

There are no duplicate records in tables used, or it never happens to try to insert a record with a key that already exists in table DDB. Additionally, I checked all the tables used by the sp to ensure there are no duplicate records (not necessary this, but checked for any case) which may possibly be candidates to insert or to take part in insert operations and make some issues.

The stored procedure uses #temp tables (5 tables) to store data temporarily. The collation of the tempdb database is same as the current database. The collations of the #temp tables that are created during sp execution also have the same collation as the current database.

The admins upgraded SQL 2005 to SQL 2008R2.
The problem on SQL 2008R2 is similar. The SP firstly never ended. I created some indexes on some tables, and updated statistics for all tables and then appeared the same problem as above on SQL 2005. And I did the same checks as above for FK, collations, duplicate possible inserts and it didn’t work.

I run DBCC CHECKTABLE , DBCC CHECKDB , and they passed well without any errors reporting.

I have resolved the problem, but the issue now is that I have doubts for my solution. What I did was replacing the #temp tables within the sp with real-temp tables – tables that now exist in the databases and are re-created on every SP run.

One of the questions is why it worked so long time with #temp tables, and now stopped?

With my thoughts for this issue I’m now a bit more in the admin area, so expect some such discussions and thoughts … So guys, can you guess, discuss or explain something on this case that is not clear to me. I will try to reconstruct the problem and try again with more analysis on this.

Kind regards
IgorMi








Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1336902
Posted Saturday, July 28, 2012 8:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 3, 2014 5:22 AM
Points: 92, Visits: 321
As a first step I would like to suggest that you start a server side trace to see as to what is happening.

From the perspective of not capturing too much information and also not adding to much overhead to the server please add a filter so that it captures all events and errors only for SPID associated with stored procedure execution.

This will give you some sort of handle to start your troubleshooting with.

The positive thing is that you seem to be able to replicate this problem very easily and that means the solution is not far away. This is really a interesting problem and a great opportunity for learning something new.



Post #1336925
Posted Saturday, July 28, 2012 11:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 36,952, Visits: 31,461
IgorMi (7/28/2012)
...or it never happens to try to insert a record with a key that already exists in table DDB


Despite your claim and based on the fact that you have no triggers (and I'm assuming no Indexed Views), the only thing that will give you a PK violation is an attempt to insert duplicate data.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1336962
Posted Sunday, July 29, 2012 2:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:26 PM
Points: 2,929, Visits: 2,947

Despite your claim and based on the fact that you have no triggers (and I'm assuming no Indexed Views), the only thing that will give you a PK violation is an attempt to insert duplicate data.


I know it is hard to you to give as most precise answer as you could do, and me too
However I appreciate much.

If I come up with a clear explanation why it happened I'll surely reply on this thread.



select * from deposits_daily_balance ddb
where ddb.val_date_d = cast('2012-07-24' as datetime) or ddb.reg_date_d = cast('2012-07-24' as datetime)
-- 0 records
select ddb.num_cue_s, count(*)
from ddb_temp_DDB ddb -- or from #ddb_temp_DDB ddb
group by num_cue_s
having count(*)>1
--0 records

primary key is composed of num_cue_s,val_date_d and reg_date_d

declare @t_date_d datetime
set @t_date_d = cast('2012-07-24' as datetime)

INSERT INTO deposits_daily_balance
SELECT
tdb.num_cue_s,
@t_date_d AS val_date_d,
@t_date_d AS reg_date_d,
val_date_balance_n,
recalc_int_n,
matured_int_n,
matured_bonus_int_n,
OD_matured_int_n,
int_accrued_n,
bonus_int_accrued_n,
OD_int_accrued_n,
tdb.int_mes_n,
accrued_fee_n,
paid_maintenance_fee_n,
put_on_reservation_n,
out_of_reservation_n,
tdb.od_limit_n,
tdb.od_fac_l,
cor_reg_n,
tdb.branch_s,
tdb.account_definition_id_n,
tdb.estado_s,
tdb.fee_offbalance_l,
AMF_last_payment_date_d,
AMF_last_accrual_date_d

-- this does not work
FROM #ddb_temp_DDB tdb
where not exists (select 1 from deposits_daily_balance ddb_t where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)
-- this does not work
-- this works
FROM ddb_temp_DDB tdb
where not exists (select 1 from deposits_daily_balance ddb_t where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)
-- this works






Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1336973
Posted Sunday, July 29, 2012 10:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
Does this work ?

 ....FROM #ddb_temp_DDB tdb
where not exists (select 1 from deposits_daily_balance ddb_t
where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)
COLLATE 'SQL_Latin1_General_CP1_CI_AS'

Post #1336999
Posted Monday, July 30, 2012 2:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:26 PM
Points: 2,929, Visits: 2,947
Sachin Nandanwar (7/29/2012)
Does this work ?

 ....FROM #ddb_temp_DDB tdb
where not exists (select 1 from deposits_daily_balance ddb_t
where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)
COLLATE 'SQL_Latin1_General_CP1_CI_AS'



Unfortunately not.

Kind regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1337534
Posted Monday, July 30, 2012 2:20 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 2,692, Visits: 3,383
What do you mean by "it does not work?" You mean it never completes? I assume you have a create table statement for your temporary table or a SELECT INTO. Are you sure that statement is completeing? What is your entire script?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1337537
Posted Tuesday, July 31, 2012 5:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:26 PM
Points: 2,929, Visits: 2,947
SQLKnowItAll (7/30/2012)
What do you mean by "it does not work?" You mean it never completes? I assume you have a create table statement for your temporary table or a SELECT INTO. Are you sure that statement is completeing? What is your entire script?


"it does not work" means it finishes with constraint violation, and therefore no insertion is done.

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1337813
Posted Tuesday, July 31, 2012 6:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 2,692, Visits: 3,383
IgorMi (7/31/2012)
SQLKnowItAll (7/30/2012)
What do you mean by "it does not work?" You mean it never completes? I assume you have a create table statement for your temporary table or a SELECT INTO. Are you sure that statement is completeing? What is your entire script?


"it does not work" means it finishes with constraint violation, and therefore no insertion is done.

Regards
IgorMi
Ok, what is the violation exactly? Can you please post the full script you are using (edited for confidentiality if need be). It is really hard to help you without the entire picture.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1337821
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse