Increase columns width of merge replicated table

We are using merge replication and it is working fine. We are looking for increase in on numeric column 10,5 instead of 7,2. How I can do it?
Any suggession highly appreciated by us.
ThanksI have only been able to accomplish this by copying out the data and the rowguid for each record, sp_repldropcolumn the old column, sp_repladdcolumn'ing it back in, and copying the data back in. (might not have sproc names exactly right)

I remember reading about a builtin sproc that you could execute that would run certain commands on a database or table that was in replication, but I can't find it's name, and I don't know it's limitations.

Incorrect UnPivot metadata

Hi

When using unpivot transformation, what exactly this error denote

"Incorrect UnPivot metadata. In an UnPivot transform, all input columns with a PivotKeyValue that is set, and are pointing to the same DestinationColumn, must have metadata that exactly matches "

data on which i was trying unpivoting is -

Name

Pd1

Pd2

Pd3

Pd4

Utsav

111

211

311

411

Verma

122

222

322

422

Nucleus

133

233

333

433

Noida

144

244

344

444

Assume in your scenario, "Name" is set as a pass-thru column, and all other 4 columns point to a same destination column

Then the error suggests that you don't have the same exact datatype on Pd1, Pd2, Pd3, Pd4 (they need to be all I4, or all I2...etc)

Thanks

Wenyang

Incorrect results from FTS on multiple columns

I'm writing a FTS query which needs to search on two different columns.

E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.

I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?

Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?

Shailesh Patel...

The KB article only applies to SQL Server 7.0 as indicated. If you are having problems with your implementation please post a repro script with some sample data and the expected results.

|||My database contains the following structure:
Table name: Resumes
Columns:
ResumeID bigint identity(1,1),
Title varchar(100),
ResumeText varchar(max),
Skills varchar(250)

The ResumeID is set as primary key and columns Title, ResumeText and Skills
are FT indexed.

A record contains "Software Developer .NET" in Title,
resume text which also contains word "india" in ResumeText and
"C#, ASP.NET and SQL Server" in Skills columns.

If I pass a query as ("software" AND "developer" AND "india" AND "asp.net") in CONTAINSTABLE or CONTAINS which looks for a resume which statisfies this condition.

The queries which I wrote search on all the column which are FT indexed as a
result of which even if I pass the above query it does not returns any result.

It seems that the FT query is working on each columns individually.
That's why if I pass ("software" AND "developer") in a query it locates the record.

Should I create another column and dump the data of
Title, ResumeText and Skills in it and perform a FTS on this new column?
or
Is there any other feasible method to solve this problem?

Incorrect query stats from full text engine

Hi
We have a table that is approx 800,000 rows with about 6 columns of which 3
are full text indexed.
This table is full text queried then the results are used to filter other
tables in a
"select * from maintable where category=.. and primaryid in (select key from
CONTAINSTABLE(fulltexttable, Keywords, 'FORMSOF(INFLECTIONAL, "glass")'))"
Every so often we get a slow perfoming query with these. Putting them into
Management Studio and asking for a query plan shows its the remote scan
consuming all the time. Hovering the mouse over the "Remote Scan" to get the
statistics gives wildly inaccurate estimates of number of rows, and then also
gives impossible number of "Actual Rows Returned" e.g. on this query I have
just done it has claims to have returned 13,350,965 rows - this from a table
that only has approx 800,000 rows.
The indexed columns may have duplicate words but checking the specific
example the searched for word definately appears less that 800,000 times in
the whole table.
Any one got any suggestions?
Thanks
Chris
Just to say, is MS SQL 2005 Standard Edition SP2 64 bit edition
|||The statistics returned my SQL FTS are not accurate as you have discovered.
You should work on tuning other parts of the query to remove spooling, etc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"chrisredburn" <chrisredburn@.discussions.microsoft.com> wrote in message
news:DC403EF1-81FC-42FE-BF36-AA8457E48755@.microsoft.com...
> Hi
> We have a table that is approx 800,000 rows with about 6 columns of which
> 3
> are full text indexed.
> This table is full text queried then the results are used to filter other
> tables in a
> "select * from maintable where category=.. and primaryid in (select key
> from
> CONTAINSTABLE(fulltexttable, Keywords, 'FORMSOF(INFLECTIONAL, "glass")'))"
> Every so often we get a slow perfoming query with these. Putting them
> into
> Management Studio and asking for a query plan shows its the remote scan
> consuming all the time. Hovering the mouse over the "Remote Scan" to get
> the
> statistics gives wildly inaccurate estimates of number of rows, and then
> also
> gives impossible number of "Actual Rows Returned" e.g. on this query I
> have
> just done it has claims to have returned 13,350,965 rows - this from a
> table
> that only has approx 800,000 rows.
> The indexed columns may have duplicate words but checking the specific
> example the searched for word definately appears less that 800,000 times
> in
> the whole table.
> Any one got any suggestions?
> Thanks
> Chris
|||In the execution plan returned for this query, the remote scan cost is 90% of
the query. The plan says that the next stage that the FTS gets passed to is
a filter that takes 1% and reduces the 13,000,000 down to 110 rows (the final
number of rows returned). The next largest part of the query, at 4%, is an
Index Seek. The rest is made up of 1 merge join, 2 inner joins and a few
filters, but as these are only working on a small (approx 600) number of rows
they aren't appearing to take any time up.
Surely if the statistics are wrong, then the query planneris going to start
making bad choices about how to plan the query?
"Hilary Cotter" wrote:

> The statistics returned my SQL FTS are not accurate as you have discovered.
> You should work on tuning other parts of the query to remove spooling, etc.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "chrisredburn" <chrisredburn@.discussions.microsoft.com> wrote in message
> news:DC403EF1-81FC-42FE-BF36-AA8457E48755@.microsoft.com...
>
>
|||A profiler trace of the query, showing SQL:BatchCompleted and
SQL:FullTextQuery events for the query, has...
SQL:FullTextQuey Duration = 164596
SQL:BatchCompleted Duration = 165274
|||I'd recommend look at a couple of 2 options:
1) use top_n_by_rank parameter if don't have to retrieve all of the results
but just a window (say top 100). The "n" value for the should be in this case
100*avg selectivity of the non-ft part (or actually smaller - the smaller,
the better)
2) If this is a plan issue, consider using OPTIMIZE FOR parameter to hint
the FT search string to CONTAINSTABLE.
3) make sure your index is not too fragmented; reorganization of an index
will improve card estimates. FTS cardinality estimation typically works ok
for single terms but worse for expressions.
Regards,
-Denis.
"chrisredburn" wrote:
[vbcol=seagreen]
> In the execution plan returned for this query, the remote scan cost is 90% of
> the query. The plan says that the next stage that the FTS gets passed to is
> a filter that takes 1% and reduces the 13,000,000 down to 110 rows (the final
> number of rows returned). The next largest part of the query, at 4%, is an
> Index Seek. The rest is made up of 1 merge join, 2 inner joins and a few
> filters, but as these are only working on a small (approx 600) number of rows
> they aren't appearing to take any time up.
> Surely if the statistics are wrong, then the query planneris going to start
> making bad choices about how to plan the query?
>
> "Hilary Cotter" wrote:

Incorrect Compatibility level Error 15414:

John,
I ran the indexed views query and indexed computed columns
query on the database that has the compatability level set
to 70. Only one table returned from the indexed computed
columns query.
Output from indexed computed columns query
Object_Name Column_Name
AccountDetail AcctDtlSrceTble
__________________________________________________________
DROP INDEX AccountDetail.AcctDtlSrceTble
How do I obtain a sql script to regenerate the
AccountDetail.AcctDtlSrceTble indexed computed column?
What are the steps that I need to take to change the
compatiblity level 80 to 70?
Thank You,
John
This suggests you are using some capabilities available in
SQL2000 that were
not previously available in down level versions. To check
for indexed views
or indexes on computed columns run the following query in
Query Analyzer.
These indexes will need to be dropped in order to
downgrade the database
compatability
-- indexed views
select name from sysobjects where xtype = 'V'
and objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsIndexed')=1
-- indexed computed columns
select object_name(c.id),c.name
from syscolumns c join sysindexkeys k
on c.colid = k.colid and c.id=k.id
where columnproperty(c.id,c.name,'IsComputed')=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsIndexed')=1
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in
message
news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
I have a SQL Server 2000 Enterprise Edition along with
SP3A with several databases. These databases are 20 GB in
size. On one of the databases the Compatibility level was
changed from 70 to 80. I need to change the compatiblity
level 80 to 70.
When tried to change the compatibilty level I received the
following error:
Error 15414: Cannot set compatibilty level because
database has a view or computed column is indexed. These
indexes require a SQL Server compatible database.
Please help me resolve this problem.
John
name
---
---
-- ---
---
--
AccountDetail
AcctDtlSrceTble
(1 row(s) affected)The easiest way is to use the Query Analyzer Object Browser and expand the
node for the table and then the underlying one for Indexes. You can then
select the relavent index, right click on it and choose Script Object to New
Window As > Create.You can do the same to generate the script to drop it.
You can use the system stored procedure dbcmptlevel to change the
compatability mode e.g.
EXEC sp_dbcmptlevel 'pubs', 70
However, I'm not clear on why you need to do this ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:257801c427d3$568d9ac0$a101280a@.phx.gbl...
> John,
> I ran the indexed views query and indexed computed columns
> query on the database that has the compatability level set
> to 70. Only one table returned from the indexed computed
> columns query.
> Output from indexed computed columns query
> Object_Name Column_Name
> AccountDetail AcctDtlSrceTble
> __________________________________________________________
> DROP INDEX AccountDetail.AcctDtlSrceTble
> How do I obtain a sql script to regenerate the
> AccountDetail.AcctDtlSrceTble indexed computed column?
> What are the steps that I need to take to change the
> compatiblity level 80 to 70?
> Thank You,
> John
>
> This suggests you are using some capabilities available in
> SQL2000 that were
> not previously available in down level versions. To check
> for indexed views
> or indexes on computed columns run the following query in
> Query Analyzer.
> These indexes will need to be dropped in order to
> downgrade the database
> compatability
> -- indexed views
> select name from sysobjects where xtype = 'V'
> and objectproperty(id,'IsMSShipped')=0
> and objectproperty(id,'IsIndexed')=1
> -- indexed computed columns
> select object_name(c.id),c.name
> from syscolumns c join sysindexkeys k
> on c.colid = k.colid and c.id=k.id
> where columnproperty(c.id,c.name,'IsComputed')=1
> and objectproperty(c.id,'IsMSShipped')=0
> and objectproperty(c.id,'IsIndexed')=1
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "John" <anonymous@.discussions.microsoft.com> wrote in
> message
> news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
> I have a SQL Server 2000 Enterprise Edition along with
> SP3A with several databases. These databases are 20 GB in
> size. On one of the databases the Compatibility level was
> changed from 70 to 80. I need to change the compatiblity
> level 80 to 70.
> When tried to change the compatibilty level I received the
> following error:
> Error 15414: Cannot set compatibilty level because
> database has a view or computed column is indexed. These
> indexes require a SQL Server compatible database.
> Please help me resolve this problem.
> John
>
>
> name
>
> ---
> ---
> -- ---
> ---
> --
> AccountDetail
> AcctDtlSrceTble
> (1 row(s) affected)

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!

Incorrect column order in syscolumns / Information_schema.columns

When dropping a column, syscolumns keeps the old ordinal
for the following columns. Is this correct / expected
behavior?
This also affects Information_schema.Columns. See example
below.
Is there any SUPPORTED way to refresh or update this info?
Regards
Hans Schyldt
Drop Table HS_Test
go
CREATE TABLE [dbo].[HS_Test] (
[C1] [int] IDENTITY (1, 1) NOT NULL ,
[C2] [int] NOT NULL ,
[C3] [varchar] (10) NOT NULL ,
[C4] [varchar] (10) NOT NULL) ON [PRIMARY]
go
select
T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
from Information_Schema.Columns as T1
where T1.Table_Name = 'HS_Test'
order by T1.Ordinal_Position
go
alter table HS_Test
Drop Column C2
go
select
T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
from Information_Schema.Columns as T1
where T1.Table_Name = 'HS_Test'
order by T1.Ordinal_Position
go
Drop Table HS_Test
goThis is correct behavior, but can be annoying.
Lot of people will argue that order and ordinal numbers do not matter, but
they matter to me.
What I did to correct this, was making a change with the Enterprise Manager
and reversing that change. Using such a change that the enterprise manager
rebuilds the whole table. In this way my ordinals where again as they were
in other databases. (So I could compare the databases again). This worked
fast enough so I didn't look for 'simpler' commands which only changed
metadata.
ben brugman
"Hans Schyldt" <hans.reply_to_forum.schyldt@.intentia.se> wrote in message
news:0e4701c367e7$70a16ec0$a101280a@.phx.gbl...
> When dropping a column, syscolumns keeps the old ordinal
> for the following columns. Is this correct / expected
> behavior?
> This also affects Information_schema.Columns. See example
> below.
> Is there any SUPPORTED way to refresh or update this info?
> Regards
> Hans Schyldt
>
> Drop Table HS_Test
> go
> CREATE TABLE [dbo].[HS_Test] (
> [C1] [int] IDENTITY (1, 1) NOT NULL ,
> [C2] [int] NOT NULL ,
> [C3] [varchar] (10) NOT NULL ,
> [C4] [varchar] (10) NOT NULL) ON [PRIMARY]
> go
> select
> T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
> from Information_Schema.Columns as T1
> where T1.Table_Name = 'HS_Test'
> order by T1.Ordinal_Position
> go
> alter table HS_Test
> Drop Column C2
> go
> select
> T1.Table_Name, T1.Column_Name, T1.Ordinal_Position
> from Information_Schema.Columns as T1
> where T1.Table_Name = 'HS_Test'
> order by T1.Ordinal_Position
> go
> Drop Table HS_Test
> go

Inconsistent UDF column order

I have a UDF that when I run by two different users, gives two different
orders of data columns. One follows the syntax of the function and the othe
r
is mis-ordered but returns this way consistently. Has anyone else
encountered this?Can you post the ddl?
AMB
"ZachB" wrote:

> I have a UDF that when I run by two different users, gives two different
> orders of data columns. One follows the syntax of the function and the ot
her
> is mis-ordered but returns this way consistently. Has anyone else
> encountered this?|||Not sure what you mean by posting the ddl but here's the syntax of the UDF:
CREATE FUNCTION dbo. MISMOqryMINRegistrationCldDateNE(@.Enter_
Begin_Date
datetime,
@.Enter_End_Date datetime)
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT dbo.dbo_Tracking_File_Ext.f755#MERS,
dbo.MERS.MERS_MINNumber AS [MISMO MERS], dbo.tblLoanDetails.FirstSecond,
dbo.dbo_Tracking_File_Ext.f422#ClosingDate AS [MV Note
Date], dbo.tblLoanInfo.ClsdTDDate AS NoteDate,
dbo.LOAN_DETAILS.ClosingDate AS [MISMO ClosingDate],
dbo.tblLoanDetails.[Loan Amount],
dbo.LOAN_DETAILS.DisbursementDate AS [Funding Date],
dbo.dbo_Tracking_File_Ext.F251#CompanyName1,
dbo.dbo_Tracking_File_Ext.f252#CompanyName2,
dbo.GENERIC_ENTITY_LenderName._UnparsedName AS [MISMO CompanyName1],
' ' AS [MISMO CompanyName2],
dbo.qryBorrJoin.firstname, dbo.qryBorrJoin.B1MI, dbo.qryBorrJoin.Name AS
B1LastName, dbo.qryBorrJoin.BSSN,
dbo.qryBorrJoin.CoFirstName, dbo.qryBorrJoin.B2MI,
dbo.qryBorrJoin.coLastName, dbo.qryBorrJoin.CBSSN, dbo.tblLoanInfo.[Security
Address Street],
dbo.tblLoanInfo.City, dbo.tblLoanInfo.State,
dbo.tblLoanInfo.ZIP, dbo.dbo_Tracking_File.f555#property_county,
dbo.PROPERTY._County AS [MISMO property_county],
dbo.dbo_Tracking_File_Ext.f519#TrusteeName,
dbo.GENERIC_ENTITY_Trustee._UnparsedName AS [MISMO
TrusteeName], dbo.tblStateLookup.StateTrustVMort,
dbo.tblClosedLoan.LoanNumber,
dbo.[tblPurpose Lookup].PurpComerica,
dbo.tblClosedLoan.CommitID, dbo.tblClosedLoan.SandDYN,
dbo.tblClosedLoan.PSStatus
FROM dbo.tblStateLookup RIGHT OUTER JOIN
dbo.dbo_Tracking_File RIGHT OUTER JOIN
dbo.qryBorrJoin INNER JOIN
dbo.tblLoanInfo INNER JOIN
dbo.tblLoanDetails INNER JOIN
dbo.tblClosedLoan ON dbo.tblLoanDetails.LoanDetailID =
dbo.tblClosedLoan.NCLoanNumber ON
dbo.tblLoanInfo.[Acct Number] =
dbo.tblLoanDetails.[Loan Number] ON
dbo.qryBorrJoin.[Loan Number] = dbo.tblLoanInfo.[Acct
Number] LEFT OUTER JOIN
dbo.[tblPurpose Lookup] ON dbo.tblLoanInfo.Purpose =
dbo.[tblPurpose Lookup].[Purpose Lookup] LEFT OUTER JOIN
dbo.dbo_Tracking_File_Ext ON
dbo.tblClosedLoan.LoanNumber = dbo.dbo_Tracking_File_Ext.Loan_ID ON
dbo.dbo_Tracking_File.Loan_ID =
dbo.tblClosedLoan.LoanNumber ON dbo.tblStateLookup.StateID =
dbo.tblLoanInfo.State LEFT OUTER JOIN
dbo._CLOSING_DOCUMENTS LEFT OUTER JOIN
dbo.GENERIC_ENTITY_LenderName ON
dbo._CLOSING_DOCUMENTS.CLDC_ID = dbo.GENERIC_ENTITY_LenderName.CLDC_ID LEFT
OUTER JOIN
dbo.GENERIC_ENTITY_Trustee RIGHT OUTER JOIN
dbo.RECORDABLE_DOCUMENT ON
dbo.GENERIC_ENTITY_Trustee.RCDO_ID = dbo.RECORDABLE_DOCUMENT.RCDO_ID ON
dbo._CLOSING_DOCUMENTS.CLDC_ID =
dbo.RECORDABLE_DOCUMENT.CLDC_ID RIGHT OUTER JOIN
dbo.MORTGAGE_TERMS_MaxAppl LEFT OUTER JOIN
dbo.LOAN_APPLICATION ON
dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID = dbo.LOAN_APPLICATION.APPL_ID LEFT OUTER
JOIN
dbo.PROPERTY ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
dbo.PROPERTY.APPL_ID ON
dbo._CLOSING_DOCUMENTS.LOAN_ID =
dbo.LOAN_APPLICATION.LOAN_ID LEFT OUTER JOIN
dbo.LOAN_DETAILS ON dbo._CLOSING_DOCUMENTS.CLDC_ID =
dbo.LOAN_DETAILS.CLDC_ID LEFT OUTER JOIN
dbo.MERS ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
dbo.MERS.APPL_ID ON
dbo.tblClosedLoan.LoanNumber =
dbo.MORTGAGE_TERMS_MaxAppl.LenderLoanIdentifier
WHERE (dbo.tblLoanInfo.ClsdTDDate BETWEEN @.Enter_Begin_Date AND
@.Enter_End_Date) AND (dbo.tblClosedLoan.SandDYN = 0 OR
dbo.tblClosedLoan.SandDYN IS NULL) AND
(dbo.tblClosedLoan.PSStatus <> N'rescinded')
ORDER BY dbo.qryBorrJoin.Name, dbo.qryBorrJoin.firstname )|||ZachB,
When you say "gives two different orders of data columns", Do you mean
different sort of the result or that the column list is different?. How are
you querying this table function?
The "order by" clause used inside the function does not guarantee any order
of the result when you use:
declare @.sd datetime
declare @.ed datetime
set @.sd = '20050101'
set @.ed = '20050321'
select col1, col2, ..., coln
from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
you have to use an "order by" clause again if you want the order of the rows
to be consistent.
select col1, col2, ..., coln
from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
order by col1, ...
AMB
"ZachB" wrote:

> Not sure what you mean by posting the ddl but here's the syntax of the UDF
:
> CREATE FUNCTION dbo. MISMOqryMINRegistrationCldDateNE(@.Enter_
Begin_Date
> datetime,
> @.Enter_End_Date datetime)
> RETURNS TABLE
> AS
> RETURN ( SELECT TOP 100 PERCENT dbo.dbo_Tracking_File_Ext.f755#MERS,
> dbo.MERS.MERS_MINNumber AS [MISMO MERS], dbo.tblLoanDetails.FirstSecond,
> dbo.dbo_Tracking_File_Ext.f422#ClosingDate AS [MV No
te
> Date], dbo.tblLoanInfo.ClsdTDDate AS NoteDate,
> dbo.LOAN_DETAILS.ClosingDate AS [MISMO ClosingDate],
> dbo.tblLoanDetails.[Loan Amount],
> dbo.LOAN_DETAILS.DisbursementDate AS [Funding Date],
> dbo.dbo_Tracking_File_Ext.F251#CompanyName1,
> dbo.dbo_Tracking_File_Ext.f252#CompanyName2,
> dbo.GENERIC_ENTITY_LenderName._UnparsedName AS [MISMO CompanyName1],
> ' ' AS [MISMO CompanyName2],
> dbo.qryBorrJoin.firstname, dbo.qryBorrJoin.B1MI, dbo.qryBorrJoin.Name AS
> B1LastName, dbo.qryBorrJoin.BSSN,
> dbo.qryBorrJoin.CoFirstName, dbo.qryBorrJoin.B2MI,
> dbo.qryBorrJoin.coLastName, dbo.qryBorrJoin.CBSSN, dbo.tblLoanInfo.[Securi
ty
> Address Street],
> dbo.tblLoanInfo.City, dbo.tblLoanInfo.State,
> dbo.tblLoanInfo.ZIP, dbo.dbo_Tracking_File.f555#property_county,
> dbo.PROPERTY._County AS [MISMO property_county],
> dbo.dbo_Tracking_File_Ext.f519#TrusteeName,
> dbo.GENERIC_ENTITY_Trustee._UnparsedName AS [MISMO
> TrusteeName], dbo.tblStateLookup.StateTrustVMort,
> dbo.tblClosedLoan.LoanNumber,
> dbo.[tblPurpose Lookup].PurpComerica,
> dbo.tblClosedLoan.CommitID, dbo.tblClosedLoan.SandDYN,
> dbo.tblClosedLoan.PSStatus
> FROM dbo.tblStateLookup RIGHT OUTER JOIN
> dbo.dbo_Tracking_File RIGHT OUTER JOIN
> dbo.qryBorrJoin INNER JOIN
> dbo.tblLoanInfo INNER JOIN
> dbo.tblLoanDetails INNER JOIN
> dbo.tblClosedLoan ON dbo.tblLoanDetails.LoanDetailID
=
> dbo.tblClosedLoan.NCLoanNumber ON
> dbo.tblLoanInfo.[Acct Number] =
> dbo.tblLoanDetails.[Loan Number] ON
> dbo.qryBorrJoin.[Loan Number] = dbo.tblLoanInfo.[Acc
t
> Number] LEFT OUTER JOIN
> dbo.[tblPurpose Lookup] ON dbo.tblLoanInfo.Purpose =
> dbo.[tblPurpose Lookup].[Purpose Lookup] LEFT OUTER JOIN
> dbo.dbo_Tracking_File_Ext ON
> dbo.tblClosedLoan.LoanNumber = dbo.dbo_Tracking_File_Ext.Loan_ID ON
> dbo.dbo_Tracking_File.Loan_ID =
> dbo.tblClosedLoan.LoanNumber ON dbo.tblStateLookup.StateID =
> dbo.tblLoanInfo.State LEFT OUTER JOIN
> dbo._CLOSING_DOCUMENTS LEFT OUTER JOIN
> dbo.GENERIC_ENTITY_LenderName ON
> dbo._CLOSING_DOCUMENTS.CLDC_ID = dbo.GENERIC_ENTITY_LenderName.CLDC_ID LEF
T
> OUTER JOIN
> dbo.GENERIC_ENTITY_Trustee RIGHT OUTER JOIN
> dbo.RECORDABLE_DOCUMENT ON
> dbo.GENERIC_ENTITY_Trustee.RCDO_ID = dbo.RECORDABLE_DOCUMENT.RCDO_ID ON
> dbo._CLOSING_DOCUMENTS.CLDC_ID =
> dbo.RECORDABLE_DOCUMENT.CLDC_ID RIGHT OUTER JOIN
> dbo.MORTGAGE_TERMS_MaxAppl LEFT OUTER JOIN
> dbo.LOAN_APPLICATION ON
> dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID = dbo.LOAN_APPLICATION.APPL_ID LEFT OUT
ER
> JOIN
> dbo.PROPERTY ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
> dbo.PROPERTY.APPL_ID ON
> dbo._CLOSING_DOCUMENTS.LOAN_ID =
> dbo.LOAN_APPLICATION.LOAN_ID LEFT OUTER JOIN
> dbo.LOAN_DETAILS ON dbo._CLOSING_DOCUMENTS.CLDC_ID =
> dbo.LOAN_DETAILS.CLDC_ID LEFT OUTER JOIN
> dbo.MERS ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
> dbo.MERS.APPL_ID ON
> dbo.tblClosedLoan.LoanNumber =
> dbo.MORTGAGE_TERMS_MaxAppl.LenderLoanIdentifier
> WHERE (dbo.tblLoanInfo.ClsdTDDate BETWEEN @.Enter_Begin_Date AND
> @.Enter_End_Date) AND (dbo.tblClosedLoan.SandDYN = 0 OR
> dbo.tblClosedLoan.SandDYN IS NULL) AND
> (dbo.tblClosedLoan.PSStatus <> N'rescinded')
> ORDER BY dbo.qryBorrJoin.Name, dbo.qryBorrJoin.firstname )|||The UDF is being called from an MS Access .adp project. The "order" of the
data columns is different meaning in one case it shows Column A, Column B,
Column C, Column D but in the other it shows Column B, Column C, Column A,
Column D (Even if the syntax says SELECT Column A, Column B, Column C, Colum
n
D.
row 1 test1 test2 test3 test4
vs.
row 1 test2 test3 test1 test4
"Alejandro Mesa" wrote:
> ZachB,
> When you say "gives two different orders of data columns", Do you mean
> different sort of the result or that the column list is different?. How ar
e
> you querying this table function?
> The "order by" clause used inside the function does not guarantee any orde
r
> of the result when you use:
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '20050101'
> set @.ed = '20050321'
> select col1, col2, ..., coln
> from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
> you have to use an "order by" clause again if you want the order of the ro
ws
> to be consistent.
> select col1, col2, ..., coln
> from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
> order by col1, ...
>
> AMB
> "ZachB" wrote:
>|||ZachB,
Can you trace the statements sent to sql server by the project?
AMB
"ZachB" wrote:
> The UDF is being called from an MS Access .adp project. The "order" of th
e
> data columns is different meaning in one case it shows Column A, Column B,
> Column C, Column D but in the other it shows Column B, Column C, Column A,
> Column D (Even if the syntax says SELECT Column A, Column B, Column C, Col
umn
> D.
> row 1 test1 test2 test3 test4
> vs.
> row 1 test2 test3 test1 test4
> "Alejandro Mesa" wrote:
>|||Not that I'm aware of. I know you can check the properties of a particular
spid under Current Activity and see what syntax is or has just been run. Bu
t
I would assume that since the two users are hitting the same .adp they
shouldn't be passing different statements.
The MS Access .adp menu option states:
Open table 'dbo.MISMOqryMINRegistrationCldDateNE'
and this is just one example. This mis-ordering happens consisently across
several UDFs that I'm working with.
"Alejandro Mesa" wrote:

> ZachB,
> Can you trace the statements sent to sql server by the project?
>
> AMB|||Use Profiler to trace activities in the server. You can read about it in BOL
.
AMB
"ZachB" wrote:
> Not that I'm aware of. I know you can check the properties of a particula
r
> spid under Current Activity and see what syntax is or has just been run.
But
> I would assume that since the two users are hitting the same .adp they
> shouldn't be passing different statements.
> The MS Access .adp menu option states:
> Open table 'dbo.MISMOqryMINRegistrationCldDateNE'
> and this is just one example. This mis-ordering happens consisently acros
s
> several UDFs that I'm working with.
> "Alejandro Mesa" wrote:
>|||Anyone else? While I'm learning to trace, has anyone ever had a UDF return
data in different COLUMN order for different users? Let me know. Thanks i
n
advance.
"ZachB" wrote:

> I have a UDF that when I run by two different users, gives two different
> orders of data columns. One follows the syntax of the function and the ot
her
> is mis-ordered but returns this way consistently. Has anyone else
> encountered this?|||"ZachB" <ZachB@.discussions.microsoft.com> wrote in message
news:5966BAE6-5CC4-4055-A8D9-454B2E5A2E9C@.microsoft.com...
> Anyone else? While I'm learning to trace, has anyone ever had a UDF
return
> data in different COLUMN order for different users? Let me know. Thanks
in
> advance.
>
Wild Guesses:
1. The clients are executing different code. One client has been updated
and the other hasn't.
2. The clients are attached to different databases, one on test and one on
production.
3. It isn't really happening - The client preferences are different so that
on one client things appear differently but aren't actually different.
(e.g. hidden display controls, etc)
4. You are using Select * and there is a weird caching thing going on.
Good Luck.
Jim

Incomplete data in excel fileshare -missing many rows

Hello,
I have a report with 35 columns, meant as a datafeed. The report uses a view
on the (separate) SQL server so it only needs to put the values in the table.
When ik run the report for medium datasets, everything works fine. However
when I run it for more then 20,000 rows the exel-sheet only contains about
16300 rows. The report is meant as a subscription with a fileshare on the
local server. When i run the report via the viewer, an export to excel is
timed out, however an export to csv works and contains all rows. Sadly this
is not the format in which i need to supply the data.
can anybody offer any assistance?What version of Excel are you using? Have you passed the row limit?
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
news:AD96B5F8-4A8C-4D2B-87B2-DE343CF8C3F6@.microsoft.com...
> Hello,
> I have a report with 35 columns, meant as a datafeed. The report uses a
> view
> on the (separate) SQL server so it only needs to put the values in the
> table.
> When ik run the report for medium datasets, everything works fine. However
> when I run it for more then 20,000 rows the exel-sheet only contains about
> 16300 rows. The report is meant as a subscription with a fileshare on the
> local server. When i run the report via the viewer, an export to excel is
> timed out, however an export to csv works and contains all rows. Sadly
> this
> is not the format in which i need to supply the data.
> can anybody offer any assistance?|||I kept testing in the weekeind and it's my bad. RS generates the file fine,
Our sending software seems to truncate the file. Sorry.
"Mary Bray [MVP]" wrote:
> What version of Excel are you using? Have you passed the row limit?
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> news:AD96B5F8-4A8C-4D2B-87B2-DE343CF8C3F6@.microsoft.com...
> > Hello,
> >
> > I have a report with 35 columns, meant as a datafeed. The report uses a
> > view
> > on the (separate) SQL server so it only needs to put the values in the
> > table.
> >
> > When ik run the report for medium datasets, everything works fine. However
> > when I run it for more then 20,000 rows the exel-sheet only contains about
> > 16300 rows. The report is meant as a subscription with a fileshare on the
> > local server. When i run the report via the viewer, an export to excel is
> > timed out, however an export to csv works and contains all rows. Sadly
> > this
> > is not the format in which i need to supply the data.
> >
> > can anybody offer any assistance?
>
>

Including NULL columns as empty elements in SELECT FOR XML

Hi everyone,

I was wondering if it is possible for a SELECT FOR XML statement to map a row with a NULL value in a column to an empty element in XML?

For example, let's say I have the following table:

CREATE TABLE NetworkAdapter

(

ID int PRIMARY KEY

MacAddress char(17)

)

The table has one row with the values (10, NULL). Can I use the SELECT FOR XML statement to return the following XML:

<NetworkAdapter>

<ID>10</ID>

<MacAddress /> -- Or <MacAddress></MacAddress>, doesn't matter

</NetworkAdapter>

Is it possible to do this without using ISNULL on the MacAddress column? Or if not, how would you do it using ISNULL?

Another somewhat related question ... Is it possible to use the SELECT FOR XML statement to return a set of empty elements for a SELECT statement that has no results? Using the NetworkAdapter table with just that one row listed above, let's say I have the following query:

SELECT *

FROM NetworkAdapter

WHERE ID = '5'

This query returns no results, but I would like to use it in conjunction with FOR XML to return this:

<NetworkAdapter>

<ID />

<MacAddress />

</NetworkAdapter>

Thanks.

There is a directive ELEMENTS XSINIL that causes NULL database values to be returned as an empty element with the attribute xsi:nil="true" e.g.

Code Snippet

SELECT ID, MacAddress

FROM NetworkAdapter

FOR XML AUTO, ELEMENTS XSINIL;

will then return

Code Snippet

<NetworkAdapter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ID>1</ID>

<MacAddress xsi:nil="true" />

</NetworkAdapter>

for rows where MacAdress is NULL. See http://msdn2.microsoft.com/en-us/library/ms178079.aspx

|||

Great! Thanks for your reply.

Including columns with SQL Server Allow Nulls Checked

Why is it that when I include a column from my SQL Server database table, which has it's Allow Nulls checked, in the data source of a control that the record becomes not update-able? How do I get around this?

If your record is going to have null values in it (which is quite common) you have to add a DataSet file to your site, create a TableAdapter and then use the TableAdapter as the data source 'Object' for the data control. ... ... Wait, ... let me breath... ... that's not the end of it... ... ... When choosing the data source for the control, make sure when you get to the Define Parameters dialog that the advanced property ConvertEmptyStringToNull is true. It took me about 12 hours to figure this out but I have also read that it has taken others up to a week so I guess I'm doing good. I REALLY WISH MICROSOFT WOULD HAVE SPELLED THIS OUT MORE CLEARLY IN THEIR HELP FILES.

Included columns in index.. performance implications ?

How detrimental is it to create multiple indexes that have included columns
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
ThanksIt is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If thes
e
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> How detrimental is it to create multiple indexes that have included column
s
> as opposed to creating composite indexes that include that all those colum
ns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index.
.
> the only difference will be whether I decide to have those included column
s
> or not..
> Thanks
>

Included columns in index.. performance implications ?

How detrimental is it to create multiple indexes that have included columns
as opposed to creating composite indexes that include that all those columns
?
I want to add the index with included columns but also concerned about
slowness in performance on writes. Either way I want to create the index..
the only difference will be whether I decide to have those included columns
or not..
Thanks
It is not really a performance decision. It depends if those columns will be
used, for example, on the WHERE clause or just on the SELECT clause. If these
columns will be listed only in the SELECT clause then use included columns.
If you are going to use those columns as a search criteria then included
columns would not help.
Regarding performance, include columns are better because they are only
stored at the leaf level of the index.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> How detrimental is it to create multiple indexes that have included columns
> as opposed to creating composite indexes that include that all those columns
> ?
> I want to add the index with included columns but also concerned about
> slowness in performance on writes. Either way I want to create the index..
> the only difference will be whether I decide to have those included columns
> or not..
> Thanks
>