How to replace error message with custom message?

• Roust_m

  SSCoach

  Points: 17410

  More actions

  November 12, 2003 at 3:19 am

  #83552

  Hi!

  Use this code:

  EXEC('[' + @server_name + ']' + '.' + @db_name + '..sp_helprotect ' + @table_name)

  to get a list of permissions for a table on another server.

  When no permissions exist I get:

  quote:

  ---

  Server: Msg 15330, Level 11, State 1, Line 1

  There are no matching rows on which to report.

  ---

  Is it possible to replace this message with custom message like this:

  'Permissions on table ' + @server_name + '.' + @db_name + '..' + @table_name + ' do not exist'

  (without creating custom analog of sp_helprotect)

  Perhaps I need something like:

  SET [DO NOT PRINT ERROR MESSAGES]
  
  EXEC('[' + @server_name + ']' + '.' + @db_name + '..sp_helprotect ' + @table_name)
  
  IF @@ERROR<>0 PRINT 'Permissions on table ' +  @server_name + '.' + @db_name + '..' + @table_name + ' do not exist'
  
  

  Thanks.

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 12, 2003 at 3:38 am

  #481673

  This might help

  http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=18119

  Frank

  http://www.insidesql.de

  http://www.familienzirkus.de

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  November 12, 2003 at 7:27 am

  #481696

  One way is to use temp table

  SET NOCOUNT ON 
  
  
  
  CREATE TABLE #temp ( 
  
  Owner varchar (36), 
  
  Object varchar (62), 
  
  Grantee varchar (18), 
  
  Grantor varchar (36), 
  
  ProtectType varchar (11), 
  
  Action varchar (10), 
  
  [Column] varchar (38)) 
  
  
  
  INSERT INTO #temp 
  
  EXEC('[' + @server_name + ']' + '.' + @db_name + '..sp_helprotect ') 
  
  
  
  IF EXISTS(SELECT 1 FROM #temp WHERE Object = @table_name) 
  
  BEGIN 
  
  SELECT * FROM #temp WHERE Object = @table_name 
  
  END 
  
  ELSE 
  
  BEGIN 
  
  PRINT 'Permissions on table ' +  @server_name + '.' + @db_name + '..' + @table_name + ' do not exist' 
  
  END 
  
  
  
  DROP TABLE #temp
  
  
  
  SET NOCOUNT OFF 
  
   

  Far away is close at hand in the images of elsewhere.
  Anon.