June 8, 2012 at 3:58 am
Hi, I wonder if anyone can explain the following:
I have a table that has a column [unique_name] defined as Varchar(64) and this contains ID numbers some of which have leading Zeros, e.g. '001007'.
When I run a View with
SELECT unique_name FROM mytable
it displays '001007', however if I run the same SQL in a Query window the leading zeros are dropped and I get '1007'.
Any Idea what's going on?
Thanks
June 8, 2012 at 4:10 am
Can you post the structure of the table and the view please.
Sounds like the view is doing an implicit conversion of the VARCHAR column to INT.
Please post structure of both to confirm - thanks.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
June 8, 2012 at 4:12 am
tbraithwaite (6/8/2012)
Hi, I wonder if anyone can explain the following:I have a table that has a column [unique_name] defined as Varchar(64) and this contains ID numbers some of which have leading Zeros, e.g. '001007'.
When I run a View with
SELECT unique_name FROM mytable
it displays '001007', however if I run the same SQL in a Query window the leading zeros are dropped and I get '1007'.
Any Idea what's going on?
Thanks
can you clarify please
are you running the query from management studio or from some sort of application?
if i run the following from management studio they both return the same
create table temp (mydata varchar(64))
go
insert into temp select '001007'
select mydata from temp
go
create view vw_temp as
select mydata from temp
go
select * from vw_temp
go
drop view vw_temp
go
drop table temp
MVDBA
June 8, 2012 at 4:13 am
Table Structure is:
USE [Academic_Year_2011_2012]
GO
/****** Object: Table [dbo].[CT_STAFF] Script Date: 06/08/2012 11:12:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CT_STAFF](
[staff_id] [int] NOT NULL,
[unique_name] [varchar](64) NOT NULL,
[name] [varchar](64) NULL,
[title] [varchar](16) NULL,
[sex] [char](1) NULL,
[address1] [varchar](64) NULL,
[address2] [varchar](64) NULL,
[address3] [varchar](64) NULL,
[address4] [varchar](64) NULL,
[postcode] [varchar](10) NULL,
[room_id] [int] NULL,
[dept_id] [int] NULL,
[tag1] [varchar](10) NULL,
[tag2] [varchar](10) NULL,
[tag3] [varchar](10) NULL,
[allowance_week] [int] NULL,
[allowance_total] [int] NULL,
[weekly_target] [int] NULL,
[total_target] [int] NULL,
[schedulable] [char](1) NOT NULL CONSTRAINT [DF_STAFF_SCHED] DEFAULT ('Y'),
[office_tel] [varchar](32) NULL,
[home_tel] [varchar](32) NULL,
[mobile] [varchar](32) NULL,
[fax] [varchar](32) NULL,
[varchar](64) NULL,
[www] [varchar](128) NULL,
[profile] [varchar](32) NULL,
[deaf_loop] [char](1) NOT NULL CONSTRAINT [DF_STAFF_LOOP] DEFAULT ('N'),
[wheelchair_access] [char](1) NOT NULL CONSTRAINT [DF_STAFF_WHEELCHAIR] DEFAULT ('N'),
[exam_staff_pref] [int] NOT NULL CONSTRAINT [DF_STAFF_EXAM_STAFF_PREF] DEFAULT ((0)),
[exam_seniority] [int] NOT NULL CONSTRAINT [DF_STAFF_EXAM_SEN] DEFAULT ((0)),
[exam_invigilator] [char](1) NOT NULL CONSTRAINT [DF_STAFF_EXAM_INV] DEFAULT ('N'),
[notes] [text] NULL,
[lookup_id1] [varchar](32) NULL,
[lookup_id2] [varchar](32) NULL,
[lookup_id3] [varchar](32) NULL,
[date_change] [datetime] NOT NULL CONSTRAINT [DF_STAFF_DATE_CHANGE] DEFAULT (getdate()),
[user_id_change] [int] NULL,
[origin_id] [int] NULL,
[original_id] [varchar](32) NULL,
CONSTRAINT [PX_STAFF] PRIMARY KEY CLUSTERED
(
[staff_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [FK_STAFF_DEPT] FOREIGN KEY([dept_id])
REFERENCES [dbo].[CT_DEPT] ([dept_id])
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [FK_STAFF_DEPT]
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [FK_STAFF_ORIGIN] FOREIGN KEY([origin_id])
REFERENCES [dbo].[CT_ORIGIN] ([origin_id])
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [FK_STAFF_ORIGIN]
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [FK_STAFF_ROOM] FOREIGN KEY([room_id])
REFERENCES [dbo].[CT_ROOM] ([room_id])
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [FK_STAFF_ROOM]
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [FK_STAFF_USER] FOREIGN KEY([user_id_change])
REFERENCES [dbo].[CT_USER] ([user_id])
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [FK_STAFF_USER]
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [CK_STAFF_ALLOWANCETOTAL] CHECK (([allowance_total]>=(0)))
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [CK_STAFF_ALLOWANCETOTAL]
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [CK_STAFF_ALLOWANCEWEEK] CHECK (([allowance_week]>=(0)))
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [CK_STAFF_ALLOWANCEWEEK]
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [CK_STAFF_BOOLS] CHECK ((([schedulable]='Y' OR [schedulable]='N') AND ([deaf_loop]='Y' OR [deaf_loop]='N') AND ([wheelchair_access]='Y' OR [wheelchair_access]='N') AND ([sex]='M' OR [sex]='F') AND ([exam_invigilator]='Y' OR [exam_invigilator]='N')))
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [CK_STAFF_BOOLS]
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [CK_STAFF_STAFFID] CHECK (([staff_id]>(0)))
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [CK_STAFF_STAFFID]
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [CK_STAFF_TOTALTARGET] CHECK (([total_target]>=(0)))
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [CK_STAFF_TOTALTARGET]
GO
ALTER TABLE [dbo].[CT_STAFF] WITH NOCHECK ADD CONSTRAINT [CK_STAFF_WEEKLYTARGET] CHECK (([weekly_target]>=(0)))
GO
ALTER TABLE [dbo].[CT_STAFF] CHECK CONSTRAINT [CK_STAFF_WEEKLYTARGET]
View/SQL is:
SELECT staff_id, unique_name, name, title, sex, address1, address2, address3, address4, postcode, room_id, dept_id, tag1, tag2, tag3, allowance_week, allowance_total,
weekly_target, total_target, schedulable, office_tel, home_tel, mobile, fax, email, www, profile, deaf_loop, wheelchair_access, exam_staff_pref, exam_seniority,
exam_invigilator, notes, lookup_id1, lookup_id2, lookup_id3, date_change, user_id_change, origin_id, original_id
FROM CT_STAFF
June 8, 2012 at 4:13 am
derek.colley (6/8/2012)
Can you post the structure of the table and the view please.Sounds like the view is doing an implicit conversion of the VARCHAR column to INT.
Please post structure of both to confirm - thanks.
hi derek - just to save confusion - he's saying the view comes out with the leading zeros - therefore it's more likely his app, not the view which is doing an implicit convert
(ms access and excel have a habit of doing this)
MVDBA
June 8, 2012 at 4:18 am
No this is just in Management Studio.
If I run the Query from another database with the full reference to the source database then the leading zeros don't get dropped.
e.g. SELECT staff_id, unique_name, name, title, sex, address1, address2, address3, address4, postcode, room_id, dept_id, tag1, tag2, tag3, allowance_week, allowance_total,
weekly_target, total_target, schedulable, office_tel, home_tel, mobile, fax, email, www, profile, deaf_loop, wheelchair_access, exam_staff_pref, exam_seniority,
exam_invigilator, notes, lookup_id1, lookup_id2, lookup_id3, date_change, user_id_change, origin_id, original_id
FROM Academic_Year_2011_2012.dbo.CT_STAFF
ORDER BY Unique_name
gives:
staff_idunique_namenametitlesexaddress1address2address3address4postcoderoom_iddept_idtag1tag2tag3allowance_weekallowance_totalweekly_targettotal_targetschedulableoffice_telhome_telmobilefaxemailwwwprofiledeaf_loopwheelchair_accessexam_staff_prefexam_seniorityexam_invigilatornoteslookup_id1lookup_id2lookup_id3date_changeuser_id_changeorigin_idoriginal_id
2809001007xxxx, yyy MrMNULLNULLNULLNULL5411442NULLNULLNULLNULLNULLNULL49680YNULLNULLNULLNULLNULLNN00NNULLNULLNULLNULL2007-08-06 14:32:22.000NULL879001007
2726031778zzz, www MrMNULLNULLNULLNULL3539NULLNULLNULLNULLNULLNULLNULLYNULLNULLNULLNULLNULLNN00NNULLNULLNULLNULL2007-08-06 11:04:38.000NULL879031778
2715043363aaa, bbb MsFNULLNULLNULLNULL5901451NULLNULLNULLNULLNULLNULL24840YNULLNULLNULLNULLNULLNN00NNULLNULLNULLNULL2010-09-14 12:19:15.000NULL879043363
June 8, 2012 at 4:20 am
I've also just discovered that if I give the full path to the source DB and run the query from that DB is also gives the leading zeros.
Rather strange, but I can live with the full path. If you have any clues I'd still like to know why.
Thanks
June 8, 2012 at 4:27 am
tbraithwaite (6/8/2012)
I've also just discovered that if I give the full path to the source DB and run the query from that DB is also gives the leading zeros.Rather strange, but I can live with the full path. If you have any clues I'd still like to know why.
Thanks
Are there any other versions of the CT_STAFF table hanging around? Different schemas, different databases, perhaps?
My guess would be that you are picking up one of these by accident and that by fully qualifying the db name you are picking up something different.
June 8, 2012 at 4:28 am
i'm sure you've already checked , but can you run the query from your database and make sure you specify the schema name (but not the db)
ie select x,y,z from dbo.mytable
does it return the leading zeros - if it does then i would hazzard a guess that you have a view or table that is in a different schema and your users default schema is going there
MVDBA
June 8, 2012 at 4:29 am
+1 phil - you beat me to it by 1 minute
also possibly a synonym???
MVDBA
June 8, 2012 at 4:30 am
There's definitely VARCHAR to INT conversion going on somewhere but I can't see it.
And you're definitely doing this in SSMS?
Why would the scope of the SELECT command matter ... unless....
Do you have a similar table in your default database by any chance, called CT_STAFF? If so (perhaps created accidentally), check the datatype of the UNIQUE_NAME column.
That's the only thing I can think of that would cause this behaviour internally, as Michael Vessey said an external application could also force conversion.
EDIT: Michael, Phil - you beat me to it by 2 mins!
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
June 8, 2012 at 4:35 am
Hi both,
Yes just having the dbo does return leading zeros. Also this does too:
SELECT staff_id, unique_name, name, title, sex, address1, address2, address3, address4, postcode, room_id, dept_id, tag1, tag2, tag3, allowance_week, allowance_total,
weekly_target, total_target, schedulable, office_tel, home_tel, mobile, fax, email, www, profile, deaf_loop, wheelchair_access, exam_staff_pref, exam_seniority,
exam_invigilator, notes, lookup_id1, lookup_id2, lookup_id3, date_change, user_id_change, origin_id, original_id
FROM CT_STAFF
ORDER BY Unique_name
Looks like I need to always include the dbo, I shouldn't be lazy in these things I know.
There are two other databases that have the same tables (it's our timetable system and we have to create a new DB for each year.)
Thanks for your help and quick repsonses.
Tony
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply