how to print error message in stored procedure
below is my stored procedure,it will work most of the times ,
but it is not working when error msg is 213 ,
can anyone solve this please
create procedure [dbo].[ARCHIVE_PR_RECORDS]
as begin
DECLARE @ErrorMessage varchar(2000)
,@ErrorSeverity tinyint
,@ErrorState tinyint
,@count int
,@total integer
,@success integer
,@failure integer
,@LOAD_LIMT INTEGER
,@DATE DATETIME
,@ID INT
BEGIN TRY
SET @ID = (SELECT ISNULL(env_archlogsid,0) FROM ENV_ARCHLOGS)
SET @LOAD_LIMT = (SELECT load_limit FROM [192.168.6.2].maximo.dbo.env_archive_trans WHERE env_object = 'PR')
SET @DATE = (SELECT env_date_value FROM [192.168.6.2].maximo.dbo.env_archive_trans WHERE env_object = 'PR')
set @total =(select COUNT() FROM [192.168.6.2].maximo.dbo.pr WHERE statusdate <=@DATE)
set @count = (select COUNT() FROM [192.168.6.2].maximo.dbo.pr WHERE statusdate <=@DATE)
while @count <> 0
begin
insert into dbo.pr select top (select @LOAD_LIMT) * from [192.168.6.2].maximo.dbo.pr
/**INSERT INTO dbo.prline SELECT * FROM [192.168.6.2].maximo.dbo.prLINE WHERE prnum IN (select top (select @LOAD_LIMT) prnum from [192.168.6.2].maximo.dbo.pr)
AND SITEID IN (select top (select @LOAD_LIMT) SITEID from [192.168.6.2].maximo.dbo.pr)
DELETE FROM [192.168.6.2].maximo.dbo.prLINE WHERE prnum IN (select top (select @LOAD_LIMT) prnum from [192.168.6.2].maximo.dbo.pr)
AND SITEID IN (select top (select @LOAD_LIMT) SITEID from [192.168.6.2].maximo.dbo.pr)
**/
DELETE top (select @LOAD_LIMT) FROM [192.168.6.2].maximo.dbo.pr
select @count = COUNT(*) FROM [192.168.6.2].maximo.dbo.pr
end
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
print @ErrorMessage
213 is presumably? "Column name or number of supplied values does not match table definition", I imagine that that closes the database connection and therefore cannot be caught (i.e. it is more of a Syntax Error rather than a Runtime error)
I have some observations on your code in case helpful:
If there can ever be multiple rows in ENV_ARCHLOGS this will select a row at random
This has to process the same query twice, which may well be "expensive" given that [192.168.6.2] is presumably "remote"?
Personally I would want an alias name for [192.168.6.2] in case the IP address ever changes - either established via DNS, or failing that a bodge in the HOSTS file
Similarly, you are making the exact same count twice.
No ORDER BY so the records selected will be random, and quite likely different each time the query is run (even if underlying data in source table does not change)
As said above the SELECT TOP will be a random selection unless you use ORDER BY.
If your intention is to delete the rows after inserting them into [prline]?? then this code is very risky. It is unlikely that the rows deleted will be the same as the rows copied - more rows may have been added / removed from the source between the INSERT and the DELETE, adnthus the SELECT TOP may be different (even if you added an ORDER BY). If it was me I would delete ONLY rows from Remote that were IN the initial selection that was inserted into PRLINE - for example, I would store them in a #TEMP table, then insert them into [prline] and then delete matching rows from Remote (note that there is STILL a risk that the rows have been changed in the interval, so it would be possible that the copy you have locally does NOT match the remote copy that you then delete)
Again, in the absence of an ORDER BY this is a deletion of random records from [pr]