• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

Unable to transfer image data in SQL Server 2000

I am receiving a "The text, ntext, and image data types are invalid for local variables." error when attempting to save the attached code in SQL Server 2000. It is vital I be able to transfer both image and text data for the client between databases I am working with. How can I work around this problem? The client does not have the desire to change the field type from image to something else as it is heavily involved in using image and text types of data.


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO
 
 
ALTER PROCEDURE [pm].[usp_ArchChart]
(
	@MinPatientCode		int
	,@MaxPatientCode	int
) 
AS
 
SET NOCOUNT ON 
 
-- declare all variables!
 
DECLARE  
	@PatientCode 	int
	,@Document	image
	,@CurrentPage	int
	,@LinesLeft	int
 
-- declare the cursor
 
DECLARE cur_Chart CURSOR FOR
SELECT    
 
	[PatientCode]
	,[Document]
	,[CurrentPage]
	,[LinesLeft]
FROM      
	[pm].[Chart]
WHERE
	[PatientCode] >= @MinPatientCode
	AND [PatientCode] <= @MaxPatientCode
 
OPEN cur_Chart
FETCH cur_Chart INTO 
	@PatientCode
	,@Document
	,@CurrentPage
	,@LinesLeft
 
-- start the main processing loop.
 
WHILE @@Fetch_Status = 0
 
   BEGIN
						/* check if the patient code is in the archive */	
	IF NOT EXISTS
	(
		SELECT TOP 1
			b.[PatientCode]
		FROM 
			[PromedArch].[pm].[assresp] AS b
		WHERE 
			b.[PatientCode] = @PatientCode
	)
		BEGIN				/* if it does not, add it			*/
			INSERT INTO [PromedArch].[pm].[Chart]	
			(
				[PatientCode]
				,[Document]
				,[CurrentPage]
				,[LinesLeft]
			)
			VALUES
			(
				@PatientCode
				,@Document
				,@CurrentPage
				,@LinesLeft
			)
		END
	ELSE
		BEGIN				/* if it does exist, update it			*/
			UPDATE [PromedArch].[pm].[Chart]
			SET
				[Document] = @Document
				,[CurrentPage] = @CurrentPage
				,[LinesLeft] = @LinesLeft
			WHERE 
				[PatientCode] = @PatientCode
		END
 
	-- Get the next row.
	FETCH cur_Chart INTO 
		@PatientCode
		,@Document
		,@CurrentPage
		,@LinesLeft
   END
 
CLOSE cur_Chart
DEALLOCATE cur_Chart
 
-- delete the original records from the production database
 
DELETE
FROM 
	[pm].[Chart]
WHERE
	[PatientCode] >= @MinPatientCode
	AND [PatientCode] <= @MaxPatientCode
 
-- clean up the archive database
 
DELETE 
FROM
	[PromedArch].[pm].[Chart]
WHERE
	[PatientCode] > @MaxPatientCode
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

0
AlexanderS1
Asked:
AlexanderS1
1 Solution
 
Anthony PerkinsCommented:
Try it this way:

ALTER PROCEDURE [pm].[usp_ArchChart]
(
      @MinPatientCode            int
      ,@MaxPatientCode      int
)
AS
 
SET NOCOUNT ON
 
UPDATE      t
SET            [Document] = s.Document,
            [CurrentPage] = s.CurrentPage,
            [LinesLeft] = s.LinesLeft
From      [PromedArch].[pm].[Chart] t
            Inner Join [pm].[Chart] s On t.PatientCode = s.PatientCode
WHERE      s.PatientCode BETWEEN @MinPatientCode And @MaxPatientCode
 

INSERT      [PromedArch].[pm].[Chart] (
                  [PatientCode]
                  ,[Document]
                  ,[CurrentPage]
                  ,[LinesLeft])
Select      s.PatientCode,
            s.Document,
            s.CurrentPage,
            s.LinesLeft
From      [pm].[Chart] s
            Left Join [PromedArch].[pm].[Chart] t On s.PatientCode = t.PatientCode
WHERE      s.PatientCode BETWEEN @MinPatientCode And @MaxPatientCode
            And t.PatientCode Is Null

 
-- delete the original records from the production database
DELETE      [pm].[Chart]
WHERE      PatientCode BETWEEN @MinPatientCode And @MaxPatientCode
 
-- clean up the archive database
DELETE      [PromedArch].[pm].[Chart]
WHERE      PatientCode > @MaxPatientCode
0
 
AlexanderS1Author Commented:
Thank you for your help with this solution. It resolved the issue I was experiencing.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now