Problem in transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem in transaction

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-01-07 : 00:19:38

I have a truncate table and a create temp table DDL in an SP
which uses transaction.
I need to preform truncate operation and create table operation by applying the same transactioin by using some other statements
which supports txn
can anyone help me by giving some sample to do this operation

susan

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-01-07 : 02:17:31

Can you explain more please? I don't understand what you're trying to do.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 03:28:18

I need to preform truncate operation and create table operation by applying the same transactioin by using some other statements
can you exlain above statement?

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-01-07 : 05:09:45

actually what i want to do is
I am writing a truncate statement for a table and a create statement for a temporary table and inserting values for the temporary table inside a SP.
Its working fine
But when i write the same statements inside a transacetion like this its not working both the operations are not performed

CREATE PROCEDURE USP_SAMPLE
AS
BEGIN

DECLARE
@i_RETURNCODE INT,
@a_c_ERRMSG VARCHAR,
@i_TRAN_COUNT_UPD INT

SELECT
@i_RETURNCODE = 0

SELECT @i_TRAN_COUNT_UPD = @@TRANCOUNT

IF ( @i_TRAN_COUNT_UPD > 0)
SAVE TRAN TRAN_SAMP
ELSE
BEGIN TRAN TRAN_SAMP

TRUNCATE TABLE
UST_SAMP_DETAILS

CREATE TABLE #TMP_SAMP_DETAILS
(
USER_ID VARCHAR(20),
USER_NAME VARCHAR(20),
USER_VNET VARCHAR(20)
)
insert into #tmp_samp_details values('1','2','3')
IF (@@error <> 0)
BEGIN
SELECT @i_RETURNCODE = 1
SELECT @a_c_ERRMSG = 'Error while creating #TMP_SAMP_DETAILS'

GOTO errorhandler
END
ELSE
COMMIT TRAN TRAN_SAMP
RETURN @i_RETURNCODE

errorhandler:
ROLLBACK TRAN TRAN_SAMP
RETURN @i_RETURNCODE
End
Go

susan

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-07 : 05:22:50

You mean the create and the insert ? If yes, then local temporary tables are visible only in the current session. You might be checking the same in a different session. Alternatively, You could make your table a global temporary table names with a double number sign (##table_name). (##table_name) will be visible from all sessions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 08:59:04

quote:
Originally posted by sakets_2000

You mean the create and the insert ? If yes, then local temporary tables are visible only in the current session. You might be checking the same in a different session. Alternatively, You could make your table a global temporary table names with a double number sign (##table_name). (##table_name) will be visible from all sessions.

sorry i didnt understand what you're telling here. As per above posted code OP is doing create and insert inisde same sp. didnt understand what you mean by different session then

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2009-01-08 : 05:14:04

Hi thank u so much now my SP is executing fine.

susan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources