Database	Proc	Application	Created	Links
sybsystemprocs	sp_replication_path		31 Aug 14	Defects 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     ** 17260, "Can't run %1! from within a transaction."
9     ** 17421, "No such database -- run sp_helpdb to list databases."
10    ** 17756, "The execution of the stored procedure '%1!' in database
11    **	  '%2!' was aborted because there was an error in writing the
12    **	  replication log record."
13    ** 18102, Table '%1!' does not exist in this database.
14    ** 18107, Stored procedure '%1!' does not exist in this database.
15    ** 18118, Object '%1!' does not exist in this database.
16    ** 18375, "You are not authorized to execute this stored procedure. Only the 
17    **	  System Administrator (SA), the Database Owner (DBO) or a user with 
18    **	  replication_role authorization can execute this stored procedure."
19    ** 18408, "The stored procedure must be executed with the name of the
20    **	  current database."
21    ** 18409, "The built-in function '%1!' failed. Please see any other messages 
22    **	  printed along with this message."
23    ** 18481, '%1!' is an invalid configuration parameter.
24    ** 19213, "Invalid argument or unsupported command: %1!."
25    ** 19384, The '%1!' parameter is required with '%2!' parameter.
26    ** 19415, The '%1!' parameter is required for the '%2!' command.
27    ** 19805, An unexpected syntax error has occurred.
28    ** 19859, Argument '%1!' is not valid for '%2!'.
29    **
30    */
31    create procedure sp_replication_path
32        @dbname varchar(30), /* always database name	     */
33        @action varchar(30) = NULL, /* add|drop|config|bind|unbind|list */
34        /*
35        ** sp_replication_path has an additional four positional parameters the
36        ** specific usage of these parameters is dictated by the syntax of the
37        ** action; so, rather than give a name that is meaningful in some context
38        ** but misleading in others, the anonymous '@p...' names are used
39        */
40        @p1 varchar(255) = NULL,
41        @p2 varchar(1023) = NULL, /* must be able to hold long names
42        **  of the form site.db.owner.name */
43        @p3 varchar(1023) = NULL, /* must be able to hold long names
44        **  of the form site.db.owner.name */
45        @p4 varchar(255) = NULL,
46        @rs_encpwd varbinary(256) = NULL /* encrypted rs password, 
47    				     ** internal use for replication.
48    				     */
49    as
50    
51        declare @status int, /* Local status			*/
52            @retval int, /* Return status 		*/
53            @has_sa_role int,
54            @has_repl_role int,
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    
67        declare @MRP_CLASS int /* MRP_CLASS constant		*/
68        select @MRP_CLASS = 41
69    
70        declare @STATIC_KEY int /* for rs password encryption */
71        select @STATIC_KEY = 54
72    
73    
74        if @@trancount > 0
75        begin
76            /* 
77            ** 17260, "Can't run %1! from within a transaction."
78            */
79            raiserror 17260, "sp_relication_path"
80            return (1)
81        end
82    
83        /*
84        ** if the dbname was not supplied, or the dbname is "help" and
85        ** no action parameter was supplied then we jump to the syntax help
86        */
87        if ((@dbname is NULL) or
88                (@dbname = "help" and @action is NULL))
89        begin
90            goto usage
91        end
92    
93        set chained off
94        set transaction isolation level 1
95    
96        /* Init variables */
97        select @blt_failure = - 2, /* LT_REP_GET_FAILED */
98            @rep_all = 2048, /* LT_REP_ALL */
99            @rep_l1 = 4096, /* LT_REP_L1 */
100           @rep_constant = - 32768,
101           @report = NULL
102   
103   
104       /* Get the database owner */
105       select @dbuid = suid, @dbid = dbid
106       from master.dbo.sysdatabases
107       where name = @dbname
108   
109       if (@dbid is null)
110       begin
111           /*
112           ** 17421, "No such database -- run sp_helpdb to display databases."
113           */
114           raiserror 17421
115           return (1)
116       end
117   
118       /*
119       ** Check SA and REPLICATION role.
120       ** Keep their status in local variables for auditing later.
121       */
122       select @has_sa_role = charindex("sa_role", show_role())
123       select @has_repl_role = charindex("replication_role", show_role())
124   
125       /*
126       **  Only the Database Owner (DBO) or
127       **  Accounts with SA role or replication role can execute it.
128       **  First check if we are the DBO if the database name is specified.
129       */
130       if (suser_id() != @dbuid)
131       begin
132           /* Check if we have sa_role or replication_role. */
133           if (@has_sa_role = 0 and @has_repl_role = 0)
134           begin
135               /* Audit authorization failure */
136               select @retval = proc_role("sa_role")
137               select @retval = proc_role("replication_role")
138   
139               /*
140               **  18375, "You are not authorized to execute this stored
141               ** procedure. Only the System Administrator (SA), the
142               ** Database Owner (DBO) or a user with replication_role
143               ** authorization can execute this stored procedure."
144               */
145               raiserror 18375
146               return (1)
147           end
148       end
149   
150       /* Audit authorization succeeded */
151       if (@has_sa_role > 0)
152           select @retval = proc_role("sa_role")
153       if (@has_repl_role > 0)
154           select @retval = proc_role("replication_role")
155   
156   
157       /*
158       ** Make sure that the database is the current database.
159       */
160       if (@dbname != db_name())
161       begin
162           /*
163           ** 18408, "The stored procedure must be executed with the name of the
164           **	   current database."
165           */
166           raiserror 18408
167           return (1)
168       end
169   
170       /* 
171       ** Check first if the action syntax is correct 
172       */
173       select @action = lower(@action)
174       if (@action is null or @action not in ('add', 'drop', 'config',
175                   'bind', 'unbind', 'list'))
176       begin
177           if (@action != "help")
178           begin
179               raiserror 19213, @action
180           end
181           goto usage
182       end
183   
184   
185       /*
186       ** action is 'list' so we will do the work here without the builtin
187       */
188       if (@action = 'list')
189       begin
190           /*
191           ** Display information about the MRP environment. With no parameters
192           ** show everything organized by path. If a single parameter is given 
193           ** it must be one of the supported types (currently path and table|
194           ** sproc objects); information will be organized by the specified type
195           ** If the second parameter is supplied its type must match the given
196           ** type in the first parameter; detailed information about the given
197           ** object will be provided.
198           **
199           ** Note: 'list' does not require a call to the built-in
200           **
201           **  Var.  
202           ** -----  -------------------------------------
203           **  @p1   type to display (optional)
204           **        must be valid type (path|table|sproc)
205           **  @p2   name of object to display (optional)
206           **        name must exist and agree with type
207           **  @p3	  unused
208           **  @p4   unused
209           **
210           ** Errors raised by 'list'
211           **
212           **	error	 description
213           **	-----	-------------------------------------------------------
214           **	18102	Table '%1!' does not exist in this database.
215           **	18107	Stored procedure '%1!' does not exist in this database.
216           **	18118	Object '%1!' does not exist in this database.
217           **	19805	An unexpected syntax error has occurred.
218           **	19859	Argument '%1!' is not valid for '%2!'.
219           **	
220           */
221   
222           /*
223           ** There are two basic ways we can look at the MRP data; from either
224           ** an binding- or path-centric view (axis). 
225           ** For a binding-centric view (default) we start with the list of bound
226           ** objects and display the path(s) to which each is bound. Searches on
227           ** the binding axis need a further qualifier to indicate what type of
228           ** object we are looking for (i.e. table, sproc, etc.)
229           **
230           ** For a path-centric view the list is built upon the set of paths and
231           ** objects bound to each path are displayed.
232           **
233           ** Both binding and path axis searches may be further restricted by
234           ** name.
235           **
236           ** The search_omin/omax are used to limit searches on the underlying
237           ** layer once we have acquired an object id. Searches on these values
238           ** are always search_omin <=  <= search_omax. There are only
239           ** two choices for the values to be set to:
240           **  - search_omin = 1, search_omax = MAXINT indicates a wildcard
241           **    search that will match all valid values for object ids, we are
242           **    looking for all instances of a broader search type
243           **  - search_omin = search_omax =