July 13, 2011 at 8:39 am
Dear,
i want to create clustered index on view => i want to replicate an indexed view but i get allways an error.
below my view :
USE [CPDS_PROD]
GO
/****** Object: View [dbo].[vwReplEmptyProvisionsHomeTerminal3] Script Date: 07/13/2011 16:18:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vwReplEmptyProvisionsHomeTerminal3] with schemabinding
AS
(
select Berthing_id,
Vessel_id
from
(SELECT dbo.Berthings.Berthing_id,
dbo.Berthings.Vessel_id,
dbo.Vessels.VesselCode,
dbo.Vessels.VesselName,
dbo.Vessels.LloydsRegistryNbr,
dbo.Vessels.CallSign,
dbo.Countries.FlagName,
dbo.Vessels.InService as VesselsInService,
dbo.Berthings.voyage,
dbo.Berthings.plannerberth,
dbo.Berthings.PlannerSpecialArrival,
dbo.Berthings.PlannerSpecialDeparture,
dbo.Berthings.Service_id,
coalesce(dbo.Services_Ports.ServiceCode, dbo.Services.BerthingCode) as ServiceCode,
dbo.Services.ServiceName,
dbo.Services.InService as ServicesInService,
dbo.Berthings.PlannerArrivalPilot,
dbo.Berthings.PlannerArrivalBerth,
dbo.EmptyProvisions.DischargedEmpties20DV,
dbo.EmptyProvisions.DischargedEmpties20OT,
dbo.EmptyProvisions.DischargedEmpties20FL,
dbo.EmptyProvisions.DischargedEmpties20RF,
dbo.EmptyProvisions.DischargedEmpties40DV,
dbo.EmptyProvisions.DischargedEmpties40OT,
dbo.EmptyProvisions.DischargedEmpties40FL,
dbo.EmptyProvisions.DischargedEmpties40HRF,
dbo.EmptyProvisions.DischargedEmpties40HC,
dbo.Berthings.InService as BerthingsInService,
dbo.Berthings.InsTime,
dbo.Berthings.UpdTime,
dbo.Berthings.DelTime
FROM
dbo.Berthings
LEFT JOIN dbo.EmptyProvisions ON (dbo.Berthings.Berthing_id = dbo.EmptyProvisions.Berthing_id)
LEFT JOIN dbo.Vessels ON (dbo.Berthings.Vessel_id = dbo.Vessels.Vessel_id)
LEFT JOIN dbo.Countries ON (dbo.Vessels.Country_id = dbo.Countries.Country_id)
LEFT JOIN dbo.Services ON (dbo.Berthings.Service_id = dbo.Services.Service_id)
LEFT JOIN dbo.Services_Ports ON (dbo.Berthings.Service_id = dbo.Services_Ports.Service_id) and
(dbo.Berthings.CurrentPort_id = dbo.Services_Ports.Port_id)
WHERE dbo.Berthings.CurrentPort_id = 743) as a --only antwerp
)
GO
Then i want to create an clustered unique index and i get the following error
CREATE UNIQUE CLUSTERED INDEX [IX_vwReplEmptyProvisionsHomeTerminal2_index_Berthing_id]
ON [dbo].[vwReplEmptyProvisionsHomeTerminal3] ([Berthing_id])
Msg 10109, Level 16, State 1, Line 1
Cannot create index on view "CPDS_PROD.dbo.vwReplEmptyProvisionsHomeTerminal3" because it references derived table "a" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.
How can i solve this. If i not use a derived table he has problems with my left joins
what is the trick here???
Thank you
WHEEL
July 13, 2011 at 8:44 am
I far as I know you can not create a index on a view that has a view or sub table in it.
http://msdn.microsoft.com/en-us/library/aa933148(v=sql.80).aspx
July 13, 2011 at 8:55 am
I tried this below but it gives another error
ALTER VIEW [dbo].[vwReplEmptyProvisionsHomeTerminal4] with schemabinding
AS
(
SELECT dbo.Berthings.Berthing_id,
dbo.Berthings.Vessel_id,
dbo.Berthings.Service_id,
(select dbo.Services.BerthingCode
from dbo.Services
where (dbo.Berthings.Service_id = dbo.Services.Service_id)
) as ServiceCode,
FROM
dbo.Berthings
WHERE dbo.Berthings.CurrentPort_id = 743 --only antwerp
when i create the index
CREATE UNIQUE CLUSTERED INDEX [IX_vwReplEmptyProvisionsHomeTerminal4_index_Berthing_id]
ON [dbo].[vwReplEmptyProvisionsHomeTerminal4] ([Berthing_id])
then i get following error
Msg 10127, Level 16, State 1, Line 1
Cannot create index on view "CPDS_PROD.dbo.vwReplEmptyProvisionsHomeTerminal4" because it contains one or more subqueries. Consider changing the view to use only joins instead of subqueries. Alternatively, consider not indexing this view.
How can i solve this dificult question??,
Thank you
Wheel
July 13, 2011 at 8:59 am
i want to replicate fields out of 4 5 tables that are joined with 'left joins' and want that the replication creates a new table at the subscriber site with the same layout as the view
It is possible with indexed view but then i cannot use left joins
what is a reasonnable alternative
Thank you
Wheel
July 13, 2011 at 9:14 am
i want to replicate fields out of 4 5 tables that are joined with 'left joins' and want that the replication creates a new table at the subscriber site with the same layout as the view
It is possible with indexed view but then i cannot use left joins
what is a reasonnable alternative
Thank you
Wheel
Then why don't you create a stored procedure that just updates the table at the subscriber site on a set job schedule?
July 13, 2011 at 9:20 am
i taught it was the best to use the replication software of sql server itself.
It works perfectly with indexed views if i do not use left joins
So i taught it has to be possible with these also
What is the trick?
Thank you
Wheel
July 13, 2011 at 9:26 am
i taught it was the best to use the replication software of sql server itself.
It works perfectly with indexed views if i do not use left joins
So i taught it has to be possible with these also
What is the trick?
Thank you
Wheel
Wheel
So you are using replication? What is it you actually want to accomplish? If you want to use an indexed view then you will simply have to follow the rules to accomplish that...which could mean what ever.
July 13, 2011 at 9:39 am
Dear
I allready set up replication of an indexed view. It creates a new table (same layout as the indexed view) in the subscriber database.
So that works perfectly.
Now i want to do the same replication with an indexed view but now i have to use 'Left joins' in my view.
So i cannot create my indexed view => i cannot replicate this indexed view => so it will not create a new table in subscriber database if i use a non indexed view.
Can someone help me to create an indexed view with left joins or how can i avoid this
Thank you
Wheel
July 13, 2011 at 9:43 am
Dear
I allready set up replication of an indexed view. It creates a new table (same layout as the indexed view) in the subscriber database.
So that works perfectly.
Now i want to do the same replication with an indexed view but now i have to use 'Left joins' in my view.
So i cannot create my indexed view => i cannot replicate this indexed view => so it will not create a new table in subscriber database if i use a non indexed view.
Can someone help me to create an indexed view with left joins or how can i avoid this
Thank you
Wheel
Ok so why dont you set up a process that will not require you to have to use those left joins? Such as a stored proc to update a static table that has your index view based on.
July 13, 2011 at 9:51 am
How, pleas
Then i ave to add, change, delete records ever ... seconds
Or every ... seconds truncate this table and refill it
Thank you
Chris Vandewiele
July 13, 2011 at 9:58 am
Agreed unless you can get the same results by eliminating the index viewed rules. Such as instead of using the view use the 2 tables. Sorry can not help any better than that but your in a black box with your issue. Meaning i do not have enough info to help better than assumptions.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply