User Defined Function as a Computed Column -- sql server replication

Buzz
5/31/2005 9:24:02 PM

It looks worse then I thought.

My function in table B of the computed column uses data from another table.
So the function needs to be created after table A but before table B.

I also looked at how the UDF is handled from a drop perspective and it won't
work either. Replication stuffs the drop command in a pre-processing step.
Well if a table that still uses it exists in the subscriber database, it
won't be dropped!

May someone confirm computed columns are not supported by replication in sql
server SP4 so that I can figure out a new plan? Anyone know a managable
workaround? Or should I just abandon computed columns altogether.

Thanks,
Buzz

[quoted text, click to view]

Paul Ibison
6/1/2005 12:00:00 AM

Buzz,
there are a few options:
put the UDF in a pre-snapshot script
use sp_addscriptexec
use a separate publication
use SQL 2005 where you can specify the processing order of articles
In my case I like to use a separate publication. Unlike SQL 2005 there is no
way in SQL 2000 that changes to the UDF will be automatically propagated
unlike. If it does change, I have the simple matter of reinitializing the
UDF publication, and of knowing when it last occurred.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Paul Ibison
6/1/2005 12:00:00 AM

Buzz,
I just created this function and successfully replicated it:

CREATE FUNCTION "test" ()
RETURNS int AS
BEGIN
declare @count int
set @count = (select count(*) from xxx)
return @count
END

However there's no table xxx on the subscriber (or publisher).

Please can you push this subscription locally (no ftp but fileshares), and
enable logging of the distribution agent to see if there's more info. Also,
please run udf_Bit_Rate_0.sql manually on the initial subscriber to see if
there is an error message - try this first.

Rgds,
Paul Ibison

Paul Ibison
6/1/2005 12:00:00 AM

Buzz,
as UDFs use deferred name resolution, I think you can make things easy by
ensuring the UDF is created before both tables?
Rgds,
Paul Ibison

to
[quoted text, click to view]

Buzz
6/1/2005 8:01:01 AM

Thanks for the detailed steps.

However, I need to specify the order as my computed column on Table B uses
information from Table A. SQL 2005 is out as an option for me.

I suppose I could try to exclude the calculated columns from the publication
and add the computed columns to the tables after the initialization was done
in a post-processing script.

Ugly.

Again, thanks for the ideas Paul,

Buzz

[quoted text, click to view]

Buzz
6/1/2005 9:38:02 AM

Hi Paul,

I was trying to test your theory but I ran into this error:
The schema script
'\\W2KSERVER\ReplData\ftp\W2KSERVER_Test_Pub\20050601101419\udf_Bit_Rate_0.sql' could not be propagated to the subscriber.

I am using FTP and compression. I checked the snapshot.cab file and it is
indeed in there.

Other newsgroup posting talk about security access but since it is in the
..cab file with the rest of the files, I would think that is not the problem.

Thanks,
Buzz

[quoted text, click to view]

Buzz
6/1/2005 7:03:21 PM

Hi Paul,

You've been very helpful, it's great to get assistance on this.

I ran udf.sql against the subscriber. It errored out on the DROP which I
normally don't care about. But that got me thinking, I checked the Merge
Agent Session History and it said the call to OSQL for the file bombed. I
changed the straight DROP into a IF EXISTS on the sysobjects to avoid the
error and the synchronization works!

1) However, from a maintenance perspective in production, if I find a bug in
the function, I'll have to right a more complex script that drops all
dependant table columns, alters the function and then adds back the columns.

2) The other method I've worked through is that I can create the function in
database 2, then I create a wrapper function in database 1 to call it, then
the computed column calls the wrapper in database 1.

I can rebuild my functions in database 2 to my hearts desire as long as I
keep the function interface the same. Then I can use a simple publication to
move new function changes. The ability to easily fix the guts of the function
when its out in the field I think has value.

I have never gone to production with Sql Server or Replication so I was
wondering what the best practice was?

Thanks,
Buzz

[quoted text, click to view]

Paul Ibison
6/2/2005 9:07:02 AM

Buzz,
I'll test this, but I don't see any reason why you can't use
sp_addscriptexec with an ALTER FUNCTION statement in it.
Cheers,
Paul Ibison

Buzz
6/2/2005 1:31:05 PM

Hi Paul,

Just with my limited experience with computed columns, once the function is
part of a table, if I execute an ALTER, I get:

Cannot ALTER 'udfw_Bit_Rate' because it is being referenced by object
'Bit_Report'.

This restriction is outside of replication. However, if there was a bug in a
function that needed fixing, because the table is part of replication, it
would be really complex to get the steps right to drop the computed column
from the table, alter the function, and then re-add the computed column back
to the table (and still not screw up replication with all those people out on
laptops just wanting to synchronize data).

So I simplified my process a bit. Instead of creating a seperate database, I
just create my real functions prefixed by udf. I think include them as
articles in the publication. I then create wrapper functions prefixed by udfw
that the tables reference for their computed columns. Those udfw functions
are then part of the pre-processing script of the snapshot.

I know I am stuck with the public interface of the udfw but at least now I
have abstracted the table being tied to the true function. And if I need more
information in that function, I can just select from the current row
(hmmmm... maybe as a best practice, I will always pass in the key fields of
the record to the function.)

One thing I have to test now is if I change the udf, how does it get
synchronized with existing subscribers without forcing them to reinitialize
and thus loose all of their data (so I'm told).

Thanks,
Buzz

[quoted text, click to view]

Paul Ibison
6/2/2005 10:16:52 PM

Buzz,
I quite like the abstraction solution. I hadn't realised that computed
columns are schema bound to the UDF, once related. This makes sense, but I
didn't realize this was mandatory - I'd like to take more of a look at this
tomorrow.
For your changes to the 'UDF' functions - these could be propagated using
sp_addscriptexec and without reinitialization.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Raymond Mak [MSFT]
6/7/2005 1:21:01 PM

Hi Buzz,

It is rather unfortunate that replication of computed columns on udf
dependencies is quite broken in SQL2000, the only workarounds that I can
think of are:

1) Put the udf definition in a pre-snapshot script
2) Filter out computed columns depending on udfs

I must say that neither of the above is a particularly acceptable solutions.
But at the risk of getting myself into trouble, this and a bunch other
dependency problems are addressed in SQL2005.

-Raymond

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : User Defined Function as a Computed Column