Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Operand type clash: int is incompatible with ntext

Author  Topic 

robertnzana
Starting Member

42 Posts
Posted - 2008-06-02 : 11:35:16
Created stored proc in 2005 and now I have to move it to 2000.

Getting this error:
Msg 206, Level 16, State 2, Procedure InsertCompany, Line 27
Operand type clash: int is incompatible with ntext

Can't figure it out. Not even sure if the line number is correct. But, here's the stored proc...
CREATE PROCEDURE [dbo].[InsertCompany]
	(
		@PackageId int, 
		@CompanyName nvarchar(50), 
		@Address1 nvarchar(100), 
		@Address2 nvarchar(100), 
		@City nvarchar(50), 
		@State nvarchar(50), 
		@Zip nvarchar(10), 
		@ContactPerson nvarchar(50), 
		@ContactPhone nvarchar(50), 
		@ContactPhone2 nvarchar(50), 
		@ContactEmail nvarchar(50), 
		@OpenSun bit, @WorkHrSunFrom nvarchar(50), @WorkHrSunTo nvarchar(50), 
		@OpenMon bit, @WorkHrMonFrom nvarchar(50), @WorkHrMonTo nvarchar(50), 
		@OpenTue bit, @WorkHrTueFrom nvarchar(50), @WorkHrTueTo nvarchar(50), 
		@OpenWed bit, @WorkHrWedFrom nvarchar(50), @WorkHrWedTo nvarchar(50), 
		@OpenThu bit, @WorkHrThuFrom nvarchar(50), @WorkHrThuTo nvarchar(50), 
		@OpenFri bit, @WorkHrFriFrom nvarchar(50), @WorkHrFriTo nvarchar(50), 
		@OpenSat bit, @WorkHrSatFrom nvarchar(50), @WorkHrSatTo nvarchar(50)
	)

AS
BEGIN
	DECLARE @MyNewIdentity int

	INSERT INTO [Companies] 
	(
		[PackageId], 
		[CompanyName], 
		[Address1], 
		[Address2], 
		[City], 
		[State], 
		[Zip], 
		[ContactPerson], 
		[ContactPhone], 
		[ContactPhone2], 
		[ContactEmail], 
		[DateAdded], 
		[WorkHours], 
		[OpenSun], [WorkHrSunFrom], [WorkHrSunTo], 
		[OpenMon], [WorkHrMonFrom], [WorkHrMonTo], 
		[OpenTue], [WorkHrTueFrom], [WorkHrTueTo], 
		[OpenWed], [WorkHrWedFrom], [WorkHrWedTo], 
		[OpenThu], [WorkHrThuFrom], [WorkHrThuTo], 
		[OpenFri], [WorkHrFriFrom], [WorkHrFriTo], 
		[OpenSat], [WorkHrSatFrom], [WorkHrSatTo]
	) 
	VALUES 
	(
		@PackageId, 
		@CompanyName, 
		@Address1, 
		@Address2, 
		@City, 
		@State, 
		@Zip, 
		@ContactPerson, 
		@ContactPhone, 
		@ContactPhone2, 
		@ContactEmail, 
		GETDATE(), 
		0, 
		@OpenSun, @WorkHrSunFrom, @WorkHrSunTo, 
		@OpenMon, @WorkHrMonFrom, @WorkHrMonTo, 
		@OpenTue, @WorkHrTueFrom, @WorkHrTueTo, 
		@OpenWed, @WorkHrWedFrom, @WorkHrWedTo, 
		@OpenThu, @WorkHrThuFrom, @WorkHrThuTo, 
		@OpenFri, @WorkHrFriFrom, @WorkHrFriTo, 
		@OpenSat, @WorkHrSatFrom, @WorkHrSatTo
	)

	SET @MyNewIdentity = SCOPE_IDENTITY()
    -- set @MyNewIdentity = @@IDENTITY
	
	-- Now INSERT default attachments	
	INSERT INTO AttachmentType(CompanyId, AttachmentType)
	SELECT @MyNewIdentity AS CompanyId, AttachmentType FROM AttachmentTypeDefaults

RETURN
END


Thanks everyone!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-02 : 12:12:12
Can you post the structures of tables involved? Also how are you executing this procedure? What values are you passing for params?
Go to Top of Page

robertnzana
Starting Member

42 Posts
Posted - 2008-06-02 : 13:09:00
the stored proc works in 2005 so i'm trying to recreate it in 2000 enviro

the table structure is denoted by the input vars of the stored procs

any ideas? thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-02 : 13:21:13
quote:
Originally posted by robertnzana

the stored proc works in 2005 so i'm trying to recreate it in 2000 enviro

the table structure is denoted by the input vars of the stored procs

any ideas? thanks

I dont know magic
You havent given tables involved, no idea about data types of columns that table contains and i dont know how you call it either. You have given only a procedure only on which i cant find any syntactical problem too. So using the current info provided, i dont think i can guide you anywhere.Only thing i can guess is that you're trying to insert some int value to ntext field or viceversa or trying for a concatenation b/w them.
Go to Top of Page

robertnzana
Starting Member

42 Posts
Posted - 2008-06-02 : 13:29:22
Thanks! Here's the 2 table layouts...

[dbo].[Companies](
	[CompanyId] [int] IDENTITY(1,1) NOT NULL,
	[PackageId] [int] NULL,
	[CompanyName] [nvarchar](50) NOT NULL,
	[Address1] [nvarchar](100) NULL,
	[Address2] [nvarchar](100) NULL,
	[City] [nvarchar](50) NULL,
	[State] [nchar](50) NULL,
	[Zip] [nchar](10) NULL,
	[ContactPerson] [nvarchar](50) NULL,
	[ContactPhone] [nvarchar](50) NULL,
	[ContactPhone2] [nvarchar](50) NULL,
	[ContactEmail] [nchar](50) NULL,
	[DateAdded] [datetime] NULL CONSTRAINT [DF_Companies_DateAdded]  DEFAULT (getdate()),
	[WorkHours] [nvarchar](max) NULL,
	[OpenSun] [bit] NULL CONSTRAINT [DF_Companies_OpenSun]  DEFAULT ((0)),
	[WorkHrSunFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrSunFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrSunTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrSunTo]  DEFAULT (N'5:00 PM'),
	[OpenMon] [bit] NULL CONSTRAINT [DF_Companies_OpenMon]  DEFAULT ((1)),
	[WorkHrMonFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrMonFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrMonTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrMonTo]  DEFAULT (N'5:00 PM'),
	[OpenTue] [bit] NULL CONSTRAINT [DF_Companies_OpenTue]  DEFAULT ((1)),
	[WorkHrTueFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrTueFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrTueTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrTueTo]  DEFAULT (N'5:00 PM'),
	[OpenWed] [bit] NULL CONSTRAINT [DF_Companies_OpenWed]  DEFAULT ((1)),
	[WorkHrWedFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrWedFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrWedTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrWedTo]  DEFAULT (N'5:00 PM'),
	[OpenThu] [bit] NULL CONSTRAINT [DF_Companies_OpenThu]  DEFAULT ((1)),
	[WorkHrThuFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrThuFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrThuTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrThuTo]  DEFAULT (N'5:00 PM'),
	[OpenFri] [bit] NULL CONSTRAINT [DF_Companies_OpenFri]  DEFAULT ((1)),
	[WorkHrFriFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrFriFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrFriTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrFriTo]  DEFAULT (N'5:00 PM'),
	[OpenSat] [bit] NULL CONSTRAINT [DF_Companies_OpenSat]  DEFAULT ((0)),
	[WorkHrSatFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrSatFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrSatTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrSatTo]  DEFAULT (N'5:00 PM')

[dbo].[AttachmentType](
	[AttachmentTypeId] [int] IDENTITY(1,1) NOT NULL,
	[CompanyId] [int] NULL,
	[AttachmentType] [nvarchar](50) NOT NULL

Basically I'm trying to INSERT a record into the Companies table, then get the newly created record's pk, then use that to insert some records into AttachmentType.

Can you help?

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-02 : 13:38:51
quote:
Originally posted by robertnzana

Thanks! Here's the 2 table layouts...

[dbo].[Companies](
	[CompanyId] [int] IDENTITY(1,1) NOT NULL,
	[PackageId] [int] NULL,
	[CompanyName] [nvarchar](50) NOT NULL,
	[Address1] [nvarchar](100) NULL,
	[Address2] [nvarchar](100) NULL,
	[City] [nvarchar](50) NULL,
	[State] [nchar](50) NULL,
	[Zip] [nchar](10) NULL,
	[ContactPerson] [nvarchar](50) NULL,
	[ContactPhone] [nvarchar](50) NULL,
	[ContactPhone2] [nvarchar](50) NULL,
	[ContactEmail] [nchar](50) NULL,
	[DateAdded] [datetime] NULL CONSTRAINT [DF_Companies_DateAdded]  DEFAULT (getdate()),
	[WorkHours] [nvarchar](max) NULL,
	[OpenSun] [bit] NULL CONSTRAINT [DF_Companies_OpenSun]  DEFAULT ((0)),
	[WorkHrSunFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrSunFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrSunTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrSunTo]  DEFAULT (N'5:00 PM'),
	[OpenMon] [bit] NULL CONSTRAINT [DF_Companies_OpenMon]  DEFAULT ((1)),
	[WorkHrMonFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrMonFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrMonTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrMonTo]  DEFAULT (N'5:00 PM'),
	[OpenTue] [bit] NULL CONSTRAINT [DF_Companies_OpenTue]  DEFAULT ((1)),
	[WorkHrTueFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrTueFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrTueTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrTueTo]  DEFAULT (N'5:00 PM'),
	[OpenWed] [bit] NULL CONSTRAINT [DF_Companies_OpenWed]  DEFAULT ((1)),
	[WorkHrWedFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrWedFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrWedTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrWedTo]  DEFAULT (N'5:00 PM'),
	[OpenThu] [bit] NULL CONSTRAINT [DF_Companies_OpenThu]  DEFAULT ((1)),
	[WorkHrThuFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrThuFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrThuTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrThuTo]  DEFAULT (N'5:00 PM'),
	[OpenFri] [bit] NULL CONSTRAINT [DF_Companies_OpenFri]  DEFAULT ((1)),
	[WorkHrFriFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrFriFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrFriTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrFriTo]  DEFAULT (N'5:00 PM'),
	[OpenSat] [bit] NULL CONSTRAINT [DF_Companies_OpenSat]  DEFAULT ((0)),
	[WorkHrSatFrom] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrSatFrom]  DEFAULT (N'8:00 AM'),
	[WorkHrSatTo] [nvarchar](50) NULL CONSTRAINT [DF_Companies_WorkHrSatTo]  DEFAULT (N'5:00 PM')

[dbo].[AttachmentType](
	[AttachmentTypeId] [int] IDENTITY(1,1) NOT NULL,
	[CompanyId] [int] NULL,
	[AttachmentType] [nvarchar](50) NOT NULL

Basically I'm trying to INSERT a record into the Companies table, then get the newly created record's pk, then use that to insert some records into AttachmentType.

Can you help?

Thanks!


You dont have nvarchar(max) in sql 2000. BLOB datatypes like varchar(max),nvarchar(max),... are available only in sql 2005 and later. B/w why are you using nvarchar for storing fields like WorkHours?
Go to Top of Page

robertnzana
Starting Member

42 Posts
Posted - 2008-06-02 : 13:53:49
U are AWESOME. It was the MAX that is causing the problems!!! Thanks so much.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-02 : 14:00:43
quote:
Originally posted by robertnzana

U are AWESOME. It was the MAX that is causing the problems!!! Thanks so much.

You're welcome
Now i guess you understood the importance of providing enough information to get quick and accurate soln.
Go to Top of Page

robertnzana
Starting Member

42 Posts
Posted - 2008-06-02 : 14:18:29
thanks
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site