· 5 years ago · Mar 19, 2020, 02:06 PM
1/***********************************************************************
2 ICON SCHEMA
3
4 WSOL Conversion DB Schema
5 * CHANGED CQ, CQI IDS FROM INT TO BIGINT
6 * CQI ADDITIONAL COLUMNS POLICY_NUMBER, EFFECTIVE_DATE, CQIInsertion, CustomerId
7 * CQI EXPANDED FILEPATH, IRFILETYPE
8
9*************************************************************************/
10-- If using SQL Server 2005 the scripts do not need to be modified.
11-- If using SQL Server 2000 replace occurances of (MAX) with (4000) - NOTE: Include the parenthesis in the find and the replace
12
13IF EXISTS(SELECT name FROM sysobjects
14 WHERE name = 'ConversionAnns' AND type = 'U')
15 DROP TABLE ConversionAnns
16GO
17IF EXISTS(SELECT name FROM sysobjects
18 WHERE name = 'ConversionTask' AND type = 'U')
19 DROP TABLE ConversionTask
20GO
21IF EXISTS(SELECT name FROM sysobjects
22 WHERE name = 'ConversionQueueItems' AND type = 'U')
23 DROP TABLE ConversionQueueItems
24GO
25IF EXISTS(SELECT name FROM sysobjects
26 WHERE name = 'ConversionExecutionTimes' AND type = 'U')
27 DROP TABLE ConversionExecutionTimes
28GO
29IF EXISTS(SELECT name FROM sysobjects
30 WHERE name = 'ConversionProgress' AND type = 'U')
31 DROP TABLE ConversionProgress
32GO
33IF EXISTS(SELECT name FROM sysobjects
34 WHERE name = 'ConversionQueue' AND type = 'U')
35 DROP TABLE ConversionQueue
36GO
37IF EXISTS(SELECT name FROM sysobjects
38 WHERE name = 'ConversionMessageHistory' AND type = 'U')
39 DROP TABLE ConversionMessageHistory
40GO
41IF EXISTS(SELECT name FROM sysobjects
42 WHERE name = 'ConversionIr3xSysCounts' AND type = 'U')
43 DROP TABLE ConversionIr3xSysCounts
44GO
45IF EXISTS(SELECT name FROM sysobjects
46 WHERE name = 'ConversionConvertDef' AND type = 'U')
47 DROP TABLE ConversionConvertDef
48GO
49IF EXISTS(SELECT name, type FROM sysobjects
50 WHERE name = 'ConCtsView' AND type = 'V')
51 DROP VIEW ConCtsView
52GO
53IF EXISTS(SELECT name, type FROM sysobjects
54 WHERE name = 'ConProgView' AND type = 'V')
55 DROP VIEW ConProgView
56GO
57IF EXISTS(SELECT name, type FROM sysobjects
58 WHERE name = 'ConAllView' AND type = 'V')
59 DROP VIEW ConAllView
60GO
61IF EXISTS(SELECT name, type FROM sysobjects
62 WHERE name = 'ConErrView' AND type = 'V')
63 DROP VIEW ConErrView
64GO
65IF EXISTS(SELECT name, type FROM sysobjects
66 WHERE name = 'ConErrDetails' AND type = 'V')
67 DROP VIEW ConErrDetails
68GO
69IF EXISTS(SELECT name, type FROM sysobjects
70 WHERE name = 'ConHourCtsView' AND type = 'V')
71 DROP VIEW ConHourCtsView
72GO
73IF EXISTS(SELECT name, type FROM sysobjects
74 WHERE name = 'ConPageCount' AND type = 'V')
75 DROP VIEW ConPageCount
76GO
77IF EXISTS(SELECT name, type FROM sysobjects
78 WHERE name = 'ConErrReset' AND type = 'P')
79 DROP PROCEDURE ConErrReset
80GO
81
82
83-- table used only by 3x implementation
84CREATE TABLE [dbo].[ConversionIr3xSysCounts](
85 [machineid] [char](1) NOT NULL,
86 [latestcount] [bigint] NOT NULL,
87 CONSTRAINT [PK_Ir3xSysCounts] PRIMARY KEY CLUSTERED ( [machineid] ASC) ON [PRIMARY]
88) ON [PRIMARY]
89GO
90
91-- Queue Table
92CREATE TABLE [dbo].[ConversionQueue](
93 [id] [bigint] IDENTITY(1,1) NOT NULL,
94 [converted] [int] NOT NULL CONSTRAINT [DF_ConversionQueue_converted] DEFAULT ((0)),
95 [machinename] [varchar](100) NULL,
96 [priority] [int] NULL, -- used by Convert It Now
97 [bookmark] [uniqueidentifier] NULL,
98 [timestamp] [datetime] NULL, -- last time the queue item was touched
99 [tableid] [int] NOT NULL,
100 [ReferNum] [varchar] (100) NOT NULL,
101 CONSTRAINT [PK_ConversionQueue] PRIMARY KEY CLUSTERED ( [id] ASC )
102) ON [PRIMARY]
103GO
104
105-- index on converted
106CREATE NONCLUSTERED INDEX [IX_ConversionQueue_converted] ON [dbo].[ConversionQueue]
107(
108 [converted] ASC
109) ON [PRIMARY]
110GO
111
112-- index on priority and converted
113CREATE NONCLUSTERED INDEX [IX_ConversionQueue_priority_converted] ON [dbo].[ConversionQueue]
114(
115 [priority] ASC,
116 [converted] ASC
117) ON [PRIMARY]
118GO
119
120-- Queue Item Table
121
122CREATE TABLE [dbo].[ConversionQueueItems](
123 [id] [bigint] IDENTITY(1,1) NOT NULL,
124 [conversionqueueid] [bigint] NULL,
125 [filepath] [varchar](max) NULL,
126 [filepathexists] [int] NOT NULL,
127 [fileformat] [varchar](max) NULL,
128 [fileblob] [image] NULL,
129 [annpath] [varchar](255) NULL,
130 [annpathexists] [int] NOT NULL,
131 [annblob] [image] NULL,
132 [irdrawer] [varchar](300) NULL,
133 [irfiletype] [varchar](300) NULL,
134 [irfilenumber] [varchar](300) NULL,
135 [irfilename] [varchar](max) NULL,
136 [irfoldertype] [varchar](max) NULL,
137 [irdoctype] [varchar](max) NULL,
138 [irdocid] [int] NULL,
139 [irdocdate] [datetime] NULL,
140 [irpagedescription] [varchar](max) NULL,
141 [irpagemark] [int] NULL,
142 [irorientation] [int] NULL,
143 [refernum] [varchar](max) NULL,
144 [orderid] [int] NULL,
145 [mappingkey] [varchar](300) NULL,
146 [CQIInsertion] [datetime] NULL,
147 [ClientID] [varchar](1000) NULL,
148 [Policy_Number] [varchar](max) NULL,
149 [TAMClientID] [varchar](1000) NULL,
150 [tableid] [int] NOT NULL,
151CONSTRAINT [PK_ConversionQueueItems] PRIMARY KEY CLUSTERED ( [id] ASC )ON [PRIMARY]
152) ON [PRIMARY]
153GO
154
155ALTER TABLE [dbo].[ConversionQueueItems] WITH NOCHECK ADD CONSTRAINT [FK_ConversionQueueItems_ConversionQueue] FOREIGN KEY([conversionqueueid])
156REFERENCES [dbo].[ConversionQueue] ([id])
157NOT FOR REPLICATION
158GO
159
160ALTER TABLE [dbo].[ConversionQueueItems] NOCHECK CONSTRAINT [FK_ConversionQueueItems_ConversionQueue]
161GO
162
163-- Always create this index
164CREATE NONCLUSTERED INDEX [IX_ConversionQueueItems_conversionqueueid] ON [dbo].[ConversionQueueItems]
165(
166 [conversionqueueid] ASC
167)ON [PRIMARY]
168GO
169
170-- Used in conjunction with Convert It Now
171CREATE NONCLUSTERED INDEX [IX_ConversionQueueItems_drawerfilenumfiletype] ON [dbo].[ConversionQueueItems]
172(
173 [irdrawer] ASC,
174 [irfilenumber] ASC
175)ON [PRIMARY]
176GO
177
178CREATE NONCLUSTERED INDEX [IX_ConversionQueueItems_mappingkey] ON [dbo].[ConversionQueueItems]
179(
180 [mappingkey] ASC
181)ON [PRIMARY]
182GO
183
184-- Conversion Progress table
185CREATE TABLE [dbo].[ConversionProgress](
186 [id] [bigint] IDENTITY(1,1) NOT NULL,
187 [conversionqueueid] [bigint] NULL,
188 [doccount] [int] NULL,
189 [pagecount] [int] NULL,
190 [duration] [int] NULL,
191 [success] [int] NULL,
192 [queueitemstatus] [varchar](MAX) NULL,
193 [errormessage] [varchar](MAX) NULL,
194 [machinename] [varchar](100) NULL,
195 [timestamp] [datetime] NULL CONSTRAINT [DF_conversionprogress_timestamp] DEFAULT (getdate()),
196 CONSTRAINT [PK_conversionprogress] PRIMARY KEY CLUSTERED ( [id] ASC ) ON [PRIMARY]
197) ON [PRIMARY]
198GO
199
200ALTER TABLE [dbo].[ConversionProgress] WITH NOCHECK ADD CONSTRAINT [FK_ConversionProgress_ConversionQueue] FOREIGN KEY([conversionqueueid])
201REFERENCES [dbo].[ConversionQueue] ([id])
202NOT FOR REPLICATION
203GO
204
205ALTER TABLE [dbo].[ConversionProgress] NOCHECK CONSTRAINT [FK_ConversionProgress_ConversionQueue]
206GO
207
208-- Always create this index
209CREATE NONCLUSTERED INDEX [IX_ConversionProgress_conversionqueueid] ON [dbo].[ConversionProgress]
210(
211 [conversionqueueid] ASC
212)ON [PRIMARY]
213GO
214
215-- Conversion Task Execution Times
216CREATE TABLE [dbo].[ConversionExecutionTimes](
217 [id] [bigint] IDENTITY(1,1) NOT NULL,
218 [conversionprogressid] [bigint] NOT NULL,
219 [source] [varchar](50) NOT NULL,
220 [elapsedmilliseconds] [bigint] NOT NULL,
221 CONSTRAINT [PK_ConversionExecutionTimes] PRIMARY KEY CLUSTERED
222(
223 [id] ASC
224) ON [PRIMARY]
225) ON [PRIMARY]
226GO
227
228ALTER TABLE [dbo].[ConversionExecutionTimes] WITH CHECK ADD CONSTRAINT [FK_ConversionExecutionTimes_ConversionProgress] FOREIGN KEY([conversionprogressid])
229REFERENCES [dbo].[ConversionProgress] ([id])
230GO
231
232ALTER TABLE [dbo].[ConversionExecutionTimes] CHECK CONSTRAINT [FK_ConversionExecutionTimes_ConversionProgress]
233GO
234
235CREATE NONCLUSTERED INDEX [IX_ConversionExecutionTimes_conversionprogressid] ON [dbo].[ConversionExecutionTimes]
236(
237 [conversionprogressid] ASC
238)ON [PRIMARY]
239GO
240
241-- Conversion Message History Table
242CREATE TABLE [dbo].[ConversionMessageHistory](
243 [id] [bigint] IDENTITY(1,1) NOT NULL,
244 [messagecode] [int] NOT NULL,
245 [datetimeoccurance] [datetime] NOT NULL,
246 [machinename] [varchar](100) NULL,
247CONSTRAINT [PK_conversionmessagehistory] PRIMARY KEY CLUSTERED ( [id] ASC ) ON [PRIMARY]
248) ON [PRIMARY]
249
250GO
251
252CREATE TABLE [dbo].[ConversionTask](
253 [id] [bigint] IDENTITY(1,1) NOT NULL,
254 [conversionqueueitemsid] [bigint] NOT NULL,
255 [flowid] [varchar](100) NOT NULL,
256 [stepid] [varchar](100) NOT NULL,
257 [description] [varchar](255) NULL,
258 [userid] [varchar](50) NULL,
259 [priority] [int] NOT NULL CONSTRAINT [DF_ConversionTask_priority] DEFAULT ((5)),
260 [availabledate] [datetime] NOT NULL CONSTRAINT [DF_ConversionTask_availabledate] DEFAULT (getdate()),
261 [TAM-Action] [varchar](50) NULL,
262 [TAM-Code] [varchar](50) NULL,
263-- [attribinttest] [int] NULL, -- This is an example of an attribute or criteria instance column
264CONSTRAINT [PK_Task_id] PRIMARY KEY CLUSTERED
265(
266 [id] ASC
267) ON [PRIMARY]
268) ON [PRIMARY]
269
270GO
271
272
273ALTER TABLE [dbo].[ConversionTask] WITH CHECK ADD CONSTRAINT [FK_ConversionTask_ConversionQueueItems] FOREIGN KEY([conversionqueueitemsid])
274REFERENCES [dbo].[ConversionQueueItems] ([id])
275GO
276ALTER TABLE [dbo].[ConversionTask] CHECK CONSTRAINT [FK_ConversionTask_ConversionQueueItems]
277GO
278
279CREATE NONCLUSTERED INDEX [IX_ConversionTask_conversionqueueitemsid] ON [dbo].[ConversionTask]
280(
281 [conversionqueueitemsid] ASC
282)ON [PRIMARY]
283GO
284
285
286CREATE TABLE [dbo].[ConversionConvertDef](
287 [converted] [int] NOT NULL,
288 [description] [varchar](255) NOT NULL,
289 CONSTRAINT [PK_ConversionConvertDefId] PRIMARY KEY CLUSTERED ( [converted] ASC )ON [PRIMARY]
290) ON [PRIMARY]
291GO
292
293INSERT INTO ConversionConvertDef (converted, description)
294VALUES (-1, 'Erred')
295INSERT INTO ConversionConvertDef (converted, description)
296VALUES (0, 'Pending')
297INSERT INTO ConversionConvertDef (converted, description)
298VALUES (1, 'Success')
299INSERT INTO ConversionConvertDef (converted, description)
300VALUES (2, 'Processing')
301INSERT INTO ConversionConvertDef (converted, description)
302VALUES (3, 'ON HOLD')
303INSERT INTO ConversionConvertDef (converted, description)
304VALUES (-2, 'Staged')
305
306ALTER TABLE [dbo].[ConversionQueue] WITH NOCHECK ADD CONSTRAINT [FK_ConversionQueue_ConversionConvertDef] FOREIGN KEY([converted])
307REFERENCES [dbo].[ConversionConvertDef] ([converted])
308NOT FOR REPLICATION
309GO
310
311-- This table can be altered if the link needs to be made
312-- using something other than ConversionQueueItems.id as the link.
313CREATE TABLE [dbo].[ConversionAnns](
314 [id] [bigint] IDENTITY(1,1) NOT NULL,
315 [conversionqueueitemsid] [bigint] NULL, -- this can be changed to something else although by default this is the fastest/most efficient lookup
316 [annpath] [varchar](255) NULL,
317 [annpathexists] [int] NOT NULL CONSTRAINT [DF_ConversionAnns_annpathexists] DEFAULT ((1)),
318 [annblob] [image] NULL,
319 [pagenumber] [int] NULL, -- page number starts at 1 for the first page and increments for multipage image files.
320CONSTRAINT [PK_Anns_id] PRIMARY KEY CLUSTERED
321(
322 [id] ASC
323) ON [PRIMARY]
324) ON [PRIMARY]
325GO
326
327-- If the link above in ConversionAnns is changed this needs to be changed as well
328ALTER TABLE [dbo].[ConversionAnns] WITH CHECK ADD CONSTRAINT [FK_ConversionAnns_ConversionQueueItems] FOREIGN KEY([conversionqueueitemsid])
329REFERENCES [dbo].[ConversionQueueItems] ([id])
330GO
331ALTER TABLE [dbo].[ConversionAnns] CHECK CONSTRAINT [FK_ConversionAnns_ConversionQueueItems]
332GO
333
334CREATE NONCLUSTERED INDEX [IX_ConversionAnns_conversionqueueitemsid] ON [dbo].[ConversionAnns]
335(
336 [conversionqueueitemsid] ASC
337)ON [PRIMARY]
338GO
339
340-- Create views
341
342-- Used to view the converted counts
343CREATE VIEW [dbo].[ConCtsView]
344AS
345SELECT TOP 100 PERCENT LEFT(CONVERT(VARCHAR(25), CONVERT(MONEY, COUNT(cq.id)), 1), LEN(CONVERT(VARCHAR(25), CONVERT(MONEY, COUNT(cq.id)), 1)) - 3) AS Counts, cs.converted AS [Converted status], cs.description AS [Converted description]
346FROM dbo.ConversionQueue AS cq WITH (nolock) RIGHT OUTER JOIN ConversionConvertDef AS cs WITH (nolock) ON cq.converted = cs.converted
347GROUP BY cs.converted, cs.description
348ORDER BY 2 ASC
349GO
350
351-- Used to view most import tables linked together
352CREATE VIEW [dbo].[ConAllView]
353AS
354SELECT cq.id AS [cq id], cq.converted, cq.machinename, cq.priority, cqi.id AS [cqi id], cqi.filepath, cqi.irdrawer,
355 cqi.irfilenumber, cqi.irfilename, cqi.irfoldertype, cqi.irdoctype, cqi.irdocid, cqi.irdocdate, cqi.irpagedescription, cqi.refernum, cqi.orderid, cqi.mappingkey,
356 cqi.irpagemark, ct.id AS [ct id], ct.flowid, ct.stepid, ct.description, ct.userid, ct.priority AS [ct priority], ct.availabledate
357FROM ConversionQueue cq (nolock)
358 INNER JOIN ConversionQueueItems cqi (nolock) ON cq.id = cqi.conversionqueueid
359 LEFT OUTER JOIN ConversionTask ct ON cqi.id = ct.conversionqueueitemsid
360GO
361
362-- Used to view the conversion progress info linked together
363CREATE VIEW [dbo].[ConProgView]
364AS
365SELECT cq.id AS [cq id], cq.converted, cp.id AS [cp id], cp.doccount, cp.pagecount, cp.duration, cp.success, cp.queueitemstatus, cp.errormessage, cp.machinename,
366 cp.timestamp, cqi.id AS [cqi id], cqi.filepath, cqi.irdrawer, cqi.irfilenumber, cqi.irfilename,
367 cqi.irfoldertype, cqi.irdoctype, cqi.irdocid, cqi.irdocdate, cqi.irpagedescription, cqi.refernum, cqi.orderid, cqi.mappingkey, cqi.irpagemark, ct.id AS [ct id],
368 ct.flowid, ct.stepid, ct.description, ct.userid, ct.priority, ct.availabledate
369FROM ConversionProgress cp (nolock) INNER JOIN
370 (
371 SELECT MAX(cp.id) AS id
372 FROM ConversionQueue cq (nolock) INNER JOIN ConversionProgress cp (nolock) ON cq.id = cp.conversionqueueid
373 GROUP BY cq.id
374 ) q1 ON cp.id = q1.id
375 RIGHT OUTER JOIN ConversionQueue cq ON cq.id = cp.conversionqueueid
376 INNER JOIN ConversionQueueItems cqi (nolock) ON cq.id = cqi.conversionqueueid
377 LEFT OUTER JOIN ConversionTask ct (nolock) ON cqi.id = ct.conversionqueueitemsid
378GO
379
380-- Grouping of errors
381CREATE VIEW [dbo].[ConErrView]
382AS
383SELECT TOP 100 PERCENT COUNT(cp.id) AS [counts], queueitemstatus, errormessage
384FROM ConversionProgress cp (nolock) INNER JOIN
385(
386 SELECT MAX(cp.id) AS id
387 FROM ConversionQueue cq (nolock) INNER JOIN ConversionProgress cp (nolock) ON cq.id = cp.conversionqueueid
388 WHERE cq.converted = -1
389 GROUP BY cq.id
390) q1 ON cp.id = q1.id
391GROUP BY queueitemstatus, errormessage
392ORDER BY counts DESC
393GO
394
395
396-- Error details
397CREATE VIEW [dbo].[ConErrDetails]
398AS
399SELECT TOP 100 PERCENT cp.conversionqueueid, cp.machinename, cp.timestamp, cp.queueitemstatus, cp.errormessage, cqi.irfilenumber, cqi.irdrawer, cqi.irfoldertype, cqi.irdoctype, cqi.refernum
400FROM ConversionProgress cp INNER JOIN ConversionQueueItems cqi ON cp.conversionqueueid = cqi.conversionqueueid
401INNER JOIN (
402 SELECT MAX(cp.id) AS id
403 FROM ConversionQueue cq (nolock) INNER JOIN ConversionProgress cp (nolock) ON cq.id = cp.conversionqueueid
404 WHERE cq.converted = -1
405 GROUP BY cq.id
406) q1 ON cp.id = q1.id
407ORDER BY cp.queueitemstatus, cp.errormessage, cp.timestamp DESC
408GO
409
410-- Grouping of counts per hour
411CREATE VIEW [dbo].[ConHourCtsView]
412AS
413SELECT CONVERT(VARCHAR, [timestamp], 112) + ' ' + LEFT(CONVERT(VARCHAR, [timestamp], 108), 2) AS [Time Stamp], COUNT(id) AS [Document Totals]
414FROM ConversionProgress
415WHERE success = 1
416GROUP BY CONVERT(VARCHAR, [timestamp], 112) + ' ' + LEFT(CONVERT(VARCHAR, [timestamp], 108), 2)
417GO
418
419-- Get a count of total pages converted during the conversion process
420CREATE VIEW dbo.ConPageCount
421AS
422SELECT SUM(pagecount) AS ConvertedPages
423FROM ConversionProgress cp INNER JOIN ConversionQueue cq ON cp.conversionqueueid = cq.id
424WHERE cq.converted = 1
425AND cp.success = 1
426GO
427
428-- Reset erred items back to 0
429CREATE PROCEDURE [dbo].[ConErrReset]
430AS
431BEGIN
432
433 UPDATE ConversionQueue SET converted = 0, machinename = NULL, bookmark = NULL, [timestamp] = NULL
434 WHERE converted = -1
435END
436GO
437
438CREATE PROCEDURE [dbo].[ConRebuildAllIndexes]
439AS
440BEGIN
441DEClARE @DatabaseId INT, @ObjectId INT, @IndexId INT, @IndexName VARCHAR(512), @FragmentPercent DECIMAL, @ParentTable VARCHAR(512) DECLARE @statement NVARCHAR(1000) DECLARE @dbid INT, @minFragmentation INT
442
443SET @dbid = (SELECT DB_ID())
444SET @minFragmentation = 30
445
446SELECT ps.database_id AS [DatabaseId], ps.OBJECT_ID AS [ObjectId], ps.index_id AS [IndexId], si.name AS [IndexName], ps.avg_fragmentation_in_percent AS [FragmentPercent],
447( SELECT distinct so.name
448 FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) AS ParentTable INTO #fragmentedIndexes FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) AS ps INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID AND ps.index_id = si.index_id WHERE ps.database_id = @dbid AND si.name IS NOT NULL AND ps.avg_fragmentation_in_percent > @minFragmentation
449
450DECLARE masterlist CURSOR
451FOR SELECT f.DatabaseId, f.ObjectId, f.IndexId, f.IndexName, f.FragmentPercent, f.ParentTable
452 FROM #fragmentedIndexes f
453 ORDER BY f.FragmentPercent DESC
454
455OPEN masterlist
456 FETCH NEXT FROM masterlist
457 INTO @DatabaseId, @ObjectId, @IndexId, @IndexName, @FragmentPercent, @ParentTable
458
459 WHILE @@FETCH_STATUS = 0
460 BEGIN
461
462 SET @statement = 'ALTER INDEX ' + @IndexName + ' ON ' + @ParentTable + ' REBUILD'
463 EXEC sp_executesql @statement
464
465 FETCH NEXT FROM masterlist
466 INTO @DatabaseId, @ObjectId, @IndexId, @IndexName, @FragmentPercent, @ParentTable
467 END
468CLOSE masterlist
469
470DEALLOCATE masterlist
471
472DROP TABLE #fragmentedIndexes
473
474end
475go
476
477-- Create and populate ConversionQueueCounts table
478if exists(
479 select *
480 from information_schema.tables t
481 where table_name='ConversionQueueCounts'
482 )
483 drop table ConversionQueueCounts;
484create table ConversionQueueCounts(
485 converted int,
486 count int
487 );
488
489insert into ConversionQueueCounts (converted,count) values(0,(select isnull(count(converted),0) from ConversionQueue cq with(nolock) where cq.converted=0));
490insert into ConversionQueueCounts (converted,count) values(1,(select isnull(count(converted),0) from ConversionQueue cq with(nolock) where cq.converted=1));
491insert into ConversionQueueCounts (converted,count) values(2,(select isnull(count(converted),0) from ConversionQueue cq with(nolock) where cq.converted=2));
492insert into ConversionQueueCounts (converted,count) values(-1,(select isnull(count(converted),0) from ConversionQueue cq with(nolock) where cq.converted=-1));
493
494go
495/*
496
497
498if exists(
499 select *
500 from sys.indexes i
501 where i.name='IX_ConversionQueue_bookmark_include_id'
502 )
503 drop index IX_ConversionQueue_bookmark_include_id on ConversionQueue;
504go
505create nonclustered index IX_ConversionQueue_bookmark_include_id
506 on ConversionQueue(bookmark)
507 include (id);
508
509
510*/