sql server 2000 to sql server 2008

Question

sql server 2000 to sql server 2008

thomas511

Grasshopper

Points: 16
More actions
May 6, 2010 at 10:10 am

#222228

Hi,
We recently migrated a db from sql server 2000 to sql server 2008. The query below ran without errors on sql 2000, but on 2008 I'm getting the error message "Implicit conversion from data type unknown to int is not allowed. Use the convert function to run this query." The field causing the problem is IsActive which is a bit type. The immediate fix would be to place a convert() on IsActive, but I don't understand why this would be necessary. Thank you in advance for your help. 😀
if not exists ( SELECT * from BusinessTypes where BusinessTypeGroupID = 0 )
BEGIN
SET IDENTITY_INSERT BusinessTypes ON
INSERT INTO BusinessTypes
(BusinessTypeID,
BusinessTypeCode,
BusinessTypeGroupID,
Name,
IsActive,
CreatedDate,
CreatedBy )
Values
(0,
null,
0,
'',
1,
GetDate(),
888)
SET IDENTITY_INSERT BusinessTypes OFF
END
The table BusinessTypes is defined as:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BusinessTypes](
[BusinessTypeID] [dbo].[ID] IDENTITY(1,1) NOT NULL,
[BusinessTypeCode] [dbo].[CodeField] NULL,
[Name] [dbo].[Name] NOT NULL DEFAULT (''),
[Description] [dbo].[DESC] NULL DEFAULT (''),
[IsActive] [bit] NOT NULL DEFAULT ((1)),
[BusinessTypeGroupID] [dbo].[ID] NOT NULL,
[CreatedDate] [dbo].[CreatedDateFieldType] NOT NULL DEFAULT (getdate()),
[CreatedBy] [dbo].[CreatedByFieldType] NOT NULL,
[LastModifiedDate] [dbo].[CreatedDateFieldType] NULL,
[LastModifiedBy] [dbo].[CreatedByFieldType] NULL,
CONSTRAINT [BusinessTypes_PK] PRIMARY KEY CLUSTERED
(
[BusinessTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[BusinessTypes] WITH CHECK ADD CONSTRAINT [BusinessTypeGroups_BusinessTypes_FK1] FOREIGN KEY([BusinessTypeGroupID])
REFERENCES [dbo].[BusinessTypeGroups] ([BusinessTypeGroupID])
GO
ALTER TABLE [dbo].[BusinessTypes] CHECK CONSTRAINT [BusinessTypeGroups_BusinessTypes_FK1]

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

I don't see a problem with the IsActive bit field. Why do you think that's where the problem lies and how sure are you of that?

It's a little hard to troubleshoot this because I don't know anything about your user defined data types. I'm guessing that's where the problem is but it's just a guess.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

bteraberry (5/6/2010)
I don't see a problem with the IsActive bit field. Why do you think that's where the problem lies and how sure are you of that?
It's a little hard to troubleshoot this because I don't know anything about your user defined data types. I'm guessing that's where the problem is but it's just a guess.

Same here, that code should work, 1 is 1 is 1 no matter the version you're running.

This seems like a case of error message pointing to the wrong thing. We need the whole code and sample data to find the real problem.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Ninja's_RGR'us (5/6/2010)
This seems like a case of error message pointing to the wrong thing. We need the whole code and sample data to find the real problem.

I agree. Please provide sample data and the pertinent code.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

thomas511 Grasshopper Points: 16 More actions · Answer 4

Thanks for the replies. I agree, I don't think this is a bit type related issue. I think it may be a server issue more than a code issue, because I was able to run the entire script successfully which was throwing those implicit conversion errors all day yesterday. I still haven't found the source of the problem, but I have enough to look elsewhere. Thanks for the tips guys.