Link to home
Create AccountLog in
Avatar of niceguy971
niceguy971

asked on

SSRS 2005--TRY...CATCH

I need to create SSRS 2005 Report which query some databases and display their status.

The code in my SP:

CREATE PROCEDURE [dbo].[usp_TESTDB]  
AS
BEGIN--start SP
      SET NOCOUNT ON;

 create table #tInfo
(
Server varchar(100)
,Result varchar(100)
)

declare @mName varchar(100)
select top 1 @mName=Name from [MY_Server_1].[MY_DB_1].[dbo].My_Table_1

IF @@ERROR <> 0
  insert into #tInfo(Server,Result) Values ('[MY_Server_1]', 'Failure')
ELSE
  insert into #tInfo(Server,Result) Values ('[MY_Server_1]', 'Success')
 

--similar logic for other servers:

select top 1 @mName=Name from [MY_Server_2].[MY_DB_2].[dbo].My_Table_2

IF @@ERROR <> 0
  insert into #tInfo(Server,Result) Values ('[MY_Server_2]', 'Failure')
ELSE
  insert into #tInfo(Server,Result) Values ('[MY_Server_2]', 'Success')


  select * from #tInfo
 
  drop table #tInfo

END--end SP
---


In SSRS report (the code in dataset):

BEGIN TRY
  EXEC [dbo].[usp_TESTDB]
END TRY

BEGIN CATCH
SELECT
  ERROR_NUMBER() AS Server,
  ERROR_MESSAGE() AS Result
END CATCH

Is there a better way to do it???

If there was an error..I need to know which database(table) caused this error.
Avatar of jogos
jogos
Flag of Belgium image

Then you must place that try/catch in your procedure. Save the string '[MY_Server_1]' in a variable and use that to add to the error when you raise the error yourself in your catchblock.
ASKER CERTIFIED SOLUTION
Avatar of Howard Cantrell
Howard Cantrell
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account