Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

FIND CHART OPEN ISSUES

General Discussion

Last Post by mdoak 10 years ago

4 Posts

3 Users

0 Reactions

59 Views

RSS

Crow

(@dustincrowell)

Posts: 104

Estimable Member

Topic starter

Does anyone have the script to create the FINDCHARTOPENISSUES Procedure. We recently lost this procedure.

Thanks.

Posted : August 26, 2014 4:00 am

mikeseale

(@mikeseale)

Posts: 344

Reputable Member

I added cus infront of the name so it won't be removed when we do patches/upgrades:

USE [DATABASENAME]
GO

/*** Object:  StoredProcedure [dbo].[cusFindChartOpenIssues]    Script Date: 08/26/2014 13:11:29 ***/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[cusFindChartOpenIssues]
    @TableToTest varchar(60) = NULL,
    @PID numeric(19,0) = NULL,
    @CheckNullsOnly bit = NULL
AS
SET NOCOUNT ON

DECLARE @PidSelect NVARCHAR(50) = ''
DECLARE @FlagPidSelect NVARCHAR(50) = ''
IF @PID IS NOT NULL
BEGIN
	  SET @PidSelect = ' AND PID = ' + CAST(@pid as varchar)
	  SET @FlagPidSelect = ' AND ContextID1 = ' + cast(@PID as varchar)
END

IF @CheckNullsOnly IS NULL
BEGIN

	DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), PidColumn VARCHAR(40), DataFound BIT)
		IF @TableToTest IS NULL
		BEGIN
	    		INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
				SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
				FROM    Information_Schema.Columns AS C
						INNER Join Information_Schema.Tables AS T
							ON C.Table_Name = T.Table_Name
					AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
				WHERE   Table_Type = 'Base Table'
						And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
						And C.COLUMN_NAME <> 'RegNote'  -- only computed column outside of PatientVisitProcs
						And C.Table_Name IN  ('PatientProfile',
											'ALLERGY',
											'APPT',
											'ASSESS',
											'DIRECTIV',
											'DOCIMAGES',
											'DOCUMENT',
											'FLAG',
											'InteractionOverride',
											'Med_Override',
											'MEDDX',
											'MEDICATE',
											'MEDICATIONHISTORY',
											'OBS',
											'ORDDX',
											'ORDERS',
											'ORDTOCOMPLETE',
											'PatientContacts',
											'PatientRelationship',
											'PRESCRIB',
											'PROBLEM',
											'PROBLEMLISTVIEWS',
											'UI_LAYOUT')
		END
		ELSE  -- Tablename specified
			INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
			SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
			FROM    Information_Schema.Columns AS C
					INNER Join Information_Schema.Tables AS T
						ON C.Table_Name = T.Table_Name
				AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
			WHERE   Table_Type = 'Base Table'
					And C.Table_Name = @TableToTest
					And C.COLUMN_NAME <> 'RegNote'  -- only computed column outside of PatientVisitProcs
					And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')

		update @Temp SET PidColumn = 'Pid' where TableName <> 'Flag'
		update @Temp SET PidColumn = 'ContextId1' where TableName = 'Flag'

	DECLARE @i INT
	DECLARE @MAX INT
	DECLARE @TableName sysname
	DECLARE @ColumnName sysname
	DECLARE @SchemaName sysname
	DECLARE @SQL NVARCHAR(4000)
	DECLARE @PARAMETERS NVARCHAR(4000)
	DECLARE @DataExists BIT
	DECLARE @SQLTemplate NVARCHAR(4000)

	SET @SQLTemplate = 'If Exists(Select top 1 *
									  From   ReplaceTableName Where '
	IF @PID is not null
	BEGIN
	  SET @SQLTemplate = @SQLTemplate + 'ReplacePidColumn = ' + CAST(@pid as varchar)
	END
	ELSE
	 SET @SQLTemplate = @SQLTemplate + '1=1'
	SELECT @SQLTemplate = @SQLTemplate +   -- tried doing this with function showInvalidXMLChars but performance was terrible
	' AND ( Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(01)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(02)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(03)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(04)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(05)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(06)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(07)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(08)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(11)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(12)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(14)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(15)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(16)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(17)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(18)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(19)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(20)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(21)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(22)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(23)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(24)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(25)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(26)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(27)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(28)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(29)+''%''
	or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(30)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName])  like ''%''+CHAR(31)+''%''
									  ))
										 Set @DataExists = 1
									 Else
										 Set @DataExists = 0'
								,
			@PARAMETERS = '@DataExists Bit OUTPUT',
			@i = 1

	SELECT @i = 1, @MAX = MAX(RowId)
	FROM   @Temp

	WHILE @i <= @MAX
		BEGIN
			SELECT  @SQL = REPLACE(REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName), 'ReplacePidColumn', PidColumn)
			FROM    @Temp
			WHERE   RowId = @i

			--PRINT @SQL
			EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

			IF @DataExists =1
				UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

			SET @i = @i + 1

		END

	DECLARE @Temp_TableName varchar(300)	                           -- jmm Aug 16, 2013
	SELECT  @Temp_TableName = MIN(TableName) FROM @Temp              -- jmm Aug 16, 2013
	PRINT 'Tables / Columns with bad characters ('+@Temp_TableName+')'
	SELECT  TableName, ColumnName
	FROM    @Temp
	WHERE   DataFound = 1

	PRINT 'Script to Correct -- Please review before executing any queries'

    -- Note: tried using replace instead of function call for performance but that does not work with text data type
	SELECT 'update ' + TableName + ' set ' + ColumnName +
		' = dbo.stripInvalidXMLChars(' + ColumnName + ') where ('
		+ 'Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(01)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(02)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(03)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(04)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(05)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(06)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(07)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(08)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(11)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(12)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(14)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(15)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(16)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(17)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(18)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(19)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(20)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(21)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(22)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(23)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(24)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(25)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(26)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(27)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(28)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(29)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(30)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(31)+''%'')'
	+ @PidSelect
		 FROM @Temp
		 WHERE DataFound = 1
		 AND TableName <> 'Flag'

	SELECT 'update ' + TableName + ' set ' + ColumnName +
		' = dbo.stripInvalidXMLChars(' + ColumnName + ') where ('
		+ 'Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(01)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(02)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(03)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(04)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(05)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(06)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(07)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(08)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(11)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(12)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(14)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(15)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(16)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(17)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(18)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(19)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(20)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(21)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(22)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(23)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(24)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(25)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(26)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(27)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(28)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(29)+''%''
	or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(30)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ')  like ''%''+CHAR(31)+''%'')'
				+ @FlagPidSelect
		 FROM @Temp
		 WHERE DataFound = 1
		 AND TableName = 'Flag'
END -- @CheckNullsOnly is NULL

-- Check for NULLs in specific fields
IF @PID is NULL
BEGIN
    IF EXISTS (SELECT TOP 1 ID from FLAG where MESSAGE is null)
		    SELECT 'update Flag set Message = '''' where Message is NULL'
    IF EXISTS (SELECT TOP 1 SDID from Document where ConfType is null)
		    SELECT 'update Document set ConfType = 0 where ConfType is NULL'
END
ELSE
BEGIN
    IF EXISTS (SELECT TOP 1 ID from FLAG where ContextId1 = @PID AND MESSAGE is null)
		    SELECT 'update Flag set Message = '''' where Message is NULL' + @FlagPidSelect
    IF EXISTS (SELECT TOP 1 SDID from Document where PID = @PID AND ConfType is null)
	        SELECT 'update Document set confType = 0 where confType is NULL' + @PidSelect
	FROM DOCUMENT where CONFTYPE is NULL and PID = @PID
END

GO

Posted : August 26, 2014 4:13 am

Crow

(@dustincrowell)

Posts: 104

Estimable Member

Topic starter

Thanks Alot!

Posted : August 26, 2014 4:25 am

mdoak

(@mdoak)

Posts: 92

Estimable Member

Does anyone have the stripInvalidXMLChars stored procedure that is necessary if the first part of this returns a result (an obs term with invalid xml characters). I am being instructed to run

update OBS set OBSVALUE = dbo.stripInvalidXMLChars(OBSVALUE) where (Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(01)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(02)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(03)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(04)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(05)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(06)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(07)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(08)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(11)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(12)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(14)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(15)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(16)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(17)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(18)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(19)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(20)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(21)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(22)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(23)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(24)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(25)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(26)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(27)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(28)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(29)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(30)+'%' or Convert(nVarChar(4000), OBSVALUE) like '%'+CHAR(31)+'%') AND PID = 1642703861723000

but I don't have the stripInvalidXMLChars function.

Thanks.

Posted : September 15, 2014 10:42 am

24 Forums
9,940 Topics
39.9 K Posts
2 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed