SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


temp tables work oddly


temp tables work oddly

Author
Message
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10164 Visits: 5155
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,
My blog: www.igormicev.com
Vikrant S Patil
Vikrant S Patil
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 343
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.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)SSC Guru (209K reputation)

Group: General Forum Members
Points: 209883 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10164 Visits: 5155

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 Sad
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,
My blog: www.igormicev.com
Sachin Nandanwar
Sachin Nandanwar
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1525 Visits: 2633
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'



--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10164 Visits: 5155
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,
My blog: www.igormicev.com
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13186 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10164 Visits: 5155
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,
My blog: www.igormicev.com
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13186 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search