Link problem in my script file

I have a script file that I need to change the link because the view is leaving out some records. The problem description follows the script.

The script is:

CREATE view v_Flight_Check(Flight_No, Doc_No, Flight_Doc_No, Leg_no, Actual_Departing_Date, Actual_Arriving_Date,
Flight_Legs, Actual_Hours, Actual_Over_Load, Actual_Landings, Actual_Air_Time, Flight_Type_A, Flight_Type_B,
Aircraft_Id, Doc_Status,Aircraft_Doc_No,LogBookPage, Actual_From_Airport,Actual_To_Airport, Actual_Flight_Legs, FLActual_Hours, FLActual_Over_Load,
Start_ECU_Airframe_Hrs, End_Ecu_Airframe_Hrs, End_Ecu_Ac_hrs,
Crew,Position, From_Leg_Doc_No, To_Leg_Doc_No,Last_Name, First_Name, Tail_No,HSN,Serial_No,RECORD_TYPE) AS

SELECT DISTINCT Flight_Log.Flight_No,Flight_Log_Leg.Doc_no, Flight_Log_Leg.Flight_Doc_No, Flight_Log_Leg.Leg_no, Flight_Log_Leg.Actual_Departing_Date, Flight_Log_Leg.Actual_Arriving_Date,
Flight_Log_Leg.Flight_Legs, Flight_Log_Leg.Actual_Hours, Flight_Log_Leg.Actual_Over_Load, Flight_Log_Leg.Actual_Landings, Flight_Log_Leg.Actual_Air_Time, Flight_Log_Leg.Flight_Type_A, Flight_Log_Leg.Flight_Type_B,
Flight_Log.Aircraft_Id, Flight_log.Doc_Status, Flight_Log.Aircraft_Doc_No,Flight_Log.LogBookPage, Flight_Log.Actual_From_Airport,Flight_Log.Actual_To_Airport, Flight_Log.Actual_Flight_Legs, Flight_Log.Actual_Hours, Flight_Log.Actual_Over_Load,
Flight_Log.Start_ecu_Airframe_hrs, Flight_Log.End_ECU_Airframe_hrs,Flight_Log.End_ecu_ac_hrs,
Flight_Log_Crew.User_code,Flight_Log_Crew.Position, Flight_Log_Crew.From_Leg_Doc_No, Flight_Log_Crew.To_Leg_Doc_No, Userlist.L_name,UserList.F_Name,Aircraft_hdr.tail_no,Aircraft_hdr.hsn, Aircraft_Hdr.Serial_No,'FL'

FROM GDB_01_4.dbo.FLIGHT_LOG,
GDB_01_4.dbo.Aircraft_HDR,
GDB_01_4.dbo.Flight_Log_leg Right Outer Join
GDB_01_4.dbo.FLIGHT_LOG_CREW ON GDB_01_4.dbo.FLIGHT_LOG_CREW.FLIGHT_LOG_DOC_NO = GDB_01_4.dbo.FLIGHT_LOG_LEG.FLIGHT_DOC_NO,
GDB_01_4.dbo.Userlist

Where Flight_Log.Doc_No = Flight_Log_Leg.Flight_Doc_No and
FLIGHT_LOG_LEG.DOC_NO >= fLIGHT_LOG_CREW.FROM_LEG_DOC_NO AND FLIGHT_LOG_LEG.DOC_NO <= fLIGHT_LOG_CREW.TO_LEG_DOC_NO and
Flight_Log_Crew.User_Code = Userlist.ccode and Flight_Log.Aircraft_Doc_No = Aircraft_Hdr.Doc_No

The problem is that if there was no crew listed on the Flight Leg, the entire flight and leg is left off. I need all of the records in the FLight Leg Table, even if no crew was listed.

Does anyone know how I can fix this? My script writing skills are minimal.

Thanks

SBoyd

You need to move some of the conditions in the WHERE clause to the OUTER JOIN ON clause. This should provide the results you are looking for. It is however hard to tell which conditions without looking at some data and the relationship between the tables in detail.

Link problem in my script file

I have a script file that I need to change the link because the view is leaving out some records. The problem description follows the script.

The script is:

CREATE view v_Flight_Check(Flight_No, Doc_No, Flight_Doc_No, Leg_no, Actual_Departing_Date, Actual_Arriving_Date,
Flight_Legs, Actual_Hours, Actual_Over_Load, Actual_Landings, Actual_Air_Time, Flight_Type_A, Flight_Type_B,
Aircraft_Id, Doc_Status,Aircraft_Doc_No,LogBookPage, Actual_From_Airport,Actual_To_Airport, Actual_Flight_Legs, FLActual_Hours, FLActual_Over_Load,
Start_ECU_Airframe_Hrs, End_Ecu_Airframe_Hrs, End_Ecu_Ac_hrs,
Crew,Position, From_Leg_Doc_No, To_Leg_Doc_No,Last_Name, First_Name, Tail_No,HSN,Serial_No,RECORD_TYPE) AS

SELECT DISTINCT Flight_Log.Flight_No,Flight_Log_Leg.Doc_no, Flight_Log_Leg.Flight_Doc_No, Flight_Log_Leg.Leg_no, Flight_Log_Leg.Actual_Departing_Date, Flight_Log_Leg.Actual_Arriving_Date,
Flight_Log_Leg.Flight_Legs, Flight_Log_Leg.Actual_Hours, Flight_Log_Leg.Actual_Over_Load, Flight_Log_Leg.Actual_Landings, Flight_Log_Leg.Actual_Air_Time, Flight_Log_Leg.Flight_Type_A, Flight_Log_Leg.Flight_Type_B,
Flight_Log.Aircraft_Id, Flight_log.Doc_Status, Flight_Log.Aircraft_Doc_No,Flight_Log.LogBookPage, Flight_Log.Actual_From_Airport,Flight_Log.Actual_To_Airport, Flight_Log.Actual_Flight_Legs, Flight_Log.Actual_Hours, Flight_Log.Actual_Over_Load,
Flight_Log.Start_ecu_Airframe_hrs, Flight_Log.End_ECU_Airframe_hrs,Flight_Log.End_ecu_ac_hrs,
Flight_Log_Crew.User_code,Flight_Log_Crew.Position, Flight_Log_Crew.From_Leg_Doc_No, Flight_Log_Crew.To_Leg_Doc_No, Userlist.L_name,UserList.F_Name,Aircraft_hdr.tail_no,Aircraft_hdr.hsn, Aircraft_Hdr.Serial_No,'FL'

FROM GDB_01_4.dbo.FLIGHT_LOG,
GDB_01_4.dbo.Aircraft_HDR,
GDB_01_4.dbo.Flight_Log_leg Right Outer Join
GDB_01_4.dbo.FLIGHT_LOG_CREW ON GDB_01_4.dbo.FLIGHT_LOG_CREW.FLIGHT_LOG_DOC_NO = GDB_01_4.dbo.FLIGHT_LOG_LEG.FLIGHT_DOC_NO,
GDB_01_4.dbo.Userlist

Where Flight_Log.Doc_No = Flight_Log_Leg.Flight_Doc_No and
FLIGHT_LOG_LEG.DOC_NO >= fLIGHT_LOG_CREW.FROM_LEG_DOC_NO AND FLIGHT_LOG_LEG.DOC_NO <= fLIGHT_LOG_CREW.TO_LEG_DOC_NO and
Flight_Log_Crew.User_Code = Userlist.ccode and Flight_Log.Aircraft_Doc_No = Aircraft_Hdr.Doc_No

The problem is that if there was no crew listed on the Flight Leg, the entire flight and leg is left off. I need all of the records in the FLight Leg Table, even if no crew was listed.

Does anyone know how I can fix this? My script writing skills are minimal.

Thanks

SBoyd

You need to move some of the conditions in the WHERE clause to the OUTER JOIN ON clause. This should provide the results you are looking for. It is however hard to tell which conditions without looking at some data and the relationship between the tables in detail.

Limiting Records Returned

I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 Server, and want to ensure the fastest performance for my users. I expect the database to grow to around 25,000 records (after that, they'll be archived), and would like opi
nions/comments on the best way to access the data. I would guess that a recordset would be fastest, but how do you set up a form to be based on a recordset instead of just hooking directly to the underlying table?
TIA,
Terry Roberts
Check the ADO and ADP references in the Microsoft Office
2000/Visual Basic Programmer's Guide:
http://msdn.microsoft.com/library/de...radobasics.asp
You can also find a list of Access resources here:
http://msdn.microsoft.com/library/en...acacclinks.asp
And...you really should considering getting the book: Access
Developer's Guide to SQL Server by
Mary Chipman and Andy Baron.
-Sue
On Mon, 10 May 2004 09:11:08 -0700, "Terry Roberts"
<anonymous@.discussions.microsoft.com> wrote:

>I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 Server, and want to ensure the fastest performance for my users. I expect the database to grow to around 25,000 records (after that, they'll be archived), and would like op
inions/comments on the best way to access the data. I would guess that a recordset would be fastest, but how do you set up a form to be based on a recordset instead of just hooking directly to the underlying table?
>TIA,
>Terry Roberts

Limiting Records Returned

I'm building an Access front end (A2K2 ADP project) to view records on a SQL
2000 Server, and want to ensure the fastest performance for my users. I exp
ect the database to grow to around 25,000 records (after that, they'll be ar
chived), and would like opi
nions/comments on the best way to access the data. I would guess that a reco
rdset would be fastest, but how do you set up a form to be based on a record
set instead of just hooking directly to the underlying table?
TIA,
Terry RobertsCheck the ADO and ADP references in the Microsoft Office
2000/Visual Basic Programmer's Guide:
adobasics.asp" target="_blank">http://msdn.microsoft.com/library/d...
adobasics.asp
You can also find a list of Access resources here:
http://msdn.microsoft.com/library/e.../acacclinks.asp
And...you really should considering getting the book: Access
Developer's Guide to SQL Server by
Mary Chipman and Andy Baron.
-Sue
On Mon, 10 May 2004 09:11:08 -0700, "Terry Roberts"
<anonymous@.discussions.microsoft.com> wrote:

>I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 S
erver, and want to ensure the fastest performance for my users. I expect the databas
e to grow to around 25,000 records (after that, they'll be archived), and would like
op
inions/comments on the best way to access the data. I would guess that a recordset would be
fastest, but how do you set up a form to be based on a recordset instead of just hooking dir
ectly to the underlying table?
>TIA,
>Terry Roberts

Limiting Records Returned

I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 Server, and want to ensure the fastest performance for my users. I expect the database to grow to around 25,000 records (after that, they'll be archived), and would like opinions/comments on the best way to access the data. I would guess that a recordset would be fastest, but how do you set up a form to be based on a recordset instead of just hooking directly to the underlying table
TIA
Terry RobertsCheck the ADO and ADP references in the Microsoft Office
2000/Visual Basic Programmer's Guide:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovradobasics.asp
You can also find a list of Access resources here:
http://msdn.microsoft.com/library/en-us/dnacc2k/html/acacclinks.asp
And...you really should considering getting the book: Access
Developer's Guide to SQL Server by
Mary Chipman and Andy Baron.
-Sue
On Mon, 10 May 2004 09:11:08 -0700, "Terry Roberts"
<anonymous@.discussions.microsoft.com> wrote:
>I'm building an Access front end (A2K2 ADP project) to view records on a SQL 2000 Server, and want to ensure the fastest performance for my users. I expect the database to grow to around 25,000 records (after that, they'll be archived), and would like opinions/comments on the best way to access the data. I would guess that a recordset would be fastest, but how do you set up a form to be based on a recordset instead of just hooking directly to the underlying table?
>TIA,
>Terry Roberts

Limited User Rights

Hi All,
How can I give the user view rights only?How can I give the user view rights only?I wouldn't grant them any other permissions.

-PatP|||I agree. But what command do I use to do that?|||I wouldn't grant them any other permissions.

-PatP

Don't you mean you would add the to the db_datareader database role?|||Make them members of the db_datareader and db_denydatawriter roles.

Limitation of updating views

Are there any limitations that can render a view "un-updatable"?There are a lot of restrictions, look for 'updatable view' in Books Online and you can find the limitations of updatable views

Limit View in SQL 2005

When adding users to the SQL Server, is there a way to limit what DB's the
user can actually view when using Enterprise Manager?
i.e. When User1 logs into Enterprise Manger, they can only see DB1 in the
list of DB's, they can't see Master, Model, or any others?
The objective is to let a company connect to their Database, without see
what other clients/databases are hosted on the same DB server.
ThanksHi Kevin,
This is indeed possible in SQL Server 2005 but the master and tempdb
databases will be visible to all users - there is no way you can get
around that. Every user needs to have access to these databases. It's
just the way SQL Server works.
In order to explain how to make USER databases invisible to others I am
attaching the following script. It simulates a situation with three
databases, each belonging to different companies. Each company has its
own login.
create database company1_db
create database company2_db
create database company3_db
go
create login company1 with password = 'password1234*'
go
create login company2 with password = 'password1234*'
go
create login company3 with password = 'password1234*'
go
--At this point, all three logins (company1, company2 and company3)
--can see all user databases (company1_db, company2_db and company3_db).
use master
go
deny view any database to public
--At this point, all three logins (company1, company2 and company3)
--can see just the master and tempdb databases.
use company1_db
go
sp_changedbowner 'company1'
use company2_db
go
sp_changedbowner 'company2'
use company3_db
go
sp_changedbowner 'company3'
--At this point, the three logins (company1, company2 and company3)
--can see the master and tempdb databases as well as their own database.
Hope this helps!
Jonathan
Kevin A wrote:
> When adding users to the SQL Server, is there a way to limit what DB's the
> user can actually view when using Enterprise Manager?
> i.e. When User1 logs into Enterprise Manger, they can only see DB1 in the
> list of DB's, they can't see Master, Model, or any others?
> The objective is to let a company connect to their Database, without see
> what other clients/databases are hosted on the same DB server.
> Thanks
>|||JPD (jpd@.nospamplease.com) writes:
> use master
> go
> deny view any database to public
> --At this point, all three logins (company1, company2 and company3)
> --can see just the master and tempdb databases.
> use company1_db
> go
> sp_changedbowner 'company1'
>
> use company2_db
> go
> sp_changedbowner 'company2'
>
> use company3_db
> go
> sp_changedbowner 'company3'
But the apparent problem with that is there for every company are several
logins.
What SQL 2005 offers is clearly not sufficient. For this reason I submitted
this entry on Connect a while back:
https://connect.microsoft.com/SQLSe...=2738
30
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

limit user account to a table or database or view

Hello I think i have read in other posts that there is not way in
sql200 to limit access to a view to a certain user. What work arounds
are there for this, Can I schedule a dts export to a different database
or table and the assign rights only to data database or table? Can
anyone give some steps to create a secure login to a single sql view?
thanksIn SQL Server 2000, a user in a database does not have any access to an
object (table, view, proc, etc.) unless that user 1) owns it, 2) they
have been granted explicit permissions to it or 3) they have implicit
permissions to it via their role membership within the DB.
If you want a single DB user to only have access to a single view in a
database and no other database objects in that database, you just grant
them the appropriate permissions to that view with the GRANT statement
(presumably we're talking about SELECT permissions here), make sure the
"public" role in the database has no explicit permissions to anything
(which, by default, it doesn't) and make sure the user is not a member
of any other role in the DB (every user in a database is a member of
"public", you can't change that). This will guarantee that the only
object the user can interact with in the DB is that view.
Is that your goal? (Also, are you talking about SQL 2000 or SQL 2005?)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
joe wrote:

>Hello I think i have read in other posts that there is not way in
>sql200 to limit access to a view to a certain user. What work arounds
>are there for this, Can I schedule a dts export to a different database
>or table and the assign rights only to data database or table? Can
>anyone give some steps to create a secure login to a single sql view?
>thanks
>
>|||Hi,
Nope its not possible to in SQL 2000 but you can restrict your users to
access it using sp_revokedbaccess , offcourse they can see that this db
exists in dblist but they wont able to access it . this is a fixed in SQL
2005.
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"joe" wrote:

> Hello I think i have read in other posts that there is not way in
> sql200 to limit access to a view to a certain user. What work arounds
> are there for this, Can I schedule a dts export to a different database
> or table and the assign rights only to data database or table? Can
> anyone give some steps to create a secure login to a single sql view?
> thanks
>|||I think we need some clarification from the OP as to what he's trying to
achieve. Joe, what exactly are you trying to do? Are you trying to
*hide* a database from various SQL server logins in SQL Enterprise
Manager (SQL 2000) on a particular server (Andy's reply)? Or are you
trying to make sure a particular database user *can only access a
single*, specific object within the database (my original reply)? Or
are you trying to make sure that a particular database user *cannot*
access a particular database object within the database (this can be
done with the DENY statement)?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Andy Davis wrote:

>Hi,
>Nope its not possible to in SQL 2000 but you can restrict your users to
>access it using sp_revokedbaccess , offcourse they can see that this db
>exists in dblist but they wont able to access it . this is a fixed in SQL
>2005.
>Regards
>|||Hi,
true we need to wait for his response.
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Mike Hodgson" wrote:

> I think we need some clarification from the OP as to what he's trying to
> achieve. Joe, what exactly are you trying to do? Are you trying to
> *hide* a database from various SQL server logins in SQL Enterprise
> Manager (SQL 2000) on a particular server (Andy's reply)? Or are you
> trying to make sure a particular database user *can only access a
> single*, specific object within the database (my original reply)? Or
> are you trying to make sure that a particular database user *cannot*
> access a particular database object within the database (this can be
> done with the DENY statement)?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Andy Davis wrote:
>
>|||I know this question is old and that comments are not likely to be helpful,
but my interpretation of the question was that Joe wants a particular user
to be able to access a specific view but all other users should not have
access to the view. My understanding of the requirement is that the
requirement does not prevent a single user from having access to anything
else.
It is certainly best to get the requirements clarified before attempting to
suggest solutions.
I did not quote Mike's comment because it is posted using HTML format and
when I replied to it Outlook Express did not provide for it the quotation
symbols at the left of quoted text.