1
2 /*
3 ** Messages for "sp_help_rep_agent" 18425
4 **
5 ** 17421, "No such database -- run sp_helpdb to list databases."
6 ** 18374, "Database '%1!' is not configured to use Replication Agent. Run sp_config_rep_agent without parameters to see a list of databases that use Replication Agent. Use the ENABLE option of sp_config_rep_agent to configure a database to use this feature."
7 ** 18375, "You are not authorized to execute this stored procedure. Only the System Administrator (SA), the Database Owner (DBO) or a user with replication_role authorization can execute this stored procedure."
8 ** 18425, "Incorrect syntax for sp_help_rep_agent. Usage: sp_help_rep_agent [ [,{recovery | process | config | scan | security | send | all } ] ]."
9 */
10 create procedure sp_help_rep_agent
11 @dbname varchar(30) = NULL, /* database name - optional */
12 @whattoprint varchar(30) = NULL /* what properties to print */
13 as
14
15 declare @dbid int /* dbid of the database */
16 declare @dbuid int /* id of the owner of the database */
17 declare @msg varchar(250)
18 declare @sptlang int
19 declare @procval int
20 declare @type int /* Type of the config. parameter */
21 declare @has_sa_role int /* User has SA role. */
22 declare @has_repl_role int /* User has REPLICATION role. */
23 declare @dbstatus3 int /* Database's status3 */
24 declare @skip_status int /* Holds db status to be skipped
25 ** from the sysdatabases select */
26 declare @tempdb_mask int /* Mask indicating this is a
27 ** temporary database */
28 declare @num_multithread_rat int /* Number of databases running in
29 ** multithread mode. */
30
31
32 if @@trancount = 0
33 begin
34 set chained off
35 end
36
37 set transaction isolation level 1
38
39 /* Turn rowcounting off. */
40 set nocount on
41 /*
42 ** Initialize the variable with the status to be skipped from the
43 ** select from sysdatabases:
44 **
45 ** status3 = 8 : Database is being shutdown
46 ** status3 = 4096 : Shutdown of the database is complete
47 */
48 select @skip_status = 8 | 4096
49 select @tempdb_mask = number
50 from master.dbo.spt_values
51 where type = "D3" and name = "TEMPDB STATUS MASK"
52
53 select @skip_status = @skip_status | @tempdb_mask
54
55 select @sptlang = @@langid
56
57 if @@langid != 0
58 begin
59 if not exists (
60 select * from master.dbo.sysmessages where error
61 between 17050 and 17069
62 and langid = @@langid)
63 select @sptlang = 0
64 end
65
66 if (@dbname is not NULL)
67 begin
68 /*
69 ** Verify the database name and get the @dbid and @dbuid
70 */
71 select @dbid = dbid, @dbuid = suid, @dbstatus3 = status3
72 from master.dbo.sysdatabases
73 where name = @dbname
74
75 /*
76 ** If @dbname not found, say so and list the databases.
77 */
78 if @dbid is NULL
79 begin
80 /*
81 ** 17421, "No such database -- run sp_helpdb to list databases."
82 */
83 raiserror 17421
84 return (1)
85 end
86
87 /*
88 ** Verify that this database is not being shutdown, or the shutdown
89 ** is complete after a HA takeover.
90 ** status3 = 8 : Database is being shutdown
91 ** status3 = 4096 : Shutdown of the database is complete
92 ** or it is a temporary database.
93 */
94 if (((@dbstatus3 & 8) = 8) or
95 ((@dbstatus3 & 4096) = 4096) or
96 ((@dbstatus3 & @tempdb_mask != 0)) or (@dbid = 2))
97 begin
98 raiserror 18374, @dbname
99 return (1)
100 end
101
102 /* Verify that the database has the Rep Agent property enabled. */
103 if is_rep_agent_enabled(@dbid) = 0
104 begin
105 /* 18374, "Database '%1!' is not configured to use Replication Agent.
106 ** Run sp_config_rep_agent without parameters to see a list of
107 ** databases that use Replication Agent. Use the ENABLE option of
108 ** sp_config_rep_agent to configure a database to use this feature."
109 */
110 raiserror 18374, @dbname
111 return (1)
112 end
113 end
114
115 /*
116 ** Check SA and REPLICATION role.
117 ** Keep their status in local variables for auditing later.
118 */
119 select @has_sa_role = charindex("sa_role", show_role())
120 select @has_repl_role = charindex("replication_role", show_role())
121
122 /*
123 ** Only the Database Owner (DBO) or
124 ** Accounts with SA role or replication role can execute it.
125 ** First check if we are the DBO if the database name is specified.
126 */
127 if (@dbname is NULL) or (suser_id() != @dbuid)
128 begin
129 /* Check if we have sa_role or replication_role. */
130 if (@has_sa_role = 0 and @has_repl_role = 0)
131 begin
132 /*
133 ** Audit failure. This will result in three messages, but
134 ** we will live with that until there is a better 'proc_role()'
135 ** interface.
136 */
137 select @procval = proc_role("sa_role")
138 select @procval = proc_role("replication_role")
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 success(es) */
151 if (@has_sa_role > 0)
152 select @procval = proc_role("sa_role")
153 if (@has_repl_role > 0)
154 select @procval = proc_role("replication_role")
155
156 /* Validate @whattoprint */
157
158 if @whattoprint is not NULL and
159 not (@whattoprint in ("recovery", "config", "scan", "process", "security", "send", "all"))
160 begin
161 /* 18425, "Incorrect syntax for sp_help_rep_agent. Usage:
162 ** sp_help_rep_agent [ [,{recovery | process | config |
163 ** scan | security | send | all } ] ]."
164 */
165 raiserror 18425
166 return (1)
167 end
168
169 /* If no category is specified, then print all categories */
170 if (@whattoprint is NULL)
171 begin
172 select @whattoprint = "all"
173 end
174
175 if (@whattoprint in ("recovery", "all"))
176 begin
177 print "Replication Agent Recovery status"
178 if @dbname is not NULL
179 begin
180 /* For a given database */
181
182 /* 'select into' the recovery information into a temp table */
183 select "dbname" = @dbname,
184 "connect_dataserver" = send.Dataserver,
185 "connect_database" = scan.DBName,
186 "status" = scan.Status,
187 "rs_servername" = send.ReplicationServer,
188 "rs_username" = send.Username
189 into #help_rep_agent_recovery1
190 from master..monRepScanners scan,
191 master..monRepSenders send
192 where scan.DBID = @dbid and
193 send.DBID = scan.DBID
194
195 /* Output and format the content of the temp table */
196 exec sp_autoformat #help_rep_agent_recovery1
197
198 /* drop the temp table */
199 drop table #help_rep_agent_recovery1
200 end
201 else
202 begin
203 /* 'select into' the recovery information into a temp table */
204
205 select "dbname" = db.name,
206 "connect_dataserver" = send.Dataserver,
207 "connect_database" = scan.DBName,
208 "status" = scan.Status,
209 "rs_servername" = send.ReplicationServer,
210 "rs_username" = send.Username
211 into #help_rep_agent_recovery2
212 from master.dbo.sysdatabases db,
213 master..monRepScanners scan,
214 master..monRepSenders send
215 where 1 = (case when ((db.status3 & @skip_status) = 0)
216 then is_rep_agent_enabled(db.dbid) else 0
217 end) and
218 scan.DBID = db.dbid and
219 send.DBID = scan.DBID
220
221 /* Output and format the content of the temp table */
222 exec sp_autoformat #help_rep_agent_recovery2
223
224 /* drop the temp table */
225 drop table #help_rep_agent_recovery2
226 end
227 end
228 if (@whattoprint in ("process", "all"))
229 begin
230 print "Replication Agent Process status"
231 if @dbname is not NULL
232 begin
233 /* For a given database */
234
235 /* 'select into' the process information into a temp table */
236 if (is_multithread_rep_agent(@dbid) = 1)
237 begin
238 /* We will report only scanners first */
239 select "dbname" = @dbname,
240 "spid" = convert(varchar(11), scan.SPID),
241 "sleep_status" = scan.SleepStatus,
242 "state" = scan.Status,
243 "retry_count" = convert(varchar(11), 0),
244 "last_error" = convert(varchar(11), 0)
245 into #help_rep_agent_process0
246 from master..monRepScanners scan
247 where scan.DBID = @dbid
248
249 /* Output and format the content of the temp table */
250 exec sp_autoformat #help_rep_agent_process0
251
252 /* drop the temp table */
253 drop table #help_rep_agent_process0
254
255 print "Replication Agent (sender) Process status"
256
257 /* 'select into' the process information into a temp table */
258 select "dbname" = @dbname,
259 "spid" = convert(varchar(11), send.SPID),
260 "sleep_status" = send.SleepStatus,
261 "state" = send.Status,
262 "retry_count" = convert(varchar(11), send.NumberOfRetries),
263 "last_error" = convert(varchar(11), send.LastRepServerError)
264 into #help_rep_agent_process2
265 from master..monRepSenders send
266 where send.DBID = @dbid
267
268 /* Output and format the content of the temp table */
269 exec sp_autoformat #help_rep_agent_process2
270
271 /* drop the temp table */
272 drop table #help_rep_agent_process2
273 end
274 else
275 begin
276 /* For a single task model the scanner is also a sender */
277 select "dbname" = @dbname,
278 "spid" = convert(varchar(11), scan.SPID),
279 "sleep_status" = scan.SleepStatus,
280 "state" = scan.Status,
281 "retry_count" = convert(varchar(11), send.NumberOfRetries),
282 "last_error" = convert(varchar(11), send.LastRepServerError)
283 into #help_rep_agent_process1
284 from master..monRepScanners scan,
285 master..monRepSenders send
286 where scan.DBID = @dbid and
287 send.DBID = scan.DBID and
288 scan.SPID = send.SPID
289
290 /* Output and format the content of the temp table */
291 exec sp_autoformat #help_rep_agent_process1
292
293 /* drop the temp table */
294 drop table #help_rep_agent_process1
295 end
296 end
297 else
298 begin
299 /* We will report multiple rows when more senders are running */
300 select "dbname" = db.name,
301 "spid" = convert(varchar(11), scan.SPID),
302 "sleep_status" = scan.SleepStatus,
303 "state" = scan.Status,
304 "retry_count" = convert(varchar(11), rep_agent_config(db.dbid, "process", "retry count")),
305 "last_error" = convert(varchar(11), rep_agent_config(db.dbid, "process", "last error"))
306 into #help_rep_agent_process3
307 from master.dbo.sysdatabases db,
308 master..monRepScanners scan
309 where 1 = (case when ((db.status3 & @skip_status) = 0)
310 then is_rep_agent_enabled(db.dbid) else 0
311 end) and
312 scan.DBID = db.dbid
313
314 /* Output and format the content of the temp table */
315 exec sp_autoformat #help_rep_agent_process3
316
317 /* drop the temp table */
318 drop table #help_rep_agent_process3
319
320 /* 'select into' the process information into a temp table */
321 select "dbname" = name,
322 "spid" = convert(varchar(11), send.SPID),
323 "sleep_status" = send.SleepStatus,
324 "retry_count" = convert(varchar(11), send.NumberOfRetries),
325 "last_error" = convert(varchar(11), send.LastRepServerError)
326 into #help_rep_agent_process4
327 from master.dbo.sysdatabases db,
328 master..monRepSenders send
329 where 1 = (case when ((db.status3 & @skip_status) = 0)
330 then (case when ((is_rep_agent_enabled(db.dbid) = 1) and
331 (is_multithread_rep_agent(db.dbid) = 1))
332 then 1
333 else 0
334 end)
335 else 0
336 end) and
337 send.DBID = db.dbid
338
339 select @num_multithread_rat = count(*) from #help_rep_agent_process4
340
341 /* Display, if more than one database running multithread Rep Agent */
342 if (@num_multithread_rat > 0)
343 begin
344 print "Replication Agent (sender) Process status"
345
346 /* Output and format the content of the temp table */
347 exec sp_autoformat #help_rep_agent_process4
348
349 end
350
351 /* drop the temp table */
352 drop table #help_rep_agent_process4
353 end
354 end
355 if (@whattoprint in ("scan", "all"))
356 begin
357 print "Replication Agent Scan status"
358 if @dbname is not NULL
359 begin
360 /* For a given database */
361
362 /* 'select into' the scan information into a temp table */
363 select "dbname" = @dbname,
364 "start_marker" = scan.StartMarker,
365 "end_marker" = scan.EndMarker,
366 "current_marker" = scan.CurrentMarker,
367 "log_recs_scanned" = scan.LogRecordsScanned,
368 "oldest_transaction" = scan.LogRecordsScanned
369 into #help_rep_agent_scan1
370 from master..monRepScanners scan
371 where scan.DBID = @dbid
372
373 /* Output and format the content of the temp table */
374 exec sp_autoformat #help_rep_agent_scan1
375
376 /* drop the temp table */
377 drop table #help_rep_agent_scan1
378 end
379 else
380 begin
381 /* 'select into' the scan information into a temp table */
382 select "dbname" = db.name,
383 "start_marker" = scan.StartMarker,
384 "end_marker" = scan.EndMarker,
385 "current_marker" = scan.CurrentMarker,
386 "log_recs_scanned" = scan.LogRecordsScanned,
387 "oldest_transaction" = scan.LogRecordsScanned
388 into #help_rep_agent_scan2
389 from master..monRepScanners scan,
390 master.dbo.sysdatabases db
391 where 1 = (case when ((db.status3 & @skip_status) = 0)
392 then is_rep_agent_enabled(db.dbid) else 0
393 end) and
394 scan.DBID = db.dbid
395
396 /* Output and format the content of the temp table */
397 exec sp_autoformat #help_rep_agent_scan2
398
399 /* drop the temp table */
400 drop table #help_rep_agent_scan2
401 end
402 end
403 if (@whattoprint in ("config", "all"))
404 begin
405 print "Replication Agent Configuration"
406 if @dbname is not NULL
407 begin
408 /* For a given database */
409
410 /* 'select into' the config information into a temp table */
411 select "dbname" = @dbname,
412 "auto_start" = rep_agent_config(@dbid, "config", "auto start"),
413 "rs_servername" = convert(varchar(30), rep_agent_config(@dbid, "config", "rs servername")),
414 "rs_username" = convert(varchar(30), rep_agent_config(@dbid, "config", "rs username")),
415 "connect_dataserver" = convert(varchar(30), rep_agent_config(@dbid, "recovery", "connect dataserver")),
416 "connect_database" = convert(varchar(30), rep_agent_config(@dbid, "recovery", "connect database")),
417 "scan_batch_size" = convert(varchar(11), rep_agent_config(@dbid, "config", "scan batch size")),
418 "scan_timeout" = convert(varchar(11), rep_agent_config(@dbid, "config", "scan timeout")),
419 "retry_timeout" = convert(varchar(11), rep_agent_config(@dbid, "config", "retry timeout")),
420 "skip_ltl_errors" = convert(varchar(5), rep_agent_config(@dbid, "config", "skip ltl errors")),
421 "batch_ltl" = convert(varchar(5), rep_agent_config(@dbid, "config", "batch ltl")),
422 "send_warm_standby_xacts" = convert(varchar(5), rep_agent_config(@dbid, "config", "send warm standby xacts")),
423 "send_maint_xacts_to_replicate" = convert(varchar(5), rep_agent_config(@dbid, "config", "send maint xacts to replicate")),
424 "ha_failover" = convert(varchar(5), rep_agent_config(@dbid, "config", "ha failover")),
425 "skip_unsupported_features" = convert(varchar(5), rep_agent_config(@dbid, "config", "skip unsupported features")),
426 "short_ltl_keywords" = convert(varchar(5), rep_agent_config(@dbid, "config", "short ltl keywords")),
427 "send_buffer_size" = convert(varchar(11), rep_agent_config(@dbid, "config", "send buffer size")),
428 "priority" = convert(varchar(11), rep_agent_config(@dbid, "config", "priority")),
429 "send_structured_oqids" = rep_agent_config(@dbid, "config", "send structured oqids"),
430 "data_limits_filter_mode" = rep_agent_config(@dbid, "config", "data limits filter mode"),
431 "schema_cache_growth_factor" = rep_agent_config(@dbid, "config", "schema cache growth factor"),
432 "startup_delay" = convert(varchar(11), rep_agent_config(@dbid, "config", "startup delay")),
433 "bind_to_engine" = rep_agent_config(@dbid, "config", "bind to engine"),
434 "ltl_batch_size" = convert(varchar(11), rep_agent_config(@dbid, "config", "ltl batch size"))
435
436 , "multithread_rep_agent" = convert(varchar(5), rep_agent_config(@dbid, "config", "multithread rep agent"))
437 , "number_of_send_buffers" = convert(varchar(11), rep_agent_config(@dbid, "config", "number of send buffers"))
438 , "max_number_of_replication_paths" = convert(varchar(11), rep_agent_config(@dbid, "config", "max number replication paths"))
439 , "ltl_metadata_reduction" = convert(varchar(5), rep_agent_config(@dbid, "config", "ltl metadata reduction"))
440 , "activate_monitoring" = convert(varchar(5), rep_agent_config(@dbid, "config", "activate monitoring"))
441 , "ddl_path_for_unbound_objects" = convert(varchar(5), rep_agent_config(@dbid, "config", "ddl path for unbound objects"))
442
443 into #help_rep_agent_config1
444
445 /* Output and format the content of the temp table */
446 exec sp_autoformat #help_rep_agent_config1
447
448 /* drop the temp table */
449 drop table #help_rep_agent_config1
450
451 end
452 else
453 begin
454
455 /* 'select into' the config information into a temp table */
456 select "dbname" = name,
457 "auto_start" = rep_agent_config(dbid, "config", "auto start"),
458 "rs_servername" = convert(varchar(30), rep_agent_config(dbid, "config", "rs servername")),
459 "rs_username" = convert(varchar(30), rep_agent_config(dbid, "config", "rs username")),
460 "connect_dataserver" = convert(varchar(30), rep_agent_config(dbid, "recovery", "connect dataserver")),
461 "connect_database" = convert(varchar(30), rep_agent_config(dbid, "recovery", "connect database")),
462 "scan_batch_size" = convert(varchar(11), rep_agent_config(dbid, "config", "scan batch size")),
463 "scan_timeout" = convert(varchar(11), rep_agent_config(dbid, "config", "scan timeout")),
464 "retry_timeout" = convert(varchar(11), rep_agent_config(dbid, "config", "retry timeout")),
465 "skip_ltl_errors" = convert(varchar(5), rep_agent_config(dbid, "config", "skip ltl errors")),
466 "batch_ltl" = convert(varchar(5), rep_agent_config(dbid, "config", "batch ltl")),
467 "send_warm_standby_xacts" = convert(varchar(5), rep_agent_config(dbid, "config", "send warm standby xacts")),
468 "send_maint_xacts_to_replicate" = convert(varchar(5), rep_agent_config(dbid, "config", "send maint xacts to replicate")),
469 "ha_failover" = convert(varchar(5), rep_agent_config(dbid, "config", "ha failover")),
470 "skip_unsupported_features" = convert(varchar(5), rep_agent_config(dbid, "config", "skip unsupported features")),
471 "short_ltl_keywords" = convert(varchar(5), rep_agent_config(dbid, "config", "short ltl keywords")),
472 "send_buffer_size" = convert(varchar(11), rep_agent_config(dbid, "config", "send buffer size")),
473 "priority" = convert(varchar(11), rep_agent_config(dbid, "config", "priority")),
474 "send_structured_oqids" = rep_agent_config(dbid, "config", "send structured oqids"),
475 "data_limits_filter_mode" = rep_agent_config(dbid, "config", "data limits filter mode"),
476 "schema_cache_growth_factor" = rep_agent_config(dbid, "config", "schema cache growth factor"),
477 "startup_delay" = convert(varchar(11), rep_agent_config(dbid, "config", "startup delay")),
478 "bind_to_engine" = rep_agent_config(dbid, "config", "bind to engine"),
479 "ltl_batch_size" = convert(varchar(11), rep_agent_config(dbid, "config", "ltl batch size"))
480
481 , "multithread_rep_agent" = convert(varchar(5), rep_agent_config(@dbid, "config", "multithread rep agent"))
482 , "number_of_send_buffers" = convert(varchar(11), rep_agent_config(@dbid, "config", "number of send buffers"))
483 , "max_number_of_replication_paths" = convert(varchar(11), rep_agent_config(@dbid, "config", "max number replication paths"))
484 , "ltl_metadata_reduction" = convert(varchar(5), rep_agent_config(@dbid, "config", "ltl metadata reduction"))
485 , "activate_monitoring" = convert(varchar(5), rep_agent_config(@dbid, "config", "activate monitoring"))
486 , "ddl_path_for_unbound_objects" = convert(varchar(5), rep_agent_config(@dbid, "config", "ddl path for unbound objects"))
487
488 into #help_rep_agent_config2
489 from master.dbo.sysdatabases
490 where 1 = (case when ((status3 & @skip_status) = 0)
491 then is_rep_agent_enabled(dbid) else 0
492 end)
493
494 /* Output and format the content of the temp table */
495 exec sp_autoformat #help_rep_agent_config2
496
497 /* drop the temp table */
498 drop table #help_rep_agent_config2
499
500 end
501 end
502 /* If category is "security", or "all" */
503 if (@whattoprint in ("security", "all"))
504 begin
505 /* Print the title, and set the category. */
506 print "Replication Agent Security Configuration"
507
508 /* If dbname was specified, do specified database. */
509 if @dbname is not NULL
510 begin
511 /* Retrieve the information requested. */
512 select "dbname" = @dbname,
513 "security_mechanism" =
514 rep_agent_config(@dbid, "security",
515 "security mechanism"),
516 "unified_login" =
517 convert(varchar(5), rep_agent_config(@dbid, "security",
518 "unified login")),
519 "msg_confidentiality" =
520 convert(varchar(5), rep_agent_config(@dbid, "security",
521 "msg confidentiality")),
522 "msg_integrity" =
523 convert(varchar(5), rep_agent_config(@dbid, "security",
524 "msg integrity")),
525 "msg_replay_detection" =
526 convert(varchar(5), rep_agent_config(@dbid, "security",
527 "msg replay detection")),
528 "msg_origin_check" =
529 convert(varchar(5), rep_agent_config(@dbid, "security",
530 "msg origin check")),
531 "msg_out_of_sequence_check" =
532 convert(varchar(5), rep_agent_config(@dbid, "security",
533 "msg out-of-sequence check")),
534 "mutual_authentication" =
535 convert(varchar(5), rep_agent_config(@dbid, "security",
536 "mutual authentication")),
537 "net_password_encryption" =
538 convert(varchar(5), rep_agent_config(@dbid, "security",
539 "net password encryption"))
540 into #help_rep_agent_security1
541
542 /* Output and format the content of the temp table. */
543 /* And because the column name 'msg_out_of_sequence_check' should */
544 /* be renamed as 'msg_out-of-sequence_check', the selectlist is */
545 /* set for 'sp_autoformat' */
546 exec sp_autoformat #help_rep_agent_security1,
547 "'dbname' = dbname,
548 'security_mechanism' = security_mechanism,
549 'unified_login' = unified_login,
550 'msg_confidentiality' = msg_confidentiality,
551 'msg_integrity' = msg_integrity,
552 'msg_replay_detection' = msg_replay_detection,
553 'msg_origin_check' = msg_origin_check,
554 'msg_out-of-sequence_check' = msg_out_of_sequence_check,
555 'mutual_authentication' = mutual_authentication,
556 'net_password_encryption'= net_password_encryption"
557
558 /* drop the temp table */
559 drop table #help_rep_agent_security1
560
561 end
562 else
563 /* If dbname was not specified, do all enabled databases. */
564 begin
565 /* Retrieve the information requested. */
566 select "dbname" = name,
567 "security_mechanism" =
568 rep_agent_config(dbid, "security",
569 "security mechanism"),
570 "unified_login" =
571 convert(varchar(5), rep_agent_config(dbid, "security",
572 "unified login")),
573 "msg_confidentiality" =
574 convert(varchar(5), rep_agent_config(dbid, "security",
575 "msg confidentiality")),
576 "msg_integrity" =
577 convert(varchar(5), rep_agent_config(dbid, "security",
578 "msg integrity")),
579 "msg_replay_detection" =
580 convert(varchar(5), rep_agent_config(dbid, "security",
581 "msg replay detection")),
582 "msg_origin_check" =
583 convert(varchar(5), rep_agent_config(dbid, "security",
584 "msg origin check")),
585 "msg_out_of_sequence_check" =
586 convert(varchar(5), rep_agent_config(dbid, "security",
587 "msg out-of-sequence check")),
588 "mutual_authentication" =
589 convert(varchar(5), rep_agent_config(dbid, "security",
590 "mutual authentication")),
591 "net_password_encryption" =
592 convert(varchar(5), rep_agent_config(dbid, "security",
593 "net password encryption"))
594 into #help_rep_agent_security2
595 from master.dbo.sysdatabases
596 where 1 = (case when ((status3 & @skip_status) = 0)
597 then is_rep_agent_enabled(dbid) else 0
598 end)
599
600 /* Output and format the content of the temp table. */
601 /* And because the column name 'msg_out_of_sequence_check' should */
602 /* be renamed as 'msg_out-of-sequence_check', the selectlist is */
603 /* set for 'sp_autoformat' */
604 exec sp_autoformat #help_rep_agent_security2,
605 "'dbname' = dbname,
606 'security_mechanism' = security_mechanism,
607 'unified_login' = unified_login,
608 'msg_confidentiality' = msg_confidentiality,
609 'msg_integrity' = msg_integrity,
610 'msg_replay_detection' = msg_replay_detection,
611 'msg_origin_check' = msg_origin_check,
612 'msg_out-of-sequence_check' = msg_out_of_sequence_check,
613 'mutual_authentication' = mutual_authentication,
614 'net_password_encryption'= net_password_encryption"
615
616 /* drop the temp table */
617 drop table #help_rep_agent_security2
618
619 end
620 end
621 if (@whattoprint in ("send", "all"))
622 begin
623 print "Replication Agent Send status"
624 if @dbname is not NULL
625 begin
626 /* For a given database */
627
628 /* 'select into' the send information into a temp table */
629 select "dbname" = @dbname,
630 "sender_spid" = send.SPID,
631 "total_send_buffers" = send.MessageQueueSize,
632 "send_buffers_used" = send.MessagesInQueue
633 into #help_rep_agent_send1
634 from master..monRepSenders send
635 where send.DBID = @dbid
636
637 /* Output and format the content of the temp table */
638 exec sp_autoformat #help_rep_agent_send1
639
640 /* drop the temp table */
641 drop table #help_rep_agent_send1
642 end
643 else
644 begin
645 /* 'select into' the send information into a temp table */
646 select "dbname" = db.name,
647 "sender_spid" = send.SPID,
648 "total_send_buffers" = send.MessageQueueSize,
649 "send_buffers_used" = send.MessagesInQueue
650 into #help_rep_agent_send2
651 from master.dbo.sysdatabases db,
652 master..monRepSenders send
653 where 1 = (case when ((db.status3 & @skip_status) = 0)
654 then is_rep_agent_enabled(db.dbid) else 0
655 end) and
656 send.DBID = db.dbid
657
658 /* Output and format the content of the temp table */
659 exec sp_autoformat #help_rep_agent_send2
660
661 /* drop the temp table */
662 drop table #help_rep_agent_send2
663 end
664 end
665
666 return (0)
667
668
exec sp_procxmode 'sp_help_rep_agent', 'AnyMode'
go
Grant Execute on sp_help_rep_agent to public
go
| DEFECTS |
 QJWI 5 Join or Sarg Without Index |
193 |
| QJWI 5 Join or Sarg Without Index |
219 |
| QJWI 5 Join or Sarg Without Index |
287 |
 MINU 4 Unique Index with nullable columns master..sysmessages |
master..sysmessages |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
196 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
222 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
250 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
269 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
291 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
315 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
347 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
374 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
397 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
446 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
495 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
546 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
604 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
638 |
| MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
659 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type} |
51 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
62 |
| QTYP 4 Comparison type mismatch smallint = int |
62 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
218 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
312 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
337 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
394 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
656 |
| TNOI 4 Table with no index master..monRepScanners |
master..monRepScanners |
| TNOI 4 Table with no index master..monRepSenders |
master..monRepSenders |
| TNOU 4 Table with no unique index master..spt_values |
master..spt_values |
 MGTP 3 Grant to public master..monRepScanners |
|
| MGTP 3 Grant to public master..monRepSenders |
|
| MGTP 3 Grant to public master..spt_values |
|
| MGTP 3 Grant to public master..sysdatabases |
|
| MGTP 3 Grant to public master..sysmessages |
|
| MGTP 3 Grant to public sybsystemprocs..sp_help_rep_agent |
|
| MNER 3 No Error Check should check @@error after select into |
183 |
| MNER 3 No Error Check should check return value of exec |
196 |
| MNER 3 No Error Check should check @@error after select into |
205 |
| MNER 3 No Error Check should check return value of exec |
222 |
| MNER 3 No Error Check should check @@error after select into |
239 |
| MNER 3 No Error Check should check return value of exec |
250 |
| MNER 3 No Error Check should check @@error after select into |
258 |
| MNER 3 No Error Check should check return value of exec |
269 |
| MNER 3 No Error Check should check @@error after select into |
277 |
| MNER 3 No Error Check should check return value of exec |
291 |
| MNER 3 No Error Check should check @@error after select into |
300 |
| MNER 3 No Error Check should check return value of exec |
315 |
| MNER 3 No Error Check should check @@error after select into |
321 |
| MNER 3 No Error Check should check return value of exec |
347 |
| MNER 3 No Error Check should check @@error after select into |
363 |
| MNER 3 No Error Check should check return value of exec |
374 |
| MNER 3 No Error Check should check @@error after select into |
382 |
| MNER 3 No Error Check should check return value of exec |
397 |
| MNER 3 No Error Check should check @@error after select into |
411 |
| MNER 3 No Error Check should check return value of exec |
446 |
| MNER 3 No Error Check should check @@error after select into |
456 |
| MNER 3 No Error Check should check return value of exec |
495 |
| MNER 3 No Error Check should check @@error after select into |
512 |
| MNER 3 No Error Check should check return value of exec |
546 |
| MNER 3 No Error Check should check @@error after select into |
566 |
| MNER 3 No Error Check should check return value of exec |
604 |
| MNER 3 No Error Check should check @@error after select into |
629 |
| MNER 3 No Error Check should check return value of exec |
638 |
| MNER 3 No Error Check should check @@error after select into |
646 |
| MNER 3 No Error Check should check return value of exec |
659 |
| MUCO 3 Useless Code Useless Brackets |
66 |
| MUCO 3 Useless Code Useless Brackets |
84 |
| MUCO 3 Useless Code Useless Brackets |
94 |
| MUCO 3 Useless Code Useless Brackets |
96 |
| MUCO 3 Useless Code Useless Brackets |
99 |
| MUCO 3 Useless Code Useless Brackets |
111 |
| MUCO 3 Useless Code Useless Brackets |
130 |
| MUCO 3 Useless Code Useless Brackets |
146 |
| MUCO 3 Useless Code Useless Brackets |
151 |
| MUCO 3 Useless Code Useless Brackets |
153 |
| MUCO 3 Useless Code Useless Brackets |
166 |
| MUCO 3 Useless Code Useless Brackets |
170 |
| MUCO 3 Useless Code Useless Brackets |
175 |
| MUCO 3 Useless Code Useless Brackets |
215 |
| MUCO 3 Useless Code Useless Brackets |
228 |
| MUCO 3 Useless Code Useless Brackets |
236 |
| MUCO 3 Useless Code Useless Brackets |
309 |
| MUCO 3 Useless Code Useless Brackets |
329 |
| MUCO 3 Useless Code Useless Brackets |
330 |
| MUCO 3 Useless Code Useless Brackets |
342 |
| MUCO 3 Useless Code Useless Brackets |
355 |
| MUCO 3 Useless Code Useless Brackets |
391 |
| MUCO 3 Useless Code Useless Brackets |
403 |
| MUCO 3 Useless Code Useless Brackets |
490 |
| MUCO 3 Useless Code Useless Brackets |
503 |
| MUCO 3 Useless Code Useless Brackets |
596 |
| MUCO 3 Useless Code Useless Brackets |
621 |
| MUCO 3 Useless Code Useless Brackets |
653 |
| MUCO 3 Useless Code Useless Brackets |
666 |
| QAFM 3 Var Assignment from potentially many rows |
49 |
| QAPT 3 Access to Proxy Table master..monRepScanners |
190 |
| QAPT 3 Access to Proxy Table master..monRepSenders |
191 |
| QAPT 3 Access to Proxy Table master..monRepScanners |
213 |
| QAPT 3 Access to Proxy Table master..monRepSenders |
214 |
| QAPT 3 Access to Proxy Table master..monRepScanners |
246 |
| QAPT 3 Access to Proxy Table master..monRepSenders |
265 |
| QAPT 3 Access to Proxy Table master..monRepScanners |
284 |
| QAPT 3 Access to Proxy Table master..monRepSenders |
285 |
| QAPT 3 Access to Proxy Table master..monRepScanners |
308 |
| QAPT 3 Access to Proxy Table master..monRepSenders |
328 |
| QAPT 3 Access to Proxy Table master..monRepScanners |
370 |
| QAPT 3 Access to Proxy Table master..monRepScanners |
389 |
| QAPT 3 Access to Proxy Table master..monRepSenders |
634 |
| QAPT 3 Access to Proxy Table master..monRepSenders |
652 |
| QCTC 3 Conditional Table Creation |
183 |
| QCTC 3 Conditional Table Creation |
205 |
| QCTC 3 Conditional Table Creation |
239 |
| QCTC 3 Conditional Table Creation |
258 |
| QCTC 3 Conditional Table Creation |
277 |
| QCTC 3 Conditional Table Creation |
300 |
| QCTC 3 Conditional Table Creation |
321 |
| QCTC 3 Conditional Table Creation |
363 |
| QCTC 3 Conditional Table Creation |
382 |
| QCTC 3 Conditional Table Creation |
411 |
| QCTC 3 Conditional Table Creation |
456 |
| QCTC 3 Conditional Table Creation |
512 |
| QCTC 3 Conditional Table Creation |
566 |
| QCTC 3 Conditional Table Creation |
629 |
| QCTC 3 Conditional Table Creation |
646 |
| QISO 3 Set isolation level |
37 |
| QNAJ 3 Not using ANSI Inner Join |
190 |
| QNAJ 3 Not using ANSI Inner Join |
212 |
| QNAJ 3 Not using ANSI Inner Join |
284 |
| QNAJ 3 Not using ANSI Inner Join |
307 |
| QNAJ 3 Not using ANSI Inner Join |
327 |
| QNAJ 3 Not using ANSI Inner Join |
389 |
| QNAJ 3 Not using ANSI Inner Join |
651 |
| QNUA 3 Should use Alias: Column name should use alias db |
321 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid} |
60 |
| VNRD 3 Variable is not read @sptlang |
63 |
| VNRD 3 Variable is not read @procval |
154 |
| VUNU 3 Variable is not used @msg |
17 |
| VUNU 3 Variable is not used @type |
20 |
 MSUB 2 Subquery Marker |
59 |
| MTR1 2 Metrics: Comments Ratio Comments: 23% |
10 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 42 = 46dec - 6exi + 2 |
10 |
| MTR3 2 Metrics: Query Complexity Complexity: 230 |
10 |
| PRED_QUERY_COLLECTION 2 {mrs=master..monRepScanners, mrs2=master..monRepSenders} 0 |
183 |
| PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, mrs=master..monRepScanners, mrs2=master..monRepSenders} 0 |
205 |
| PRED_QUERY_COLLECTION 2 {mrs=master..monRepScanners, mrs2=master..monRepSenders} 0 |
277 |
| PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, mrs=master..monRepScanners} 0 |
300 |
| PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, mrs=master..monRepSenders} 0 |
321 |
| PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, mrs=master..monRepScanners} 0 |
382 |
| PRED_QUERY_COLLECTION 2 {d=master..sysdatabases, mrs=master..monRepSenders} 0 |
646 |
