May 17, 2009 at 7:55 pm
Hello my colleges,
I would like to know if anyone has ever required to use a Union join within a publication filter?
When i go to build the snapshot it fails saying
SQL Server Management Studio could not create article 'TableA'.
------------------------------
ADDITIONAL INFORMATION:
Replication filter procedures may not contain columns of large object, large value, XML or UDT type.
Cannot generate a filter view or procedure. Verify that the value specified for the @filter_clause parameter of sp_addarticle can be added to the WHERE clause of a SELECT statement to produce a valid query. Changed database context to 'DatabaseA'. (Microsoft SQL Server, Error: 341)
Now if i use either half as the filter it works, it only fails when i try to use both with the Union.
SELECT
Version in (select max(Version) from TableA as expr where
(TableA.A= Aand TableA.B = B and TableA.C = C and TableA.D = D and TableA.E = E and F= 9))
union All
SELECT * FROM [dbo].[TableA] as Ta
where Ta.Version in (select max(Version) from TableA as expr
where (Ta.A= A and Ta.B= Band Ta.C= C and Ta.D= D and Ta.E= E and Z in (51,52,53) and F = 0))
Can anyone suggest a solution??
Cheers 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
May 17, 2009 at 10:30 pm
As the error message states, you need to supply a filter that can be used in a WHERE statement.
You can re-write your statement as
SELECT * FROM [dbo].[TableA] TA WHERE
Version in (select max(Version) from TableA as expr where
(TA.A= Aand TA.B = B and TA.C = C and TA.D = D and TA.E = E and F= 9))
OR Ta.Version in (select max(Version) from TableA as expr
where (Ta.A= A and Ta.B= Band Ta.C= C and Ta.D= D and Ta.E= E and Z in (51,52,53) and F = 0))
So the filter becomes
Version in (select max(Version) from TableA as expr where
(TA.A= Aand TA.B = B and TA.C = C and TA.D = D and TA.E = E and F= 9))
OR Ta.Version in (select max(Version) from TableA as expr
where (Ta.A= A and Ta.B= Band Ta.C= C and Ta.D= D and Ta.E= E and Z in (51,52,53) and F = 0))
May 17, 2009 at 11:20 pm
Master stroke
Thank you so much 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply