When is an integer column not an integer column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

When is an integer column not an integer column?

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2004-07-01 : 00:02:31

Hi there,

SQL 7 sp4

Ive got a query that has been working for 3 years and then all of a sudden stops working! It is a join statement that joins 2 tables based on the MemberID (int) column:

INNER JOIN
tblMembers MEM ON MEM.Memberid = T.Memberid

Recently a developer added a new column to the tblMembers table...but didnt touch anything else. Now this query will not return the correct resultset unless I change the following:

INNER JOIN
tblMembers MEM ON CAST(MEM.Memberid as int) = CAST(T.Memberid as int)

Im not too sure why this has stopped working, and all I can think of is maybe the developer changed the table using his SQL 2000 version of Enterprise manager and the Server is SQL 7.0

Is there a way to find out why the values "MEM.Memberid = T.Memberid" do not equal each other?

Full Query is as follows:

SELECT T.MEMBERID, MEM.FIRSTNAME, MEM.SURNAME, R.ROUNDID,
MEM.COMPANYID, C.TITLE, M.MATCHID, T.MATCHID AS TMATCHID
FROM dbo.tblMatch M
INNER JOIN
dbo.tblRounds R ON M.ROUNDID = R.ROUNDID
INNER JOIN
dbo.tblTips T ON M.MATCHID = T.MATCHID
INNER JOIN
tblMembers MEM ON CAST(MEM.Memberid as int) = CAST(T.Memberid as int)
INNER JOIN
tblCompany C ON MEM.Companyid = C.Companyid
WHERE R.ROUNDID = 13 AND
R.RESULTSENTERED = 1 AND MEM.COMPANYID = 'VIn045'
ORDER BY mem.companyid

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-01 : 02:06:03

can you post DDL for the underlying tables?

-ec

Aamir
Starting Member

14 Posts

Posted - 2004-07-01 : 04:19:18

Hi,
I thought the new member promotes the data type of memberid alongwith adding a new filed.

if u post the DDL of underlying table, that would be easy to figure out the solution/reason.

SoftGuru

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2004-07-01 : 08:13:53

Here is the DDL:

CREATE TABLE [dbo].[tblTips] (
[MEMBERID] [int] NOT NULL ,
[ROUNDID] [int] NOT NULL ,
[MATCHID] [int] NOT NULL ,
[TEAMID] [int] NOT NULL ,
[POINTS] [tinyint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMembers] (
[MEMBERID] [int] IDENTITY (1, 1) NOT NULL ,
[COMPANYID] [nvarchar] (10) NOT NULL ,
[USERNAME] [varchar] (150) NOT NULL ,
[PASSWORD] [varchar] (10) NULL ,
[FIRSTNAME] [varchar] (50) NULL ,
[SURNAME] [varchar] (50) NULL ,
[NICKNAME] [varchar] (50) NULL ,
[ISEMAILFORMATHTML] [bit] NOT NULL ,
[NEWSLETTER] [bit] NOT NULL ,
[DATEADDED] [datetime] NOT NULL ,
[LOGINCOUNT] [int] NOT NULL ,
[LASTLOGIN] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblMembers] WITH NOCHECK ADD
CONSTRAINT [PK_tblMembers] PRIMARY KEY CLUSTERED
(
[MEMBERID]
) WITH FILLFACTOR = 92 ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IX_tblTips] ON [dbo].[tblTips]([MEMBERID], [ROUNDID], [MATCHID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblMembers] ADD
CONSTRAINT [DF_tblMembers_ISEMAILFORMATHTML] DEFAULT (1) FOR [ISEMAILFORMATHTML],
CONSTRAINT [DF_tblMembers_NEWSLETTER] DEFAULT (1) FOR [NEWSLETTER],
CONSTRAINT [DF_tblMembers_DATEADDED] DEFAULT (getdate()) FOR [DATEADDED],
CONSTRAINT [DF_tblMembers_LOGONCOUNT] DEFAULT (0) FOR [LOGINCOUNT],
CONSTRAINT [DF_tblMembers_LASTLOGIN] DEFAULT (getdate()) FOR [LASTLOGIN],
CONSTRAINT [IX_tblMembers] UNIQUE NONCLUSTERED
(
[USERNAME]
) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTips] ADD
CONSTRAINT [FK_tblTips_tblMatch] FOREIGN KEY
(
[MATCHID]
) REFERENCES [dbo].[tblMatch] (
[MATCHID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_tblTips_tblMembers] FOREIGN KEY
(
[MEMBERID]
) REFERENCES [dbo].[tblMembers] (
[MEMBERID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_tblTips_tblRounds] FOREIGN KEY
(
[ROUNDID]
) REFERENCES [dbo].[tblRounds] (
[ROUNDID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_tblTips_tblTeams] FOREIGN KEY
(
[TEAMID]
) REFERENCES [dbo].[tblTeams] (
[TEAMID]
) NOT FOR REPLICATION
GO

ALTER TABLE [dbo].[tblMembers] ADD
CONSTRAINT [FK_tblMembers_tblCompany] FOREIGN KEY
(
[COMPANYID]
) REFERENCES [dbo].[tblCompany] (
[COMPANYID]
) NOT FOR REPLICATION
GO

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2004-07-02 : 04:06:10

More on this;

Before this error happened, my sql server ran out of space due to some large log files. Im thinking this may be the cause to my problem as it seems the Index on tblTips went corrupt. I found this out because on an insert I got errors for violating the IX_tblTips Index. After reindexing the table, the violating Index error went away and I could also do the join without casting the column as an integer.

This one has proved to be a difficult debug!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources