Isnull declaration stopping from subscription

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Isnull declaration stopping from subscription

Author

Topic

shobhaaradhya
Starting Member

37 Posts

Posted - 2012-10-19 : 06:24:31

Hi,

I am facing serious issue in one of the script which is causing problem in subscribing the db.

The function is as below.

create FUNCTION [dbo].[Fn_Get_Names]
(
@Data1 decimal(18,9),
@Data2 decimal(18,9),
@Data3 bigint
)
RETURNS @tmptbl TABLE
(
v_rec1 decimal(18,9),
v_rec2 decimal(18,9),
v_rec3 decimal(18,9),
v_rec4 AS (ISNULL(CONVERT(decimal(18,9),(v_rec2+v_rec3-v_rec1)),0))
)

AS

BEGIN

DECLARE @Debug bit
SET @debug = 0

DECLARE d_rec1 decimal(18,9)
DECLARE d_rec2 decimal(18,9)
DECLARE d_rec3 decimal(18,9)

----------------------------------------
code for some calculation here

----------------------------------------

INSERT INTO @tmptbl
(
v_rec1,
v_rec2,
v_rec3
)
VALUES
( d_rec1,
d_rec2,
d_rec3
)
RETURN
END

Below is the error details

The schema script '\\SERVER1\C$\Program Files (x86)\Microsoft SQL Server\MSSQL$DEVELOPMENT\ReplData\ftp\SERVER1$DEV_EMPLOYEE_PubEmp\20121019095745\Fn_Get_Currency_Values_2793.sch' could not be propagated to the subscriber.

The schema script '\\SERVER1\C$\Program Files (x86)\Microsoft SQL Server\MSSQL$DEVELOPMENT\ReplData\ftp\SERVER1$DEV_EMPLOYEE_PubEmp\20121019095745\Fn_Get_Names_2793.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
Extracted file 'Fn_Get_Names_2793.sch'
(Source: TESTSRV\TKT (Agent); Error number: 20151)
---------------------------------------------------------------------------------------------------------------
Line 1: Incorrect syntax near 'isnull'.

If I remove (ISNULL(CONVERT(decimal(18,9),(v_rec2+v_rec3-v_rec1)),0)) from the table declaration it works fine, else the subscription shows the above error.

Is there any workaround for this or any other way to rewrite this script?

Thanks in advance,

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-19 : 17:39:31

What is on the subscribing end? Another SQL Server or a different SQL Provider? In any case, "ISNULL" is NOT a part of the SQL language. It is proprietary to Microsoft (much like "GO" but that's another story for another time). Try replacing "ISNULL" with "COALESCE" and see if that floats your boat.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources