VIEW to other DB has wrong data type

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 2005 Forums

Transact-SQL (2005)

VIEW to other DB has wrong data type

Author

Topic

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-01 : 16:35:02

Hi, I did some searching but I am not seeing my issue addressed. I have a view in one DB that points to a table in another DB both on the same server. However, the view shows a particular column as INT when it is defined as NVARCHAR(9) in the base table. Any know how to get the view to show the correct data type?

For example:

-- Source Table
CREATE TABLE dbo.Foo
(
	FooId int NOT NULL,
	FooName nvarchar(255) NOT NULL,
	AddressLine1 nvarchar(255) NULL,
	AddressLine2 nvarchar(255) NULL,
	AddressLine3 nvarchar(255) NULL,
	City nvarchar(255) NULL,
	StateOrProvince nvarchar(50) NULL,
	PostalCode nvarchar(50) NULL,
	Country nvarchar(255) NULL,
	FooNumber nvarchar(9) NULL
)
GO

-- View
CREATE VIEW dbo.Foo
AS
SELECT 
	FooId,
	FooName,
	AddressLine1,
	AddressLine2,
	AddressLine3,
	City,
	StateOrProvince,
	PostalCode,
	Country,
	FooNumber

FROM 
	SourceDB.dbo.Foo WITH (NOLOCK)
GO

When I look at the view in Management Studio it shows FooNumber as an INT. Additionally, if I select from the view I get INT values out of FooNumber.

If anyone has any ideas, please pass them along.

Thanks,

-Ryan

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-01 : 16:36:58

did you alter the table recently? drop/recreate the view and see if it makes any difference.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-01 : 16:38:35

quote:
Originally posted by dinakar

did you alter the table recently? drop/recreate the view and see if it makes any difference.

Yeah, I've done that several times trying to figure out what is going on.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-01 : 16:43:24

was the table on the SourceDB altered recently?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-01 : 16:46:21

quote:
Originally posted by dinakar

was the table on the SourceDB altered recently?

Yes. The issues existed and I tried several things to try resolve it. One of the things I did was to drop and recreate the offending column.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-01 : 17:01:21

drop/recreate the view. Views are not automaticaly refreshed when underlying schema changes.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-01 : 18:00:21

quote:
Originally posted by dinakar

drop/recreate the view. Views are not automaticaly refreshed when underlying schema changes.

Yep, as mentioned above, I did drop and re-create the view several times. The thing that is strange is that the column that is having the issue has alwasy been NVARCHAR(9).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-01 : 18:09:58

[code]CREATE VIEW dbo.Foo
AS
SELECT
FooId,
FooName,
AddressLine1,
AddressLine2,
AddressLine3,
City,
StateOrProvince,
PostalCode,
Country,
CAST(FooNumber AS NVARCHAR(9)) AS FooNumber

FROM
SourceDB.dbo.Foo WITH (NOLOCK)[/code]

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-01 : 18:13:07

quote:

Originally posted by Peso

CREATE VIEW dbo.Foo
AS
SELECT 
	FooId,
	FooName,
	AddressLine1,
	AddressLine2,
	AddressLine3,
	City,
	StateOrProvince,
	PostalCode,
	Country,
	CAST(FooNumber AS NVARCHAR(9)) AS FooNumber

FROM 
	SourceDB.dbo.Foo WITH (NOLOCK)

Peter Larsson
Helsingborg, Sweden

Peso, thats like beating it with a stick.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-01 : 18:16:28

I know. It only hides the symptom.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources