Solved

Unable to transfer image data in SQL Server 2000

Posted on 2008-10-23
2
410 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 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

16 Experts available now in Live!

Get 1:1 Help Now