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

Begin Tran...Commit Tran with Exec SQL Insert Cmd Expand / Collapse
Author
Message
Posted Thursday, June 17, 2010 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 6, 2011 12:50 PM
Points: 6, Visits: 66
For my education, I EXEC a dynamic sql that is embedded in a BEGIN TRAN ... COMMIT TRAN. A simple example and assume the var have been defined:

BEGIN TRAN
SET @sqlcmd = 'INSERT INTO dbo.TARGET_TBL'
+ ' (colA,colB)'
+ ' SELECT Acol, Bcol FROM dbo.SOURCVE_TBL'
EXEC (@sqlcmd)
SET @theErrNbr = @@ERROR
COMMIT TRAN

@sqlcmd has the name of the target table in a string which the outer layer, BEGIN TRAN ... COMMIT TRAN may not see.

Question1: Will COMMIT TRAN know to commit the changes to the SOURCE_TBL? (executed dynamically). What about ROLLBACK?

Question2: I will receive the @@ERROR number if it fails?

Excuse my unawareness and MANY THANKS.
Post #939147
Posted Friday, June 18, 2010 4:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 3,969, Visits: 5,212
1 - instead of using EXEC(@Sql), rather declare your @Sql as NVARCHAR and use EXEC sp_executesql @Sql
2 - wrap the exec in a BEGIN TRY .. END TRY block and trap the @@Error value in the BEGIN CATCH ... END CATCH block (the @@Error value will be passed back to the calling / executing command)


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #939453
Posted Friday, June 18, 2010 4:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 2,372, Visits: 7,562
Well, lets see shall we?

USE TestingDB
DECLARE @sqlcmd AS VARCHAR(4000)
DECLARE @theErrNbr AS VARCHAR(500)
CREATE TABLE table1 (
colA VARCHAR(50),
colB VARCHAR(50))
CREATE TABLE table2 (
Acol VARCHAR(50),
Bcol VARCHAR(50))
INSERT INTO table2(Acol,Bcol)
SELECT 'Jack','Jill'
UNION ALL SELECT 'Jack','Beanstalk'
UNION ALL SELECT 'Queen','Hearts'

--Working version
BEGIN TRAN
SET @sqlcmd = 'INSERT INTO table1'
+ ' (colA,colB)'
+ ' SELECT Acol, Bcol FROM table2'
EXEC (@sqlcmd)
SET @theErrNbr = @@ERROR
COMMIT TRAN
SELECT * FROM table1
SELECT @theErrNbr AS 'ERROR'
/*Output
colA colB
-------------------------------------------------- --------------------------------------------------
Jack Jill
Jack Beanstalk
Queen Hearts

ERROR
--------------------------------------------------
0
*/
--Cleanup
DROP TABLE table1
CREATE TABLE table1 (
colA VARCHAR(50),
colB VARCHAR(50))
DROP TABLE table2
--Not working version due to lack of table2
BEGIN TRAN
SET @sqlcmd = 'INSERT INTO table1'
+ ' (colA,colB)'
+ ' SELECT Acol, Bcol FROM table2'
EXEC (@sqlcmd)
SET @theErrNbr = @@ERROR
COMMIT TRAN
SELECT * FROM table1
SELECT @theErrNbr AS 'ERROR'
--Cleanup
DROP TABLE table1
/*Ouput
Msg 208, Level 16, State 1, Line 1
Invalid object name 'table2'.
colA colB
-------------------------------------------------- --------------------------------------------------


ERROR
--------------------------------------------------
208
*/

Hope that answers your question!



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #939454
Posted Friday, June 18, 2010 9:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 6, 2011 12:50 PM
Points: 6, Visits: 66
MANY THANKS for your replies.
Post #939715
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse