1
2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3
4 /*
5 ** 17756, "The execution of the stored procedure '%1!' in database
6 ** '%2!' was aborted because there was an error in writing the
7 ** replication log record."
8 */
9
10 create procedure sp_procxmode
11 @procname varchar(767) = null,
12 @execmode varchar(30) = null
13 as
14
15 declare @uid int
16 declare @oid int
17 declare @msg varchar(250) /* message text */
18 declare @dbname varchar(255)
19 declare @owner varchar(30)
20 declare @objname varchar(257)
21 declare @objid int
22 declare @alltranmask int
23 declare @retcode int
24 declare @cmd varchar(255)
25
26 if @@trancount = 0
27 begin
28 set chained off
29 end
30
31 set transaction isolation level 1
32
33 /*
34 ** If either parameter is null we will be joining with a temporary table
35 ** to convert transaction mode numbers (0, 16, 32, 256) to strings
36 ** ("Unchained", "Chained", "Any Mode", "Dynamic Ownership Chain").
37 ** The "Dynamic Ownership Chain" is not an exclusive mode. It exists
38 ** in conjunction with one of the other three.
39 */
40 if ((@procname is null) or (@execmode is null))
41 begin
42 create table #execmode(intval integer, charval varchar(30))
43 insert into #execmode values (0, 'Unchained')
44 insert into #execmode values (16, 'Chained')
45 insert into #execmode values (32, 'Any Mode')
46 insert into #execmode values (256, 'Dynamic Ownership Chain')
47
48 select @alltranmask = 16 + 32
49 end
50
51 /*
52 ** If the first parameter is null, we're to report the transaction-modes
53 ** of every stored procedure in the current database.
54 */
55 if (@procname is null)
56 begin
57 select 'procedure name' = o.name, 'user name' = user_name(o.uid),
58 'transaction mode' = t.charval
59 from sysobjects o, #execmode t
60 where ((o.type = 'P') or (o.type = 'XP')) and
61 (((o.sysstat2 & t.intval) != 0) or
62 ((t.intval = 0) and (o.sysstat2 & @alltranmask) = 0))
63 order by o.name
64
65 return (0)
66 end
67
68 /*
69 ** If only the second parameter is null, we will report the
70 ** transaction-mode of the specified procedure.
71 */
72 if ((@procname is not null) and (@execmode is null))
73 begin
74 /*
75 ** Check if proc name is prefixed with the dbname. The proc sp_namecrack
76 ** cannot be used, because sp_procxmode is created before and it
77 ** would cause a dependency failure at creation time.
78 */
79 if @procname like "%.%.%.%"
80 begin
81 select @procname = substring(@procname, charindex(".", @procname) + 1, 125)
82 end
83
84 if @procname like "%.%.%"
85 begin
86 select @dbname = substring(@procname, 1, charindex(".", @procname) - 1)
87 select @procname = substring(@procname, charindex(".", @procname) + 1, 125)
88 end
89
90 if @procname like "%.%"
91 begin
92 select @owner = substring(@procname, 1, charindex(".", @procname) - 1)
93 select @procname = substring(@procname, charindex(".", @procname) + 1, 125)
94 end
95
96 select @objname = @procname
97
98 /*
99 ** If a database qualifies, it has to be this database.
100 ** Execute the procedure in the target database.
101 */
102 if ((@dbname is not null) and (@dbname != db_name()))
103 begin
104 select @cmd = @dbname + '.dbo.sp_procxmode'
105 select @procname = @owner + '.' + @objname
106 exec @retcode = @cmd @procname
107 return (@retcode)
108 end
109
110 select @objid = object_id(@procname)
111 if (@objid is not null)
112 begin
113 if not exists (select 1 from sysobjects
114 where id = @objid
115 and type in ('P', 'XP'))
116 begin
117 select @objid = null
118 end
119 end
120
121 if (@objid is null)
122 begin
123 /*
124 ** Force an error message, since we haven't
125 ** installed sp_getmessage yet.
126 */
127 dbcc update_tmode(@procname, 'anymode')
128 return (1)
129 end
130
131
132 select 'procedure name' = o.name, 'user name' = user_name(o.uid),
133 'transaction mode' = t.charval
134 from sysobjects o, #execmode t
135 where (@objid = o.id) and
136 ((o.type = 'P') or (o.type = 'XP')) and
137 (((o.sysstat2 & t.intval) != 0) or
138 ((t.intval = 0) and (o.sysstat2 & @alltranmask) = 0))
139
140 return (0)
141 end
142
143 /*
144 ** If neither parameter is null, we're to set the transaction-mode
145 ** of the specified procedure to the specified value.
146 */
147 if ((@procname is not null) and (@execmode is not null))
148 begin
149 /* Start the transaction to log the execution of this procedure.
150 **
151 ** IMPORTANT: The name "rs_logexec is significant and is used by
152 ** Replication Server
153 */
154 begin transaction rs_logexec
155
156 /*
157 ** Update transaction-mode in both sysobjects and DES.
158 */
159 dbcc update_tmode(@procname, @execmode)
160
161 /* If dbcc update_tmode returned an error, return
162 ** an error now.
163 */
164 if (@@error != 0)
165 begin
166 rollback transaction
167 return (1)
168 end
169
170 /*
171 ** Write the log record to replicate this invocation
172 ** of the stored procedure.
173 */
174 if (logexec() != 1)
175 begin
176 /*
177 ** 17756, "The execution of the stored procedure '%1!'
178 ** in database '%2!' was aborted because there
179 ** was an error in writing the replication log
180 ** record."
181 */
182 select @dbname = db_name()
183 raiserror 17756, 'sp_procxmode', @dbname
184 rollback transaction
185 return (1)
186 end
187
188 commit transaction
189
190 end
191
exec sp_procxmode 'sp_procxmode', 'AnyMode'
go
Grant Execute on sp_procxmode to public
go
