Post reply

View Oddity

  • November 17, 2004 at 10:27 am

    #163280

    There is a view based on a single table. The table has a mixture of data types for the columns. The view does nothing out of the ordinary. It simply selects the columns from the table and often renames them eg

    CREATE VIEW vwName (First_Col, Second Col) AS SELECT t1, t3

    FROM TabName

    Running a query using the DATEADD function on the server/database in which it was created works fine. When running it on a different server using a linked server throws the error: Argument data type varchar is invalid for argument 2 of dateadd function.

    So, looking into this I ran sp_help against the view and a number of integer fields are displayed as varchar(20) fields. If I check the table the fields are in fact integer.

    Any idea why a simple view would change the data type of the field when not using CAST or CONVERT? The columns themselves contain numbers and nulls or just numbers depending upon the column we are talking about.

    Any help would be appreciated.

    Thanks,

    George

  • November 18, 2004 at 3:11 am

    #530606

    Hmmmm, very odd.

    The only thing I can think of is, are you sure that the 2 servers are set up the same way (sort orders; collation and that sort of thing..).

    When I get some time (ha!) I'll try this on our setup here and see if I can reproduce the problem.

    Sam

  • November 18, 2004 at 9:10 am

    #530689

    Maybe the table was altered after the view was created. Weird things can happen if you change the columns of a table (after the view was created), especially on views that have * as part of the column list. For example:

    create table t (a int, b int)

insert into t values (1,2)
go
create view v as select * from t
go
select b from v
    alter table t drop column b
alter table t add c char(1)
    select b from v
    drop view v
drop table t

    The solution, in this case, is to recreate the view (or at least alter it).

    Razvan

  • November 19, 2004 at 11:13 am

    #530887

    Both good answers. Thank you. I checked the collation and setups and all seems fine there. And yes, I was thinking about recreating or altering the view. Just was curious as to whether there was a known set of circumstances that might cause this.

    I'll try changing the view and see what happens.

    Thanks,

    George

Viewing 4 posts - 1 through 3 (of 3 total)

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