Solved

Unable to transfer image data in SQL Server 2000

Posted on 2008-10-23
2
411 Views
Last Modified: 2012-05-05
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
Comment
Question by:AlexanderS1
2 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 22791935
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
 

Author Closing Comment

by:AlexanderS1
ID: 31509351
Thank you for your help with this solution. It resolved the issue I was experiencing.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now