DatabaseProcApplicationCreatedLinks
sybsystemprocssp_check_remote_tempdb  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Messages for sp_check_remote_tempdb
4     **
5     ** 19818, "Local temporary database '%1!' is skipped for this operation. The
6     **         database is accessible from the owner instance '%2!' only."
7     ** 19820, "The database '%1!' is only accessible from the owner instance 
8     **	   '%2!'. Please execute the procedure on that instance.
9     */
10    
11    /*
12    ** Description
13    **      It checks if the database is a local tempdb owned by a remote
14    **	instance and print msg depending on the action requested. 
15    **
16    ** Parameters
17    **      @dbid	- Database id
18    **	@action - 'skip' or 'reject'. Depending on the value it
19    **		  will raise a skip or reject warning. Any other
20    **		  value will avoid printing any message.
21    ** Returns
22    **      1       - Database is local tempdb owned by remote instance. 
23    **      0       - Otherwise
24    */
25    create procedure sp_check_remote_tempdb
26        @dbid int,
27        @action sysname = NULL
28    as
29        begin
30            declare @instanceid int,
31                @instancename sysname,
32                @dbname varchar(255),
33                @error int,
34                @msg varchar(1024)
35    
36            if (@@clustermode = 'shared disk cluster')
37            begin
38                select @instanceid = isnull(db_instanceid(@dbid), 0)
39                if (@instanceid > 0 and @instanceid != @@instanceid)
40                begin
41                    if (@action = 'skip')
42                        select @error = 19818
43                    else if (@action = 'reject')
44                        select @error = 19820
45                    else
46                        select @error = 0
47    
48                    if (@error != 0)
49                    begin
50                        select @dbname = db_name(@dbid)
51                        select @instancename =
52                            instance_name(@instanceid)
53    
54                        exec sp_getmessage @error, @msg output
55                        print " "
56                        print @msg, @dbname, @instancename
57                    end
58    
59                    return 1
60                end
61            end
62    
63            return 0
64        end
65    


exec sp_procxmode 'sp_check_remote_tempdb', 'AnyMode'
go

Grant Execute on sp_check_remote_tempdb to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_check_remote_tempdb  
 MNER 3 No Error Check should check return value of exec 54
 MUCO 3 Useless Code Useless Begin-End Pair 29
 MUCO 3 Useless Code Useless Brackets 36
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 48
 MTR1 2 Metrics: Comments Ratio Comments: 44% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 6dec - 2exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 24 25

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)