Problem with user defined funtions

Hi,
I defined a publisher and a pushsubscriber with some articles. The articles
were Tables, Views and user defined functions. The pushsubscriber was created
with the sync_type=automatic. When I synchronisize the publisher with the
subscriber, the merge-agent fails on the follwing view creation command:
CREATE VIEW [dbo].[TGS_CATALOG]
AS
SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
+
dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
Latin1_General_CS_AS AS PFAD,
dbo.TGS_CATALOG_MASTER.ENTITYNUM,
dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
dbo.TGS_CATALOG_MASTER.KEYS,
dbo.TGS_CATALOG_MASTER.REVIERID
FROM dbo.TGS_BAUMTHEMEN INNER JOIN
dbo.TGS_CATALOG_MASTER ON dbo.TGS_BAUMTHEMEN.REVIERID
= dbo.TGS_CATALOG_MASTER.REVIERID AND
dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
dbo.TGS_REVIERE.REVIERID
I looked in the logfile and saw that there was a drop command of the user
defined function 'NullStringFilter', but no recreationof it before the
create command
was proccessed. Therfore the creation of the view fails.
What can I do to change the order of the schema creation process ?
I want to recreate the user defined functions before the recreation of the
views
starts !!!
Best regards
Axel Lanser
replication uses sysdepends to figure out the ordering of articles.
Sometimes this table can be out of sync (read erroneous).
I tend to deploy my problematic schema using a pre-snapshot command where
all constraints are disabled. Then I set all articles up so that they use
the delete data in existing table in the name conflicts section. I then use
a post-snapshot command to enable the constraints.
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
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> Hi,
> I defined a publisher and a pushsubscriber with some articles. The
articles
> were Tables, Views and user defined functions. The pushsubscriber was
created
> with the sync_type=automatic. When I synchronisize the publisher with the
> subscriber, the merge-agent fails on the follwing view creation command:
> CREATE VIEW [dbo].[TGS_CATALOG]
> AS
> SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
> dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
> dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
> dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
> +
> dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
> Latin1_General_CS_AS AS PFAD,
> dbo.TGS_CATALOG_MASTER.ENTITYNUM,
> dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
> dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
> dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
> dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
> dbo.TGS_CATALOG_MASTER.KEYS,
> dbo.TGS_CATALOG_MASTER.REVIERID
> FROM dbo.TGS_BAUMTHEMEN INNER JOIN
> dbo.TGS_CATALOG_MASTER ON
dbo.TGS_BAUMTHEMEN.REVIERID
> = dbo.TGS_CATALOG_MASTER.REVIERID AND
> dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
> dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
> dbo.TGS_REVIERE.REVIERID
> I looked in the logfile and saw that there was a drop command of the user
> defined function 'NullStringFilter', but no recreationof it before the
> create command
> was proccessed. Therfore the creation of the view fails.
> What can I do to change the order of the schema creation process ?
> I want to recreate the user defined functions before the recreation of the
> views
> starts !!!
> Best regards
> Axel Lanser
|||Hi,
how can I create pre- and post-snapshots ?
Best regards
Axel Lanser
"Hilary Cotter" wrote:

> replication uses sysdepends to figure out the ordering of articles.
> Sometimes this table can be out of sync (read erroneous).
> I tend to deploy my problematic schema using a pre-snapshot command where
> all constraints are disabled. Then I set all articles up so that they use
> the delete data in existing table in the name conflicts section. I then use
> a post-snapshot command to enable the constraints.
> --
> 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
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> articles
> created
> dbo.TGS_BAUMTHEMEN.REVIERID
>
>
|||ALN:
You need to write you own SQL File where you set your custom ALTER TABLE
commands for disabling constraints. While setting up publication you can
specify which SQL file needs to be run before snapshot is applied. Check BOL
for @.pre_snapshot_script parameter in sp_addpublication procedure.
"ALN" wrote:
[vbcol=seagreen]
> Hi,
> how can I create pre- and post-snapshots ?
> Best regards
> Axel Lanser
> "Hilary Cotter" wrote:
|||Hi,
thank you very much for your fast response. It helps me.
Best regards
Axel Lanser
"Mark" wrote:
[vbcol=seagreen]
> ALN:
> You need to write you own SQL File where you set your custom ALTER TABLE
> commands for disabling constraints. While setting up publication you can
> specify which SQL file needs to be run before snapshot is applied. Check BOL
> for @.pre_snapshot_script parameter in sp_addpublication procedure.
>
> "ALN" wrote: