SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Data lost in view creation....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 09/01/2005 :  14:21:04  Show Profile  Reply with Quote
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

USA
483 Posts

Posted - 09/01/2005 :  14:35:40  Show Profile  Reply with Quote
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(?)

Edited by - Thrasymachus on 09/01/2005 14:36:26
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 09/01/2005 :  14:55:43  Show Profile  Reply with Quote
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.
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 09/01/2005 :  14:57:00  Show Profile  Reply with Quote
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
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

USA
483 Posts

Posted - 09/02/2005 :  09:24:54  Show Profile  Reply with Quote
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(?)
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 09/02/2005 :  09:54:55  Show Profile  Reply with Quote
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!
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

USA
483 Posts

Posted - 09/02/2005 :  10:02:29  Show Profile  Reply with Quote
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(?)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/02/2005 :  10:13:12  Show Profile  Reply with Quote
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
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 09/02/2005 :  11:48:41  Show Profile  Reply with Quote
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

Edited by - nicki_assumption on 09/02/2005 11:50:25
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000