Trouble in suppressing error messages
-
Greetings!
How do we suppress Error messages which comes in the resultset window when we execute a T-SQL block? Any help in this direction appreciated.
Thanks,
Dilip
-
Where are you executing the T-SQL? Query Analyzer, application, etc... ???
What T-SQL are you executing?
What error are you getting?
You might not be able to suppress all error messages because some errors are batch terminating so any error handling T-SQL that you have will not be executed.
--------------------
Colt 45 - the original point and click interface
-
Hey Phil,
Thanks for the prompt reply!
Actually, Im executing a batch of T-SQL statements and getting it in a .txt file, Here, I want to suppress this error message Server: Msg 207, Level 16, State 3, Line 1 which comes in the .txt file.
Is it possible or not to totally suppress/ignore the T-SQL Error messages?
Regards,
Dilip
-
Ok, so I gather you get a text file with the T-SQL statments, still got no idea how you're executing the T-SQL.
If you're running a client application, like a VB app or something you can catch the error there and basically ignore it.
Otherwise, you're out of luck as error messages are always sent to the calling process.
--------------------
Colt 45 - the original point and click interface -
I'm using the QA and here's my script..This will retreive all the rowcounts for all tables in a database which has this column called "dataareaid" and for a particular criteria.
declare @tblName varchar(150),
@CompanyName char(3),
@buildSQL varchar(200)
set @CompanyName = 'dat'-- <---Change the Company here
declare curTbls cursor for
select [Table] = object_name(id)
from dbo.sysindexes
where indid < 2
and rows 0
and objectproperty(id, 'IsUserTable') = 1
open curTbls
fetch next from curTbls into @tblName
Set @buildSQL='select ''' + @tblName + ''' ,count(*) from ' + @tblName + ' where DATAAREAID = ''' + @CompanyName + ''''
while(@@fetch_status=0)
begin
--set @buildSQL='select ''' + @tblName + ''' ,count(*) from ' + @tblName + ' where DATAAREAID = ''' + @CompanyName + ''''
exec(@buildSQL)
fetch next from curTbls into @tblName
end
close curTbls
deallocate curTbls
GO
Also, I wanted some suggestions for optimizing this query becoz when this is executed on a 350GB ERP database which hosts a lot of transactions, the QA doesn't respond.
Regards,
Dilip
-
So basically you're running this SQL statement over every table in the database. Some of the tables don't contain the column you're referencing, so you want to ignore the "invalid column" error.
Wouldn't it be better to only run the query for tables that contain that column??
SELECT sObj.[name] AS [Table] FROM dbo.sysindexes sInd INNER JOIN dbo.sysobjects sObj ON sInd.[id] = sObj.[id] INNER JOIN dbo.syscolumns sCol ON sObj.[id] = sCol.[id] WHERE sInd.[indid] < 2 AND sInd.[rows] <> 0 AND sObj.[xtype] = 'U' AND sCol.[name] = 'DATAAREAID'
As for optimizing, try replacing COUNT(*) with COUNT( <insert primary key, or non-nullable field> ). Also, depending on selectivity, an index on the DATAAREAID column might help as well.
--------------------
Colt 45 - the original point and click interface -
This query looks impressive, But can i do away with the cursor i.e. Can I extend this query to place the criteria value for dataareaid column or I will have to live with the cursor?
Thanks
-
Given the way you are trying to achieve the result, you won't be able to do without the cursor. If the table list is long you could change the cursor to a fast_forward read only cursor. That will minimise the overhead.
To remove the cursor you'd have to approach the task in a different way and have a select statement fro each table. These could all reside in the one stored procedure to allow for easy execution.
--------------------
Colt 45 - the original point and click interface -
I think I will have to live with the cursor because Im using this script on an ERP database which hosts 1350 tables.
Thanks for all your help. Appreciate it.
Regards,
Dilip
-
Cursors add a lot of overhead. I've always been able to solve problems using temp tables.
DECLARE @COUNTER INT
DECLARE @SIZE INT
DECLARE @ERROR INT
DECLARE @RECORD_ID INT
DECLARE @SOME_COLUMN VARCHAR(128)
DECLARE @TEMP_TABLE TABLE(
RECORD_ID INT
,SOME_COLUMN VARCHAR(128)
,LOOP_ID INT IDENTITY
 
-- Populate table variable with records of interest.
-- If you need to run over the entire table, then use the base table
-- instead of a table variable. As long as it has an identity that is.
-- Also, if you have a break in the identity sequence, you can use
-- the CONTINUE command within an IF statement to get around it.
INSERT INTO @TEMP_TABLE( RECORD_ID, SOME_COLUMN )
SELECT SOME_TABLE_ID, SOME_COLUMN
FROM SOME_TABLE
WHERE SOME_CONDITION_EXISTS
SET @COUNTER = 1
SELECT @SIZE = COUNT(*) + 1 FROM @TEMP_TABLE
BEGIN TRANSACTION some_sort_of_transaction
WHILE @COUNTER < @SIZE
BEGIN
-- Populate variables
SELECT
@RECORD_ID = RECORD_ID
,@SOME_COLUMN = SOME_COLUMN
FROM
@TEMP_TABLE
WHERE
LOOP_ID = @COUNTER
-- Run whatever code you need below.
EXECUTE @ERROR = USP_SOME_SPROCK( @RECORD_ID, @SOME_COLUMN )
IF @ERROR <> 0
BEGIN
PRINT
'Error executing USP_SOME_SPROCK; @ERROR = '
+ CAST( @ERROR AS VARCHAR )
+ '; @RECORD_ID = ' + CAST( @RECORD_ID AS VARCHAR )
+ '; @COUNTER = ' + CAST( @COUNTER AS VARCHAR )
ROLLBACK TRANSACTION some_sort_of_transaction
BREAK
END
SET @COUNTER = @COUNTER + 1
END
IF @ERROR = 0
BEGIN
COMMIT TRANSACTION some_sort_of_transaction
END
Also, BOL warns against using system tables because they may change in the future, so MS supports the use of INFORMATION_SCHEMA. It doesn't always give me what I want, but when it does, it's safe to use in long-lived objects.
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply