Is this a "Bug" of SQL2000?
-
Hello everyone,
I noticed a weid result when I was using system table to get script of user defined function.
I use the following script to retrieve the content of UDF:
select so.name as [FNName],
sc.colid as [ID], sc.name as [Parameter],
st.name as [DataType], sc.length as [Size],
sp.name as [Caption], sp.value as [ParameterEP], sm.text as [Script]
from sysobjects so
left outer join syscolumns sc on sc.id = so.id
left outer join systypes st on sc.xusertype = st.xusertype
left outer join syscomments sm on sm.id = so.id
left outer join sysproperties sp on (so.id = sp.id and sp.smallid = sc.colid and sp.type = 4)
where so.id = (select id from sysobjects where name ='fnBuildBinderName') and sc.colid = 1
I got almost all the text of that function, as follows:
/*
-- Create date: 5/19/2005
-- Written by: Kim Abels
-- Reviewed by:
-- Approved by:
-- Description:
Returns the concatenated binder name
*/
CREATE FUNCTION dbo.fnBuildBinderName(@strDate VARCHAR(10),@strDiv VARCHAR(5),@strCP VARCHAR(5),@strCL VARCHAR(5),
@strArea VARCHAR(5),@strMN VARCHAR(5))
RETURNS VARCHAR(45)
AS
BEGIN
DECLARE @strBinderName VARCHAR(45)
set @strbindername = @strDiv + '-' + @strCP + '-' + @strCL + '-' + @strArea
IF @strMN<> NULL
BEGIN
set @strBinderName = @strbinderName + '-' + @strMN
END
IF @strDate <> NULL
BEGIN
set @strBinderName = @strDate + '-' + @strBinderName
END
RETURN @strBinderName
-- 1 asl;dkfjasl;dfjkasdl;fkjasdf
-- 2 asl;dkfjasl;dfjkasdl;fkjasdf
-- 3 asl;dkfjasl;dfjkasdl;fkjasdf
-- 4 asl;dkfjasl;dfjkasdl;fkjasdf
-- 5 asl;dkfjasl;dfjkasdl;fkjasdf
-- 6 asl;dkfjasl;dfjkasdl;fkjasdf
-- 7 asl;dkfjasl;dfjkasdl;fkjasdf
-- 8 asl;dkfjasl;dfjkasdl;fkjasdf
-- 9 asl;dkfjasl;dfjkasdl;fkjasdf
-- 10 asl;dkfjasl;dfjkasdl;fkjasdf
-- 11 asl;dkfjasl;dfjkasdl;fkjasdf
-- 12 asl;dkfjasl;dfjkasdl;fkjasdf
-- 13 asl;dkfjasl;dfjkasdl;fkjasdf
-- 14 asl;dkfjasl;dfjkasdl;fkjasdf
-- 15 asl;dkfjasl;dfjkasdl;fkjasdf
-- 16 asl;dkfjasl;dfjkasdl;fkjasdf
-- 17 asl;dkfjasl;dfjkasdl;fkjasdf
-- 18 asl;dkfjasl;dfjkasdl;fkjasdf
-- 19 asl;dkfjasl;dfjkasdl;fkjasdf
-- 20 asl;dkfjasl;dfjkasdl;fkjasdf
-- 21 asl;dkfjasl;dfjkasdl;fkjasdf
-- 22 asl;dkfjasl;dfjkasdl;fkjasdf
-- 23 asl;dkfjasl;dfjkasdl;fkjasdf
-- 24 asl;dkfjasl;dfjkasdl;fkjasdf
-- 25 asl;dkfjasl;dfjkasdl;fkjasdf
-- 26 asl;dkfjasl;dfjkasdl;fkjasdf
-- 27 asl;dkfjasl;dfjkasdl;fkjasdf
-- 28 asl;dkfjasl;dfjkasdl;fkjasdf
-- 29 asl;dkfjasl;dfjkasdl;fkjasdf
-- 30 asl;dkfjasl;dfjkasdl;fkjasdf
-- 31 asl;dkfjasl;dfjkasdl;fkjasdf
-- 32 asl;dkfjasl;dfjkasdl;fkjasdf
-- 33 asl;dkfjasl;dfjkasdl;fkjasdf
-- 34 asl;dkfjasl;dfjkasdl;fkjasdf
-- 35 asl;dkfjasl;dfjkasdl;fkjasdf
-- 36 asl;dkfjasl;dfjkasdl;fkjasdf
-- 37 asl;dkfjasl;dfjkasdl;fkjasdf
-- 38 asl;dkfjasl;dfjkasdl;fkjasdf
-- 39 asl;dkfjasl;dfjkasdl;fkjasdf
-- 40 asl;dkfjasl;dfjkasdl;fkjasdf
-- 41 asl;dkfjasl;dfjkasdl;fkjasdf
-- 42 asl;dkfjasl;dfjkasdl;fkjasdf
-- 43 asl;dkfjasl;dfjkasdl;fkjasdf
-- 44 asl;dkfjasl;dfjkasdl;fkjasdf
-- 45 asl;dkfjasl;dfjkasdl;fkjasdf
-- 46 asl;dkfjasl;dfjkasdl;fkjasdf
-- 47 asl;dkfjasl;dfjkasdl;fkjasdf
-- 48 asl;dkfjasl;dfjkasdl;fkjasdf
-- 49 asl;dkfjasl;dfjkasdl;fkjasdf
-- 50 asl;dkfjasl;dfjkasdl;fkjasdf
-- 51 asl;dkfjasl;dfjkasdl;fkjasdf
-- 52 asl;dkfjasl;dfjkasdl;fkjasdf
-- 53 asl;dkfjasl;dfjkasdl;fkjasdf
-- 54 asl;dkfjasl;dfjkasdl;fkjasdf
-- 55 asl;dkfjasl;dfjkasdl;fkjasdf
-- 56 asl;dkfjasl;dfjkasdl;fkjasdf
-- 57 asl;dkfjasl;dfjkasdl;fkjasdf
-- 58 asl;dkfjasl;dfjkasdl;fkjasdf
-- 59 asl;dkfjasl;dfjkasdl;fkjasdf
-- 60 asl;dkfjasl;dfjkasdl;fkjasdf
-- 61 asl;dkfjasl;dfjkasdl;fkjasdf
-- 62 asl;dkfjasl;dfjkasdl;fkjasdf
-- 63 asl;dkfjasl;dfjkasdl;fkjasdf
-- 64 asl;dkfjasl;dfjkasdl;fkjasdf
-- 65 asl;dkfjasl;dfjkasdl;fkjasdf
-- 66 asl;dkfjasl;dfjkasdl;fkjasdf
-- 67 asl;dkfjasl;dfjkasdl;fkjasdf
-- 68 asl;dkfjasl;dfjkasdl;fkjasdf
-- 69 asl;dkfjasl;dfjkasdl;fkjasdf
-- 70 asl;dkfjasl;dfjkasdl;fkjasdf
-- 71 asl;dkfjasl;dfjkasdl;fkjasdf
-- 72 asl;dkfjasl;dfjkasdl;fkjasdf
-- 73 asl;dkfjasl;dfjkasdl;fkjasdf
-- 74 asl;dkfjasl;dfjkasdl;fkjasdf
-- 75 asl;dkfjasl;dfjkasdl;fkjasdf
-- 76 asl;dkfjasl;dfjkasdl;fkjasdf
-- 77 asl;dkfjasl;dfjkasdl;fkjasdf
-- 78 asl;dkfjasl;dfjkasdl;fkjasdf
-- 79 asl;dkfjasl;dfjkasdl;fkjasdf
-- 80 asl;dkfjasl;dfjkasdl;fkjasdf
-- 81 asl;dkfjasl;dfjkasdl;fkjasdf
-- 82 asl;dkfjasl;dfjkasdl;fkjasdf
-- 83 asl;dkfjasl;dfjkasdl;fkjasdf
-- 84 asl;dkfjasl;dfjkasdl;fkjasdf
-- 85 asl;dkfjasl;dfjkasdl;fkjasdf
-- 86 asl;dkfjasl;dfjkasdl;fkjasdf
-- 87 asl;dkfjasl;dfjkasdl;fkjasdf
-- 88 asl;dkfjasl;dfjkasdl;fkjasdf
-- 89 asl;dkfjasl;dfjkasdl;fkjasdf
-- 90 asl;dkfjasl;dfjkasdl;fkjasdf
-- 91 asl;dkfjasl;dfjkasdl;fkjasdf
-- 92 asl;dkfjasl;dfjkasdl;fkjasdf
-- 93 asl;dkfjasl;dfjkasdl;fkjasdf
-- 94 asl;dkfjasl;dfjkasdl;fkjasdf
-- 95 asl;dkfjasl;dfjkasdl;fkjasdf
-- 96 asl;dkfjasl;dfjkasdl;fkjasdf
-- 97 asl;dkfjasl;dfjkasdl;fkjasdf
-- 98 asl;dkfjasl;dfjkasdl;fkjasdf
-- 99 asl;dkfjasl;dfjkasdl;fkjasdf
-- 100 asl;dkfjasl;dfjkasdl;fkjasdf
-- 101 asl;dkfjasl;dfjkasdl;fkjasdf
-- 102 asl;dkfjasl;dfjkasdl;fkjasdf
-- 103 asl;dkfjasl;dfjkasdl;fkjasdf
-- 104 asl;dkfjasl;dfjkasdl;fkjasdf
-- 105 asl;dkfjasl;dfjkasdl;fkjasdf
-- 106 asl;dkfjasl;dfjkasdl;fkjasdf
-- 107 asl;dkfjasl;dfjkasdl;fkjasdf
-- 108 asl;dkfjasl;dfjkasdl;fkjasdf
-- 109 asl;dkfjasl;dfjkasdl;fkjasdf
-- 110 asl;dkfjasl;dfjkasdl;fkjasdf
-- 111 asl;dkfjasl;dfjkasdl;fkjasdf
-- 112 asl;dkfjasl;dfjkasdl;fkjasdf
-- 113 asl;dkfjasl;dfjkasdl;fkjasdf
-- 114 asl;dkfjasl;dfjkasdl;fkjasdf
-- 115 asl;dkfjasl;dfjkasdl;fkjasdf
-- 116 asl;dkfjasl;dfjkasdl;fkjasdf
-- 117 asl;dkfjasl;dfjkasdl;fkjasdf
-- 118 asl;dkfjasl;dfjkasdl;fkjasdf
-- 119 asl;dkfjasl;dfjkasdl;fkjasdf
-- 120 asl;dkfjasl;dfjkasdl;fkjasdf
-- 121 asl;dkfjasl;dfjkasdl;fkjasdf
-- 122 asl;dkfjasl;dfjkasdl;fkjasdf
-- 123 asl;dkfjasl;dfjkasdl;fkjasdf
-- 124 asl;dkfjasl;dfjkasdl;fkjasdf
-- 125 asl;dkfjasl;dfjkasdl;fkjasdf
-- 126 asl;dkfjasl;dfjkasdl;fkjasdf
-- 127 asl;dkfjasl;dfjkasdl;fkjasdf
-- 128 asl;dkfjasl;dfjkasdl;fkjasdf
-- 129 asl;dkfjasl;dfjkasdl;fkjasdf
-- 130 asl;dkfjasl;dfjkasdl;fkjasdf
-- 131 asl;dkfjasl;dfjkasdl;fkjasdf
-- 132 asl;dkfjasl;dfjkasdl;fkjasdf
-- 133 asl;dkfjasl;dfjkasdl;fkjasdf
-- 134 asl;dkfjasl;dfjkasdl;fkjasdf
-- 135 asl;dkfjasl;dfjkasdl;fkjasdf
-- 136 asl;dkfjasl;dfjkasdl;fkjasdf
-- 137 asl;dkfjasl;dfjkasdl;fkjasdf
-- 138 asl;dkfjasl;dfjkasdl;fkjasdf
-- 139 asl;dkfjasl;dfjkasdl;fkjasdf
-- 140 asl;dkfjasl;dfjkasdl;fkjasdf
-- 141 asl;dkfjasl;dfjkasdl;fkjasdf
-- 142 asl;dkfjasl;dfjkasdl;fkjasdf
-- 143 asl;dkfjasl;dfjkasdl;fkjasdf
-- 144 asl;dkfjasl;dfjkasdl;fkjasdf
-- 145 asl;dkfjasl;dfjkasdl;fkjasdf
-- 146 asl;dkfjasl;dfjkasdl;fkjasdf
-- 147 asl;dkfjasl;dfjkasdl;fkjasdf
-- 148 asl;dkfjasl;dfjkasdl;fkjasdf
-- 149 asl;dkfjasl;dfjkasdl;fkjasdf
-- 150 asl;dkfjasl;dfjkasdl;fkjasdf
-- 151 asl;dkfjasl;dfjkasdl;fkjasdf
-- 152 asl;dkfjasl;dfjkasdl;fkjasdf
-- 153 asl;dkfjasl;dfjkasdl;fkjasdf
-- 154 asl;dkfjasl;dfjkasdl;fkjasdf
-- 155 asl;dkfjasl;dfjkasdl;fkjasdf
-- 156 asl;dkfjasl;dfjkasdl;fkjasdf
-- 157 asl;dkfjasl;dfjkasdl;fkjasdf
-- 158 asl;dkfjasl;dfjkasdl;fkjasdf
-- 159 asl;dkfjasl;dfjkasdl;fkjasdf
-- 160 asl;dkfjasl;dfjkasdl;fkjasdf
-- 161 asl;dkfjasl;dfjkasdl;fkjasdf
-- 162 asl;dkfjasl;dfjkasdl;fkjasdf
-- 163 asl;dkfjasl;dfjkasdl;fkjasdf
-- 164 asl;dkfjasl;dfjkasdl;fkjasdf
-- 165 asl;dkfjasl;dfjkasdl;fkjasdf
-- 166 asl;dkfjasl;dfjkasdl;fkjasdf
-- 167 asl;dkfjasl;dfjkasdl;fkjasdf
-- 168 asl;dkfjasl;dfjkasdl;fkjasdf
-- 169 asl;dkfjasl;dfjkasdl;fkjasdf
-- 170 asl;dkfjasl;dfjkasdl;fkjasdf
-- 171 asl;dkfjasl;dfjkasdl;fkjasdf
-- 172 asl;dkfjasl;dfjkasdl;fkjasdf
-- 173 asl;dkfjasl;dfjkasdl;fkjasdf
-- 174 asl;dkfjasl;dfjkasdl;fkjasdf
-- 175 asl;dkfjasl;dfjkasdl;fkjasdf
-- 176 asl;dkfjasl;dfjkasdl;fkjasdf
-- 177 asl;dkfjasl;dfjkasdl;fkjasdf
-- 178 asl;dkfjasl;dfjkasdl;fkjasdf
-- 179 asl;dkfjasl;dfjkasdl;fkjasdf
-- 180 asl;dkfjasl;dfjkasdl;fkjasdf
-- 181 asl;dkfjasl;dfjkasdl;fkjasdf
-- 182 asl;dkfjasl;dfjkasdl;fkjasdf
-- 183 asl;dkfjasl;dfjkasdl;fkjasdf
-- 184 asl;dkfjasl;dfjkasdl;fkjasdf
-- 185 asl;dkfjasl;dfjkasdl;fkjasdf
-- 186 asl;dkfjasl;dfjkasdl;fkjasdf
-- 187 asl;dkfjasl;dfjkasdl;fkjasdf
-- 188 asl;dkfjasl;dfjkasdl;fkjasdf
-- 189 asl;dkfjasl;dfjkasdl;fkjasdf
-- 190 asl;dkfjasl;dfjkasdl;fkjasdf
-- 191 asl;dkfjasl;dfjkasdl;fkjasdf
-- 192 asl;dkfjasl;dfjkasdl;fkjasdf
-- 193 asl;dkfjasl;dfjkasdl;fkjasdf
-- 194 asl;dkfjasl;dfjkasdl;fkjasdf
-- 195 asl;dkfjasl;dfjkasdl;fkjasdf
-- 196 asl;dkfjasl;dfjkasdl;fkjasdf
-- 197 asl;dkfjasl;dfjkasdl;fkjasdf
-- 198 asl;dkfjasl;dfjkasdl;fkjasdf
-- 199 asl;dkfjasl;dfjkasdl;fkjasdf
-- 200 asl;dkfjasl;dfjkasdl;fkjasdf
-- 201 asl;dkfjasl;dfjkasdl;fkjasdf
-- 202 asl;dkfjasl;dfjkasdl;fkjasdf
-- 203 asl;dkfjasl;dfjkasdl;fkjasdf
-- 204 asl;dkfjasl;dfjkasdl;fkjasdf
-- 205 asl;dkfjasl;dfjkasdl;fkjasdf
-- 206 asl;dkfjasl;dfjkasdl;fkjasdf
-- 207 asl;dkfjasl;dfjkasdl;fkjasdf
-- 208 asl;dkfjasl;dfjkasdl;fkjasdf
-- 209 asl;dkfjasl;dfjkasdl;fkjasdf
-- 210 asl;dkfjasl;dfjkasdl;fkjasdf
-- 211 asl;dkfjasl;dfjkasdl;fkjasdf
-- 212 asl;dkfjasl;dfjkasdl;fkjasdf
-- 213 asl;dkfjasl;dfjkasdl;fkjasdf
-- 214 asl;dkfjasl;dfjkasdl;fkjasdf
-- 215 asl;dkfjasl;dfjkasdl;fkjasdf
-- 216 asl;dkfjasl;dfjkasdl;fkjasdf
-- 217 asl;dkfjasl;dfjkasdl;fkjasdf
-- 218 asl;dkfjasl;dfjkasdl;fkjasdf
-- 219 asl;dkfjasl;dfjkasdl;fkjasdf
-- 220 asl;dkfjasl;dfjkasdl;fkjasdf
-- 221 asl;dkfjasl;dfjkasdl;fkjasdf
-- 222 asl;dkfjasl;dfjkasdl;fkjasdf
-- 223 asl;dkfjasl;dfjkasdl;fkjasdf
-- 224 asl;dkfjasl;dfjkasdl;fkjasdf
-- 225 asl;dkfjasl;dfjkasdl;fkjasdf
-- 226 asl;dkfjasl;dfjkasdl;fkjasdf
-- 227 asl;dkfjasl;dfjkasdl;fkjasdf
-- 228 asl;dkfjasl;dfjkasdl;fkjasdf
-- 229 asl;dkfjasl;dfjkasdl;fkjasdf
-- 230 asl;dkfjasl;dfjkasdl;fkjasdf
-- 231 asl;dkfjasl;dfjkasdl;fkjasdf
-- 232 asl;dkfjasl;dfjkasdl;fkjasdf
-- 233 asl;dkfjasl;dfjkasdl;fkjasdf
-- 234 asl;dkfjasl;dfjkasdl;fkjasdf
-- 235 asl;dkfjasl;dfjkasdl;fkjasdf
-- 236 asl;dkfjasl;dfjkasdl;fkjasdf
-- 237 asl;dkfjasl;dfjkasdl;fkjasdf
-- 238 asl;dkfjasl;dfjkasdl;fkjasdf
-- 239 asl;dkfjasl;dfjkasdl;fkjasdf
-- 240 asl;dkfjasl;dfjkasdl;fkjasdf
-- 241 asl;dkfjasl;dfjkasdl;fkjasdf
-- 242 asl;dkfjasl;dfjkasdl;fkjasdf
-- 243 asl;dkfjasl;dfjkasdl;fkjasdf
-- 244 asl;dkfjasl;dfjkasdl;fkjasdf
-- 245 asl;dkfjasl;dfjkasdl;fkjasdf
-- 246 asl;dkfjasl;dfjkasdl;fkjasdf
-- 247 asl;dkfjasl;dfjkasdl;fkjasdf
-- 248 asl;dkfjasl;dfjkasdl;fkjasdf
-- 249 asl;dkfjasl;dfjkasdl;fkjasdf
-- 250 asl;dkfjasl;dfjkasdl;fkjasdf
-- 251 asl;dkfjasl;dfjkasdl;fkjasdf
-- 252 asl;dkfjasl;dfjkasdl;fkjasdf
-- 253 asl;dkfjasl;dfjkasdl;fkjasdf
-- 254 asl;dkfjasl;dfjkasdl;fkjasdf
-- 255 asl;dkfjasl;dfjkasdl;fkjasdf
-- 256 asl;dkfjasl;dfjkasdl;fkjasdf
-- 257 asl;dkfjasl;dfjkasdl;fkjasdf
-- 258 asl;dkfjasl;dfjkasdl;fkjasdf
-- 259 asl;dkfjasl;dfjkasdl;fkjasdf
-- 260 asl;dkfjasl;dfjkasdl;fkjasdf
-- 261 asl;dkfjasl;dfjkasdl;fkjasdf
-- 262 asl;dkfjasl;dfjkasdl;fkjasdf
-- 263 asl;dkfjasl;dfjkasdl;fkjasdf
-- 264 asl;dkfjasl;dfjkasdl;fkjasdf
-- 265 asl;dkfjasl;dfjkasdl;fkjasdf
-- 266 asl;dkfjasl;dfjkasdl;fkjasdf
-- 267 asl;dkfjasl;dfjkasdl;fkjasdf
-- 268 asl;dkfjasl;dfjkasdl;fkjasdf
-- 269 asl;dkfjasl;dfjkasdl;fkjasdf
-- 270 asl;dkfjasl;dfjkasdl;fkjasdf
-- 271 asl;dkfjasl;dfjkasdl;fkjasdf
-- 272 asl;dkfjasl;dfjkasdl;fkjasdf
-- 273 asl;dkfjasl;dfjkasdl;fkjasdf
-- 274 asl;dkfjasl;dfjkasdl;fkjasdf
-- 275 asl;dkfjasl;dfjkasdl;fkjasdf
-- 276 asl;dkfjasl;dfjkasdl;fkjasdf
-- 277 asl;dkfjasl;dfjkasdl;fkjasdf
-- 278 asl;dkfjasl;dfjkasdl;fkjasdf
-- 279 asl;dkfjasl;dfjkasdl;fkjasdf
-- 280 asl;dkfjasl;dfjkasdl;fkjasdf
-- 281 asl;dkfjasl;dfjkasdl;fkjasdf
-- 282 asl;dkfjasl;dfjkasdl;fkjasdf
-- 283 asl;dkfjasl;dfjkasdl;fkjasdf
-- 284 asl;dkfjasl;dfjkasdl;fkjasdf
-- 285 asl;dkfjasl;dfjkasdl;fkjasdf
-- 286 asl;dkfjasl;dfjkasdl;fkjasdf
-- 287 asl;dkfjasl;dfjkasdl;fkjasdf
-- 288 asl;dkfjasl;dfjkasdl;fkjasdf
-- 289 asl;dkfjasl;dfjkasdl;fkjasdf
-- 290 asl;dkfjasl;dfjkasdl;fkjasdf
-- 291 asl;dkfjasl;dfjkasdl;fkjasdf
-- 292 asl;dkfjasl;dfjkasdl;fkjasdf
-- 293 asl;dkfjasl;dfjkasdl;fkjasdf
-- 294 asl;dkfjasl;dfjkasdl;fkjasdf
-- 295 asl;dkfjasl;dfjkasdl;fkjasdf
-- 296 asl;dkfjasl;dfjkasdl;fkjasdf
-- 297 asl;dkfjasl;dfjkasdl;fkjasdf
END
However, something is missing:
From "--91 asldfjasdf... " to "--200asdlfjasdf", the data is missing.
But, when I use: exec sp_helptext fnBuildBinderName
everything is there.
What does it mean? does it mean the script is not integrated in system table syscomments? I have no idea, personnally I think something is wrong with that system table.
Can anyone try my code and tell me what's wrong with the code? or SQL2000?
Thanks.
-
This is not a bug. The text column of syscomments is nVarChar (4000). If the text spills over, then a new row is inserted and identified with an incrementing colid value.
-
I understand there is a 4000 limitation in text column of syscomments, in my script, actually more than one records are returned, which means the script has been splitted to different records.
But, problem is still there, ever the original script text is splitted, no data should be missing, if I concatenate all the splitted records together, I should be able to get a whole script, but in the first script I posted, it is not. That's weid because in the second script I posted, it is giving me the whole script, without any missing.
-
Can you show me how you are concatenating the script back together?
-
Hi RGR'Us, thank you for your reply, here is my front end code to concatenate the splitted records.
Public Function MergeDataSet(ByVal oldDS As DataSet, ByVal type As String) As DataSet
Dim i, j, k, colid As Integer
Dim oldscript, extrascript As String
Dim oldname, newname As String
Dim newDS As DataSet
newDS = oldDS.Clone
Dim newdr As DataRow
oldscript =
CType(oldDS.Tables(type).Rows(0).Item(7), String)
oldname = CType(oldDS.Tables(type).Rows(0).Item(0), String)
colid = 1
i = 0
j = 0
k = 0
Dim colnum As Integer = oldDS.Tables(type).Columns.Count
Dim rownum As Integer = oldDS.Tables(type).Rows.Count
'The first row is added to newDS
newdr = newDS.Tables(type).NewRow
For k = 0 To colnum - 1
If oldDS.Tables(type).Rows(i).Item(k) Is DBNull.Value Then
newdr.Item(k) = 0
Else
newdr.Item(k) = oldDS.Tables(type).Rows(i).Item(k)
End If
Next
newDS.Tables(type).Rows.Add(newdr)
If rownum > 1 Then
For i = 1 To rownum - 1
newname =
CType(oldDS.Tables(type).Rows(i).Item(0), String)
extrascript =
CType(oldDS.Tables(type).Rows(i).Item(7), String)
If newname = oldname Then
'if newname = oldname, it means there are more than two records for the same object
'hence the [Script] should be concatenated.
colid = colid + 1
newDS.Tables(type).Rows(j).Item(7) = oldscript + extrascript
Else
'add a new row into newDS
newdr = newDS.Tables(type).NewRow
j = j + 1
For k = 0 To colnum - 1
If oldDS.Tables(type).Rows(i).Item(k) Is DBNull.Value Then
newdr.Item(k) = 0
Else
newdr.Item(k) = oldDS.Tables(type).Rows(i).Item(k)
End If
Next
newDS.Tables(type).Rows.Add(newdr)
colid = 1
'start again for a new record
oldname = newname
oldscript =
CType(oldDS.Tables(type).Rows(i).Item(7), String)
End If
Next
End If
MergeDataSet = newDS
End Function
-
Can't help you there without debugging... specially since my .net courses are over 18 months old
. Seems okay but I don't see that little thing that makes this whole mess fail. Sorry if I can't help more than that. -
Hi RGR'Us,
Thanks for your help. I actually have found a bug of my own code, I am sorry about this posting. Thank you very much.
-
I think you're making things more difficult than they need to be. Use SQL server and left joins to get all the parts, then concatenate on the client. The C# code below dumps all my stored procs into a hash table, but the concept is there.
In my case, no stored proc requires more than 4 chunks of syscomments - if you have really large objects, add additional left joins:
// SQL to get all stored procedures.
sql = " select object_name(c1.id), convert(text, c1.text), convert(text, IsNull(c2.text, '')) , "
+ " convert(text, IsNull(c3.text, '')) , convert(text, IsNull(c4.text, '')) "
+ " from "
+ " syscomments c1 "
+ " inner join sysobjects as o on (o.id = c1.id and o.type = 'P' ) "
+ " left join syscomments c2 on ( c2.id = c1.id And c2.colid = 2 ) "
+ " left join syscomments c3 on ( c3.id = c1.id And c3.colid = 3 ) "
+ " left join syscomments c4 on ( c4.id = c1.id And c4.colid = 4 ) "
+ " where c1.colid = 1 and object_name(c1.id) Not Like 'dt_%' ";
scmd = new SqlCommand(sql, Conn);
srdr = scmd.ExecuteReader();
while (srdr.Read())
{
_ProcHash.Add(srdr.GetString(0), srdr.GetValue(1).ToString() + srdr.GetValue(2).ToString() +srdr.GetValue(3).ToString() + srdr.GetValue(4).ToString() );
}
srdr.Close();
-
Do you really stop at 4 joins... isn't this a little short??
-
select object_name(id), max(colid)
from syscomments
group by object_name(id)
having max(colid) > 4
No rows returned.
*shrug*
Code was posted just for concept. Obviously to make it completely bulletproof for any environment you'd add enough joins up to the max possible size of a sproc/function.
-
Hi PW, thanks for sharing your code.
My tool is used to retrieve info from any database, so I can't assume the size of objects, that makes me have to do it my way - get a bunch of records, concatenate them back as a whole record.
I wish I can use cursor or temp table and loop, but since I can't put the code somewhere like SP, so I have to realize this in front end code, like I posted above. I already fixed my bug so it works fine.
By the way, when I export my result to pdf using Crystal Report, I found a bug again, this time it is Crystal Report. CR cannot properly recognize the '[TAB]' character, for example, in one of my SP, I have the following code:
SET NOCOUNT ON --Required for RAISEERROR to work properly via AODB
Please be aware the space between ON and --Required is a [Tab], then the CR will not properly output it.
CR is really not good, by far I have already found three bugs of it (acknowleged by them).
-
colid is smallint so the theorical limit is 32767 joins
.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply