Increase number by 1

Hello all,
I have, what i think, is a unique problem that i'm hoping some of you can help me on.

I need to create a record number that is incremented by 1 whenever someone adds a new record to the database. For example, records numbering 1,2,3 are in the database. When the users adds a new record, SQL takes the last recordno, 3 in this case, and adds 1 to it thus producing 4.

Also, i need to have the ability to replace deleted record numbers with new ones. Using the example above, say a user deletes record number 2. Whenever someone adds a new record, sql would see the missing number and assign the new record that number.

I hope i'm making sense here. Does anyone have any ideas about this? Any articles on the web that someone could point me to?

Thanks.
Richard M.hi richard,
i guess you need to do this by coding urself. u can use the feature in Sql server to increment the number by one but i don't think its possible to replace the deleted number.

so the better solution will be to add the incrementing number programatically. first declare int data type in sql server and assign 1 for the first record. for new records, check whether any number is missing and try to add new into that.

for eg, if you have 10 records, then no of 10th record should be 10 else some record is deleted. so u can use loop to check which number is missing.

i hope u can do the coding.

Incorrect Syntax using IF statement

Hi,

I'm new to SQL Server Programming, I work with ASP a lot, but lately
I've been trying to create Stored Procedures, etc. I'm having a
problem writing a simple IF statement.. I don't seem to understand why
it's giving me this error. I've search around on Google Groups, but I
still don't get it.

=================
USE msdb

IF NOT EXISTS (SELECT * FROM sysjobs WHERE name = 'Scheduled Nightfax')

END
=================

My error is:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'END'.

Thanks for any help.you need a BEGIN for every END
example

DECLARE @.v BIT
SELECT @.v = 1

IF @.v = 1
BEGIN
select 'yes'
END
ELSE
BEGIN
select 'No'
END

Or without begin...end
IF @.v = 1
select 'yes'
ELSE
select 'No'

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks, I played with it a bit and I got the result I was looking for.

==============
USE msdb
DECLARE @.JobName varchar(255)
SELECT @.JobName = name FROM sysjobs WHERE name = 'Scheduled Nightfax'

IF @.JobName = 'Scheduled Nightfax'
PRINT 'YES'
ELSE
PRINT 'NO'
==============

Incorrect syntax near XML-While creating XML Schema

Hi,

Im trying to create a xml schema like

CREATE XML SCHEMA COLLECTION BooksSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema elementFormDefault="unqualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="book">
<xsd:complexType mixed="false">
<xsd:sequence>
<xsd:element name="name" type="xsd:string"/>
<xsd:element name="author" type="xsd:string"/>
<xsd:element name="publisher" type="xsd:string"/>
<xsd:element name="cost" type="xsd:integer"/>
<xsd:element name="comments" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>';

But when i execute , im getting a error like Incorrect syntax near 'XML' .any one know why its comming??

Thanks

Hi ,

I use SqlServer 2005 and above SQL statement works fine on my pc.

Make sure you have correct permission.

To create an XML SCHEMA COLLECTION requires at least one of the following sets of permissions:

CONTROL permission on the server
ALTER ANY DATABASE permission on the server
ALTER permission on the database
CONTROL permission in the database
ALTER ANY SCHEMA permission and CREATE XML SCHEMA COLLECTION permission in the database
ALTER or CONTROL permission on the relational schema and CREATE XML SCHEMA COLLECTION permission in the database
sql

Incorrect syntax near the keyword 'OR'.

Hi,
I have a stored procedure
CREATE PROCEDURE dbo.Retrieve
(
@.SEARCH_STRING nvarchar(200),
@.COUNT int
)
AS
DECLARE @.STRING_COUNT varchar(3)
DECLARE @.SQL varchar(1000)
SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE ' +
@.SEARCH_STRING + '% )'
EXEC (@.SQL)
The stored procedure is created successfully.
But I get the error when I try to use it: (Retrieve '',10)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.
Thanks
KiranAnswered in .programming. Please don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Kiran" <Kiran@.nospam.net> wrote in message
news:O9oiPrX#EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a stored procedure
> CREATE PROCEDURE dbo.Retrieve
> (
> @.SEARCH_STRING nvarchar(200),
> @.COUNT int
> )
> AS
> DECLARE @.STRING_COUNT varchar(3)
> DECLARE @.SQL varchar(1000)
>
> SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
> SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
> WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE '
+
> @.SEARCH_STRING + '% )'
> EXEC (@.SQL)
> The stored procedure is created successfully.
> But I get the error when I try to use it: (Retrieve '',10)
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'OR'.
>
> Thanks
> Kiran
>sql

Incorrect syntax near the keyword Declare.

Dear Group,

I am trying to create a view and keep getting the Incorrect syntax near the
keyword 'Declare'" error.

Here is the code I am writing.

Create view fixed_airs (sid, fad_a2, fad_a3) as
Declare @.sid int,
@.fad_a2 int,
@.fad_a3 int
select @.sid=cast(substring(subject_id,1,8)as int) ,
@.fad_a2 =cast (substring(fad_2_4,1,1) as int),
@.fad_a3=cast(substring(fad_2_4,2,1) as int)
from parentpacket.

Thanks for the help in advance.

Jeff MagouirkJeff Magouirk wrote:
> Dear Group,
> I am trying to create a view and keep getting the Incorrect syntax near the
> keyword 'Declare'" error.
> Here is the code I am writing.
> Create view fixed_airs (sid, fad_a2, fad_a3) as
> Declare @.sid int,
> @.fad_a2 int,
> @.fad_a3 int
> select @.sid=cast(substring(subject_id,1,8)as int) ,
> @.fad_a2 =cast (substring(fad_2_4,1,1) as int),
> @.fad_a3=cast(substring(fad_2_4,2,1) as int)
> from parentpacket.
> Thanks for the help in advance.
> Jeff Magouirk

You keep getting syntax errors because you're using illegal syntax in
your CREATE VIEW statement. :D You can't use DECLARE, nor can you pass
in variables to a view. Check Books Online for proper syntax. But, in a
nutshell, you can only use a SELECT statement in a view.

Zachsql

Incorrect syntax near LEFT

Hi guys;

I am trying to create a stored procedure in my database with dynamic filter, how ever i cant even let my query work.

When i run my stored procedure i get this errorIncorrect syntax near 'LEFT'

I dnt knw wats wrong with my code.

Please help me...
below is my stored procedure..

Thanks is advance.

ALTER PROCEDURE dbo.GetClassByCustomFilter
@.pcCustomFilterNVARCHAR(500)=''
AS
DECLARE @.sSqlString nvarchar(1024)
SET @.sSqlString = 'SELECT R.cRoomNo, P.cFirstName, P.cLastName, P.cMiddleName, U.cCode AS cSubjectCode, U.cName AS cSubjectName, '
SET @.sSqlString = @.sSqlString + 'U.cDescription AS cSubjectDescription, B.cCode AS cSection, H.cName AS cRecurenceName, C.iClassID, C.iInstructorID, C.iSubjectid, C.iEnrollmentID, '
SET @.sSqlString = @.sSqlString + 'C.iRecordTypeID, C.iBlockSectionID, C.cCode, C.cType, C.iRoomID, C.cRecurrence, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, '
SET @.sSqlString = @.sSqlString + 'C.tStartTime,8) AS datetime) AS tStartTime, CAST(CONVERT(nvarchar, GETDATE(), 1) + '' '' + CONVERT(nvarchar, C.tEndTime,8) AS datetime) '
SET @.sSqlString = @.sSqlString + 'AS tEndTime, C.fUnits, C.nAllowed, C.nMaxAllowed, C.mNotes, C.tCreated, C.tEdited, C.iEditedBy, C.iCreatedby, C.adGUID '
SET @.sSqlString = @.sSqlString + 'FROM dbo.PERSONALINFO P INNER JOIN dbo.INSTRUCTORS I ON P.iPersonalInfoId = I.iPersonalInfoID RIGHT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.CLASSES C INNER JOIN dbo.BLOCKSECTIONS B ON C.iBlockSectionID = B.iBlockSectionId INNER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.ROOMS R ON C.iRoomID = R.iRoomId WHERE (C.cType <> ''0'')'
IF LEN(@.pcCustomFilter) > 0
BEGIN
SET@.sSqlString = @.sSqlString + ' AND ' + @.pcCustomFilter
END
EXEC sp_executesql @.sSqlString

Well, thanks for reminding me why I hate complex dynamic SQL strings.

Thisa line is causing you problems:

SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the:ON C.cRecurrence = H.cRecurrenceON I.iInstructorID = C.iInstructorID (two ON clauses for a single JOIN clause).|||Thanks for the reply .

The Query is correct.

I get this error because the lenght of the variable@.sSqlString is only 1024 but my query string is longer than that.

Regards|||Well, be that as it may, having to ON statements for a single JOIN clause will give you a syntax error...|||Of course it will. But the SQL statement below has 6JOIN and 6ON.

enewe thanks 4 the time.

Regards.|||The following, cut and pasted from you original post, is what I'm referencing:

SET @.sSqlString = @.sSqlString + 'dbo.SUBJECTS U ON C.iSubjectid = U.iSubjectID LEFT OUTER JOIN '
SET @.sSqlString = @.sSqlString + 'dbo.SCHEDULERECURRENCE H ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID LEFT OUTER JOIN '

Note the 'ON C.cRecurrence = H.cRecurrence ON I.iInstructorID = C.iInstructorID' of the second line. That will cause issues.

However, I'm glad you got it working...|||Of course it wont work bcoz its just a part of the statement, not the whole statement. :) jst Kid'N. But I am 101% sure that the SQL Statement below will work because I just copied it from Visual Studio .NET View Designer.

8)

Incorrect syntax near 'Go'

Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
--
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.

Incorrect syntax near 'Go'

Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
Arek
GO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>
|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.

Incorrect syntax near 'Go'

Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.

'Incorrect Syntax near' Error in Create Type in 2005

This is getting really frustrating. I've searched and tried about everything I can think of. I have an assembly added to my database by using Sql Management Console. The assembly is called TestUDT. I have defined 4 structs within that assembly, Person, Transaction, Payment, and Seller. The assembly was added to SqlServer 2005 with dbo as the owner, the permission set is SAFE, and the assembly is signed. The structs are within the Test namespace.

When ever I type the following in a Query window, just checking the syntax fails, let alone trying to execute it.

CREATE TYPE dbo.Seller EXTERNAL NAME TestUDT.[Test.Seller]

The error is:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'TestUDT'.
What am I doing wrong?

Thanks
]Monty[

I'm sorry that I can't help directly, but the same syntax works fine for me here. You sure you don't have any "strange" characters in the syntax, or....?

Niels|||OK, I figured this out.

The database existed in the original SQLServer 2000 database. When I upgraded to 2005, the compatibilty level of the database remained at 2000. After changing the compatibility level to 2005, the command now works.

Important safety tip: remember to have the compatibility set to 2005 on the database if you're going to create a CLR type.

Of course, SqlServer lets you add the assembly without complaining and no where in the docs on CREATE TYPE is this mentioned.

]Monty[|||>>Of course, SqlServer lets you add the assembly without complaining and no where in the docs on CREATE TYPE is this mentioned.

Hi Monty,
Thank you for pointing out this error in the documentation. I've created a documentation bug which will be fixed in a future refresh of Books Online.

Just as an FYI, you can create documentation bugs by clicking the Send Feedback button at the top of a topic. Your comments are used to automatically create a bug that is assigned to the appropriate writer. For SQL Server 2005, we will be releasing quarterly updates to Books Online, so you can expect to see your feedback incorporated in a fairly reasonable amount of time.

Regards,|||Thanks for adding the doc bug. I tried to do the feedback thing, but of course here at work we have Lotus Notes (ugh!) and all kinds of security policy, and I was never able to get a dialog or email to come up to give the feed back. For those of us in that situation, it would be nice to have an email address or an URL presented instead of just a button to click.

Thanks
]Monty[|||>For those of us in that situation, it would be nice to have an email address or an URL presented instead of just a button to click.
Thanks for the suggestion. I've forwarded your idea to the customer feedback team.

Regards,sql

INCORRECT SYNTAX NEAR "STRING" FOR ALTER SQL

HELP

I am trying to create a new column for every file in a folder

but i keep getting an sql exception - incorrect syntax near ' whatever the value of the file name is'

it works if i just type in the value directly

my code look like this

fsofolder = CreateObject("Scripting.FileSystemObject")
folder = fsofolder.GetFolder("the path to the Files\")
files = folder.Files
For Each objfile In files
sname = objfile.Name

cmd3.CommandText = "ALTER TABLE NEW ADD " & "' " & sname & " ' " & " nvarchar(MAX)"

DatabaseConnection.Open()

Try

cmd3.Connection = DatabaseConnection
cmd3.ExecuteNonQuery()
Catch ex As SqlException
MsgBox(ex.Message)
End Try

DatabaseConnection.Close()

The syntax should be Alter TabletablenameADD COLUMNcolumnname datatype

There is no place for apostophe delimiters in the syntax, and the word COLUMN is needed too.

|||

Thanks

I figured out what the problem was

cmd3.CommandText = "ALTER TABLEtablename ADD " & "'[" & sname & "]" & " nvarchar(MAX)"

It was not accepting eg Q45654656.txt as a column name

but accepting [Q45654656]

|||

database objects can't have a '.' in their names

|||

It did actually

I missed-type in the last post

the difference was the [] that enclosed the string

it accepted

sname = [textfile.txt]

but not

sname = textfile.txt

as the column name

|||

I have another question however,

is it possible to have in one string a sql command to insert into database tableonlyif the column is empty or NULL ?

maybe something like

cmd.CommandText = "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...) WHERE Columnvalue is NULL"

I appreciate the help

|||

Well, the a

fredi:

I have another question however,

is it possible to have in one string a sql command to insert into database tableonlyif the column is empty or NULL ?

maybe something like

cmd.CommandText = "INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...) WHERE Columnvalue is NULL"

I appreciate the help

Well, why don't you type in that sql statement for yourself and tell us? :)

However, wanting to do what you asked does not make sense to me. I could understand if you said you wanted to update a column only if it was null, because presumably you don't want to lose the old value. By definition, if you want to insert a record, the record shouldn't already exist, so how could a non-existent record have a value in any column?

FYI, it is possible to say (instead of the VALUES (value1, etc.)), SELECT value1, value2, etc.

|||

well here is what i am trying to do and able to do so far

-look into a folder

-create a database table in sql server using the create sql command

-alter the table and create a column named for each file name in the folder

-read each of the text file data into each column

however if i run the code again it adds the textfile data into the same columns again

I just need a statement to say if the column already has data then don't do the all the above steps

I hope this explains my situation

These work:

cmd2.CommandText = "CREATE TABLE " & DatabaseTableName & "(" & ISTCOLUMN& " nvarchar(MAX))"
cmd3.CommandText = "ALTER TABLE " & DatabaseTableName & " ADD " & sname1 & " nvarchar(MAX)"

cmd4.CommandText = "INSERT INTO " & DatabaseTableName & "( " & sname1 & " )" & "VALUES ( '" & filefields(i) & "' )"

How will I check if sname column is Null and only insert the values of filefields into it?

thanks

|||

Am I correct in saying the following?

If the column exists in the table, then you must have populated it with a value?

Because if that is true, then all you have to do is query INFORMATION_SCHEMA.COLUMNS and find out if the column exists.

If that is not true, then you can query the table to see if the column exists.

If it does, query the table to see if it has a row at all, and if so, a value in the column you are interested in.

If yes, do nothing.

If no, update the record.

Now, I have to tell you that what you are doing almost certainly violates relational data modeling.

I would be EXTREMELY SUSPICIOUS of a database design that required me to add a column to a table for every file in a directory.

The odds of this being a good database design are very, very low. Lower than the chance of my being hit by lightning this year.

Standard relational theory would tell us to create a ROW, not a COLUMN, for every file in the directory.

I am not telling you that your database design is wrong. I am telling you that it is very likely wrong, and that you should re-think your approach to be very, very sure the approach you are taking is the right one.

How many files might there be in the directory? Did you know there are limits as to how many columns can be defined for a table? Will you have more than that limit? Did you know that there are limits as to the number of bytes that can be returned for a row in a query? How many filename columns with their values will it take to go over that limit?

See <http://technet.microsoft.com/en-us/library/ms143432.aspx> for details on sql server limits.

Please reconsider your design or - to educate us all - explain why the situation you are in requires such an unusual design.

|||

Thanks David,

If the column exists in the table, then you must have populated it with a value?

is not true. I first create an empty table with at least one column then I add more columns as they show up (i.e as the text files get created). That might not be as important now as the structure of the database itself.

To say that I am fairly new to Database design would be an understatement. Thanks for enlightening me. I am still in an early stage of the design phase and you just showed me how flawed the database would be if I end up going over limits. I would reconsider my approach.

|||

Glad to have helped! I've got 25 years of computing mistakes behind me, so it's easier for me to recognize them.. Some of them are old friends. :)

So, to wrap up this thread, the correct answer is "Don't do it."

Incorrect syntax in user-defined function

In the script below is the DDL to create some tables and a UDF.

What I'm interested in is the UDF at the end. Specifically, these few
lines:

--CLOSE OTRate
--DEALLOCATE OTRate
ELSE-- @.NumRecords <= 0

If I uncommment CLOSE and DEALLOCATE and check the syntax I get a
message:

"Incorrect syntax near keyword ELSE"

Being a good little footsoldier, I want to release resources
explicitly, but clearly I'm putting the CLOSE and DEALLOCATE statements
in the wrong place.

Could someone please tell me where I ought to put them so that the
cursor is CLOSEd and DEALLOCATEd correctly.

By the way, I am not after negative comments on the data design, or the
logic (or lack of it) in the function, just why the syntax error
occurs.

Thanks as ever

Edward

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Employee]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PurchaseOrder]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[PurchaseOrder]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TimesheetItem]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TimesheetItem]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Work]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Work]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[WorkOTRate]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[WorkOTRateDefaults]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[WorkOTRateDefaults]
GO

CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DepartmentID] [int] NOT NULL ,
[JobDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[DefaultRatePerHour] [smallmoney] NULL ,
[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserGroupID] [int] NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastLogon] [datetime] NULL ,
[PasswordChange] [smalldatetime] NULL ,
[PreviousPassword1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword3] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword4] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword5] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PurchaseOrder] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[OrderDate] [datetime] NOT NULL ,
[OrderValue] [money] NOT NULL ,
[FixedPrice] [bit] NOT NULL ,
[Prepaid] [bit] NOT NULL ,
[AllocatedHours] [int] NULL ,
[RatePerHour] [money] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[TypeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL ,
[WorkComponentID] [int] NULL ,
[WorkItemID] [int] NULL ,
[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OffSite] [tinyint] NULL ,
[TravelTo] [smalldatetime] NULL ,
[TravelToMins] [int] NULL ,
[TravelFrom] [smalldatetime] NULL ,
[TravelFromMins] [int] NULL ,
[TravelMileage] [int] NULL ,
[NonChargeableMins] [int] NULL ,
[OTAuthorisedID] [int] NULL ,
[OTAuthorisedDate] [smalldatetime] NULL ,
[Abroad] [bit] NULL ,
[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ApprovalID] [int] NULL ,
[AprovalDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chargeable] [bit] NOT NULL ,
[Complete] [bit] NOT NULL ,
[ClientID] [int] NULL ,
[ClientContactID] [int] NULL ,
[Entered] [smalldatetime] NULL ,
[ApprovalRequired] [tinyint] NULL ,
[ColorCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkOTRate] (
[WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NOT NULL ,
[TimeTo] [datetime] NOT NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkOTRateDefaults] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NULL ,
[TimeTo] [datetime] NULL ,
[RateMultiplier] [float] NOT NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
Function to determine the actual cost, in minutes, of a particular
segment of work. This is what it does, or is supposed to do.
1. From the PARAMETER WorkID, determine the conclusion of the work
block associated with the TimesheetID - i.e. StartTime + DurationMins
2. Establish whether there are records in the WorkOTRate table
corresponding to this particular WorkID, weekday and time period
3. If there are, get the amount of minutes by which the work block
coincides.
4. If there are no such records, get the default values from the
WorkOTRateDefaults table
5. If the block doesn't cross any boundaries then it's just regular
work, so just count the minutes.

25/08/2005 EC
*/

CREATE FUNCTION fnGetWorkCostPerTimesheetItem(@.TimesheetID int)

RETURNS float

AS

BEGIN

DECLARE

@.OTRateTimeFrom datetime,
@.OTRateTimeTo as datetime,
@.OTRateMultiplier as float,
@.EndTime datetime,
@.ReturnValue as float,
@.OrderRatePerHour as money,
@.EmployeeRatePerHour as smallmoney,
@.NumRecords as int,
@.WorkID as int,
@.EmployeeID as int,
@.StartTime as smalldatetime,
@.Duration as int,
@.Found as int,
@.Chargeable as bit

-- Get the various bits and bobs needed for the calculation
SET @.ReturnValue = 0
SET @.Found = 0

SELECT
@.WorkID = WorkID,
@.EmployeeID = EmployeeID,
@.StartTime = Start,
@.Duration = DurationMins
FROM
TimesheetItem
WHERE
ItemID = @.TimesheetID

-- If this work is NOT chargeable, return 0
SELECT
@.Chargeable = Chargeable
FROM
[Work]
WHERE
WorkID = @.WorkID

IF @.Chargeable = 1

BEGIN
SET @.EndTime = DATEADD(mi, @.Duration, @.StartTime)

-- Get the rate per hour for this work
SELECT
@.OrderRatePerHour = RatePerHour
FROM
PurchaseOrder
WHERE
WorkID = @.WorkID

-- Get the rate per hour for the employee
SELECT
@.EmployeeRatePerHour = DefaultRatePerHour
FROM
Employee
WHERE
(EmployeeID = @.EmployeeID)

-- Find out if there's an OT Rate set up for this WorkID
SELECT
@.NumRecords = Count(*)
FROM
WorkOTRate
WHERE
((WorkID = @.WorkID) AND
(WorkDay = DATEPART(dd, @.StartTime)))

IF @.NumRecords > 0
BEGIN

DECLARE OTRate CURSOR FOR
SELECT
TimeFrom,
TimeTo,
RateMultiplier
FROM
WorkOTRate
WHERE
((WorkID = @.WorkID) AND
(WorkDay = DATEPART(dw, @.StartTime)))

OPEN OTRate
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
WHILE (@.@.fetch_status=0)
BEGIN

-- Set the two time values so that they match the date under
consideration.
SET @.OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeFrom,
@.StartTime) ,@.OTRateTimeFrom)
SET @.OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeTo ,
@.StartTime) ,@.OTRateTimeTo)

-- If the TimeTo part is < TimeFrom, then we know it crosses a
time boundary
IF @.OTRateTimeTo < @.OTRateTimeFrom
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)

-- If the time is between midnight and 8 a.m. it's the "next"
day
IF CONVERT(datetime, @.OTRateTimeFrom, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeFrom = DATEADD(dd, 1, @.OTRateTimeFrom)

IF CONVERT(datetime, @.OTRateTimeTo, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)

/*
Ok, now we're in business. There are four possible scenarios
that we are interested in (ignoring when the Timesheet item period is
entirely outside the OT rate period)
NUMBER 1
S E
OT OT

NUBMER 2
S E
OT OT

NUMBER 3
S E
OT OT

NUBMER 4
S E
OT OT

*/
-- NUMBER 1
IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 2
ELSE IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime BETWEEN
@.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
-- NUMBER 3
IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo) AND
(@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 4
ELSE IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
AND (@.EndTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
END
END
--CLOSE OTRate
--DEALLOCATE OTRate
ELSE-- @.NumRecords <= 0

BEGIN
DECLARE OTRate CURSOR FOR
SELECT
TimeFrom,
TimeTo,
RateMultiplier
FROM
WorkOTRateDefaults
WHERE
(WorkDay = DATEPART(dw, @.StartTime))

OPEN OTRate
FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier

WHILE (@.@.fetch_status=0)
BEGIN

-- Set the two time values so that they match the date under
consideration.
SET @.OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeFrom,
@.StartTime) ,@.OTRateTimeFrom)
SET @.OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @.OTRateTimeTo ,
@.StartTime) ,@.OTRateTimeTo)

-- If the TimeTo part is < TimeFrom, then we know it crosses a
time boundary
IF @.OTRateTimeTo < @.OTRateTimeFrom
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)

-- If the time is between midnight and 8 a.m. it's the "next"
day
IF CONVERT(datetime, @.OTRateTimeFrom, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeFrom = DATEADD(dd, 1, @.OTRateTimeFrom)

IF CONVERT(datetime, @.OTRateTimeTo, 108) BETWEEN '00:00' AND
'08:00'
SET @.OTRateTimeTo = DATEADD(dd, 1, @.OTRateTimeTo)

/*
Ok, now we're in business. There are four possible scenarios
that we are interested in (ignoring when the Timesheet item period is
entirely outside the OT rate period)
NUMBER 1
S E
OT OT

NUBMER 2
S E
OT OT

NUMBER 3
S E
OT OT

NUBMER 4
S E
OT OT
*/
-- NUMBER 1
IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 2
ELSE IF (@.StartTime < @.OTRateTimeFrom) AND (@.EndTime BETWEEN
@.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi,
@.OTRateTimeFrom, @.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END
-- NUMBER 3
IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo) AND
(@.EndTime > @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.OTRateTimeTo)) * @.OTRateMultiplier))
SET @.Found = 1
END
--NUMBER 4
ELSE IF (@.StartTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
AND (@.EndTime BETWEEN @.OTRateTimeFrom AND @.OTRateTimeTo)
BEGIN
SET @.ReturnValue = @.ReturnValue + (((DATEDIFF(mi, @.StartTime,
@.EndTime)) * @.OTRateMultiplier))
SET @.Found = 1
END

FETCH NEXT FROM OTRate INTO @.OTRateTimeFrom, @.OTRateTimeTo,
@.OTRateMultiplier
END
END
CLOSE OTRate
DEALLOCATE OTRate

-- If there were no matching OT records, it's just a regular block
of work in normal hours
IF @.Found = 0
SET @.ReturnValue = @.Duration
END

-- Finally we factor in the relation between the Employee's rate and
the Order's stated rate.
RETURN (@.ReturnValue * (@.EmployeeRatePerHour / @.OrderRatePerHour))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOteddysnips@.hotmail.com wrote:
> In the script below is the DDL to create some tables and a UDF.
> What I'm interested in is the UDF at the end. Specifically, these few
> lines:
> --CLOSE OTRate
> --DEALLOCATE OTRate
> ELSE-- @.NumRecords <= 0
I haven't actually read the code through thoroughly, so I don't know if
others are going to give you advice about doing it in a set oriented
fashion, but I believe that your close and deallocate are coming one
END too late. The two ENDs above them (to my reading) are the END of
the while loop and then the end of the if statement. When using ELSE,
the following should be adhered to:

IF <condition>
<statement or block>
ELSE
<statement or block
where statement is either a single statement or:

BEGIN
<statement> [<statement>...]
END

Damien|||Damien wrote:
> teddysnips@.hotmail.com wrote:
> > In the script below is the DDL to create some tables and a UDF.
> > What I'm interested in is the UDF at the end. Specifically, these few
> > lines:
> > --CLOSE OTRate
> > --DEALLOCATE OTRate
> > ELSE-- @.NumRecords <= 0
> I haven't actually read the code through thoroughly, so I don't know if
> others are going to give you advice about doing it in a set oriented
> fashion, but I believe that your close and deallocate are coming one
> END too late.

You're quite right - many thanks! As for doing it using sets - well, I
really don't have time!

Edward|||(teddysnips@.hotmail.com) writes:
> You're quite right - many thanks! As for doing it using sets - well, I
> really don't have time!

But you assume that anyone will have the time to run that code? I hope
that you can find the time to test it on full-size data, before you
devote your important time to something else!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

incorrect password

I create a SQL login on my MS-SQL 2k Personal Edition and set the password
and confirm the password.
If I immediately go back into the login and do changes in Database Access, I
get prompted to confirm the password and when I enter the password again, it
tells me that the confirmation password is incorrect.
What is wrong?
ThanksThis can occur after appying MS03-031: Cumulative Security Patch for SQL
Server
FIX: You are prompted for password confirmation after you change a standard
SQL Server login
http://support.microsoft.com/?kbid=826161
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Esmar Joensen" <EsmarJoensen@.discussions.microsoft.com> wrote in message
news:6265A40C-427B-40EA-8E94-9EC145541A0C@.microsoft.com...
>I create a SQL login on my MS-SQL 2k Personal Edition and set the password
> and confirm the password.
> If I immediately go back into the login and do changes in Database Access,
> I
> get prompted to confirm the password and when I enter the password again,
> it
> tells me that the confirmation password is incorrect.
> What is wrong?
> Thanks
>
>

Incorrect PageAudit

I've a database in SQLServer 2K Personal Edition. The database compatibility
level is set to 8. I was trying to create deployment package using Office 2K
Developer. I am using MSDE as my backend database manager.
Howerver when I try to attach my MDF in the MSDE, it gives me error as
follows:
"Invalid database page header. PageAudit Property is Incorrect"
Can anyone help me with this problem.
Thank you in advance
Dorji
hi Dorji,
Dorji wrote:
> I've a database in SQLServer 2K Personal Edition. The database
> compatibility level is set to 8. I was trying to create deployment
> package using Office 2K Developer. I am using MSDE as my backend
> database manager.
> Howerver when I try to attach my MDF in the MSDE, it gives me error as
> follows:
> "Invalid database page header. PageAudit Property is Incorrect"
Office 2000 provides MSDE 1.0 (based on SQL Server 7.0 code base)...
you can not restore/attach SQL Server 2000/MSDE 2000 databases on SQL Server
7.0/MSDE 1.0 intances..
you can perhaps download and use MSDE Rel A, updated at sp4 level from
http://www.microsoft.com/sql/msde/do...s/download.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Adrea,
Thank you so much for your kind response. But I still couldn't manage to
attach my database. In fact I've tried several of my databases from the same
source, none seem to work. I can attach exisitng databases (model etc) but no
mine.
What could it be?
Dorji
"Andrea Montanari" wrote:

> hi Dorji,
> Dorji wrote:
> Office 2000 provides MSDE 1.0 (based on SQL Server 7.0 code base)...
> you can not restore/attach SQL Server 2000/MSDE 2000 databases on SQL Server
> 7.0/MSDE 1.0 intances..
> you can perhaps download and use MSDE Rel A, updated at sp4 level from
> http://www.microsoft.com/sql/msde/do...s/download.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Dorji,
Dorji wrote:
> Hello Adrea,
> Thank you so much for your kind response. But I still couldn't
> manage to attach my database. In fact I've tried several of my
> databases from the same source, none seem to work. I can attach
> exisitng databases (model etc) but no mine.
>
are you trying to attach SQL Server 2000/MSDE 2000 databases on MSDE 1.0?
again, if this is the case, this is not a supported option...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
It works now. I did not know that MSDE installs in Windows security mode by
default. So I had to change it to SQL and the connection works fine.
However I've another thing to ask if you don't mind. I've multiple instance
of SQL Server and MSDE 2000. How can I connect to a particular Instance for
instance from MS Access ADP file?.
I must thank you for the DbaManager utility that you have posted on your
website. It is a great tool for MSDE users. It really helped me with my
project.
Dorji
"Andrea Montanari" wrote:

> hi Dorji,
> Dorji wrote:
> are you trying to attach SQL Server 2000/MSDE 2000 databases on MSDE 1.0?
> again, if this is the case, this is not a supported option...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Dorji,
Dorji wrote:
> Hi Andrea,
> It works now. I did not know that MSDE installs in Windows security
> mode by default. So I had to change it to SQL and the connection
> works fine.
this should have little or nothing to do with the problem you reported...
changing the authentication supported mode to Mixed mode does not grant you
the option to attach invalid databases...
ok..

> However I've another thing to ask if you don't mind. I've multiple
> instance of SQL Server and MSDE 2000. How can I connect to a
> particular Instance for instance from MS Access ADP file?.
in the datalink dialog you have to select the server name...
the relative combo should be already populated with all available servers...
default instances will be in the form of "ComputerName", where named
instances will be listed as "ComputerName\InstanceName"...

> I must thank you for the DbaManager utility that you have posted on
> your website. It is a great tool for MSDE users. It really helped
> me with my project.
thank you for your interest in this prj of mine... and please feel free to
(privately) contact me for any question or concern about it, as long as for
suggestions and feedback
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Incorrect columns returned by view

When I create a view consisting of an inner join between a view and a
table the columns refernced in the the view are returned incorrectly,
example

select id.itemcode, id.description, iv.linevalue, iv.vatvalue from
dbo.tbl_itemdetails id inner join dbo.vw_invoices iv where
dbo.tbl_itemdetails.itemcode = dbo.vw_invoices.itemcode

The actual value that is returned from the view is the column
immediately to the left of the linevalue column, ie stockroomThe code you have posted isn't correct syntax, you've missed the ON clause.

One possibility for you: Could it be that you have some control characters
such as carriage return/line feed in one of the columns you are returning?
Special characters sometimes cause formatting problems when displaying data
in Query Analyzer.

If you need more help please post some code we can run that will reproduce
the problem: DDL (CREATE TABLE statement(s) for the table(s)), sample data
(INSERT statements) and the actual definition of the view.

--
David Portas
SQL Server MVP
--|||Yeah it was just a quick rehash of the view, limiting the information
for demo purposes the actual view(vw_test) is below:

CREATE VIEW dbo.vw_test
AS
SELECT dbo.vw_Invoices_I.ID_Company_Number,
dbo.vw_Invoices_I.ID_Item_Code, dbo.vw_Invoices_I.I_Order_Number,
dbo.vw_Invoices_I.I_Order_Line_Number,
dbo.vw_Invoices_I.I_Invoice_Number, dbo.vw_Invoices_I.I_Customer_Number,
dbo.vw_Invoices_I.I_Delivery_Address_Code,
dbo.vw_Invoices_I.I_Line_Value *
dbo.tbl_Lagged_Sales.Percentage_Of_Sales AS Expr1,
dbo.vw_Invoices_I.I_VAT_Value,
dbo.vw_Invoices_I.I_Discount_Value, dbo.vw_Invoices_I.I_Standard_Cost,
dbo.vw_Invoices_I.I_Line_Cost_Value
FROM dbo.tbl_Lagged_Sales INNER JOIN
dbo.vw_Invoices_I ON
dbo.tbl_Lagged_Sales.Supplier_Code = dbo.vw_Invoices_I.ID_Company AND
dbo.tbl_Lagged_Sales.Lag_Product_Group =
dbo.vw_Invoices_I.ID_Product_GroupHi

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tbl_Invoices_I_DB_TEST]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tbl_Invoices_I_DB_TEST]
GO

CREATE TABLE [dbo].[tbl_Invoices_I_DB_TEST] (
[I_Company_Number] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Line_Number] [smallint] NOT NULL ,
[I_Invoice_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Disp_Seq_No] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Pack_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Stockroom] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[I_Line_Value] [numeric](17, 2) NOT NULL ,
[I_VAT_Value] [numeric](17, 2) NULL ,
[I_Discount_Value] [numeric](17, 2) NULL ,
[I_Standard_Cost] [numeric](17, 2) NULL ,
[I_Line_Quantity] [numeric](13, 3) NOT NULL ,
[I_Week_Number] [int] NULL ,
[I_Period_Number] [int] NULL ,
[I_Transaction_Type] [smallint] NULL ,
[I_Transaction_Date] [int] NULL ,
[I_Customer_Number] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[I_Delivery_Address_Code] [char] (3) COLLATE Latin1_General_CI_AS NULL
,
[I_Line_Cost_Value] [numeric](17, 2) NOT NULL ,
[I_Status] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Parent_Line_Number] [smallint] NULL ,
[I_Reason_Code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Print_Flag] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Analysis_Update_Flag] [char] (1) COLLATE Latin1_General_CI_AS
NULL ,
[I_Item_Type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Type] [int] NULL ,
[I_Month_Number] [tinyint] NOT NULL ,
[I_Year_Number] [smallint] NOT NULL ,
[I_Transaction_Date_PC] [datetime] NOT NULL ,
[I_Gross_Margin] [numeric](17, 2) NULL ,
[I_Date_Downloaded] [datetime] NULL ,
[Flagged_for_Exception] [bit] NULL
) ON [PRIMARY]
GO

CREATE VIEW dbo.vw_Invoices_I
AS
SELECT dbo.vw_Item_Details_ID.*, dbo.tbl_Invoices_I_DB_TEST.*,
dbo.vw_Customer_Details.*
FROM dbo.tbl_Invoices_I_DB_TEST INNER JOIN
dbo.vw_Customer_Details ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Customer_Details.CD_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Customer_Number =
dbo.vw_Customer_Details.CD_Customer_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Delivery_Address_Code
= dbo.vw_Customer_Details.CD_Dseq INNER JOIN
dbo.vw_Item_Details_ID ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Item_Details_ID.ID_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Item_Code =
dbo.vw_Item_Details_ID.ID_Item_Code

CREATE VIEW dbo.vw_Item_Details_ID
AS
SELECT dbo.tbl_Item_Details_ID.ID_Company_Number,
dbo.tbl_Item_Details_ID.ID_Item_Code,
dbo.tbl_Item_Details_ID.ID_Description,
dbo.tbl_Item_Details_ID.ID_STD_Cost,
dbo.tbl_Item_Details_ID.ID_Product_Type,
dbo.tbl_ILU_Z_PTYP.Z_Product_Type_Description,
dbo.tbl_Item_Details_ID.ID_Company,
dbo.tbl_ILU_A_COMP.A_Company_Description,
dbo.tbl_Item_Details_ID.ID_Brand_Type,
dbo.tbl_ILU_B_BTYP.B_Brand_Type_Description,
dbo.tbl_Item_Details_ID.ID_Brand,
dbo.tbl_ILU_C_BRND.C_Brand_Description,
dbo.tbl_Item_Details_ID.ID_Range,
dbo.tbl_ILU_D_RANG.D_Range_Description, dbo.tbl_Item_Details_ID.ID_Item,
dbo.tbl_ILU_E_ITEM.E_Item_Description,
dbo.tbl_Item_Details_ID.ID_Function_Description,
dbo.tbl_ILU_F_DESC.F_Description_Description,
dbo.tbl_Item_Details_ID.ID_Det_Fuel,
dbo.tbl_ILU_G_DET_FUEL.G_Det_Fuel_Description,
dbo.tbl_Item_Details_ID.ID_Colour,
dbo.tbl_ILU_H_COLR.H_Colour_Description,
dbo.tbl_Item_Details_ID.ID_TypeCat,
dbo.tbl_ILU_I_TYPECAT.I_TypeCat_Description,
dbo.tbl_Item_Details_ID.ID_DetFunc,
dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_Description,
dbo.tbl_Item_Details_ID.ID_Function,
dbo.tbl_ILU_K_FCTN.K_Function_Description,
dbo.tbl_Item_Details_ID.ID_Owner,
dbo.tbl_ILU_L_OWNR.L_Owner_Description,
dbo.tbl_Item_Details_ID.ID_Application,
dbo.tbl_ILU_M_APPL.M_Application_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Group,
dbo.tbl_ILU_O_PLANG.O_Planning_Group_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group,

dbo.tbl_ILU_P_PLNSG.P_Planning_Sub_Group_Descripti on,
dbo.tbl_Item_Details_ID.ID_Product_Group,
dbo.tbl_ILU_O_GROP.O_PoductGroup_Description,
dbo.tbl_Item_Details_ID.ID_Top_Fuel,
dbo.tbl_ILU_N_TFUL.N_TopFuel_Description,
dbo.tbl_Item_Details_ID.ID_Date_Last_Manufactured,
dbo.tbl_Item_Details_ID.ID_Model, dbo.tbl_ILU_P_MODL.P_Model_Description
FROM dbo.tbl_Item_Details_ID INNER JOIN
dbo.tbl_ILU_P_MODL ON
dbo.tbl_Item_Details_ID.ID_Model = dbo.tbl_ILU_P_MODL.P_MODL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_A_COMP ON
dbo.tbl_Item_Details_ID.ID_Company = dbo.tbl_ILU_A_COMP.A_COMP_ID LEFT
OUTER JOIN
dbo.tbl_ILU_I_TYPECAT ON
dbo.tbl_Item_Details_ID.ID_TypeCat = dbo.tbl_ILU_I_TYPECAT.I_TYPECAT_ID
LEFT OUTER JOIN
dbo.tbl_ILU_E_ITEM ON
dbo.tbl_Item_Details_ID.ID_Item = dbo.tbl_ILU_E_ITEM.E_ITEM_ID LEFT
OUTER JOIN
dbo.tbl_ILU_G_DET_FUEL ON
dbo.tbl_Item_Details_ID.ID_Det_Fuel =
dbo.tbl_ILU_G_DET_FUEL.G_DET_FUEL_ID LEFT OUTER JOIN
dbo.tbl_ILU_J_DETFUNC ON
dbo.tbl_Item_Details_ID.ID_DetFunc = dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_ID
LEFT OUTER JOIN
dbo.tbl_ILU_N_TFUL ON
dbo.tbl_Item_Details_ID.ID_Top_Fuel = dbo.tbl_ILU_N_TFUL.N_TFUL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_O_GROP ON
dbo.tbl_Item_Details_ID.ID_Product_Group = dbo.tbl_ILU_O_GROP.O_GROP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_C_BRND ON
dbo.tbl_Item_Details_ID.ID_Brand = dbo.tbl_ILU_C_BRND.C_BRND_ID LEFT
OUTER JOIN
dbo.tbl_ILU_K_FCTN ON
dbo.tbl_Item_Details_ID.ID_Function = dbo.tbl_ILU_K_FCTN.K_FCTN_ID LEFT
OUTER JOIN
dbo.tbl_ILU_M_APPL ON
dbo.tbl_Item_Details_ID.ID_Application = dbo.tbl_ILU_M_APPL.M_APPL_ID
LEFT OUTER JOIN
dbo.tbl_ILU_P_PLNSG ON
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group =
dbo.tbl_ILU_P_PLNSG.P_PLNSG_ID LEFT OUTER JOIN
dbo.tbl_ILU_O_PLANG ON
dbo.tbl_Item_Details_ID.ID_Planning_Group =
dbo.tbl_ILU_O_PLANG.O_PLANG_ID LEFT OUTER JOIN
dbo.tbl_ILU_L_OWNR ON
dbo.tbl_Item_Details_ID.ID_Owner = dbo.tbl_ILU_L_OWNR.L_OWNR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_H_COLR ON
dbo.tbl_Item_Details_ID.ID_Colour = dbo.tbl_ILU_H_COLR.H_COLR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_F_DESC ON
dbo.tbl_Item_Details_ID.ID_Function_Description =
dbo.tbl_ILU_F_DESC.F_DESC_ID LEFT OUTER JOIN
dbo.tbl_ILU_D_RANG ON
dbo.tbl_Item_Details_ID.ID_Range = dbo.tbl_ILU_D_RANG.D_RANG_ID LEFT
OUTER JOIN
dbo.tbl_ILU_B_BTYP ON
dbo.tbl_Item_Details_ID.ID_Brand_Type = dbo.tbl_ILU_B_BTYP.B_BTYP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_Z_PTYP ON
dbo.tbl_Item_Details_ID.ID_Product_Type = dbo.tbl_ILU_Z_PTYP.Z_PTYP_ID

CREATE VIEW dbo.vw_Customer_Details
AS
SELECT dbo.tbl_Customer_Details_CD.CD_Company_Number,
dbo.tbl_Customer_Details_CD.CD_Customer_Number,
dbo.tbl_Customer_Details_CD.CD_Dseq,
dbo.tbl_Customer_Details_CD.CD_Customer_Name,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_4,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_5,
dbo.tbl_Customer_Details_CD.CD_Post_Code_1,
dbo.tbl_Customer_Details_CD.CD_Post_Code_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_4,
dbo.tbl_Customer_Details_CD.CD_Region,
dbo.tbl_Customer_Details_CD.CD_Credit_Limit,
dbo.tbl_Customer_Details_CD.CD_Customer_Contact,
dbo.tbl_Customer_Details_CD.CD_Phone_Number,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened ,

dbo.tbl_Customer_Details_CD.CD_Bank_Account_Number ,
dbo.tbl_Customer_Details_CD.CD_Bank_Account_Name,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_1,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_2,
dbo.tbl_Customer_Details_CD.CD_Credit_Controller,
dbo.tbl_Credit_Controller.CC_Description,
dbo.tbl_Customer_Details_CD.CD_Customer_Group,

dbo.tbl_CLU_Z_CGT.Z_Customer_Group_Type_Descriptio n,
dbo.tbl_Customer_Details_CD.CD_Customer_Parent,

dbo.tbl_CLU_Y_CPT.Y_Customer_Parent_Type_Descripti on,
dbo.tbl_Customer_Details_CD.CD_Sales_Region,
dbo.tbl_CLU_A_SRGN.A_Sales_Region_Description,
dbo.tbl_Customer_Details_CD.CD_Business_Type,
dbo.tbl_CLU_B_BTYP.B_Business_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l,

dbo.tbl_CLU_C_DCNL.C_Distribution_Channel_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r,

dbo.tbl_CLU_D_DCSR.D_Distribution_Cluster_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Delivery_Type,
dbo.tbl_CLU_E_DTYP.E_Delivery_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Marketing_Director,

dbo.tbl_CLU_F_MDR.F_Marketing_Director_Resposibili ty_Description,
dbo.tbl_Customer_Details_CD.CD_Sales_Director,

dbo.tbl_CLU_G_SDR.G_Sales_Director_Responsibility_ Description,
dbo.tbl_Customer_Details_CD.CD_Account_Manager,

dbo.tbl_CLU_H_AMR.H_Account_Manager_Responsibility _Description,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened _PC,

dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er,
dbo.tbl_CLU_I_CNSL.I_Consolidated_Customer_Descrip tion
FROM dbo.tbl_Customer_Details_CD LEFT OUTER JOIN
dbo.tbl_CLU_I_CNSL ON
dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er =
dbo.tbl_CLU_I_CNSL.I_CNSL_ID LEFT OUTER JOIN
dbo.tbl_Credit_Controller ON
dbo.tbl_Customer_Details_CD.CD_Credit_Controller =
dbo.tbl_Credit_Controller.CC_ID LEFT OUTER JOIN
dbo.tbl_CLU_H_AMR ON
dbo.tbl_Customer_Details_CD.CD_Account_Manager =
dbo.tbl_CLU_H_AMR.H_AMR_ID LEFT OUTER JOIN
dbo.tbl_CLU_G_SDR ON
dbo.tbl_Customer_Details_CD.CD_Sales_Director =
dbo.tbl_CLU_G_SDR.G_SDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_F_MDR ON
dbo.tbl_Customer_Details_CD.CD_Marketing_Director =
dbo.tbl_CLU_F_MDR.F_MDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_E_DTYP ON
dbo.tbl_Customer_Details_CD.CD_Delivery_Type =
dbo.tbl_CLU_E_DTYP.E_DTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_D_DCSR ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r =
dbo.tbl_CLU_D_DCSR.D_DCSR_ID LEFT OUTER JOIN
dbo.tbl_CLU_C_DCNL ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l =
dbo.tbl_CLU_C_DCNL.C_DCNL_ID LEFT OUTER JOIN
dbo.tbl_CLU_B_BTYP ON
dbo.tbl_Customer_Details_CD.CD_Business_Type =
dbo.tbl_CLU_B_BTYP.B_BTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_A_SRGN ON
dbo.tbl_Customer_Details_CD.CD_Sales_Region =
dbo.tbl_CLU_A_SRGN.A_SRGN_ID LEFT OUTER JOIN
dbo.tbl_CLU_Y_CPT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Parent =
dbo.tbl_CLU_Y_CPT.Y_CPT_ID LEFT OUTER JOIN
dbo.tbl_CLU_Z_CGT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Group =
dbo.tbl_CLU_Z_CGT.Z_CGT_ID

The above should give you some idea of the data, I haven't include the
scripts for the base tables as this would take considerable time

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi

It is better not to specify * in the view definition especially in
production code. If you insist on doing it I would recomend using the WITH
SCHEMABINDING attribute. You will probably find that dbo.vw_Invoices_I is
not returning the correct data as you have changed the underlying tables.

Look at sp_refreshview in Books online
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_sp_ra-rz_7qnr.htm

John

"John Dennison" <john.dennison@.glendimplex.com> wrote in message
news:407fb2aa$0$204$75868355@.news.frii.net...
> Yeah it was just a quick rehash of the view, limiting the information
> for demo purposes the actual view(vw_test) is below:
> CREATE VIEW dbo.vw_test
> AS
> SELECT dbo.vw_Invoices_I.ID_Company_Number,
> dbo.vw_Invoices_I.ID_Item_Code, dbo.vw_Invoices_I.I_Order_Number,
> dbo.vw_Invoices_I.I_Order_Line_Number,
> dbo.vw_Invoices_I.I_Invoice_Number, dbo.vw_Invoices_I.I_Customer_Number,
> dbo.vw_Invoices_I.I_Delivery_Address_Code,
> dbo.vw_Invoices_I.I_Line_Value *
> dbo.tbl_Lagged_Sales.Percentage_Of_Sales AS Expr1,
> dbo.vw_Invoices_I.I_VAT_Value,
> dbo.vw_Invoices_I.I_Discount_Value, dbo.vw_Invoices_I.I_Standard_Cost,
> dbo.vw_Invoices_I.I_Line_Cost_Value
> FROM dbo.tbl_Lagged_Sales INNER JOIN
> dbo.vw_Invoices_I ON
> dbo.tbl_Lagged_Sales.Supplier_Code = dbo.vw_Invoices_I.ID_Company AND
> dbo.tbl_Lagged_Sales.Lag_Product_Group =
> dbo.vw_Invoices_I.ID_Product_GroupHi
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tbl_Invoices_I_DB_TEST]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tbl_Invoices_I_DB_TEST]
> GO
> CREATE TABLE [dbo].[tbl_Invoices_I_DB_TEST] (
> [I_Company_Number] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Order_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Order_Line_Number] [smallint] NOT NULL ,
> [I_Invoice_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Disp_Seq_No] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
> [I_Pack_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
> [I_Stockroom] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [I_Line_Value] [numeric](17, 2) NOT NULL ,
> [I_VAT_Value] [numeric](17, 2) NULL ,
> [I_Discount_Value] [numeric](17, 2) NULL ,
> [I_Standard_Cost] [numeric](17, 2) NULL ,
> [I_Line_Quantity] [numeric](13, 3) NOT NULL ,
> [I_Week_Number] [int] NULL ,
> [I_Period_Number] [int] NULL ,
> [I_Transaction_Type] [smallint] NULL ,
> [I_Transaction_Date] [int] NULL ,
> [I_Customer_Number] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
> [I_Delivery_Address_Code] [char] (3) COLLATE Latin1_General_CI_AS NULL
> ,
> [I_Line_Cost_Value] [numeric](17, 2) NOT NULL ,
> [I_Status] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Parent_Line_Number] [smallint] NULL ,
> [I_Reason_Code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Print_Flag] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Sales_Analysis_Update_Flag] [char] (1) COLLATE Latin1_General_CI_AS
> NULL ,
> [I_Item_Type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Sales_Type] [int] NULL ,
> [I_Month_Number] [tinyint] NOT NULL ,
> [I_Year_Number] [smallint] NOT NULL ,
> [I_Transaction_Date_PC] [datetime] NOT NULL ,
> [I_Gross_Margin] [numeric](17, 2) NULL ,
> [I_Date_Downloaded] [datetime] NULL ,
> [Flagged_for_Exception] [bit] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE VIEW dbo.vw_Invoices_I
> AS
> SELECT dbo.vw_Item_Details_ID.*, dbo.tbl_Invoices_I_DB_TEST.*,
> dbo.vw_Customer_Details.*
> FROM dbo.tbl_Invoices_I_DB_TEST INNER JOIN
> dbo.vw_Customer_Details ON
> dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
> dbo.vw_Customer_Details.CD_Company_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Customer_Number =
> dbo.vw_Customer_Details.CD_Customer_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Delivery_Address_Code
> = dbo.vw_Customer_Details.CD_Dseq INNER JOIN
> dbo.vw_Item_Details_ID ON
> dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
> dbo.vw_Item_Details_ID.ID_Company_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Item_Code =
> dbo.vw_Item_Details_ID.ID_Item_Code
> CREATE VIEW dbo.vw_Item_Details_ID
> AS
> SELECT dbo.tbl_Item_Details_ID.ID_Company_Number,
> dbo.tbl_Item_Details_ID.ID_Item_Code,
> dbo.tbl_Item_Details_ID.ID_Description,
> dbo.tbl_Item_Details_ID.ID_STD_Cost,
> dbo.tbl_Item_Details_ID.ID_Product_Type,
> dbo.tbl_ILU_Z_PTYP.Z_Product_Type_Description,
> dbo.tbl_Item_Details_ID.ID_Company,
> dbo.tbl_ILU_A_COMP.A_Company_Description,
> dbo.tbl_Item_Details_ID.ID_Brand_Type,
> dbo.tbl_ILU_B_BTYP.B_Brand_Type_Description,
> dbo.tbl_Item_Details_ID.ID_Brand,
> dbo.tbl_ILU_C_BRND.C_Brand_Description,
> dbo.tbl_Item_Details_ID.ID_Range,
> dbo.tbl_ILU_D_RANG.D_Range_Description, dbo.tbl_Item_Details_ID.ID_Item,
> dbo.tbl_ILU_E_ITEM.E_Item_Description,
> dbo.tbl_Item_Details_ID.ID_Function_Description,
> dbo.tbl_ILU_F_DESC.F_Description_Description,
> dbo.tbl_Item_Details_ID.ID_Det_Fuel,
> dbo.tbl_ILU_G_DET_FUEL.G_Det_Fuel_Description,
> dbo.tbl_Item_Details_ID.ID_Colour,
> dbo.tbl_ILU_H_COLR.H_Colour_Description,
> dbo.tbl_Item_Details_ID.ID_TypeCat,
> dbo.tbl_ILU_I_TYPECAT.I_TypeCat_Description,
> dbo.tbl_Item_Details_ID.ID_DetFunc,
> dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_Description,
> dbo.tbl_Item_Details_ID.ID_Function,
> dbo.tbl_ILU_K_FCTN.K_Function_Description,
> dbo.tbl_Item_Details_ID.ID_Owner,
> dbo.tbl_ILU_L_OWNR.L_Owner_Description,
> dbo.tbl_Item_Details_ID.ID_Application,
> dbo.tbl_ILU_M_APPL.M_Application_Description,
> dbo.tbl_Item_Details_ID.ID_Planning_Group,
> dbo.tbl_ILU_O_PLANG.O_Planning_Group_Description,
> dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group,
> dbo.tbl_ILU_P_PLNSG.P_Planning_Sub_Group_Descripti on,
> dbo.tbl_Item_Details_ID.ID_Product_Group,
> dbo.tbl_ILU_O_GROP.O_PoductGroup_Description,
> dbo.tbl_Item_Details_ID.ID_Top_Fuel,
> dbo.tbl_ILU_N_TFUL.N_TopFuel_Description,
> dbo.tbl_Item_Details_ID.ID_Date_Last_Manufactured,
> dbo.tbl_Item_Details_ID.ID_Model, dbo.tbl_ILU_P_MODL.P_Model_Description
> FROM dbo.tbl_Item_Details_ID INNER JOIN
> dbo.tbl_ILU_P_MODL ON
> dbo.tbl_Item_Details_ID.ID_Model = dbo.tbl_ILU_P_MODL.P_MODL_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_A_COMP ON
> dbo.tbl_Item_Details_ID.ID_Company = dbo.tbl_ILU_A_COMP.A_COMP_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_I_TYPECAT ON
> dbo.tbl_Item_Details_ID.ID_TypeCat = dbo.tbl_ILU_I_TYPECAT.I_TYPECAT_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_E_ITEM ON
> dbo.tbl_Item_Details_ID.ID_Item = dbo.tbl_ILU_E_ITEM.E_ITEM_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_G_DET_FUEL ON
> dbo.tbl_Item_Details_ID.ID_Det_Fuel =
> dbo.tbl_ILU_G_DET_FUEL.G_DET_FUEL_ID LEFT OUTER JOIN
> dbo.tbl_ILU_J_DETFUNC ON
> dbo.tbl_Item_Details_ID.ID_DetFunc = dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_N_TFUL ON
> dbo.tbl_Item_Details_ID.ID_Top_Fuel = dbo.tbl_ILU_N_TFUL.N_TFUL_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_O_GROP ON
> dbo.tbl_Item_Details_ID.ID_Product_Group = dbo.tbl_ILU_O_GROP.O_GROP_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_C_BRND ON
> dbo.tbl_Item_Details_ID.ID_Brand = dbo.tbl_ILU_C_BRND.C_BRND_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_K_FCTN ON
> dbo.tbl_Item_Details_ID.ID_Function = dbo.tbl_ILU_K_FCTN.K_FCTN_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_M_APPL ON
> dbo.tbl_Item_Details_ID.ID_Application = dbo.tbl_ILU_M_APPL.M_APPL_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_P_PLNSG ON
> dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group =
> dbo.tbl_ILU_P_PLNSG.P_PLNSG_ID LEFT OUTER JOIN
> dbo.tbl_ILU_O_PLANG ON
> dbo.tbl_Item_Details_ID.ID_Planning_Group =
> dbo.tbl_ILU_O_PLANG.O_PLANG_ID LEFT OUTER JOIN
> dbo.tbl_ILU_L_OWNR ON
> dbo.tbl_Item_Details_ID.ID_Owner = dbo.tbl_ILU_L_OWNR.L_OWNR_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_H_COLR ON
> dbo.tbl_Item_Details_ID.ID_Colour = dbo.tbl_ILU_H_COLR.H_COLR_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_F_DESC ON
> dbo.tbl_Item_Details_ID.ID_Function_Description =
> dbo.tbl_ILU_F_DESC.F_DESC_ID LEFT OUTER JOIN
> dbo.tbl_ILU_D_RANG ON
> dbo.tbl_Item_Details_ID.ID_Range = dbo.tbl_ILU_D_RANG.D_RANG_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_B_BTYP ON
> dbo.tbl_Item_Details_ID.ID_Brand_Type = dbo.tbl_ILU_B_BTYP.B_BTYP_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_Z_PTYP ON
> dbo.tbl_Item_Details_ID.ID_Product_Type = dbo.tbl_ILU_Z_PTYP.Z_PTYP_ID
> CREATE VIEW dbo.vw_Customer_Details
> AS
> SELECT dbo.tbl_Customer_Details_CD.CD_Company_Number,
> dbo.tbl_Customer_Details_CD.CD_Customer_Number,
> dbo.tbl_Customer_Details_CD.CD_Dseq,
> dbo.tbl_Customer_Details_CD.CD_Customer_Name,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_1,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_3,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_4,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_5,
> dbo.tbl_Customer_Details_CD.CD_Post_Code_1,
> dbo.tbl_Customer_Details_CD.CD_Post_Code_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_1,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_3,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_4,
> dbo.tbl_Customer_Details_CD.CD_Region,
> dbo.tbl_Customer_Details_CD.CD_Credit_Limit,
> dbo.tbl_Customer_Details_CD.CD_Customer_Contact,
> dbo.tbl_Customer_Details_CD.CD_Phone_Number,
> dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened ,
> dbo.tbl_Customer_Details_CD.CD_Bank_Account_Number ,
> dbo.tbl_Customer_Details_CD.CD_Bank_Account_Name,
> dbo.tbl_Customer_Details_CD.CD_Bank_Address_1,
> dbo.tbl_Customer_Details_CD.CD_Bank_Address_2,
> dbo.tbl_Customer_Details_CD.CD_Credit_Controller,
> dbo.tbl_Credit_Controller.CC_Description,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group,
> dbo.tbl_CLU_Z_CGT.Z_Customer_Group_Type_Descriptio n,
> dbo.tbl_Customer_Details_CD.CD_Customer_Parent,
> dbo.tbl_CLU_Y_CPT.Y_Customer_Parent_Type_Descripti on,
> dbo.tbl_Customer_Details_CD.CD_Sales_Region,
> dbo.tbl_CLU_A_SRGN.A_Sales_Region_Description,
> dbo.tbl_Customer_Details_CD.CD_Business_Type,
> dbo.tbl_CLU_B_BTYP.B_Business_Type_Description,
> dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l,
> dbo.tbl_CLU_C_DCNL.C_Distribution_Channel_Descript ion,
> dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r,
> dbo.tbl_CLU_D_DCSR.D_Distribution_Cluster_Descript ion,
> dbo.tbl_Customer_Details_CD.CD_Delivery_Type,
> dbo.tbl_CLU_E_DTYP.E_Delivery_Type_Description,
> dbo.tbl_Customer_Details_CD.CD_Marketing_Director,
> dbo.tbl_CLU_F_MDR.F_Marketing_Director_Resposibili ty_Description,
> dbo.tbl_Customer_Details_CD.CD_Sales_Director,
> dbo.tbl_CLU_G_SDR.G_Sales_Director_Responsibility_ Description,
> dbo.tbl_Customer_Details_CD.CD_Account_Manager,
> dbo.tbl_CLU_H_AMR.H_Account_Manager_Responsibility _Description,
> dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened _PC,
> dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er,
> dbo.tbl_CLU_I_CNSL.I_Consolidated_Customer_Descrip tion
> FROM dbo.tbl_Customer_Details_CD LEFT OUTER JOIN
> dbo.tbl_CLU_I_CNSL ON
> dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er =
> dbo.tbl_CLU_I_CNSL.I_CNSL_ID LEFT OUTER JOIN
> dbo.tbl_Credit_Controller ON
> dbo.tbl_Customer_Details_CD.CD_Credit_Controller =
> dbo.tbl_Credit_Controller.CC_ID LEFT OUTER JOIN
> dbo.tbl_CLU_H_AMR ON
> dbo.tbl_Customer_Details_CD.CD_Account_Manager =
> dbo.tbl_CLU_H_AMR.H_AMR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_G_SDR ON
> dbo.tbl_Customer_Details_CD.CD_Sales_Director =
> dbo.tbl_CLU_G_SDR.G_SDR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_F_MDR ON
> dbo.tbl_Customer_Details_CD.CD_Marketing_Director =
> dbo.tbl_CLU_F_MDR.F_MDR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_E_DTYP ON
> dbo.tbl_Customer_Details_CD.CD_Delivery_Type =
> dbo.tbl_CLU_E_DTYP.E_DTYP_ID LEFT OUTER JOIN
> dbo.tbl_CLU_D_DCSR ON
> dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r =
> dbo.tbl_CLU_D_DCSR.D_DCSR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_C_DCNL ON
> dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l =
> dbo.tbl_CLU_C_DCNL.C_DCNL_ID LEFT OUTER JOIN
> dbo.tbl_CLU_B_BTYP ON
> dbo.tbl_Customer_Details_CD.CD_Business_Type =
> dbo.tbl_CLU_B_BTYP.B_BTYP_ID LEFT OUTER JOIN
> dbo.tbl_CLU_A_SRGN ON
> dbo.tbl_Customer_Details_CD.CD_Sales_Region =
> dbo.tbl_CLU_A_SRGN.A_SRGN_ID LEFT OUTER JOIN
> dbo.tbl_CLU_Y_CPT ON
> dbo.tbl_Customer_Details_CD.CD_Customer_Parent =
> dbo.tbl_CLU_Y_CPT.Y_CPT_ID LEFT OUTER JOIN
> dbo.tbl_CLU_Z_CGT ON
> dbo.tbl_Customer_Details_CD.CD_Customer_Group =
> dbo.tbl_CLU_Z_CGT.Z_CGT_ID
>
> The above should give you some idea of the data, I haven't include the
> scripts for the base tables as this would take considerable time
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi

It is better not to specify * in the view definition especially in
production code. If you insist on doing it I would recomend using the WITH
SCHEMABINDING attribute. You will probably find that dbo.vw_Invoices_I is
not returning the correct data as you have changed the underlying tables.

Look at sp_refreshview in Books online
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_sp_ra-rz_7qnr.htm

John

"John Dennison" <john.dennison@.glendimplex.com> wrote in message
news:407fb2aa$0$204$75868355@.news.frii.net...
> Yeah it was just a quick rehash of the view, limiting the information
> for demo purposes the actual view(vw_test) is below:
> CREATE VIEW dbo.vw_test
> AS
> SELECT dbo.vw_Invoices_I.ID_Company_Number,
> dbo.vw_Invoices_I.ID_Item_Code, dbo.vw_Invoices_I.I_Order_Number,
> dbo.vw_Invoices_I.I_Order_Line_Number,
> dbo.vw_Invoices_I.I_Invoice_Number, dbo.vw_Invoices_I.I_Customer_Number,
> dbo.vw_Invoices_I.I_Delivery_Address_Code,
> dbo.vw_Invoices_I.I_Line_Value *
> dbo.tbl_Lagged_Sales.Percentage_Of_Sales AS Expr1,
> dbo.vw_Invoices_I.I_VAT_Value,
> dbo.vw_Invoices_I.I_Discount_Value, dbo.vw_Invoices_I.I_Standard_Cost,
> dbo.vw_Invoices_I.I_Line_Cost_Value
> FROM dbo.tbl_Lagged_Sales INNER JOIN
> dbo.vw_Invoices_I ON
> dbo.tbl_Lagged_Sales.Supplier_Code = dbo.vw_Invoices_I.ID_Company AND
> dbo.tbl_Lagged_Sales.Lag_Product_Group =
> dbo.vw_Invoices_I.ID_Product_GroupHi
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tbl_Invoices_I_DB_TEST]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tbl_Invoices_I_DB_TEST]
> GO
> CREATE TABLE [dbo].[tbl_Invoices_I_DB_TEST] (
> [I_Company_Number] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Order_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Order_Line_Number] [smallint] NOT NULL ,
> [I_Invoice_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Disp_Seq_No] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
> [I_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
> [I_Pack_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
> [I_Stockroom] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
> [I_Line_Value] [numeric](17, 2) NOT NULL ,
> [I_VAT_Value] [numeric](17, 2) NULL ,
> [I_Discount_Value] [numeric](17, 2) NULL ,
> [I_Standard_Cost] [numeric](17, 2) NULL ,
> [I_Line_Quantity] [numeric](13, 3) NOT NULL ,
> [I_Week_Number] [int] NULL ,
> [I_Period_Number] [int] NULL ,
> [I_Transaction_Type] [smallint] NULL ,
> [I_Transaction_Date] [int] NULL ,
> [I_Customer_Number] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
> [I_Delivery_Address_Code] [char] (3) COLLATE Latin1_General_CI_AS NULL
> ,
> [I_Line_Cost_Value] [numeric](17, 2) NOT NULL ,
> [I_Status] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Parent_Line_Number] [smallint] NULL ,
> [I_Reason_Code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Print_Flag] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Sales_Analysis_Update_Flag] [char] (1) COLLATE Latin1_General_CI_AS
> NULL ,
> [I_Item_Type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
> [I_Sales_Type] [int] NULL ,
> [I_Month_Number] [tinyint] NOT NULL ,
> [I_Year_Number] [smallint] NOT NULL ,
> [I_Transaction_Date_PC] [datetime] NOT NULL ,
> [I_Gross_Margin] [numeric](17, 2) NULL ,
> [I_Date_Downloaded] [datetime] NULL ,
> [Flagged_for_Exception] [bit] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE VIEW dbo.vw_Invoices_I
> AS
> SELECT dbo.vw_Item_Details_ID.*, dbo.tbl_Invoices_I_DB_TEST.*,
> dbo.vw_Customer_Details.*
> FROM dbo.tbl_Invoices_I_DB_TEST INNER JOIN
> dbo.vw_Customer_Details ON
> dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
> dbo.vw_Customer_Details.CD_Company_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Customer_Number =
> dbo.vw_Customer_Details.CD_Customer_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Delivery_Address_Code
> = dbo.vw_Customer_Details.CD_Dseq INNER JOIN
> dbo.vw_Item_Details_ID ON
> dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
> dbo.vw_Item_Details_ID.ID_Company_Number AND
> dbo.tbl_Invoices_I_DB_TEST.I_Item_Code =
> dbo.vw_Item_Details_ID.ID_Item_Code
> CREATE VIEW dbo.vw_Item_Details_ID
> AS
> SELECT dbo.tbl_Item_Details_ID.ID_Company_Number,
> dbo.tbl_Item_Details_ID.ID_Item_Code,
> dbo.tbl_Item_Details_ID.ID_Description,
> dbo.tbl_Item_Details_ID.ID_STD_Cost,
> dbo.tbl_Item_Details_ID.ID_Product_Type,
> dbo.tbl_ILU_Z_PTYP.Z_Product_Type_Description,
> dbo.tbl_Item_Details_ID.ID_Company,
> dbo.tbl_ILU_A_COMP.A_Company_Description,
> dbo.tbl_Item_Details_ID.ID_Brand_Type,
> dbo.tbl_ILU_B_BTYP.B_Brand_Type_Description,
> dbo.tbl_Item_Details_ID.ID_Brand,
> dbo.tbl_ILU_C_BRND.C_Brand_Description,
> dbo.tbl_Item_Details_ID.ID_Range,
> dbo.tbl_ILU_D_RANG.D_Range_Description, dbo.tbl_Item_Details_ID.ID_Item,
> dbo.tbl_ILU_E_ITEM.E_Item_Description,
> dbo.tbl_Item_Details_ID.ID_Function_Description,
> dbo.tbl_ILU_F_DESC.F_Description_Description,
> dbo.tbl_Item_Details_ID.ID_Det_Fuel,
> dbo.tbl_ILU_G_DET_FUEL.G_Det_Fuel_Description,
> dbo.tbl_Item_Details_ID.ID_Colour,
> dbo.tbl_ILU_H_COLR.H_Colour_Description,
> dbo.tbl_Item_Details_ID.ID_TypeCat,
> dbo.tbl_ILU_I_TYPECAT.I_TypeCat_Description,
> dbo.tbl_Item_Details_ID.ID_DetFunc,
> dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_Description,
> dbo.tbl_Item_Details_ID.ID_Function,
> dbo.tbl_ILU_K_FCTN.K_Function_Description,
> dbo.tbl_Item_Details_ID.ID_Owner,
> dbo.tbl_ILU_L_OWNR.L_Owner_Description,
> dbo.tbl_Item_Details_ID.ID_Application,
> dbo.tbl_ILU_M_APPL.M_Application_Description,
> dbo.tbl_Item_Details_ID.ID_Planning_Group,
> dbo.tbl_ILU_O_PLANG.O_Planning_Group_Description,
> dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group,
> dbo.tbl_ILU_P_PLNSG.P_Planning_Sub_Group_Descripti on,
> dbo.tbl_Item_Details_ID.ID_Product_Group,
> dbo.tbl_ILU_O_GROP.O_PoductGroup_Description,
> dbo.tbl_Item_Details_ID.ID_Top_Fuel,
> dbo.tbl_ILU_N_TFUL.N_TopFuel_Description,
> dbo.tbl_Item_Details_ID.ID_Date_Last_Manufactured,
> dbo.tbl_Item_Details_ID.ID_Model, dbo.tbl_ILU_P_MODL.P_Model_Description
> FROM dbo.tbl_Item_Details_ID INNER JOIN
> dbo.tbl_ILU_P_MODL ON
> dbo.tbl_Item_Details_ID.ID_Model = dbo.tbl_ILU_P_MODL.P_MODL_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_A_COMP ON
> dbo.tbl_Item_Details_ID.ID_Company = dbo.tbl_ILU_A_COMP.A_COMP_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_I_TYPECAT ON
> dbo.tbl_Item_Details_ID.ID_TypeCat = dbo.tbl_ILU_I_TYPECAT.I_TYPECAT_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_E_ITEM ON
> dbo.tbl_Item_Details_ID.ID_Item = dbo.tbl_ILU_E_ITEM.E_ITEM_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_G_DET_FUEL ON
> dbo.tbl_Item_Details_ID.ID_Det_Fuel =
> dbo.tbl_ILU_G_DET_FUEL.G_DET_FUEL_ID LEFT OUTER JOIN
> dbo.tbl_ILU_J_DETFUNC ON
> dbo.tbl_Item_Details_ID.ID_DetFunc = dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_N_TFUL ON
> dbo.tbl_Item_Details_ID.ID_Top_Fuel = dbo.tbl_ILU_N_TFUL.N_TFUL_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_O_GROP ON
> dbo.tbl_Item_Details_ID.ID_Product_Group = dbo.tbl_ILU_O_GROP.O_GROP_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_C_BRND ON
> dbo.tbl_Item_Details_ID.ID_Brand = dbo.tbl_ILU_C_BRND.C_BRND_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_K_FCTN ON
> dbo.tbl_Item_Details_ID.ID_Function = dbo.tbl_ILU_K_FCTN.K_FCTN_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_M_APPL ON
> dbo.tbl_Item_Details_ID.ID_Application = dbo.tbl_ILU_M_APPL.M_APPL_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_P_PLNSG ON
> dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group =
> dbo.tbl_ILU_P_PLNSG.P_PLNSG_ID LEFT OUTER JOIN
> dbo.tbl_ILU_O_PLANG ON
> dbo.tbl_Item_Details_ID.ID_Planning_Group =
> dbo.tbl_ILU_O_PLANG.O_PLANG_ID LEFT OUTER JOIN
> dbo.tbl_ILU_L_OWNR ON
> dbo.tbl_Item_Details_ID.ID_Owner = dbo.tbl_ILU_L_OWNR.L_OWNR_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_H_COLR ON
> dbo.tbl_Item_Details_ID.ID_Colour = dbo.tbl_ILU_H_COLR.H_COLR_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_F_DESC ON
> dbo.tbl_Item_Details_ID.ID_Function_Description =
> dbo.tbl_ILU_F_DESC.F_DESC_ID LEFT OUTER JOIN
> dbo.tbl_ILU_D_RANG ON
> dbo.tbl_Item_Details_ID.ID_Range = dbo.tbl_ILU_D_RANG.D_RANG_ID LEFT
> OUTER JOIN
> dbo.tbl_ILU_B_BTYP ON
> dbo.tbl_Item_Details_ID.ID_Brand_Type = dbo.tbl_ILU_B_BTYP.B_BTYP_ID
> LEFT OUTER JOIN
> dbo.tbl_ILU_Z_PTYP ON
> dbo.tbl_Item_Details_ID.ID_Product_Type = dbo.tbl_ILU_Z_PTYP.Z_PTYP_ID
> CREATE VIEW dbo.vw_Customer_Details
> AS
> SELECT dbo.tbl_Customer_Details_CD.CD_Company_Number,
> dbo.tbl_Customer_Details_CD.CD_Customer_Number,
> dbo.tbl_Customer_Details_CD.CD_Dseq,
> dbo.tbl_Customer_Details_CD.CD_Customer_Name,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_1,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_3,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_4,
> dbo.tbl_Customer_Details_CD.CD_Customer_Address_5,
> dbo.tbl_Customer_Details_CD.CD_Post_Code_1,
> dbo.tbl_Customer_Details_CD.CD_Post_Code_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_1,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_2,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_3,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group_4,
> dbo.tbl_Customer_Details_CD.CD_Region,
> dbo.tbl_Customer_Details_CD.CD_Credit_Limit,
> dbo.tbl_Customer_Details_CD.CD_Customer_Contact,
> dbo.tbl_Customer_Details_CD.CD_Phone_Number,
> dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened ,
> dbo.tbl_Customer_Details_CD.CD_Bank_Account_Number ,
> dbo.tbl_Customer_Details_CD.CD_Bank_Account_Name,
> dbo.tbl_Customer_Details_CD.CD_Bank_Address_1,
> dbo.tbl_Customer_Details_CD.CD_Bank_Address_2,
> dbo.tbl_Customer_Details_CD.CD_Credit_Controller,
> dbo.tbl_Credit_Controller.CC_Description,
> dbo.tbl_Customer_Details_CD.CD_Customer_Group,
> dbo.tbl_CLU_Z_CGT.Z_Customer_Group_Type_Descriptio n,
> dbo.tbl_Customer_Details_CD.CD_Customer_Parent,
> dbo.tbl_CLU_Y_CPT.Y_Customer_Parent_Type_Descripti on,
> dbo.tbl_Customer_Details_CD.CD_Sales_Region,
> dbo.tbl_CLU_A_SRGN.A_Sales_Region_Description,
> dbo.tbl_Customer_Details_CD.CD_Business_Type,
> dbo.tbl_CLU_B_BTYP.B_Business_Type_Description,
> dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l,
> dbo.tbl_CLU_C_DCNL.C_Distribution_Channel_Descript ion,
> dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r,
> dbo.tbl_CLU_D_DCSR.D_Distribution_Cluster_Descript ion,
> dbo.tbl_Customer_Details_CD.CD_Delivery_Type,
> dbo.tbl_CLU_E_DTYP.E_Delivery_Type_Description,
> dbo.tbl_Customer_Details_CD.CD_Marketing_Director,
> dbo.tbl_CLU_F_MDR.F_Marketing_Director_Resposibili ty_Description,
> dbo.tbl_Customer_Details_CD.CD_Sales_Director,
> dbo.tbl_CLU_G_SDR.G_Sales_Director_Responsibility_ Description,
> dbo.tbl_Customer_Details_CD.CD_Account_Manager,
> dbo.tbl_CLU_H_AMR.H_Account_Manager_Responsibility _Description,
> dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened _PC,
> dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er,
> dbo.tbl_CLU_I_CNSL.I_Consolidated_Customer_Descrip tion
> FROM dbo.tbl_Customer_Details_CD LEFT OUTER JOIN
> dbo.tbl_CLU_I_CNSL ON
> dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er =
> dbo.tbl_CLU_I_CNSL.I_CNSL_ID LEFT OUTER JOIN
> dbo.tbl_Credit_Controller ON
> dbo.tbl_Customer_Details_CD.CD_Credit_Controller =
> dbo.tbl_Credit_Controller.CC_ID LEFT OUTER JOIN
> dbo.tbl_CLU_H_AMR ON
> dbo.tbl_Customer_Details_CD.CD_Account_Manager =
> dbo.tbl_CLU_H_AMR.H_AMR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_G_SDR ON
> dbo.tbl_Customer_Details_CD.CD_Sales_Director =
> dbo.tbl_CLU_G_SDR.G_SDR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_F_MDR ON
> dbo.tbl_Customer_Details_CD.CD_Marketing_Director =
> dbo.tbl_CLU_F_MDR.F_MDR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_E_DTYP ON
> dbo.tbl_Customer_Details_CD.CD_Delivery_Type =
> dbo.tbl_CLU_E_DTYP.E_DTYP_ID LEFT OUTER JOIN
> dbo.tbl_CLU_D_DCSR ON
> dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r =
> dbo.tbl_CLU_D_DCSR.D_DCSR_ID LEFT OUTER JOIN
> dbo.tbl_CLU_C_DCNL ON
> dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l =
> dbo.tbl_CLU_C_DCNL.C_DCNL_ID LEFT OUTER JOIN
> dbo.tbl_CLU_B_BTYP ON
> dbo.tbl_Customer_Details_CD.CD_Business_Type =
> dbo.tbl_CLU_B_BTYP.B_BTYP_ID LEFT OUTER JOIN
> dbo.tbl_CLU_A_SRGN ON
> dbo.tbl_Customer_Details_CD.CD_Sales_Region =
> dbo.tbl_CLU_A_SRGN.A_SRGN_ID LEFT OUTER JOIN
> dbo.tbl_CLU_Y_CPT ON
> dbo.tbl_Customer_Details_CD.CD_Customer_Parent =
> dbo.tbl_CLU_Y_CPT.Y_CPT_ID LEFT OUTER JOIN
> dbo.tbl_CLU_Z_CGT ON
> dbo.tbl_Customer_Details_CD.CD_Customer_Group =
> dbo.tbl_CLU_Z_CGT.Z_CGT_ID
>
> The above should give you some idea of the data, I haven't include the
> scripts for the base tables as this would take considerable time
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Incorrect columns returned by view

When I create a view consisting of an inner join between a view and a
table the columns refernced in the the view are returned incorrectly,
example

select id.itemcode, id.description, iv.linevalue, iv.vatvalue from
dbo.tbl_itemdetails id inner join dbo.vw_invoices iv where
dbo.tbl_itemdetails.itemcode = dbo.vw_invoices.itemcode

The actual value that is returned from the view is the column
immediately to the left of the linevalue column, ie stockroomThe code you have posted isn't correct syntax, you've missed the ON clause.

One possibility for you: Could it be that you have some control characters
such as carriage return/line feed in one of the columns you are returning?
Special characters sometimes cause formatting problems when displaying data
in Query Analyzer.

If you need more help please post some code we can run that will reproduce
the problem: DDL (CREATE TABLE statement(s) for the table(s)), sample data
(INSERT statements) and the actual definition of the view.

--
David Portas
SQL Server MVP
--|||Yeah it was just a quick rehash of the view, limiting the information
for demo purposes the actual view(vw_test) is below:

CREATE VIEW dbo.vw_test
AS
SELECT dbo.vw_Invoices_I.ID_Company_Number,
dbo.vw_Invoices_I.ID_Item_Code, dbo.vw_Invoices_I.I_Order_Number,
dbo.vw_Invoices_I.I_Order_Line_Number,
dbo.vw_Invoices_I.I_Invoice_Number, dbo.vw_Invoices_I.I_Customer_Number,
dbo.vw_Invoices_I.I_Delivery_Address_Code,
dbo.vw_Invoices_I.I_Line_Value *
dbo.tbl_Lagged_Sales.Percentage_Of_Sales AS Expr1,
dbo.vw_Invoices_I.I_VAT_Value,
dbo.vw_Invoices_I.I_Discount_Value, dbo.vw_Invoices_I.I_Standard_Cost,
dbo.vw_Invoices_I.I_Line_Cost_Value
FROM dbo.tbl_Lagged_Sales INNER JOIN
dbo.vw_Invoices_I ON
dbo.tbl_Lagged_Sales.Supplier_Code = dbo.vw_Invoices_I.ID_Company AND
dbo.tbl_Lagged_Sales.Lag_Product_Group =
dbo.vw_Invoices_I.ID_Product_GroupHi

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tbl_Invoices_I_DB_TEST]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tbl_Invoices_I_DB_TEST]
GO

CREATE TABLE [dbo].[tbl_Invoices_I_DB_TEST] (
[I_Company_Number] [char] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Order_Line_Number] [smallint] NOT NULL ,
[I_Invoice_Number] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Disp_Seq_No] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[I_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Pack_Item_Code] [char] (15) COLLATE Latin1_General_CI_AS NULL ,
[I_Stockroom] [char] (2) COLLATE Latin1_General_CI_AS NULL ,
[I_Line_Value] [numeric](17, 2) NOT NULL ,
[I_VAT_Value] [numeric](17, 2) NULL ,
[I_Discount_Value] [numeric](17, 2) NULL ,
[I_Standard_Cost] [numeric](17, 2) NULL ,
[I_Line_Quantity] [numeric](13, 3) NOT NULL ,
[I_Week_Number] [int] NULL ,
[I_Period_Number] [int] NULL ,
[I_Transaction_Type] [smallint] NULL ,
[I_Transaction_Date] [int] NULL ,
[I_Customer_Number] [char] (8) COLLATE Latin1_General_CI_AS NULL ,
[I_Delivery_Address_Code] [char] (3) COLLATE Latin1_General_CI_AS NULL
,
[I_Line_Cost_Value] [numeric](17, 2) NOT NULL ,
[I_Status] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Parent_Line_Number] [smallint] NULL ,
[I_Reason_Code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Print_Flag] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Analysis_Update_Flag] [char] (1) COLLATE Latin1_General_CI_AS
NULL ,
[I_Item_Type] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[I_Sales_Type] [int] NULL ,
[I_Month_Number] [tinyint] NOT NULL ,
[I_Year_Number] [smallint] NOT NULL ,
[I_Transaction_Date_PC] [datetime] NOT NULL ,
[I_Gross_Margin] [numeric](17, 2) NULL ,
[I_Date_Downloaded] [datetime] NULL ,
[Flagged_for_Exception] [bit] NULL
) ON [PRIMARY]
GO

CREATE VIEW dbo.vw_Invoices_I
AS
SELECT dbo.vw_Item_Details_ID.*, dbo.tbl_Invoices_I_DB_TEST.*,
dbo.vw_Customer_Details.*
FROM dbo.tbl_Invoices_I_DB_TEST INNER JOIN
dbo.vw_Customer_Details ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Customer_Details.CD_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Customer_Number =
dbo.vw_Customer_Details.CD_Customer_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Delivery_Address_Code
= dbo.vw_Customer_Details.CD_Dseq INNER JOIN
dbo.vw_Item_Details_ID ON
dbo.tbl_Invoices_I_DB_TEST.I_Company_Number =
dbo.vw_Item_Details_ID.ID_Company_Number AND
dbo.tbl_Invoices_I_DB_TEST.I_Item_Code =
dbo.vw_Item_Details_ID.ID_Item_Code

CREATE VIEW dbo.vw_Item_Details_ID
AS
SELECT dbo.tbl_Item_Details_ID.ID_Company_Number,
dbo.tbl_Item_Details_ID.ID_Item_Code,
dbo.tbl_Item_Details_ID.ID_Description,
dbo.tbl_Item_Details_ID.ID_STD_Cost,
dbo.tbl_Item_Details_ID.ID_Product_Type,
dbo.tbl_ILU_Z_PTYP.Z_Product_Type_Description,
dbo.tbl_Item_Details_ID.ID_Company,
dbo.tbl_ILU_A_COMP.A_Company_Description,
dbo.tbl_Item_Details_ID.ID_Brand_Type,
dbo.tbl_ILU_B_BTYP.B_Brand_Type_Description,
dbo.tbl_Item_Details_ID.ID_Brand,
dbo.tbl_ILU_C_BRND.C_Brand_Description,
dbo.tbl_Item_Details_ID.ID_Range,
dbo.tbl_ILU_D_RANG.D_Range_Description, dbo.tbl_Item_Details_ID.ID_Item,
dbo.tbl_ILU_E_ITEM.E_Item_Description,
dbo.tbl_Item_Details_ID.ID_Function_Description,
dbo.tbl_ILU_F_DESC.F_Description_Description,
dbo.tbl_Item_Details_ID.ID_Det_Fuel,
dbo.tbl_ILU_G_DET_FUEL.G_Det_Fuel_Description,
dbo.tbl_Item_Details_ID.ID_Colour,
dbo.tbl_ILU_H_COLR.H_Colour_Description,
dbo.tbl_Item_Details_ID.ID_TypeCat,
dbo.tbl_ILU_I_TYPECAT.I_TypeCat_Description,
dbo.tbl_Item_Details_ID.ID_DetFunc,
dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_Description,
dbo.tbl_Item_Details_ID.ID_Function,
dbo.tbl_ILU_K_FCTN.K_Function_Description,
dbo.tbl_Item_Details_ID.ID_Owner,
dbo.tbl_ILU_L_OWNR.L_Owner_Description,
dbo.tbl_Item_Details_ID.ID_Application,
dbo.tbl_ILU_M_APPL.M_Application_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Group,
dbo.tbl_ILU_O_PLANG.O_Planning_Group_Description,
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group,

dbo.tbl_ILU_P_PLNSG.P_Planning_Sub_Group_Descripti on,
dbo.tbl_Item_Details_ID.ID_Product_Group,
dbo.tbl_ILU_O_GROP.O_PoductGroup_Description,
dbo.tbl_Item_Details_ID.ID_Top_Fuel,
dbo.tbl_ILU_N_TFUL.N_TopFuel_Description,
dbo.tbl_Item_Details_ID.ID_Date_Last_Manufactured,
dbo.tbl_Item_Details_ID.ID_Model, dbo.tbl_ILU_P_MODL.P_Model_Description
FROM dbo.tbl_Item_Details_ID INNER JOIN
dbo.tbl_ILU_P_MODL ON
dbo.tbl_Item_Details_ID.ID_Model = dbo.tbl_ILU_P_MODL.P_MODL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_A_COMP ON
dbo.tbl_Item_Details_ID.ID_Company = dbo.tbl_ILU_A_COMP.A_COMP_ID LEFT
OUTER JOIN
dbo.tbl_ILU_I_TYPECAT ON
dbo.tbl_Item_Details_ID.ID_TypeCat = dbo.tbl_ILU_I_TYPECAT.I_TYPECAT_ID
LEFT OUTER JOIN
dbo.tbl_ILU_E_ITEM ON
dbo.tbl_Item_Details_ID.ID_Item = dbo.tbl_ILU_E_ITEM.E_ITEM_ID LEFT
OUTER JOIN
dbo.tbl_ILU_G_DET_FUEL ON
dbo.tbl_Item_Details_ID.ID_Det_Fuel =
dbo.tbl_ILU_G_DET_FUEL.G_DET_FUEL_ID LEFT OUTER JOIN
dbo.tbl_ILU_J_DETFUNC ON
dbo.tbl_Item_Details_ID.ID_DetFunc = dbo.tbl_ILU_J_DETFUNC.J_DETFUNC_ID
LEFT OUTER JOIN
dbo.tbl_ILU_N_TFUL ON
dbo.tbl_Item_Details_ID.ID_Top_Fuel = dbo.tbl_ILU_N_TFUL.N_TFUL_ID LEFT
OUTER JOIN
dbo.tbl_ILU_O_GROP ON
dbo.tbl_Item_Details_ID.ID_Product_Group = dbo.tbl_ILU_O_GROP.O_GROP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_C_BRND ON
dbo.tbl_Item_Details_ID.ID_Brand = dbo.tbl_ILU_C_BRND.C_BRND_ID LEFT
OUTER JOIN
dbo.tbl_ILU_K_FCTN ON
dbo.tbl_Item_Details_ID.ID_Function = dbo.tbl_ILU_K_FCTN.K_FCTN_ID LEFT
OUTER JOIN
dbo.tbl_ILU_M_APPL ON
dbo.tbl_Item_Details_ID.ID_Application = dbo.tbl_ILU_M_APPL.M_APPL_ID
LEFT OUTER JOIN
dbo.tbl_ILU_P_PLNSG ON
dbo.tbl_Item_Details_ID.ID_Planning_Sub_Group =
dbo.tbl_ILU_P_PLNSG.P_PLNSG_ID LEFT OUTER JOIN
dbo.tbl_ILU_O_PLANG ON
dbo.tbl_Item_Details_ID.ID_Planning_Group =
dbo.tbl_ILU_O_PLANG.O_PLANG_ID LEFT OUTER JOIN
dbo.tbl_ILU_L_OWNR ON
dbo.tbl_Item_Details_ID.ID_Owner = dbo.tbl_ILU_L_OWNR.L_OWNR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_H_COLR ON
dbo.tbl_Item_Details_ID.ID_Colour = dbo.tbl_ILU_H_COLR.H_COLR_ID LEFT
OUTER JOIN
dbo.tbl_ILU_F_DESC ON
dbo.tbl_Item_Details_ID.ID_Function_Description =
dbo.tbl_ILU_F_DESC.F_DESC_ID LEFT OUTER JOIN
dbo.tbl_ILU_D_RANG ON
dbo.tbl_Item_Details_ID.ID_Range = dbo.tbl_ILU_D_RANG.D_RANG_ID LEFT
OUTER JOIN
dbo.tbl_ILU_B_BTYP ON
dbo.tbl_Item_Details_ID.ID_Brand_Type = dbo.tbl_ILU_B_BTYP.B_BTYP_ID
LEFT OUTER JOIN
dbo.tbl_ILU_Z_PTYP ON
dbo.tbl_Item_Details_ID.ID_Product_Type = dbo.tbl_ILU_Z_PTYP.Z_PTYP_ID

CREATE VIEW dbo.vw_Customer_Details
AS
SELECT dbo.tbl_Customer_Details_CD.CD_Company_Number,
dbo.tbl_Customer_Details_CD.CD_Customer_Number,
dbo.tbl_Customer_Details_CD.CD_Dseq,
dbo.tbl_Customer_Details_CD.CD_Customer_Name,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_4,
dbo.tbl_Customer_Details_CD.CD_Customer_Address_5,
dbo.tbl_Customer_Details_CD.CD_Post_Code_1,
dbo.tbl_Customer_Details_CD.CD_Post_Code_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_1,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_2,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_3,
dbo.tbl_Customer_Details_CD.CD_Customer_Group_4,
dbo.tbl_Customer_Details_CD.CD_Region,
dbo.tbl_Customer_Details_CD.CD_Credit_Limit,
dbo.tbl_Customer_Details_CD.CD_Customer_Contact,
dbo.tbl_Customer_Details_CD.CD_Phone_Number,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened ,

dbo.tbl_Customer_Details_CD.CD_Bank_Account_Number ,
dbo.tbl_Customer_Details_CD.CD_Bank_Account_Name,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_1,
dbo.tbl_Customer_Details_CD.CD_Bank_Address_2,
dbo.tbl_Customer_Details_CD.CD_Credit_Controller,
dbo.tbl_Credit_Controller.CC_Description,
dbo.tbl_Customer_Details_CD.CD_Customer_Group,

dbo.tbl_CLU_Z_CGT.Z_Customer_Group_Type_Descriptio n,
dbo.tbl_Customer_Details_CD.CD_Customer_Parent,

dbo.tbl_CLU_Y_CPT.Y_Customer_Parent_Type_Descripti on,
dbo.tbl_Customer_Details_CD.CD_Sales_Region,
dbo.tbl_CLU_A_SRGN.A_Sales_Region_Description,
dbo.tbl_Customer_Details_CD.CD_Business_Type,
dbo.tbl_CLU_B_BTYP.B_Business_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l,

dbo.tbl_CLU_C_DCNL.C_Distribution_Channel_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r,

dbo.tbl_CLU_D_DCSR.D_Distribution_Cluster_Descript ion,
dbo.tbl_Customer_Details_CD.CD_Delivery_Type,
dbo.tbl_CLU_E_DTYP.E_Delivery_Type_Description,
dbo.tbl_Customer_Details_CD.CD_Marketing_Director,

dbo.tbl_CLU_F_MDR.F_Marketing_Director_Resposibili ty_Description,
dbo.tbl_Customer_Details_CD.CD_Sales_Director,

dbo.tbl_CLU_G_SDR.G_Sales_Director_Responsibility_ Description,
dbo.tbl_Customer_Details_CD.CD_Account_Manager,

dbo.tbl_CLU_H_AMR.H_Account_Manager_Responsibility _Description,
dbo.tbl_Customer_Details_CD.CD_Date_Account_Opened _PC,

dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er,
dbo.tbl_CLU_I_CNSL.I_Consolidated_Customer_Descrip tion
FROM dbo.tbl_Customer_Details_CD LEFT OUTER JOIN
dbo.tbl_CLU_I_CNSL ON
dbo.tbl_Customer_Details_CD.CD_Consolidated_Custom er =
dbo.tbl_CLU_I_CNSL.I_CNSL_ID LEFT OUTER JOIN
dbo.tbl_Credit_Controller ON
dbo.tbl_Customer_Details_CD.CD_Credit_Controller =
dbo.tbl_Credit_Controller.CC_ID LEFT OUTER JOIN
dbo.tbl_CLU_H_AMR ON
dbo.tbl_Customer_Details_CD.CD_Account_Manager =
dbo.tbl_CLU_H_AMR.H_AMR_ID LEFT OUTER JOIN
dbo.tbl_CLU_G_SDR ON
dbo.tbl_Customer_Details_CD.CD_Sales_Director =
dbo.tbl_CLU_G_SDR.G_SDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_F_MDR ON
dbo.tbl_Customer_Details_CD.CD_Marketing_Director =
dbo.tbl_CLU_F_MDR.F_MDR_ID LEFT OUTER JOIN
dbo.tbl_CLU_E_DTYP ON
dbo.tbl_Customer_Details_CD.CD_Delivery_Type =
dbo.tbl_CLU_E_DTYP.E_DTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_D_DCSR ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Cluste r =
dbo.tbl_CLU_D_DCSR.D_DCSR_ID LEFT OUTER JOIN
dbo.tbl_CLU_C_DCNL ON
dbo.tbl_Customer_Details_CD.CD_Distribution_Channe l =
dbo.tbl_CLU_C_DCNL.C_DCNL_ID LEFT OUTER JOIN
dbo.tbl_CLU_B_BTYP ON
dbo.tbl_Customer_Details_CD.CD_Business_Type =
dbo.tbl_CLU_B_BTYP.B_BTYP_ID LEFT OUTER JOIN
dbo.tbl_CLU_A_SRGN ON
dbo.tbl_Customer_Details_CD.CD_Sales_Region =
dbo.tbl_CLU_A_SRGN.A_SRGN_ID LEFT OUTER JOIN
dbo.tbl_CLU_Y_CPT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Parent =
dbo.tbl_CLU_Y_CPT.Y_CPT_ID LEFT OUTER JOIN
dbo.tbl_CLU_Z_CGT ON
dbo.tbl_Customer_Details_CD.CD_Customer_Group =
dbo.tbl_CLU_Z_CGT.Z_CGT_ID

The above should give you some idea of the data, I haven't include the
scripts for the base tables as this would take considerable time

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!