DatabaseProcApplicationCreatedLinks
sybsystemprocssp_replication_path  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **  Messages for "sp_replication_path"
4     **
5     ** 11220, "Parameter cannot be NULL."
6     ** 17067 "Unable to encrypt password for '%1!'. See prior error message 
7     **	 for reason."
8     ** 17240, "'%1!' is not a valid name."
9     ** 17260, "Can't run %1! from within a transaction."
10    ** 17421, "No such database -- run sp_helpdb to list databases."
11    ** 17756, "The execution of the stored procedure '%1!' in database
12    **	  '%2!' was aborted because there was an error in writing the
13    **	  replication log record."
14    ** 18102, Table '%1!' does not exist in this database.
15    ** 18107, Stored procedure '%1!' does not exist in this database.
16    ** 18118, Object '%1!' does not exist in this database.
17    ** 18291, The parameter value '%1!' is invalid.
18    ** 18375, "You are not authorized to execute this stored procedure. Only the 
19    **	  System Administrator (SA), the Database Owner (DBO) or a user with 
20    **	  replication_role authorization can execute this stored procedure."
21    ** 18408, "The stored procedure must be executed with the name of the
22    **	  current database."
23    ** 18409, "The built-in function '%1!' failed. Please see any other messages 
24    **	  printed along with this message."
25    ** 18481, '%1!' is an invalid configuration parameter.
26    ** 19213, "Invalid argument or unsupported command: %1!."
27    ** 19384, The '%1!' parameter is required with '%2!' parameter.
28    ** 19415, The '%1!' parameter is required for the '%2!' command.
29    ** 19805, An unexpected syntax error has occurred.
30    ** 19859, Argument '%1!' is not valid for '%2!'.
31    **
32    */
33    create or replace procedure sp_replication_path
34        @dbname varchar(30), /* always database name	     */
35        @action varchar(30) = NULL, /* add|drop|config|bind|unbind|list */
36        /*
37        ** sp_replication_path has an additional four positional parameters the
38        ** specific usage of these parameters is dictated by the syntax of the
39        ** action; so, rather than give a name that is meaningful in some context
40        ** but misleading in others, the anonymous '@p...' names are used
41        */
42        @p1 varchar(255) = NULL,
43        @p2 varchar(1023) = NULL, /* must be able to hold long names
44        **  of the form site.db.owner.name */
45        @p3 varchar(1023) = NULL, /* must be able to hold long names
46        **  of the form site.db.owner.name */
47        @p4 varchar(255) = NULL,
48        @rs_encpwd varbinary(256) = NULL /* encrypted rs password, 
49    				     ** internal use for replication.
50    				     */
51    as
52    
53        declare @status int, /* Local status			*/
54            @retval int, /* Return status 		*/
55            @owner varchar(255), /* Owner of the object 	     	*/
56            @db varchar(255), /* Db name 	 	     	*/
57            @dbid int, /* Db id			*/
58            @dbuid int, /* Db's owner id		*/
59            @blt_failure int, /* builting failure		*/
60            @rep_constant int, /* Indicate a replicated object */
61            @rep_all int, /* DB's replication constant 	*/
62            @rep_l1 int, /* DB's replication constant	*/
63            @report varchar(255), /* Report string for successful **
64            ** actions (will be filled in   **
65            ** in the individual sections)	*/
66            @maxlen int
67    
68        declare @dummy int
69        declare @nullarg char(1)
70        declare @gp_enabled int
71        declare @status1 int
72        declare @status2 int
73        declare @status3 int
74    
75        select @status1 = 1
76        select @status2 = 1
77        select @status3 = 1
78    
79    
80        declare @MRP_CLASS int /* MRP_CLASS constant		*/
81        declare @REPAGENT_CLASS int /* REPAGENT_CLASS constant	*/
82        select @MRP_CLASS = 41, @REPAGENT_CLASS = 11
83    
84        declare @STATIC_KEY int /* for rs password encryption */
85        select @STATIC_KEY = 54
86    
87    
88        if @@trancount > 0
89        begin
90            /* 
91            ** 17260, "Can't run %1! from within a transaction."
92            */
93            raiserror 17260, "sp_relication_path"
94            return (1)
95        end
96    
97        /*
98        ** if the dbname was not supplied, or the dbname is "help" and
99        ** no action parameter was supplied then we jump to the syntax help
100       */
101       if ((@dbname is NULL) or
102               (@dbname = "help" and @action is NULL))
103       begin
104           goto usage
105       end
106   
107       set chained off
108       set transaction isolation level 1
109   
110       /* Init variables */
111       select @blt_failure = - 2, /* LT_REP_GET_FAILED */
112           @rep_all = 2048, /* LT_REP_ALL */
113           @rep_l1 = 4096, /* LT_REP_L1 */
114           @rep_constant = - 32768,
115           @report = NULL
116   
117   
118       /* Get the database owner */
119       select @dbuid = suid, @dbid = dbid
120       from master.dbo.sysdatabases
121       where name = @dbname
122   
123       if (@dbid is null)
124       begin
125           /*
126           ** 17421, "No such database -- run sp_helpdb to display databases."
127           */
128           raiserror 17421
129           return (1)
130       end
131   
132       /*
133       ** Make sure that the database is the current database.
134       */
135       if (@dbname != db_name())
136       begin
137           /*
138           ** 18408, "The stored procedure must be executed with the name of the
139           **	   current database."
140           */
141           raiserror 18408
142           return (1)
143       end
144   
145       /*
146       ** If granular permissions is not enabled, 
147       ** you must be SA, dbo or have REPLICATION role to execute this
148       ** sproc.
149       **  If granular permissions is enabled then users with 
150       ** 'manage replication' permission can execute it.
151       */
152       select @nullarg = NULL
153       execute @status1 = sp_aux_checkroleperm "dbo", "manage replication",
154           @dbname, @gp_enabled output
155   
156       if (@gp_enabled = 0)
157       begin --{
158           if (@status1 != 0)
159           begin --{
160               execute @status2 = sp_aux_checkroleperm "sa_role",
161                   @nullarg, @nullarg, @gp_enabled output
162   
163               if (@status2 != 0)
164               begin
165                   execute @status3 = sp_aux_checkroleperm
166                       "replication_role", @nullarg, @nullarg,
167                       @gp_enabled output
168               end
169   
170               if (@status3 != 0)
171               begin
172                   /*
173                   ** 18375, "You are not authorized to execute this stored
174                   **         procedure. Only the System Administrator(SA),
175                   ** 	   the Database Owner (DBO) or a user with 
176                   ** 	   replication_role authorization can execute 
177                   **	   this stored procedure."
178                   */
179                   raiserror 18375
180                   return (1)
181               end
182           end -- }
183           if (@status2 = 0)
184           begin
185               select @dummy = proc_role("sa_role")
186           end
187           if (@status3 = 0)
188           begin
189               select @dummy = proc_role("replication_role")
190           end
191       end -- }
192       else
193       begin
194           print "@status1:"
195           select @status1
196           select @dummy = proc_auditperm("manage replication", @status1, @dbname)
197           if (@status1 != 0)
198               return 1
199       end
200   
201       /* 
202       ** Check first if the action syntax is correct 
203       */
204       select @action = lower(@action)
205       if (@action is null or @action not in ('add', 'drop', 'config',
206                   'bind', 'unbind', 'list'))
207       begin
208           if (@action != "help")
209           begin
210               raiserror 19213, @action
211           end
212           goto usage
213       end
214   
215   
216       /*
217       ** action is 'list' so we will do the work here without the builtin
218       */
219       if (@action = 'list')
220       begin
221           /*
222           ** Display information about the MRP environment. With no parameters
223           ** show everything organized by path. If a single parameter is given 
224           ** it must be one of the supported types (currently path and table|
225           ** sproc objects); information will be organized by the specified type
226           ** If the second parameter is supplied its type must match the given
227           ** type in the first parameter; detailed information about the given
228           ** object will be provided.
229           **
230           ** Note: 'list' does not require a call to the built-in
231           **
232           **  Var.  
233           ** -----  -------------------------------------
234           **  @p1   type to display (optional)
235           **        must be valid type (path|table|sproc)
236           **  @p2   name of object to display (optional)
237           **        name must exist and agree with type
238           **  @p3	  unused
239           **  @p4   unused
240           **
241           ** Errors raised by 'list'
242           **
243           **	error	 description
244           **	-----	-------------------------------------------------------
245           **	18102	Table '%1!' does not exist in this database.
246           **	18107	Stored procedure '%1!' does not exist in this database.
247           **	18118	Object '%1!' does not exist in this database.
248           **	19805	An unexpected syntax error has occurred.
249           **	19859	Argument '%1!' is not valid for '%2!'.
250           **	
251           */
252   
253           /*
254           ** There are two basic ways we can look at the MRP data; from either
255           ** an binding- or path-centric view (axis). 
256           ** For a binding-centric view (default) we start with the list of bound
257           ** objects and display the path(s) to which each is bound. Searches on
258           ** the binding axis need a further qualifier to indicate what type of
259           ** object we are looking for (i.e. table, sproc, etc.)
260           **
261           ** For a path-centric view the list is built upon the set of paths and
262           ** objects bound to each path are displayed.
263           **
264           ** Both binding and path axis searches may be further restricted by
265           ** name.
266           **
267           ** The search_omin/omax are used to limit searches on the underlying
268           ** layer once we have acquired an object id. Searches on these values
269           ** are always search_omin <=  <= search_omax. There are only
270           ** two choices for the values to be set to:
271           **  - search_omin = 1, search_omax = MAXINT indicates a wildcard
272           **    search that will match all valid values for object ids, we are
273           **    looking for all instances of a broader search type
274           **  - search_omin = search_omax =  indicates that we are
275           **    seeking a specific object/path instance
276           */
277           declare @search_axis varchar(255) /* path or binding search axis     */
278           declare @explicit_all int /* flag indicating whether the all	**
279           ** axis is user requested or set   **
280           ** by default.			*/
281           declare @search_qual varchar(255) /* limits binding search to a type */
282           declare @search_name varchar(255) /* object/path name to search on   */
283           declare @search_omin int /* object id search minimum value  */
284           declare @search_omax int /* object id search maximum value  */
285   
286           declare @lpath_id int /* logical path id 		*/
287           declare @lpath_name varchar(255) /* logical path name 		*/
288           declare @ppath_id int /* physical path id 		*/
289           declare @ppath_name varchar(255) /* physical path name 		*/
290           declare @rs_id int /* RS definition id 		*/
291           declare @rs_name varchar(255) /* RS definition name 		*/
292           declare @bnd_obj int /* bound object id		*/
293           declare @obj_name varchar(255) /* bound object name		*/
294           declare @atype char(2) /* object attribute type	*/
295   
296           declare @distrib_model varchar(30) /* current distribution model 	*/
297           declare @is_active varchar(3) /* active display for path axis	*/
298   
299           /*
300           ** by default we cast a wide net across the object bindings, we'll pare
301           ** these terms down if search restrictions are applied
302           */
303           select @search_axis = "all"
304           select @explicit_all = 0
305           select @search_qual = '%'
306           select @search_name = '%'
307           select @search_omin = 1
308           select @search_omax = 2147483647
309   
310           /*
311           ** 'list' never uses @p3 or @p4 or @rs_encpwd
312           */
313           if ((@p3 is not NULL) or (@p4 is not NULL) or (@rs_encpwd is not NULL))
314           begin
315               /*
316               ** 19805
317               ** An unexpected syntax error has occured.
318               */
319               raiserror 19805
320               select @retval = 1
321               goto done
322           end
323   
324           /*
325           ** With the introduction of additional distribution models beyond
326           ** 'object' we need to keep track of what binding types are currently
327           ** in active (will be read by the server when RAT starts) so we will
328           ** create a temp table holding currently active binding object types
329           ** and populate it based on the 'multipath distribution model' config
330           ** setting.
331           */
332           create table #active_types(obj_type char(2))
333           select @distrib_model = rep_agent_config(@dbid,
334                   "current config",
335                   "multipath distribution model")
336           if (@distrib_model = "object")
337           begin
338               /*
339               ** Tables and stored procs are active
340               */
341               insert #active_types values ("T ")
342               insert #active_types values ("P ")
343           end
344           else if (@distrib_model = "filter")
345           begin
346               /*
347               ** Repfilters are active
348               */
349               insert #active_types values ("RF")
350           end
351           else
352           begin
353               if (@distrib_model != "connection")
354               begin
355                   /*
356                   ** This should never happen because the distribution
357                   ** model is validated during configuration.
358                   **
359                   ** An unexpected syntax error has occured.
360                   */
361                   raiserror 19805
362                   select @retval = 1
363                   goto done
364               end
365           end
366   
367           /*
368           ** set up the search arguments
369           */
370           if (@p1 is NOT NULL)
371           begin
372               if (@p1 = "all")
373               begin
374                   select @search_axis = "all"
375                   if (@p2 is not NULL)
376                   begin
377                       /*
378                       ** 19859 
379                       ** Argument '%1!' is not valid for '%2!'.
380                       */
381                       raiserror 19859, @p2, "all"
382                       select @retval = 1
383                       goto done
384                   end
385                   select @explicit_all = 1
386               end
387               else if (@p1 = "path")
388               begin
389                   select @search_axis = "path"
390                   if (@p2 is NOT NULL)
391                   begin
392                       if (@p2 = "default")
393                       begin
394                           raiserror 18291, @p2
395                           select @retval = 1
396                           goto done
397                       end
398   
399                       if exists (select 1 from sysattributes where
400                                   class = @MRP_CLASS and
401                                   attribute = 22 and
402                                   char_value = @p2)
403                       begin
404                           select @search_name = @p2
405                       end
406                       else
407                       begin
408                           /*
409                           ** 18118
410                           ** Object '%1!' does not exist in this database.
411                           */
412                           raiserror 18118, @p2
413                           return (1)
414                       end
415                   end
416                   else
417                   begin
418                       select @search_name = '%'
419                   end
420               end
421               else
422               begin
423                   /*
424                   ** the search_axis is binding, set the
425                   **  qualifier and limit to wildcard
426                   */
427                   select @search_axis = "binding"
428                   select @search_qual = '%'
429                   select @search_omin = 1
430                   select @search_omax = 2147483647
431                   if (@p1 is NOT NULL)
432                   begin
433                       /*
434                       ** lets make sure we have a qualified type 
435                       */
436                       if (lower(@p1) = "table")
437                       begin
438                           select @search_qual = "T "
439                       end
440                       else if (lower(@p1) = "sproc")
441                       begin
442                           select @search_qual = "P "
443                       end
444                       else if (lower(@p1) = "filter")
445                       begin
446                           select @search_qual = "RF"
447                       end
448                       else
449                       begin
450                           print "Unknown type %1!", @p1
451                           return 1
452                       end
453   
454                       /*
455                       ** if we are looking for a specific object
456                       **  grab the name for the search limit
457                       */
458                       if (@p2 is NOT NULL)
459                       begin
460                           select @bnd_obj = object_id(@p2)
461                           if (@bnd_obj is NULL)
462                           begin
463                               if (@search_qual = "T ")
464                               begin
465                                   /*
466                                   ** 18102
467                                   ** Table '%1!' does not exist
468                                   ** in this database.
469                                   */
470                                   raiserror 18102, @p2
471                                   select @retval = 1
472                               end
473                               else if (@search_qual = "P ")
474                               begin
475                                   select @bnd_obj =
476                                       object_id("sybsystemprocs.." + @p2)
477                                   if (@bnd_obj is NULL)
478                                   begin
479                                       /*
480                                       ** 18107
481                                       ** Stored procedure '%1!' 
482                                       ** does not exist in this
483                                       ** database.
484                                       */
485                                       raiserror 18107, @p2
486                                       select @retval = 1
487                                   end
488                                   else
489                                   begin
490                                       /*
491                                       ** found a system sproc
492                                       ** we have to jump past
493                                       ** the return
494                                       */
495                                       goto set_limits
496                                   end
497                               end
498                               else
499                               begin
500                                   /*
501                                   ** 18118
502                                   ** Object '%1!' does not exist
503                                   ** in this database.
504                                   */
505                                   raiserror 18118, @p2
506                                   select @retval = 1
507                               end
508                               return 1
509                           end
510                           else
511                           begin
512                               /*
513                               ** Ensure that the found object is of 
514                               ** the desired type
515                               */
516                               if ((@search_qual = "T ") and
517                                           (select count(*) from sysobjects where
518                                               name = @p2 and
519                                               type = "U") = 0)
520                               begin
521                                   /*
522                                   ** 18102
523                                   ** Table '%1!' does not exist
524                                   ** in this database.
525                                   */
526                                   raiserror 18102, @p2
527                                   return (1)
528                               end
529                               else if ((@search_qual = "P ") and
530                                           (select count(*) from sysobjects where
531                                               name = @p2 and
532                                               type = "P") = 0)
533                               begin
534                                   /*
535                                   ** 18107
536                                   ** Stored procedure '%1!' 
537                                   ** does not exist in this
538                                   ** database.
539                                   */
540                                   raiserror 18107, @p2
541                                   return (1)
542                               end
543                           end
544                           /*
545                           ** set the object limits to the object
546                           ** id so we only find the matching object
547                           ** when we search
548                           */
549   set_limits: select @search_omin = @bnd_obj
550                           select @search_omax = @bnd_obj
551                       end
552                   end
553               end
554           end
555   
556           /*
557           ** if the search_axis is binding (or all) we'll get the MRP
558           ** information from an object perspective
559           */
560           if (@search_axis = "binding" OR @search_axis = "all")
561           begin
562               /*
563               ** The first level cursor gets the object id and type, and 
564               ** the path id to which the object is bound for each matching
565               ** object in the database
566               */
567               declare binding_for_obj_id_cur cursor
568               for select object, object_type, object_info1 from sysattributes
569               where class = @MRP_CLASS
570                   and attribute in (23, 24)
571                   and object_type like @search_qual
572                   and (object >= @search_omin and object <= @search_omax)
573               for read only
574   
575               /*
576               ** Create separate temp tables to hold binding information
577               ** for active and inactive types.
578               */
579               create table #paths_for_active_bindings(Binding varchar(255),
580                   Type char(2),
581                   Path varchar(255))
582   
583               create table #paths_for_inactive_bindings(Binding varchar(255),
584                   Type char(2),
585                   Path varchar(255))
586   
587               open binding_for_obj_id_cur
588               while (1 = 1)
589               begin
590                   select @bnd_obj = 0
591                   select @obj_name = NULL
592                   select @lpath_id = 0
593                   select @lpath_name = NULL
594                   select @atype = NULL
595   
596                   fetch binding_for_obj_id_cur into
597                       @bnd_obj, @atype, @lpath_id
598   
599                   if (@@sqlstatus != 0)
600                   begin
601                       break
602                   end
603   
604                   /*
605                   ** locate the object name from the object id
606                   ** first try and find it in the local database
607                   ** if it is not there then it may be a system
608                   ** stored procedure so we need to check in
609                   ** sybsystemprocs..sysobjects (note that we
610                   ** limit the search in this case to sprocs)
611                   */
612                   select @obj_name = user_name(uid) + "." + name
613                   from sysobjects where id = @bnd_obj
614                   if (@obj_name is NULL)
615                   begin
616                       /*
617                       ** the object may be a system stored proc so we
618                       ** need to check sybsystemprocs..sysobjects as
619                       ** well (we can however narrow the search there
620                       ** to only look for procs)
621                       */
622                       select @obj_name = name from
623                           sybsystemprocs..sysobjects
624                       where id = @bnd_obj
625                           and type = 'P'
626   
627                       /*
628                       ** if there is still no object name we have
629                       ** to raise an error
630                       */
631                       if (@obj_name is NULL)
632                       begin
633                           raiserror 19805
634                           return 1
635                       end
636                   end
637   
638                   /*
639                   ** get the path name
640                   */
641                   select @lpath_name = char_value from sysattributes
642                   where class = @MRP_CLASS
643                       and attribute = 22
644                       and object_info1 = @lpath_id
645   
646                   if (@lpath_name is NULL)
647                   begin
648                       /*
649                       ** 19805
650                       ** An unexpected syntax error has occured.
651                       */
652                       raiserror 19805
653                       return 1
654                   end
655   
656                   /*
657                   ** Insert the binding information into the appropriate
658                   ** table based on whether the type is active or not.
659                   */
660                   if @atype in (select obj_type from #active_types)
661                   begin
662                       insert #paths_for_active_bindings
663                       values (@obj_name,
664                           @atype,
665                           @lpath_name)
666                   end
667                   else
668                   begin
669                       insert #paths_for_inactive_bindings
670                       values (@obj_name,
671                           @atype,
672                           @lpath_name)
673                   end
674               end
675   
676               /*
677               ** Display the bindings and clean up the tables.
678               **
679               ** The contents of the display depends on whether the 'list'
680               ** action was further qualified (i.e. more parameters):
681               **
682               ** - if list was unqualified then we will only list the active
683               **   bindings based on the current distribution model. If there
684               **   are no active bindings then an empty active binding table
685               **   will be displayed.
686               **
687               ** - if qualifier is 'all' then we will show both active and
688               **   inactive bindings, an empty table will be displayed if 
689               **   there are no inactive/active bindings.
690               **
691               ** - if the qualifier is a specific object type (i.e. object,
692               **   sproc or filter) then either the active or inactive table
693               **   will be displayed as appropriate, an empty table will be
694               **   displayed if there are no bindings of the specified type.
695               */
696               if ((@search_axis = "all") OR
697                       ((@search_axis != "all") AND
698                           (@search_qual in (select obj_type from #active_types))))
699               begin
700                   exec sp_autoformat @fulltabname =
701                       #paths_for_active_bindings,
702                       @selectlist = "Binding,Type,Path",
703                       @orderby = "order by Binding,Path"
704               end
705   
706               if ((@search_axis = "all" AND @explicit_all = 1) OR
707                       ((@search_axis != "all") AND
708                           (@search_qual not in (select obj_type from #active_types))))
709               begin
710                   exec sp_autoformat @fulltabname =
711                       #paths_for_inactive_bindings,
712                       @selectlist =
713                       "'Inactive Binding' = Binding, Type, Path",
714                       @orderby = "order by Binding,Path"
715               end
716   
717               drop table #paths_for_active_bindings
718               drop table #paths_for_inactive_bindings
719   
720               /*
721               ** clean up the cursor
722               */
723               close binding_for_obj_id_cur
724               deallocate binding_for_obj_id_cur
725           end
726   
727           /* 
728           ** if the search_axis is 'all' put a blank line between bindings
729           ** and paths
730           */
731           if (@search_axis = "all")
732           begin
733               print
734           end
735   
736           /*
737           ** if this is a "path" axis search we want to list the objects 
738           ** bound to each path and the active status
739           */
740           if (@search_axis = "path")
741           begin
742               /*
743               ** search for all paths in sysattributes 
744               */
745               declare l1_cursor cursor
746               for select object_info1, char_value from sysattributes
747               where class = @MRP_CLASS and attribute = 22
748                   and char_value like @search_name
749               for read only
750   
751               create table #bindings_for_paths(Path varchar(255),
752                   Type char(2),
753                   Binding varchar(255),
754                   Active varchar(3))
755   
756               open l1_cursor
757   
758               while (1 = 1)
759               begin
760                   fetch l1_cursor into @lpath_id, @lpath_name
761   
762                   if (@@sqlstatus != 0)
763                   begin
764                       break
765                   end
766   
767                   declare l2_cursor cursor
768                   for select object, object_type from sysattributes
769                   where class = @MRP_CLASS
770                       and object_info1 = @lpath_id
771                       and attribute in (23, 24)
772                   for read only
773   
774                   open l2_cursor
775                   while (1 = 1)
776                   begin
777                       select @obj_name = NULL
778                       fetch l2_cursor into @bnd_obj, @atype
779                       if (@@sqlstatus != 0)
780                       begin
781                           break
782                       end
783   
784                       /*
785                       ** locate the object name from the object id
786                       ** first try and find it in the local database
787                       ** if it is not there then it may be a system
788                       ** stored procedure so we need to check in
789                       ** sybsystemprocs..sysobjects (note that we
790                       ** limit the search in this case to sprocs)
791                       */
792                       select @obj_name = user_name(uid) + "." + name
793                       from sysobjects where id = @bnd_obj
794                       if (@obj_name is NULL)
795                       begin
796                           select @obj_name = name from
797                               sybsystemprocs..sysobjects
798                           where id = @bnd_obj
799                               and type = 'P'
800   
801                           if (@obj_name is NULL)
802                           begin
803                               raiserror 19805
804                               return 1
805                           end
806                       end
807   
808                       if @atype in (select obj_type from #active_types)
809                       begin
810                           select @is_active = "Yes"
811                       end
812                       else
813                       begin
814                           select @is_active = "No"
815                       end
816   
817   
818                       insert #bindings_for_paths
819                       values (@lpath_name, @atype, @obj_name, @is_active)
820                   end
821                   close l2_cursor
822                   deallocate l2_cursor
823               end
824               exec sp_autoformat @fulltabname = #bindings_for_paths,
825                   @selectlist = "Path,Type,Binding,Active",
826                   @orderby = "order by Path,Binding"
827   
828               drop table #bindings_for_paths
829   
830               close l1_cursor
831               deallocate l1_cursor
832           end
833   
834           /*
835           ** 
836           */
837           if (@search_axis = "path" OR @search_axis = "all")
838           begin
839               declare @ppath_elem int
840               /*
841               ** cursor to retrieve logical path ids
842               ** this cursor picks up both strict logical and 
843               ** physical logical path ids, they are handled 
844               ** separately in the while loop
845               */
846               declare lpath_id_curs cursor
847               for select object_info1, char_value from sysattributes
848               where class = @MRP_CLASS and attribute = 22
849                   and char_value like @search_name
850               for read only
851   
852               /*
853               ** Create temp tables for physical path -> RS destinations
854               ** and logical -> physical paths.
855               ** Note that the physcial path to RS destination has the 
856               ** is_default column that is really only used as a means
857               ** of ensuring that the default path is displayed after
858               ** all named paths. 
859               */
860               create table #dest_for_ppath(is_default int,
861                   ppath varchar(255),
862                   dest varchar(255))
863               create table #ppaths_for_lpaths(lpath varchar(255),
864                   ppath varchar(255))
865   
866               open lpath_id_curs
867               while (1 = 1)
868               begin
869                   fetch lpath_id_curs into @lpath_id, @lpath_name
870                   if (@@sqlstatus != 0)
871                   begin
872                       break
873                   end
874   
875                   /*
876                   ** handle strict logical paths
877                   */
878                   if ((select int_value from sysattributes
879                               where class = @MRP_CLASS and attribute = 21
880                                   and object_info1 = @lpath_id) = 1)
881                   begin
882                       /*
883                       ** cursor to get the set of physical path
884                       ** elements contained in this logical path
885                       ** (we need a cursor because there may be
886                       ** multiple physical paths)
887                       */
888                       declare pelem_for_lpath cursor
889                       for select object_info2 from sysattributes
890                       where class = @MRP_CLASS and attribute = 20
891                           and object_info1 = @lpath_id
892   
893                       open pelem_for_lpath
894                       while (1 = 1)
895                       begin
896                           fetch pelem_for_lpath into @ppath_elem
897                           if (@@sqlstatus != 0)
898                           begin
899                               break
900                           end
901   
902                           /*
903                           ** cursor to get the path id for the
904                           ** physical logical path that owns this
905                           ** path element (the path element may
906                           ** be in multiple logical paths)
907                           */
908                           declare pp_id_for_ppath_elem cursor
909                           for select object_info1 from sysattributes
910                           where class = @MRP_CLASS and attribute = 20
911                               and object_info2 = @ppath_elem
912   
913                           open pp_id_for_ppath_elem
914                           while (1 = 1)
915                           begin
916                               fetch pp_id_for_ppath_elem into @ppath_id
917                               if (@@sqlstatus != 0)
918                               begin
919                                   break
920                               end
921   
922                               if ((select int_value from sysattributes
923                                           where class = @MRP_CLASS and attribute = 21
924                                               and object_info1 = @ppath_id) != 0)
925                               begin
926                                   /*
927                                   ** skip strict logical paths
928                                   */
929                                   continue
930                               end
931                               select @ppath_name = char_value
932                               from sysattributes
933                               where class = @MRP_CLASS
934                                   and attribute = 22
935                                   and object_info1 = @ppath_id
936   
937                               insert #ppaths_for_lpaths
938                               values (@lpath_name,
939                                   @ppath_name)
940                               /*
941                               ** if the physical path is not
942                               ** already in the path->dest 
943                               ** table get the RS name for
944                               ** the physical path
945                               */
946                               if ((select count(*) from #dest_for_ppath
947                                           where ppath = @ppath_name) = 0)
948                               begin
949                                   /*
950                                   ** get the name of the 
951                                   ** RepServer for this  
952                                   ** physical path
953                                   */
954                                   select @rs_name = char_value
955                                   from sysattributes
956                                   where class = 11
957                                       and attribute = 0
958                                       and object_info1
959                                       = (select object_info2
960                                           from sysattributes
961                                           where class = @MRP_CLASS
962                                               and attribute = 0
963                                               and object_info1 =
964                                                   (select object_info2
965                                                   from sysattributes
966                                                   where class = @MRP_CLASS
967                                                       and attribute = 20
968                                                       and object_info1 = @ppath_id))
969   
970                                   insert #dest_for_ppath
971                                   values (0, @ppath_name,
972                                       @rs_name)
973                               end
974                               break
975                           end
976                           close pp_id_for_ppath_elem
977                           deallocate pp_id_for_ppath_elem
978                       end
979                       close pelem_for_lpath
980                       deallocate pelem_for_lpath
981                   end
982                   else
983                   begin
984                       /*
985                       ** if the path name already exists in the 
986                       ** table we can just skip it (this may occur
987                       ** if we have already populated the table 
988                       ** with elements of logical paths)
989                       */
990                       if ((select count(*) from #dest_for_ppath
991                                   where ppath = @lpath_name) > 0)
992                       begin
993                           continue
994                       end
995   
996                       /*
997                       ** get the name of the RepServer for this  
998                       ** physical path
999                       */
1000                      select @rs_name = char_value from sysattributes
1001                      where class = 11
1002                          and attribute = 0
1003                          and object_info1 =
1004                              (select object_info2
1005                              from sysattributes
1006                              where class = @MRP_CLASS
1007                                  and attribute = 0
1008                                  and object_info1 =
1009                                      (select object_info2 from
1010                                          sysattributes
1011                                      where class = @MRP_CLASS
1012                                          and attribute = 20
1013                                          and object_info1 = @lpath_id))
1014  
1015                      insert #dest_for_ppath values (0, @lpath_name,
1016                          @rs_name)
1017  
1018                  end
1019              end
1020              close lpath_id_curs
1021              deallocate lpath_id_curs
1022  
1023              if ((select count(*) from #ppaths_for_lpaths) > 0)
1024              begin
1025                  exec sp_autoformat @fulltabname = #ppaths_for_lpaths,
1026                      @selectlist = "'Logical Path'=lpath,
1027  					'Physical Path'=ppath",
1028                      @orderby = "order by lpath"
1029              end
1030  
1031              select @rs_name = char_value from sysattributes
1032              where class = 11
1033                  and attribute = 0
1034                  and object_info1 = 0
1035  
1036              /*
1037              ** The default path gets an is_default of 1 so it is always the
1038              ** last shown after all named paths in the destination list
1039              */
1040              insert #dest_for_ppath values (1, "default", @rs_name)
1041  
1042              exec sp_autoformat @fulltabname = #dest_for_ppath,
1043                  @selectlist = "'Physical Path'=ppath,'Destination'=dest",
1044                  @orderby = "order by is_default, ppath"
1045  
1046              drop table #ppaths_for_lpaths
1047              drop table #dest_for_ppath
1048          end
1049          /* Clean up the active types table */
1050          drop table #active_types
1051  
1052          select @retval = 0
1053          goto done
1054      end /* action = 'list' */
1055  
1056      /*
1057      ** All actions other than 'list' go through the replication path builtin.
1058      ** We will do some simple parameter checking here (basically ensuring that
1059      ** the count for each action is reasonable) before passing on to the builtin.
1060      */
1061      select @retval = 1 /* set default return value to 1 */
1062  
1063      if (@action = 'add')
1064      begin
1065          /*
1066          ** Add a path (physical or logical) definition to the MRP system.
1067          ** There are two distinct syntaxes depending on whether a physical
1068          ** or logical path is being added. 
1069          ** 
1070          **  Var.          physical        logical
1071          ** ------------  --------------  ---------------------------
1072          **  @p1          avatar name     'logical' keyword modifier
1073          **  @p2          rs servername   logical path name
1074          **  @p3          rs_username     avatar name
1075          **  @p4          rs_password     
1076          **  @rs_encpwd   encrypted pwd   
1077          ** 
1078          ** Errors raised by 'add'
1079          **
1080          **	error	 description
1081          **	-----	-------------------------------------------------------
1082          **	18481	'%1!' is an invalid configuration parameter.
1083          **	19384	The '%1!' parameter is required with '%2!' parameter.
1084          **	19415	The '%1!' parameter is required for the '%2!' command.
1085          */
1086          if (@p1 = 'logical')
1087          begin
1088              /*
1089              ** 'add', 'logical' requires 2 additional positional parameters
1090              */
1091              if (@p2 is NULL)
1092              begin
1093                  raiserror 19384, 'path name', 'logical'
1094                  select @retval = 1
1095                  goto done
1096              end
1097              else
1098              begin
1099                  if (@p2 = "default")
1100                  begin
1101                      raiserror 18291, @p2
1102                      select @retval = 1
1103                      goto done
1104                  end
1105              end
1106              if (@p3 is NULL)
1107              begin
1108                  raiserror 19384, 'avatar name', 'logical'
1109                  select @retval = 1
1110                  goto done
1111              end
1112              else
1113              begin
1114                  if (@p3 = "default")
1115                  begin
1116                      raiserror 18291, @p3
1117                      select @retval = 1
1118                      goto done
1119                  end
1120              end
1121              if (@p4 is not NULL)
1122              begin
1123                  raiserror 18481, @p4
1124                  select @retval = 1
1125                  goto done
1126              end
1127              if (@rs_encpwd is not NULL)
1128              begin
1129                  raiserror 18481, @rs_encpwd
1130                  select @retval = 1
1131                  goto usage
1132              end
1133              select @report = "Path '" + @p3 + "' added to logical path '" + @p2 + "'."
1134          end
1135          else
1136          begin
1137              /* 
1138              ** 'add' for physical requires all 4 positional parameters
1139              ** except for @p4 which may be null provided that 
1140              ** @rs_encpwd is not null.
1141              */
1142              if (@p1 is NULL)
1143              begin
1144                  raiserror 19415, 'Path Name', @action
1145                  select @retval = 1
1146                  goto done
1147              end
1148              else
1149              begin
1150                  if (@p1 = "default")
1151                  begin
1152                      raiserror 18291, @p1
1153                      select @retval = 1
1154                      goto done
1155                  end
1156              end
1157              if (@p2 is NULL)
1158              begin
1159                  raiserror 19415, 'RepServer name', @action
1160                  select @retval = 1
1161                  goto done
1162              end
1163              if (@p3 is NULL)
1164              begin
1165                  raiserror 19415, 'RepServer user name', @action
1166                  select @retval = 1
1167                  goto done
1168              end
1169              if ((@p4 is NULL) and (@rs_encpwd is NULL))
1170              begin
1171                  raiserror 19415, 'RepServer password', @action
1172                  select @retval = 1
1173                  goto done
1174              end
1175              if ((@p4 is not NULL) and (@rs_encpwd is not NULL))
1176              begin
1177                  raiserror 18481, @rs_encpwd
1178                  select @retval = 1
1179                  goto usage
1180              end
1181              select @report = "Path '" + @p1 + "' added."
1182  
1183              /* Check RS name length. */
1184              select @maxlen = length from master.dbo.syscolumns
1185              where id = object_id("master.dbo.sysservers")
1186                  and name = "srvnetname"
1187  
1188              if (datalength(@p2) > @maxlen)
1189              begin
1190                  /*
1191                  ** 17240, "'%1!' is not a valid name."
1192                  */
1193                  raiserror 17240, @p2
1194                  return (1)
1195              end
1196  
1197              if (@rs_encpwd is NULL)
1198              begin
1199                  /*
1200                  ** The rs password (@p4) needs to be encrypted.
1201                  */
1202                  select @rs_encpwd = internal_encrypt(@p4,
1203                          @STATIC_KEY, 0)
1204                  if (@rs_encpwd is NULL)
1205                  begin
1206                      /*
1207                      ** 17067 "Unable to encrypt password for '%1!'.
1208                      ** See prior error message for reason."
1209                      */
1210                      raiserror 17067, @dbname
1211                      select @retval = 1
1212                      goto done
1213                  end
1214                  select @p4 = NULL
1215              end
1216  
1217              select @status = replication_path_admin(@dbid, @action,
1218                      @p1, @p2, @p3,
1219                      @rs_encpwd)
1220              if (@status != 1)
1221              begin
1222                  raiserror 18409, "replication_path_admin"
1223                  select @retval = 1
1224                  goto done
1225              end
1226              goto logtran
1227          end
1228      end
1229      else if (@action = 'drop')
1230      begin
1231          /*
1232          ** Drop a path from the MRP environment. Similar to 'add', the 'drop'
1233          ** action has both physical and logical syntax
1234          **
1235          **  Var.   drop (physical)	 drop logical
1236          ** -----  --------------------- ---------------------------
1237          **  @p1	  avatar (path name)	logical
1238          **  @p2   		logical path name
1239          **  @p3   		avatar (optional)
1240          **  @p4   		
1241          **
1242          ** Errors with 'drop' action
1243          **	error	description
1244          **	-----	-------------------------------------------------------
1245          **	19384	The '%1!' parameter is required with '%2!' parameter.
1246          **	19415	The '%1!' parameter is required for the '%2!' command.
1247          **	19805	An unexpected syntax error has occured.	
1248          */
1249          if (@p1 is NULL)
1250          begin
1251              raiserror 11220
1252              select @retval = 1
1253              goto done
1254          end
1255          else
1256          begin
1257              if (@p1 = 'logical')
1258              begin
1259                  if (@p2 is NULL)
1260                  begin
1261                      raiserror 19384, "path name", "logical"
1262                      select @retval = 1
1263                      goto done
1264                  end
1265                  else
1266                  begin
1267                      if (@p2 = "default")
1268                      begin
1269                          raiserror 18291, @p2
1270                          select @retval = 1
1271                          goto done
1272                      end
1273                  end
1274                  if ((@p4 is NOT NULL) or (@rs_encpwd is not NULL))
1275                  begin
1276                      /*
1277                      ** 19805
1278                      ** An unexpected syntax error has occured.
1279                      */
1280                      raiserror 19805
1281                      select @retval = 1
1282                      goto done
1283                  end
1284                  if (@p3 is NULL)
1285                  begin
1286                      select @report = "Logical path '" + @p2 +
1287                          "' dropped."
1288                  end
1289                  else
1290                  begin
1291                      if (@p3 = "default")
1292                      begin
1293                          raiserror 18291, @p3
1294                          select @retval = 1
1295                          goto done
1296                      end
1297                      select @report = "Path '" + @p3 +
1298                          "' dropped from logical path '" +
1299                          @p2 + "'."
1300                  end
1301              end
1302              else
1303              begin
1304                  if (@p1 is NULL)
1305                  begin
1306                      raiserror 19415, "path name", @action
1307                      select @retval = 1
1308                      goto done
1309                  end
1310                  else
1311                  begin
1312                      if (@p1 = "default")
1313                      begin
1314                          raiserror 18291, @p1
1315                          select @retval = 1
1316                          goto done
1317                      end
1318                  end
1319                  if (@p2 is not NULL or /* shouldn't be other parameters */
1320                          @p3 is not NULL or
1321                          @p4 is not NULL or
1322                          @rs_encpwd is not NULL)
1323                  begin
1324                      /*
1325                      ** 19805
1326                      ** An unexpected syntax error has occured.
1327                      */
1328                      raiserror 19805
1329                      select @retval = 1
1330                      goto done
1331                  end
1332                  select @report = "Path '" + @p1 + "' dropped."
1333              end
1334          end
1335      end
1336      else if (@action = 'config')
1337      begin
1338          /* 
1339          ** Update or display the value for a given configuration property. 
1340          ** Note that most of the low level config settings for rep agent are
1341          ** set through sp_config_rep_agent, but there are a couple properties
1342          ** that either must be accessible via the sp_replication_path interface
1343          ** or are path specific which are supported through this interface. 
1344          **
1345          **  Var.          config
1346          ** ------------  -----------------------------
1347          **  @p1          physical path name (optional)
1348          **  @p2          config name  (optional)
1349          **  @p3          config value (optional)
1350          **  @p4          
1351          **  @rs_encpwd   encrypted password
1352          **
1353          ** Errors in config
1354          **	error	description
1355          **	-----	-------------------------------------------------------
1356          **	19415	The '%1!' parameter is required for the '%2!' command.
1357          **	19805	An unexpected syntax error has occured.
1358          */
1359  
1360          /*
1361          ** @p4 is never valid
1362          */
1363          if (@p4 is NOT NULL)
1364          begin
1365              /*
1366              ** 19805
1367              ** An unexpected syntax error has occured.
1368              */
1369              raiserror 19805
1370              select @retval = 1
1371              goto done
1372          end
1373  
1374          /*
1375          ** if @p3 and @rs_encpwd are NULL we are going to display config values 
1376          ** rather than change/set them
1377          */
1378          if ((@p3 is NULL) and (@rs_encpwd is NULL))
1379          begin
1380              declare @path_qual varchar(255) /* path name to search on    */
1381              declare @path_name varchar(255) /* matching path name        */
1382              declare @path_id int /* path id for matching path */
1383              declare @rscon_id int /* RS connection id          */
1384  
1385              /* 
1386              ** Valid config names are:
1387              ** - NULL which defaults to 'rs username'
1388              ** - 'rs username'
1389              ** - 'rs password' though nothing is displayed
1390              */
1391              if ((@p2 is NOT NULL) and
1392                      (@p2 not in ('rs username', 'rs password')))
1393              begin
1394                  /* 18481, '%1!' is an invalid configuration 
1395                  ** parameter.
1396                  */
1397                  raiserror 18481, @p2
1398                  select @retval = 1
1399                  goto done
1400              end
1401  
1402              if (@p1 is NOT NULL)
1403              begin
1404                  select @path_qual = @p1
1405              end
1406              else
1407              begin
1408                  select @path_qual = '%'
1409              end
1410  
1411              /*
1412              ** declare a cursor to get all paths matching the given
1413              ** name (if provided)
1414              */
1415              declare lpath_id_curs cursor
1416              for select object_info1, char_value from sysattributes
1417              where class = @MRP_CLASS and attribute = 22
1418                  and char_value like @path_qual
1419              for read only
1420  
1421              /*
1422              ** create a temp table to hold the config information
1423              ** for the path
1424              */
1425              create table #config_for_path
1426              (ppath varchar(255) not null,
1427                  rsname varchar(30) not null,
1428                  cfg_name varchar(30) not null,
1429                  cfg_dft varchar(255) default "n/a",
1430                  cfg_cfg varchar(255) default "n/a",
1431                  cfg_run varchar(255) default "n/a")
1432  
1433              open lpath_id_curs
1434              while ((@p2 is NULL) or (@p2 = 'rs username'))
1435              begin
1436                  fetch lpath_id_curs into @path_id, @path_name
1437                  if (@@sqlstatus != 0)
1438                  begin
1439                      break
1440                  end
1441                  if ((select int_value from sysattributes
1442                              where class = @MRP_CLASS and attribute = 21
1443                                  and object_info1 = @path_id) = 0)
1444                  begin
1445                      select @rscon_id = att1.object_info2
1446                      from sysattributes att1, sysattributes att2
1447                      where att1.class = @MRP_CLASS and
1448                          att2.class = @MRP_CLASS and
1449                          att1.attribute = 0 and att2.attribute = 20
1450                          and att2.object_info1 = @path_id
1451                          and att1.object_info1 = att2.object_info2
1452  
1453                      insert #config_for_path
1454                      select @path_name,
1455                              (select char_value from sysattributes
1456                              where class = @REPAGENT_CLASS and
1457                                  attribute = 0 and object_info1 = @rscon_id),
1458                          'rs username', 'n/a',
1459                              (select char_value from sysattributes
1460                              where class = @REPAGENT_CLASS and
1461                                  attribute = 1 and object_info1 = @rscon_id),
1462                          'n/a'
1463                  end
1464              end
1465              exec sp_autoformat
1466                  @fulltabname = "#config_for_path",
1467                  @selectlist = "'Path'=ppath,
1468  				'Rep Server'=rsname,
1469  				'Parameter Name'=cfg_name,
1470  				'Default Value'=cfg_dft,
1471  				'Config Value'=cfg_cfg,
1472  				'Run Value'=cfg_run",
1473                  @orderby = "order by cfg_name"
1474              drop table #config_for_path
1475              return (0)
1476          end
1477          else /* @p3 is not NULL or @rs_encpwd is not NULL */
1478          begin
1479              /*
1480              ** if we get here then we are going to change/set a config
1481              ** value - in this case the path name and config name are
1482              ** not optional, we must have them to proceed
1483              */
1484              if (@p1 is NULL)
1485              begin
1486                  raiserror 19415, 'path name', @action
1487                  select @retval = 1
1488                  goto done
1489              end
1490              else
1491              begin
1492                  if (@p1 = "default")
1493                  begin
1494                      raiserror 18291, @p1
1495                      goto done
1496                  end
1497              end
1498              if (@p2 is NULL)
1499              begin
1500                  raiserror 19415, 'config name', @action
1501                  select @retval = 1
1502                  goto done
1503              end
1504              if ((@p3 is not NULL) and (@rs_encpwd is not NULL))
1505              begin
1506                  /*
1507                  ** 19805
1508                  ** An unexpected syntax error has occured.
1509                  */
1510                  raiserror 19805
1511                  goto usage
1512              end
1513  
1514              if (@p2 = 'rs password')
1515              begin
1516                  if (@rs_encpwd is NULL)
1517                  begin
1518                      /*
1519                      ** The rs password (@p3) needs to be encrypted.
1520                      */
1521                      select @rs_encpwd = internal_encrypt(@p3,
1522                              @STATIC_KEY, 0)
1523                      if (@rs_encpwd is NULL)
1524                      begin
1525                          /*
1526                          ** 17067 "Unable to encrypt password 
1527                          ** for '%1!'.
1528                          ** See prior error message for reason."
1529                          */
1530                          raiserror 17067, @dbname
1531                      end
1532                      select @p3 = NULL
1533                  end
1534  
1535                  select @status = replication_path_admin(@dbid, @action,
1536                          @p1, @p2,
1537                          @rs_encpwd,
1538                          @p4)
1539                  if (@status != 1)
1540                  begin
1541                      raiserror 18409, "replication_path_admin"
1542                      select @retval = 1
1543                      goto done
1544                  end
1545                  goto logtran
1546              end
1547          end
1548      end
1549      else if ((@action = 'bind') or (@action = 'unbind'))
1550      begin
1551          /*
1552          ** Create or remove an association between an object and a path. 
1553          ** 
1554          **  Var.          bind	               unbind
1555          ** ------------  ------------------  ---------------
1556          **  @p1          object type         object type
1557          **  @p2          object              object
1558          **  @p3          Log. Path           Log. Path|'all'|""
1559          **  @p4                      
1560          **  @rs_encpwd               
1561          **
1562          ** The object type must be one of the supported types, currently 
1563          ** one of: table|sproc
1564          **
1565          ** For bind all 3 parameters are mandatory. For unbind, the @p3 may
1566          ** be omitted.
1567          **
1568          **	error	description
1569          **	-----	-------------------------------------------------------
1570          **	19415	The '%1!' parameter is required for the '%2!' command.
1571          **	19805	An unexpected syntax error has occured.
1572          */
1573  
1574          /*
1575          ** parts for sp_namecrack on passed in object name 
1576          */
1577          declare @long_name varchar(1023)
1578          declare @site_part varchar(255)
1579          declare @db_part varchar(255)
1580          declare @owner_part varchar(255)
1581          declare @name_part varchar(255)
1582  
1583          declare @sysobj_type varchar(2)
1584  
1585          declare @obj_name_match varchar(255)
1586          declare @usr_name_match varchar(255)
1587  
1588          declare @num_matches int
1589  
1590          declare @curr_path int
1591          declare @curr_user_id int
1592          declare @curr_obj_id int
1593          declare @curr_user_name varchar(255)
1594          declare @curr_obj_name varchar(255)
1595          declare @qualified_name varchar(511)
1596  
1597          declare @is_bound int
1598          declare @issystemproc int
1599  
1600          select @issystemproc = 0
1601  
1602          /*
1603          ** Define the set of supported types that can be bound/unbound
1604          ** Note that we need to add the "path" type for 'unbind'
1605          */
1606          create table #supported_types(obj_type varchar(32) not null unique)
1607          insert #supported_types values ("table")
1608          insert #supported_types values ("sproc")
1609          insert #supported_types values ("filter")
1610  
1611          if (@action = "unbind")
1612          begin
1613              insert #supported_types values ("path")
1614          end
1615  
1616          /*
1617          ** Both bind and unbind  require the first two parameters.
1618          ** @p1 should be a supported type.
1619          */
1620          if ((@p1 is NULL) or
1621                  ((select count(*) from #supported_types where obj_type = @p1) != 1)
1622                  and
1623                  (@p1 != 'path'))
1624          begin
1625              raiserror 19415, 'object type', @action
1626              select @retval = 1
1627              goto done
1628          end
1629  
1630          /*
1631          ** If the type is @p1 = 'path':
1632          ** 1) It is invalid with @action = 'bind'.
1633          ** 2) It is valid with @action = 'unbind' only if
1634          ** @p2 is not NULL and @p3 = 'all'. 
1635          ** Otherwise raise an error.
1636          */
1637          if (@p1 = 'path')
1638          begin
1639              if ((@action = 'bind') or
1640                      (@p2 is NULL or @p3 != 'all'))
1641  
1642              begin
1643                  raiserror 19530, @p1, @action, 'help'
1644                  select @retval = 1
1645                  goto done
1646              end
1647          end
1648  
1649          if (@p2 is NULL)
1650          begin
1651              raiserror 19415, 'object name', @action
1652              select @retval = 1
1653              goto done
1654          end
1655  
1656          /*
1657          ** 'bind' always requires @p3
1658          */
1659          if (@p3 is NULL)
1660          begin
1661              raiserror 19415, 'path name', @action
1662              select @retval = 1
1663              goto done
1664          end
1665          else
1666          begin
1667              if (@p3 = "default")
1668              begin
1669                  raiserror 18291, @p3
1670                  select @retval = 1
1671                  goto done
1672              end
1673          end
1674  
1675          /*
1676          ** @p4 and @rs_encpwd are never used by bind/unbind
1677          */
1678          if ((@p4 is NOT NULL) or (@rs_encpwd is not NULL))
1679          begin
1680              /*
1681              ** 19805
1682              ** An unexpected syntax error has occured.
1683              */
1684              raiserror 19805
1685              select @retval = 1
1686              goto done
1687          end
1688  
1689  
1690          /*
1691          ** get the pieces of the long name
1692          */
1693          select @long_name = @p2
1694  
1695          /*
1696          ** change '*' all wildcard character to '%' for our search
1697          */
1698          while (charindex('*', @long_name) > 0)
1699          begin
1700              select @long_name =
1701                  stuff(@long_name,
1702                      charindex('*', @long_name),
1703                      1, '%')
1704          end
1705  
1706          exec sp_namecrack @long_name,
1707              @site_part output,
1708              @db_part output,
1709              @owner_part output,
1710              @name_part output
1711  
1712          /* 
1713          ** Check if we are attempting to bind a system stored proc.
1714          */
1715          if ((@db_part = "sybsystemprocs" or @db_part = "master")
1716                  and substring(@name_part, 1, 3) = "sp_"
1717                  and user_id(@owner_part) = 1)
1718          begin
1719              select @issystemproc = 1
1720          end
1721  
1722          /*
1723          ** We do allow to bind system stored procedure. Any other object must be
1724          ** in the current database.
1725          */
1726          if (@db_part is NOT NULL and @db_part != db_name() and @issystemproc = 0)
1727          begin
1728              /*
1729              ** 17460, "Object must be in the current database."
1730              */
1731              raiserror 17460
1732              select @retval = 1
1733              goto done
1734          end
1735  
1736          /*
1737          ** if the object is a table or an sproc and there is a wildcard
1738          ** character ('%' or '*') in the object name then we need special
1739          ** handling
1740          */
1741          if ((@p1 in ('table', 'sproc')) AND
1742                  ((charindex('%', @p2) > 0) OR (charindex('*', @p2) > 0)))
1743          begin
1744              select @num_matches = 0
1745  
1746              /*
1747              ** get the path id from the path name
1748              */
1749              select @curr_path = object_info1 from sysattributes where
1750                  class = @MRP_CLASS and attribute = 22 and char_value = @p3
1751              if (@p3 is NULL or @curr_path is NULL or (@curr_path < 1))
1752              begin
1753                  if (@action != 'unbind' or @p3 != 'all')
1754                  begin
1755                      /*
1756                      ** The '%1!' parameter is required for the '%2!' command.
1757                      */
1758                      raiserror 19415, 'path name', @action
1759                      select @retval = 1
1760                      goto done
1761                  end
1762              end
1763              if (@owner_part is NULL)
1764              begin
1765                  select @owner_part = "dbo"
1766              end
1767  
1768  
1769              if (@p1 = "table")
1770              begin
1771                  select @sysobj_type = "U"
1772              end
1773              else
1774              begin
1775                  select @sysobj_type = "P"
1776              end
1777  
1778              /*
1779              ** outer cursor gets the uids for all user
1780              ** name like the passed in owner_part
1781              */
1782              declare currdb_matching_uids cursor
1783              for select uid, name from sysusers where
1784                  name like @owner_part
1785  
1786              /*
1787              ** main inner cursor gets the object names for all
1788              ** objects of the required type, owned by the
1789              ** given user, where the name is like the passed
1790              ** in name_part
1791              */
1792              declare currdb_matching_objects cursor
1793              for select id, name from sysobjects where
1794                  type = @sysobj_type and
1795                  uid = @curr_user_id and
1796                  name like @name_part
1797  
1798              /*
1799              ** the alternate inner cursor gets the object names for all
1800              ** system procs where the name is like the passed in name_part
1801              ** for the system procs we can also check the sysstat to make
1802              ** sure that the sproc is replicable (i.e. O_REPLICATED and
1803              ** O_PROC_SUBSCRIBABLE are set and O2_REP_LOG_SPROC is not)
1804              ** note that the additional checks on sysstat and sysstat2 are
1805              ** not enforced in the non-wildcard bind/unbind because the 
1806              ** built-in will report a meaningful error; in the wildcard 
1807              ** case a poorly chosen search could generate thousands of
1808              ** lines of error messages
1809              */
1810              declare sybsystemprocs_matching_sprocs cursor
1811              for select id, name from sybsystemprocs..sysobjects where
1812                  type = "P" and uid = 1 and
1813                  ((sysstat & 64) = 64) and
1814                  ((sysstat & 32768) = 32768) and
1815                  ((sysstat2 & 8388608) = 0) and
1816                  name like @name_part
1817  
1818              /*
1819              ** open the outer cursor and process through 
1820              ** matching uids
1821              */
1822              open currdb_matching_uids
1823              while (1 = 1)
1824              begin
1825                  fetch currdb_matching_uids into
1826                      @curr_user_id, @curr_user_name
1827                  if (@@sqlstatus != 0)
1828                  begin
1829                      break
1830                  end
1831  
1832                  /*
1833                  ** open the main inner cursor and process
1834                  ** through matching object names
1835                  */
1836                  open currdb_matching_objects
1837                  while (1 = 1)
1838                  begin
1839                      fetch currdb_matching_objects into
1840                          @curr_obj_id, @curr_obj_name
1841                      if (@@sqlstatus != 0)
1842                      begin
1843                          break
1844                      end
1845  
1846                      select @qualified_name =
1847                          @curr_user_name + "." + @curr_obj_name
1848  
1849                      if (@p3 != 'all')
1850                      begin
1851                          /*
1852                          ** get the number of bindings that match 
1853                          ** the search criterion
1854                          */
1855                          select @is_bound = count(*) from sysattributes
1856                          where class = @MRP_CLASS and
1857                              attribute = 23 and
1858                              object_info1 = @curr_path and
1859                              object = @curr_obj_id
1860  
1861                          /*
1862                          ** sanity check, we should never have more
1863                          ** than one match
1864                          */
1865                          if (@is_bound > 1)
1866                          begin
1867                              raiserror 19908, @qualified_name
1868                              select @retval = 1
1869                              goto done
1870                          end
1871                      end
1872  
1873                      /*
1874                      ** there are two cases that we check for with
1875                      ** wildcards:
1876                      ** - if action is bind and the object is
1877                      **   already bound
1878                      ** - id action is unbind and the object is
1879                      **   not currently bound
1880                      ** in both cases the state of the object is
1881                      ** unchanged so the object is silently skipped
1882                      ** (this is a bit different that the explicit
1883                      **  case where the builtin warns the user that
1884                      **  the object is already in the desired state)
1885                      */
1886                      if (((@action = 'bind') and (@is_bound = 1)) or
1887                              ((@action = 'unbind') and (@is_bound = 0)))
1888                      begin
1889                          continue
1890                      end
1891  
1892                      select @status =
1893                          replication_path_admin(@dbid,
1894                              @action,
1895                              @p1,
1896                              @qualified_name,
1897                              @p3,
1898                              @p4)
1899                      if (@status != 1)
1900                      begin
1901                          raiserror 18409, "replication_path_admin"
1902                      end
1903                      select @num_matches = @num_matches + 1
1904                  end
1905                  /*
1906                  ** close the (inner) current database objects cursor
1907                  */
1908                  close currdb_matching_objects
1909  
1910                  /*
1911                  ** if the object type is an sproc and this is the dbo
1912                  ** user then we need to check if the name_part is
1913                  ** referring to a system stored proc
1914                  */
1915                  if (@sysobj_type = "P" and @curr_user_id = 1)
1916                  begin
1917                      open sybsystemprocs_matching_sprocs
1918                      while (1 = 1)
1919                      begin
1920                          select @curr_obj_id = 0
1921                          select @curr_obj_name = NULL
1922                          fetch sybsystemprocs_matching_sprocs
1923                          into @curr_obj_id,
1924                              @curr_obj_name
1925                          if (@@sqlstatus != 0)
1926                          begin
1927                              break
1928                          end
1929  
1930                          /*
1931                          ** if the path name is not 'all' get
1932                          ** the number of bindings that match 
1933                          ** the search criterion
1934                          */
1935                          if (@p3 != 'all')
1936                          begin
1937                              select @is_bound = count(*)
1938                              from sysattributes
1939                              where class = @MRP_CLASS and
1940                                  attribute = 23 and
1941                                  object_info1 = @curr_path and
1942                                  object = @curr_obj_id
1943  
1944                              /*
1945                              ** sanity check, we should never have 
1946                              ** more than one match
1947                              */
1948                              if (@is_bound > 1)
1949                              begin
1950                                  raiserror 19908, @curr_obj_name
1951                                  select @retval = 1
1952                                  goto done
1953                              end
1954                          end
1955  
1956                          /*
1957                          ** there are two cases that we check for
1958                          ** with wildcards:
1959                          ** - if action is bind and the object is
1960                          **   already bound
1961                          ** - id action is unbind and the object
1962                          **   is not currently bound
1963                          ** in both cases the state of the object
1964                          ** is unchanged so the object is silently
1965                          ** skipped (this is a bit different that
1966                          ** the explicit case where the builtin
1967                          ** warns the user that the object is 
1968                          ** already in the desired state)
1969                          */
1970                          if (((@action = 'bind') and
1971                                      (@is_bound = 1)) or
1972                                  ((@action = 'unbind') and
1973                                      (@is_bound = 0)))
1974                          begin
1975                              continue
1976                          end
1977  
1978                          select @status =
1979                              replication_path_admin(@dbid,
1980                                  @action,
1981                                  @p1,
1982                                  @curr_obj_name,
1983                                  @p3,
1984                                  @p4)
1985                          if (@status != 1)
1986                          begin
1987                              raiserror 18409,
1988                                  "replication_path_admin"
1989                          end
1990                          select @num_matches = @num_matches + 1
1991                      end
1992                      /*
1993                      ** close the (inner) system sproc cursor
1994                      */
1995                      close sybsystemprocs_matching_sprocs
1996                  end
1997              end
1998              /*
1999              ** close the (outer) uid cursor
2000              */
2001              close currdb_matching_uids
2002  
2003              /*
2004              ** clean up all the cursors
2005              */
2006              deallocate sybsystemprocs_matching_sprocs
2007              deallocate currdb_matching_objects
2008              deallocate currdb_matching_uids
2009  
2010              /*
2011              ** set up the report string
2012              */
2013              if (@action = 'bind')
2014              begin
2015                  select @report = convert(varchar(8), @num_matches) + " "
2016                      + @p1 + "(s) matching '" + @p2
2017                      + "' bound to path '" + @p3 + "'."
2018              end
2019              else /* else action is 'unbind' */
2020              begin
2021                  select @report = convert(varchar(8), @num_matches) + " "
2022                      + @p1 + "(s) matching '" + @p2
2023                      + "' unbound from path '" + @p3 + "'."
2024              end
2025  
2026              /*
2027              ** we have already called the builtin for any matching
2028              ** objects jump to the logging for replication  
2029              */
2030              goto logtran
2031          end
2032          else
2033          begin
2034              if (@action = 'bind')
2035              begin
2036                  select @report = "The " + @p1 + " '" + @p2 +
2037                      "' is bound to path '" + @p3 + "'."
2038              end
2039              else
2040              begin
2041                  select @report = "The " + @p1 + " '" + @p2 +
2042                      "' is unbound from path '" + @p3 + "'."
2043              end
2044          end
2045      end
2046  
2047  call_builtin:
2048      /*
2049      ** if we have not performed the action via wildcard handling
2050      ** we should have a correct parameter count at this point so
2051      ** call the built-in 
2052      */
2053      select @status = replication_path_admin(@dbid, @action,
2054              @p1, @p2, @p3, @p4)
2055      if (@status != 1)
2056      begin
2057          /*
2058          ** if we do not get a successful status we need to check
2059          ** if the status indicates no action was taken; in this
2060          ** case we just clear the status message and go on
2061          */
2062          if (@status = - 1)
2063          begin
2064              select @report = NULL
2065          end
2066          else
2067          begin
2068              raiserror 18409, "replication_path_admin"
2069              select @retval = 1
2070              goto done
2071          end
2072      end
2073  
2074      /*
2075      ** Write the log records to replicate this invocation of the 
2076      ** stored procedure.
2077      */
2078  logtran:
2079      begin transaction rs_logexec
2080  
2081      if (logexec(@dbid) != 1)
2082      begin
2083          /*
2084          ** 17756, "The execution of the stored procedure '%1!'
2085          **	 in database '%2!' was aborted because there
2086          **	 was an error in writing the replication log
2087          **	 record."
2088          */
2089          raiserror 17756, "sp_replication_path", @dbname
2090          rollback transaction rs_logexec
2091          select @retval = 1
2092          goto done
2093      end
2094      commit transaction
2095  
2096      /*
2097      ** If we have gotten here then everything has gone as expected,
2098      ** if there is a report (not null) then print it out. The contents
2099      ** of the report are filled out in the individual action section.
2100      */
2101      if (@report is not NULL)
2102      begin
2103          print "%1!", @report
2104      end
2105  
2106      /*
2107      ** if we get here, everything has gone ok - set the retval to 0
2108      ** and jump to the cleanup section
2109      */
2110      select @retval = 0
2111      goto done
2112  
2113      /* end of normal processing */
2114  
2115  usage:
2116      /*
2117      ** never good news if we get here, set the retval to 1
2118      **  and print usage information before we exit
2119      */
2120      select @retval = 1
2121  
2122      print "sp_replication_path Usage:"
2123      print "sp_replication_path <dbname>, 'add', 'Path Name', 'RepServer Name', 'rs username', 'rs password'"
2124      print "sp_replication_path <dbname>, 'add', 'logical', 'Logical Path Name', 'Path Name'"
2125      print "sp_replication_path <dbname>, 'drop', 'Path Name'"
2126      print "sp_replication_path <dbname>, 'drop', 'logical', 'Logical Path Name' [, 'Path Name']"
2127      print "sp_replication_path <dbname>, 'bind', 'table'|'sproc', '[ownername.]objectname', 'Path Name'"
2128      print "sp_replication_path <dbname>, 'unbind', 'table'|'sproc', '[ownername.]objectname', 'Path Name'"
2129      print "sp_replication_path <dbname>, 'unbind', 'path', 'Path Name', 'all'"
2130      print "sp_replication_path <dbname>, 'config', 'Path Name' [, 'config name' [, 'config value']]"
2131      print "sp_replication_path <dbname>, 'list'"
2132      print "sp_replication_path <dbname>, 'list', 'all'"
2133      print "sp_replication_path <dbname>, 'list', 'table|sproc'[, '[ownername.]objectname']"
2134      print "sp_replication_path <dbname>, 'list', 'path' [, 'Path Name']"
2135  
2136  done:
2137      return @retval
2138  


exec sp_procxmode 'sp_replication_path', 'AnyMode'
go

Grant Execute on sp_replication_path to public
go
RESULT SETS
sp_replication_path_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch @search_name: varchar(255) = varchar(1023) 404
 MTYP 4 Assignment type mismatch @lpath_name: varchar(255) = varchar(768) 641
 MTYP 4 Assignment type mismatch @ppath_name: varchar(255) = varchar(768) 931
 MTYP 4 Assignment type mismatch @rs_name: varchar(255) = varchar(768) 954
 MTYP 4 Assignment type mismatch @rs_name: varchar(255) = varchar(768) 1000
 MTYP 4 Assignment type mismatch @rs_name: varchar(255) = varchar(768) 1031
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 700
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 710
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 824
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 1025
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 1042
 QCSC 4 Costly 'select count()', use 'exists()' 517
 QCSC 4 Costly 'select count()', use 'exists()' 530
 QCSC 4 Costly 'select count()', use 'exists()' 946
 QCSC 4 Costly 'select count()', use 'exists()' 990
 QCSC 4 Costly 'select count()', use 'exists()' 1023
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1}
Uncovered: [object_type, object, object_info2, object_info3, object_cinfo]
1451
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info2}
Uncovered: [object_type, object, object_info3, object_cinfo]
1451
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 400
 QTYP 4 Comparison type mismatch smallint = int 400
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 401
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 569
 QTYP 4 Comparison type mismatch smallint = int 569
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 642
 QTYP 4 Comparison type mismatch smallint = int 642
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 643
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 747
 QTYP 4 Comparison type mismatch smallint = int 747
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 769
 QTYP 4 Comparison type mismatch smallint = int 769
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 848
 QTYP 4 Comparison type mismatch smallint = int 848
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 879
 QTYP 4 Comparison type mismatch smallint = int 879
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 890
 QTYP 4 Comparison type mismatch smallint = int 890
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 910
 QTYP 4 Comparison type mismatch smallint = int 910
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 923
 QTYP 4 Comparison type mismatch smallint = int 923
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 933
 QTYP 4 Comparison type mismatch smallint = int 933
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 934
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 956
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 957
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 961
 QTYP 4 Comparison type mismatch smallint = int 961
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 962
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 966
 QTYP 4 Comparison type mismatch smallint = int 966
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 967
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1001
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1002
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1006
 QTYP 4 Comparison type mismatch smallint = int 1006
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1007
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1011
 QTYP 4 Comparison type mismatch smallint = int 1011
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1012
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1032
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1033
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1417
 QTYP 4 Comparison type mismatch smallint = int 1417
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1442
 QTYP 4 Comparison type mismatch smallint = int 1442
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1447
 QTYP 4 Comparison type mismatch smallint = int 1447
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1448
 QTYP 4 Comparison type mismatch smallint = int 1448
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1449
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1456
 QTYP 4 Comparison type mismatch smallint = int 1456
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1457
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1460
 QTYP 4 Comparison type mismatch smallint = int 1460
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1461
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1750
 QTYP 4 Comparison type mismatch smallint = int 1750
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1856
 QTYP 4 Comparison type mismatch smallint = int 1856
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1857
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1939
 QTYP 4 Comparison type mismatch smallint = int 1939
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1940
 VRUN 4 Variable is read and not initialized @curr_user_id 1795
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause pp_id_for_ppath_elem 909
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause currdb_matching_uids 1783
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 613
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 624
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 793
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 798
 MAW1 3 Warning message on %name% master..syscolumns.id: Warning message on syscolumns 1185
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 1793
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 1811
 MGTP 3 Grant to public master..syscolumns  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_replication_path  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 341
 MNER 3 No Error Check should check @@error after insert 342
 MNER 3 No Error Check should check @@error after insert 349
 MNER 3 No Error Check should check @@error after insert 662
 MNER 3 No Error Check should check @@error after insert 669
 MNER 3 No Error Check should check return value of exec 700
 MNER 3 No Error Check should check return value of exec 710
 MNER 3 No Error Check should check @@error after insert 818
 MNER 3 No Error Check should check return value of exec 824
 MNER 3 No Error Check should check @@error after insert 937
 MNER 3 No Error Check should check @@error after insert 970
 MNER 3 No Error Check should check @@error after insert 1015
 MNER 3 No Error Check should check return value of exec 1025
 MNER 3 No Error Check should check @@error after insert 1040
 MNER 3 No Error Check should check return value of exec 1042
 MNER 3 No Error Check should check @@error after insert 1453
 MNER 3 No Error Check should check return value of exec 1465
 MNER 3 No Error Check should check @@error after insert 1607
 MNER 3 No Error Check should check @@error after insert 1608
 MNER 3 No Error Check should check @@error after insert 1609
 MNER 3 No Error Check should check @@error after insert 1613
 MNER 3 No Error Check should check return value of exec 1706
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 142
 MUCO 3 Useless Code Useless Brackets 156
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 187
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 205
 MUCO 3 Useless Code Useless Brackets 208
 MUCO 3 Useless Code Useless Brackets 219
 MUCO 3 Useless Code Useless Brackets 313
 MUCO 3 Useless Code Useless Brackets 336
 MUCO 3 Useless Code Useless Brackets 344
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 370
 MUCO 3 Useless Code Useless Brackets 372
 MUCO 3 Useless Code Useless Brackets 375
 MUCO 3 Useless Code Useless Brackets 387
 MUCO 3 Useless Code Useless Brackets 390
 MUCO 3 Useless Code Useless Brackets 392
 MUCO 3 Useless Code Useless Brackets 413
 MUCO 3 Useless Code Useless Brackets 431
 MUCO 3 Useless Code Useless Brackets 436
 MUCO 3 Useless Code Useless Brackets 440
 MUCO 3 Useless Code Useless Brackets 444
 MUCO 3 Useless Code Useless Brackets 458
 MUCO 3 Useless Code Useless Brackets 461
 MUCO 3 Useless Code Useless Brackets 463
 MUCO 3 Useless Code Useless Brackets 473
 MUCO 3 Useless Code Useless Brackets 477
 MUCO 3 Useless Code Useless Brackets 516
 MUCO 3 Useless Code Useless Brackets 527
 MUCO 3 Useless Code Useless Brackets 529
 MUCO 3 Useless Code Useless Brackets 541
 MUCO 3 Useless Code Useless Brackets 560
 MUCO 3 Useless Code Useless Brackets 588
 MUCO 3 Useless Code Useless Brackets 599
 MUCO 3 Useless Code Useless Brackets 614
 MUCO 3 Useless Code Useless Brackets 631
 MUCO 3 Useless Code Useless Brackets 646
 MUCO 3 Useless Code Useless Brackets 696
 MUCO 3 Useless Code Useless Brackets 706
 MUCO 3 Useless Code Useless Brackets 731
 MUCO 3 Useless Code Useless Brackets 740
 MUCO 3 Useless Code Useless Brackets 758
 MUCO 3 Useless Code Useless Brackets 762
 MUCO 3 Useless Code Useless Brackets 775
 MUCO 3 Useless Code Useless Brackets 779
 MUCO 3 Useless Code Useless Brackets 794
 MUCO 3 Useless Code Useless Brackets 801
 MUCO 3 Useless Code Useless Brackets 837
 MUCO 3 Useless Code Useless Brackets 867
 MUCO 3 Useless Code Useless Brackets 870
 MUCO 3 Useless Code Useless Brackets 878
 MUCO 3 Useless Code Useless Brackets 894
 MUCO 3 Useless Code Useless Brackets 897
 MUCO 3 Useless Code Useless Brackets 914
 MUCO 3 Useless Code Useless Brackets 917
 MUCO 3 Useless Code Useless Brackets 922
 MUCO 3 Useless Code Useless Brackets 946
 MUCO 3 Useless Code Useless Brackets 990
 MUCO 3 Useless Code Useless Brackets 1023
 MUCO 3 Useless Code Useless Brackets 1063
 MUCO 3 Useless Code Useless Brackets 1086
 MUCO 3 Useless Code Useless Brackets 1091
 MUCO 3 Useless Code Useless Brackets 1099
 MUCO 3 Useless Code Useless Brackets 1106
 MUCO 3 Useless Code Useless Brackets 1114
 MUCO 3 Useless Code Useless Brackets 1121
 MUCO 3 Useless Code Useless Brackets 1127
 MUCO 3 Useless Code Useless Brackets 1142
 MUCO 3 Useless Code Useless Brackets 1150
 MUCO 3 Useless Code Useless Brackets 1157
 MUCO 3 Useless Code Useless Brackets 1163
 MUCO 3 Useless Code Useless Brackets 1169
 MUCO 3 Useless Code Useless Brackets 1175
 MUCO 3 Useless Code Useless Brackets 1188
 MUCO 3 Useless Code Useless Brackets 1194
 MUCO 3 Useless Code Useless Brackets 1197
 MUCO 3 Useless Code Useless Brackets 1204
 MUCO 3 Useless Code Useless Brackets 1220
 MUCO 3 Useless Code Useless Brackets 1229
 MUCO 3 Useless Code Useless Brackets 1249
 MUCO 3 Useless Code Useless Brackets 1257
 MUCO 3 Useless Code Useless Brackets 1259
 MUCO 3 Useless Code Useless Brackets 1267
 MUCO 3 Useless Code Useless Brackets 1274
 MUCO 3 Useless Code Useless Brackets 1284
 MUCO 3 Useless Code Useless Brackets 1291
 MUCO 3 Useless Code Useless Brackets 1304
 MUCO 3 Useless Code Useless Brackets 1312
 MUCO 3 Useless Code Useless Brackets 1319
 MUCO 3 Useless Code Useless Brackets 1336
 MUCO 3 Useless Code Useless Brackets 1363
 MUCO 3 Useless Code Useless Brackets 1378
 MUCO 3 Useless Code Useless Brackets 1391
 MUCO 3 Useless Code Useless Brackets 1402
 MUCO 3 Useless Code Useless Brackets 1434
 MUCO 3 Useless Code Useless Brackets 1437
 MUCO 3 Useless Code Useless Brackets 1441
 MUCO 3 Useless Code Useless Brackets 1475
 MUCO 3 Useless Code Useless Brackets 1484
 MUCO 3 Useless Code Useless Brackets 1492
 MUCO 3 Useless Code Useless Brackets 1498
 MUCO 3 Useless Code Useless Brackets 1504
 MUCO 3 Useless Code Useless Brackets 1514
 MUCO 3 Useless Code Useless Brackets 1516
 MUCO 3 Useless Code Useless Brackets 1523
 MUCO 3 Useless Code Useless Brackets 1539
 MUCO 3 Useless Code Useless Brackets 1549
 MUCO 3 Useless Code Useless Brackets 1611
 MUCO 3 Useless Code Useless Brackets 1620
 MUCO 3 Useless Code Useless Brackets 1637
 MUCO 3 Useless Code Useless Brackets 1639
 MUCO 3 Useless Code Useless Brackets 1649
 MUCO 3 Useless Code Useless Brackets 1659
 MUCO 3 Useless Code Useless Brackets 1667
 MUCO 3 Useless Code Useless Brackets 1678
 MUCO 3 Useless Code Useless Brackets 1698
 MUCO 3 Useless Code Useless Brackets 1715
 MUCO 3 Useless Code Useless Brackets 1726
 MUCO 3 Useless Code Useless Brackets 1741
 MUCO 3 Useless Code Useless Brackets 1751
 MUCO 3 Useless Code Useless Brackets 1753
 MUCO 3 Useless Code Useless Brackets 1763
 MUCO 3 Useless Code Useless Brackets 1769
 MUCO 3 Useless Code Useless Brackets 1823
 MUCO 3 Useless Code Useless Brackets 1827
 MUCO 3 Useless Code Useless Brackets 1837
 MUCO 3 Useless Code Useless Brackets 1841
 MUCO 3 Useless Code Useless Brackets 1849
 MUCO 3 Useless Code Useless Brackets 1865
 MUCO 3 Useless Code Useless Brackets 1886
 MUCO 3 Useless Code Useless Brackets 1899
 MUCO 3 Useless Code Useless Brackets 1915
 MUCO 3 Useless Code Useless Brackets 1918
 MUCO 3 Useless Code Useless Brackets 1925
 MUCO 3 Useless Code Useless Brackets 1935
 MUCO 3 Useless Code Useless Brackets 1948
 MUCO 3 Useless Code Useless Brackets 1970
 MUCO 3 Useless Code Useless Brackets 1985
 MUCO 3 Useless Code Useless Brackets 2013
 MUCO 3 Useless Code Useless Brackets 2034
 MUCO 3 Useless Code Useless Brackets 2055
 MUCO 3 Useless Code Useless Brackets 2062
 MUCO 3 Useless Code Useless Brackets 2081
 MUCO 3 Useless Code Useless Brackets 2101
 MUIN 3 Column created using implicit nullability 332
 MUIN 3 Column created using implicit nullability 579
 MUIN 3 Column created using implicit nullability 583
 MUIN 3 Column created using implicit nullability 751
 MUIN 3 Column created using implicit nullability 860
 MUIN 3 Column created using implicit nullability 863
 MUIN 3 Column created using implicit nullability 1425
 QAFM 3 Var Assignment from potentially many rows 641
 QAFM 3 Var Assignment from potentially many rows 931
 QAFM 3 Var Assignment from potentially many rows 954
 QAFM 3 Var Assignment from potentially many rows 1000
 QAFM 3 Var Assignment from potentially many rows 1031
 QAFM 3 Var Assignment from potentially many rows 1184
 QAFM 3 Var Assignment from potentially many rows 1445
 QAFM 3 Var Assignment from potentially many rows 1749
 QCRS 3 Conditional Result Set 195
 QCTC 3 Conditional Table Creation 332
 QCTC 3 Conditional Table Creation 579
 QCTC 3 Conditional Table Creation 583
 QCTC 3 Conditional Table Creation 751
 QCTC 3 Conditional Table Creation 860
 QCTC 3 Conditional Table Creation 863
 QCTC 3 Conditional Table Creation 1425
 QCTC 3 Conditional Table Creation 1606
 QISO 3 Set isolation level 108
 QNAJ 3 Not using ANSI Inner Join 1446
 QNAM 3 Select expression has no name @status1 195
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute}
400
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
518
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
531
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
569
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
642
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute}
747
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
769
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute}
848
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
879
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
890
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info2, class, attribute}
910
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
923
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
933
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
956
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
961
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
966
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
1001
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
1006
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
1011
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
1032
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
1185
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute}
1417
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
1442
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
1456
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_info1, attribute}
1460
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, attribute}
1750
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object, attribute, class}
1856
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_info1, object, attribute, class}
1939
 VNRD 3 Variable is not read @blt_failure 111
 VNRD 3 Variable is not read @rep_all 112
 VNRD 3 Variable is not read @rep_l1 113
 VNRD 3 Variable is not read @rep_constant 114
 VNRD 3 Variable is not read @dbuid 119
 VNRD 3 Variable is not read @gp_enabled 167
 VNRD 3 Variable is not read @dummy 196
 VNRD 3 Variable is not read @site_part 1707
 VUNU 3 Variable is not used @owner 55
 VUNU 3 Variable is not used @db 56
 VUNU 3 Variable is not used @rs_id 290
 VUNU 3 Variable is not used @obj_name_match 1585
 VUNU 3 Variable is not used @usr_name_match 1586
 CRDO 2 Read Only Cursor Marker (has for read only clause) 568
 CRDO 2 Read Only Cursor Marker (has for read only clause) 746
 CRDO 2 Read Only Cursor Marker (has for read only clause) 768
 CRDO 2 Read Only Cursor Marker (has for read only clause) 847
 CRDO 2 Read Only Cursor Marker (has for read only clause) 1416
 CUPD 2 Updatable Cursor Marker (updatable by default) 889
 CUPD 2 Updatable Cursor Marker (updatable by default) 909
 CUPD 2 Updatable Cursor Marker (updatable by default) 1783
 CUPD 2 Updatable Cursor Marker (updatable by default) 1793
 CUPD 2 Updatable Cursor Marker (updatable by default) 1811
 MRST 2 Result Set Marker 195
 MSUB 2 Subquery Marker 399
 MSUB 2 Subquery Marker 517
 MSUB 2 Subquery Marker 530
 MSUB 2 Subquery Marker 878
 MSUB 2 Subquery Marker 922
 MSUB 2 Subquery Marker 946
 MSUB 2 Subquery Marker 959
 MSUB 2 Subquery Marker 964
 MSUB 2 Subquery Marker 990
 MSUB 2 Subquery Marker 1004
 MSUB 2 Subquery Marker 1009
 MSUB 2 Subquery Marker 1441
 MSUB 2 Subquery Marker 1455
 MSUB 2 Subquery Marker 1459
 MSUB 2 Subquery Marker 1621
 MTR1 2 Metrics: Comments Ratio Comments: 42% 33
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 250 = 264dec - 16exi + 2 33
 MTR3 2 Metrics: Query Complexity Complexity: 979 33
 PRED_QUERY_COLLECTION 2 {a=sybsystemprocs..sysattributes, a2=sybsystemprocs..sysattributes} 0 1445

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#config_for_path (1) 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..syscolumns (1)  
writes table tempdb..#paths_for_active_bindings (1) 
read_writes table tempdb..#supported_types (1) 
reads table sybsystemprocs..sysusers  
writes table tempdb..#paths_for_inactive_bindings (1) 
read_writes table tempdb..#dest_for_ppath (1) 
reads table sybsystemprocs..sysattributes  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_namecrack  
read_writes table tempdb..#ppaths_for_lpaths (1) 
writes table tempdb..#bindings_for_paths (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   read_writes table tempdb..#colinfo_af (1) 
   reads table master..systypes (1)  
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
writes table sybsystemprocs..sp_replication_path_rset_001 
read_writes table tempdb..#active_types (1) 
reads table master..sysdatabases (1)