Richard Quadling
asked on
URGENT : Missing something basic about @@ERROR and OSQL.
Hi.
I have a VERY old DOS application which creates 54 TAB files containing 1 tables' data per file.
The program also creates a text file which is used as a T-SQL script for osql.
The script deletes the data from the DB in bottom up (so no orphans are created in the relationships between the tables).
The script then bulk inserts new data into the tables in top down (so to not create orphans).
Finally, the script shrinks the datafiles.
When this works, it is working fine. Nearly 6 million rows in 54 tables takes up 2.3GB. All good.
Sometimes, the DOS data is invalid - VERY VERY occassionally - bad app - can't get it fixed - living with it by rewriting a web version using MS SQL!
I want to use a transaction to allow me to rollback if the import fails.
So, I have something like this (edited to show the issue)
------------EDITED SAMPLE OF T-SQL script------------
ALTER DATABASE [TripleCDataConversion]
SET RECOVERY SIMPLE
BEGIN TRANSACTION
DELETE TripleCDataConversion.dbo. Distributo rs
DELETE TripleCDataConversion.dbo. Customers
DELETE TripleCDataConversion.dbo. CommentCod es
IF @@ERROR <> 0
BEGIN
PRINT 'Failed Import Startup'
END
ELSE
BEGIN
PRINT 'Successful Import Startup'
END
IF @@ERROR = 0
BEGIN
BULK INSERT TripleCDataConversion.dbo. CommentCod es
FROM 'D:\CCC2SQL\CO.TAB'
WITH
(
FIELDTERMINATOR = '\t',
KEEPIDENTITY,
KEEPNULLS,
LASTROW = 136,
ORDER (UniqueID ASC)
)
IF @@ERROR <> 0
BEGIN
PRINT 'Failed to import CommentCodes '
END
ELSE
BEGIN
PRINT 'Successfully imported CommentCodes '
END
END
IF @@ERROR = 0
BEGIN
BULK INSERT TripleCDataConversion.dbo. Customers
FROM 'D:\CCC2SQL\CU1.TAB'
WITH
(
FIELDTERMINATOR = '\t',
KEEPIDENTITY,
KEEPNULLS,
LASTROW = 1790,
ORDER (UniqueID ASC)
)
IF @@ERROR <> 0
BEGIN
PRINT 'Failed to import Customers '
END
ELSE
BEGIN
PRINT 'Successfully imported Customers '
END
END
IF @@ERROR = 0
BEGIN
BULK INSERT TripleCDataConversion.dbo. Distributo rs
FROM 'D:\CCC2SQL\DI.TAB'
WITH
(
FIELDTERMINATOR = '\t',
KEEPIDENTITY,
KEEPNULLS,
LASTROW = 1458,
ORDER (UniqueID ASC)
)
IF @@ERROR <> 0
BEGIN
PRINT 'Failed to import Distributors '
END
ELSE
BEGIN
PRINT 'Successfully imported Distributors '
END
END
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
PRINT 'Import successful'
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT 'Failed Import finalization'
END
DBCC SHRINKDATABASE ([TripleCDataConversion], 0)
ALTER DATABASE [TripleCDataConversion]
SET RECOVERY FULL
------------EDITED SAMPLE OF T-SQL script------------
The command to get this all working is ...
OSQL -U sa -P xxx -S yyy -d TripleCDataConversion -i D:\CCC2SQL\CCC_BULK.SQL -o D:\CCC2SQL\CCC_BULK.LOG -n -b -e
The log file for the time it all went wrong is ...
------------EDITED SAMPLE OF OSQL output------------
(1457 rows affected)
(1789 rows affected)
(136 rows affected)
Successful Import Startup
(136 rows affected)
Successfully imported CommentCodes
Msg 4864, Level 16, State 1, Server BANDACCESS, Line 1
Bulk insert data conversion error (type mismatch) for row 136, column
120 (NarrativeChangedDateTime) .
Failed to import Customers
(1458 rows affected)
Successfully imported Distributors
Import successful
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
41 1 356672 131072 356544 356544
41 2 2560 2560 2560 2560
(2 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
------------EDITED SAMPLE OF OSQL output------------
As you can see the Bulk Insert for Customers failed. The message is only printed because @@ERROR <> 0.
But, a few lines later, I re-test @@ERROR and it is now 0! So the import continues with the next table and then commits the whole lot.
Why is @@ERROR getting reset? Can you only "look" at it once?
How do I get this working?
I COULD nest the bulk inserts, but this would be 54 levels deep and I suspect that wouldn't work.
HELP!
Richard Quadling.
I have a VERY old DOS application which creates 54 TAB files containing 1 tables' data per file.
The program also creates a text file which is used as a T-SQL script for osql.
The script deletes the data from the DB in bottom up (so no orphans are created in the relationships between the tables).
The script then bulk inserts new data into the tables in top down (so to not create orphans).
Finally, the script shrinks the datafiles.
When this works, it is working fine. Nearly 6 million rows in 54 tables takes up 2.3GB. All good.
Sometimes, the DOS data is invalid - VERY VERY occassionally - bad app - can't get it fixed - living with it by rewriting a web version using MS SQL!
I want to use a transaction to allow me to rollback if the import fails.
So, I have something like this (edited to show the issue)
------------EDITED SAMPLE OF T-SQL script------------
ALTER DATABASE [TripleCDataConversion]
SET RECOVERY SIMPLE
BEGIN TRANSACTION
DELETE TripleCDataConversion.dbo.
DELETE TripleCDataConversion.dbo.
DELETE TripleCDataConversion.dbo.
IF @@ERROR <> 0
BEGIN
PRINT 'Failed Import Startup'
END
ELSE
BEGIN
PRINT 'Successful Import Startup'
END
IF @@ERROR = 0
BEGIN
BULK INSERT TripleCDataConversion.dbo.
FROM 'D:\CCC2SQL\CO.TAB'
WITH
(
FIELDTERMINATOR = '\t',
KEEPIDENTITY,
KEEPNULLS,
LASTROW = 136,
ORDER (UniqueID ASC)
)
IF @@ERROR <> 0
BEGIN
PRINT 'Failed to import CommentCodes '
END
ELSE
BEGIN
PRINT 'Successfully imported CommentCodes '
END
END
IF @@ERROR = 0
BEGIN
BULK INSERT TripleCDataConversion.dbo.
FROM 'D:\CCC2SQL\CU1.TAB'
WITH
(
FIELDTERMINATOR = '\t',
KEEPIDENTITY,
KEEPNULLS,
LASTROW = 1790,
ORDER (UniqueID ASC)
)
IF @@ERROR <> 0
BEGIN
PRINT 'Failed to import Customers '
END
ELSE
BEGIN
PRINT 'Successfully imported Customers '
END
END
IF @@ERROR = 0
BEGIN
BULK INSERT TripleCDataConversion.dbo.
FROM 'D:\CCC2SQL\DI.TAB'
WITH
(
FIELDTERMINATOR = '\t',
KEEPIDENTITY,
KEEPNULLS,
LASTROW = 1458,
ORDER (UniqueID ASC)
)
IF @@ERROR <> 0
BEGIN
PRINT 'Failed to import Distributors '
END
ELSE
BEGIN
PRINT 'Successfully imported Distributors '
END
END
IF @@ERROR = 0
BEGIN
COMMIT TRANSACTION
PRINT 'Import successful'
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT 'Failed Import finalization'
END
DBCC SHRINKDATABASE ([TripleCDataConversion], 0)
ALTER DATABASE [TripleCDataConversion]
SET RECOVERY FULL
------------EDITED SAMPLE OF T-SQL script------------
The command to get this all working is ...
OSQL -U sa -P xxx -S yyy -d TripleCDataConversion -i D:\CCC2SQL\CCC_BULK.SQL -o D:\CCC2SQL\CCC_BULK.LOG -n -b -e
The log file for the time it all went wrong is ...
------------EDITED SAMPLE OF OSQL output------------
(1457 rows affected)
(1789 rows affected)
(136 rows affected)
Successful Import Startup
(136 rows affected)
Successfully imported CommentCodes
Msg 4864, Level 16, State 1, Server BANDACCESS, Line 1
Bulk insert data conversion error (type mismatch) for row 136, column
120 (NarrativeChangedDateTime)
Failed to import Customers
(1458 rows affected)
Successfully imported Distributors
Import successful
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
41 1 356672 131072 356544 356544
41 2 2560 2560 2560 2560
(2 rows affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
------------EDITED SAMPLE OF OSQL output------------
As you can see the Bulk Insert for Customers failed. The message is only printed because @@ERROR <> 0.
But, a few lines later, I re-test @@ERROR and it is now 0! So the import continues with the next table and then commits the whole lot.
Why is @@ERROR getting reset? Can you only "look" at it once?
How do I get this working?
I COULD nest the bulk inserts, but this would be 54 levels deep and I suspect that wouldn't work.
HELP!
Richard Quadling.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If worked then close the Question
Regards
Imran
Regards
Imran
ASKER
In my small test, this has worked. Will need to wait until overnight run though for full testing. But can't see it being an issue.
Ok !
ASKER
Easy points! All working fine. I even broke the data on purpose to test it!
ASKER
Thank you!
DOH! DOH! DOH!!!!!!!!!