Data lost in view creation....

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)

Data lost in view creation....

Author

Topic

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-09-01 : 14:21:04

Hi- I'm running this query to create a view... pasted below. If I run it and don't create the view and specify one item I am looking for, it gives me the correct data, but if I just select it from the view, it doesn't. Here's the view and below that is the query that does yeild correct data:

drop view vwIndComps
create view vwIndComps as
select IDS.Id as prid,
c.*,a.*,b.*
,d.*,e.*
from
(select LVPrID as Id from vwComplicationsLV
union
select LNPrID as Id from vwComplicationsLN
union
Select SRPrID as Id from vwComplicationsSR
union
Select IndPrID as Id from vwIndications
union
Select SI_PRID as Id from vwSIProcs) IDs
Left join vwComplicationsLV a
on IDS.Id = a.LVPrID
Left Join vwComplicationsLN b
on IDS.Id = b.LNPrID
Left Join vwComplicationsSR c
on IDS.Id = c.SRPrID
Left Join vwIndications d
on IDS.Id = d.IndPrID
Left Join vwSIProcs e
on IDS.Id = e.SI_PRID

THIS query does give me data while the view gives me null for the same column name [AIFS Inflow Vessel]

select IDS.Id as prid,
e.[AIFS Inflow Vessel]
from
(select LVPrID as Id from vwComplicationsLV
union
select LNPrID as Id from vwComplicationsLN
union
Select SRPrID as Id from vwComplicationsSR
union
Select IndPrID as Id from vwIndications
union
Select SI_PRID as Id from vwSIProcs) IDs
Left join vwComplicationsLV a
on IDS.Id = a.LVPrID
Left Join vwComplicationsLN b
on IDS.Id = b.LNPrID
Left Join vwComplicationsSR c
on IDS.Id = c.SRPrID
Left Join vwIndications d
on IDS.Id = d.IndPrID
Left Join vwSIProcs e
on IDS.Id = e.SI_PRID
where e.SI_PRID='635080102'

Any ideas?
Thanks a lot
Nicki

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-01 : 14:35:40

I bet there is a break in the ownership chain. Are all of the tables and views owned by dbo? Building views on top of views is a dark and troubling path you are heading down.

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-09-01 : 14:55:43

I agree with you- it seems really tenuous but I don't have a choice b/c the DBA won't let me drop and create tables every week. I need to dynamically create these tables to query, I'd much rather use tables.

All the views are owned by the same user.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-09-01 : 14:57:00

oh- also, when i create the view with e.* first in the line of columns, i get my data. it's like it's running out of memory or something when it gets to the end.

thanks a lot

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-02 : 09:24:54

Why not just query the tables directly? Post your table and view definitions, sample data and expected results. Either I if I have time or someone else here will help with a rewrite.

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-09-02 : 09:54:55

it's a long story. basically i've written stored procedures that create a cross tab look at tables so that the values in the tables have become the column names in the views- those values may change- be added, subtracted or edited. so i need to recreate the view to account for those changes once a week. i am querying the tables via the view. it's not much of a difference. i could run it manually as a statement and select from the results but i can't have my client do that :) it's very fast, using table indexes well and all that. and it works when i re-order everything. so i re-ordered it for now, but i don't trust it. i thought there might be something obvious about it though- that someone would tell me "oh, there is a limit of columns in views that erases your data if you go over it" or something. thanks for your replies!

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-02 : 10:02:29

Well then, do you understand what I mean when I talk about "ownership-chain"? If every object (view, table, stored procedure etc..) is not owned by the same user (or dbo) then you can run into problems where queries return results for one user with certain permissions and not return anything for another user different permissions. This is why it is a good idea that everything is owned by dbo (ie dbo.MyTable and dbo.MyStoredProcedure) in a production environment.

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 10:13:12

We explicitly name everything, and reference everything, with "dbo." prefix. Even though we have no non-DBO-owned objects. (Its supposedly better for performance, but we do it more to explictly prevent an accidental non-DBO owner object - e.g. when a developer creates some stuff logged in with db-Owner permissions, rather than sysAdmin)

Kristen

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-09-02 : 11:48:41

they are all owned by the same object- sorry, i thought i mentioned that. in the job scheduler, they are explicitly stated, but not in my submission here. it does work fine when i re-order the columns so i doubt it would be an ownership thing anyway but they are all owned by the same user, not dbo

thanks

i have been given a user on this server so i don't have the option of being dbo- i'm not the admin of this particular server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources