NOT ABLE TO CATCH ERRORS IN SQL SERVER 2008R2

Question

I am trying to catch the errors in the below query using try catch block but even though query is throwing error, query is not hitting catch block. When I am executing the query without using TRY-CATCH block I am getting below error :

Msg 7314, Level 16, State 1, Procedure PROC_NAME, Line 97 The OLE DB provider "SQLNCLI11" for linked server "LINKEDSERVER" does not contain the table ""DB_NAME"."dbo"."VIEW_NAME"". The table either does not exist or the current user does not have permissions on that table. Msg 2020, Level 16, State 1, Line 34 The dependencies reported for entity "SP_NAME" might not include references to all columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

SELECT DISTINCT NAME INTO #ALL_SPS FROM SYSOBJECTS SO,SYSCOMMENTS SC WHERE SO.ID = SC.ID 
AND TEXT LIKE '%LINKEDSERVER%'

CREATE TABLE #ERRORS
(
    ERRORNUMBER VARCHAR(100),
    ERRORSEVERITY   VARCHAR(100),
    ERRORSTATE  VARCHAR(100),
    ERRORPROCEDURE  VARCHAR(100),
    ERRORLINE   VARCHAR(100),
    ERRORMESSAGE VARCHAR(100)
)

CREATE TABLE #TEMP(SP_NAME VARCHAR(500),DB VARCHAR(100), LINKEDSERVER VARCHAR(100))

BEGIN    
DECLARE @SP_NAME VARCHAR(MAX)  
DECLARE @STARTTIME DATETIME =GETDATE()

DECLARE HDR_CURSOR CURSOR FOR   

SELECT NAME FROM #ALL_SPS --IS TEMP TABLE IMPORTED FROM SHARED EXCEL 

OPEN HDR_CURSOR    

FETCH NEXT FROM HDR_CURSOR INTO @SP_NAME

WHILE (@@FETCH_STATUS = 0 )    
   BEGIN    
BEGIN TRY
BEGIN TRANSACTION;

    INSERT INTO #TEMP
    SELECT  
    TBLSQLREFERENCEDENTITY.REFERENCED_ENTITY_NAME AS REFERENCEDENTITY,  
    COALESCE(REFERENCED_DATABASE_NAME,DB_NAME()) AS REFERENCEDDATABASE,  
    COALESCE(TBLSQLREFERENCEDENTITY.REFERENCED_SERVER_NAME,'CURRENT SERVER') AS REFERENCEDSERVER   
    FROM SYS.DM_SQL_REFERENCED_ENTITIES('DBO.'+@SP_NAME, 'OBJECT') TBLSQLREFERENCEDENTITY  
    WHERE  TBLSQLREFERENCEDENTITY.REFERENCED_SERVER_NAME ='LINKEDSERVER'
    GROUP BY REFERENCED_ENTITY_NAME,REFERENCED_DATABASE_NAME,REFERENCED_SERVER_NAME 
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH

INSERT INTO #ERRORS
SELECT ERROR_NUMBER() AS ERRORNUMBER
     ,ERROR_SEVERITY() AS ERRORSEVERITY
     ,ERROR_STATE() AS ERRORSTATE
     ,ERROR_PROCEDURE() AS ERRORPROCEDURE
     ,ERROR_LINE() AS ERRORLINE
     ,ERROR_MESSAGE() AS ERRORMESSAGE;

END CATCH
  FETCH NEXT FROM HDR_CURSOR INTO @SP_NAME

 END    
 CLOSE HDR_CURSOR    
 DEALLOCATE HDR_CURSOR    
 SELECT DATEDIFF(MS,@STARTTIME,GETDATE()) 'TIME TAKEN IN MS'
 SELECT DISTINCT * FROM #TEMP
 SELECT DISTINCT * FROM #ERRORS

END

Please, please, please don't use the old-style comma-syntax JOINs. https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause — Shawn, Dec 05 '17 at 14:54
Doublecheck the permissions on the shared server. Do the proper users have access to the shared tables? — Shawn, Dec 05 '17 at 15:00
Are you a user listed in the Security context of the Linked Server? I believe the security can get pretty granular, depending on how you set it up. Default options, if no users are defined, is to not use a security context, but that can be changed. Server Objects > Linked Servers > [Linked Server Name] > Security page . So even if your user has permissions on the server that is linked at B, you may not be able to use the Linked Server connection from A to B. — Shawn, Dec 07 '17 at 19:35
Also check that the user connecting through the Shared Server actually has Select permission on the table. On Linked Server > [Database Name] > Tables > [Table Name] - Right Click for Properties -> Permissions = Users or roles -- should have Select Premission Grant checked for appropriate User or Role. — Shawn, Dec 07 '17 at 19:39

S3S · Answer 1 · 2017-12-07T19:38:23.357

1

You are mixing compile and run-time errors. Assuming you have permissions to that table, a better way to do this would be something like...

...
BEGIN TRANSACTION;

--see if that object exists in your linked server...
if(select 1 
   from  YourLinkedServer.master.sys.objects
   where [name] = @SP_NAME) is not null


--if it exists, then insert from it...
INSERT INTO #TEMP
SELECT  
TBLSQLREFERENCEDENTITY.REFERENCED_ENTITY_NAME AS REFERENCEDENTITY,  
COALESCE(REFERENCED_DATABASE_NAME,DB_NAME()) AS REFERENCEDDATABASE,  
COALESCE(TBLSQLREFERENCEDENTITY.REFERENCED_SERVER_NAME,'CURRENT SERVER') AS REFERENCEDSERVER   
FROM SYS.DM_SQL_REFERENCED_ENTITIES('DBO.'+@SP_NAME, 'OBJECT') TBLSQLREFERENCEDENTITY  
WHERE  TBLSQLREFERENCEDENTITY.REFERENCED_SERVER_NAME ='LINKEDSERVER'
GROUP BY REFERENCED_ENTITY_NAME,REFERENCED_DATABASE_NAME,REFERENCED_SERVER_NAME 

COMMIT TRANSACTION;

...

DETERMINE THE OBJECTS IN CURRENT DB THAT AREN"T IN THE LINKED SERVER

use [theDatabaseYourCareAbout]

select
    [name]
from sys.objects
where type in ('P','V') --procedures and views
except
select
    [name]
from YourLinkedServer.master.sys.objects
where type in ('P','V') --procedures and views

edited Dec 07 '17 at 19:38

answered Dec 05 '17 at 15:04

S3S

24,809
5
26
45

Thanks for sharing your view. But I actually want to catch the error so that I can track the missing objects and document it and inform to the concerned team. We are doing System Integration Testing. – Shardul Dec 07 '17 at 19:21
Is there any way by which we can compare the two database(all the objects in database ex. Views , Stored Procedure, Tables) and get the difference between them such as missing objects or even missing schema ex. mismatch in column names or count of columns in table or views. It will be very helpful if you can share some code so that I can use it as a reference. – Shardul Dec 07 '17 at 19:26
well, you can still use that to catch runtime errors. However, you aren't going to be able to catch compile time errors. – S3S Dec 07 '17 at 19:32
see the edit. That's about the extent of this question @Shardul – S3S Dec 07 '17 at 19:38

NOT ABLE TO CATCH ERRORS IN SQL SERVER 2008R2

1 Answers1