SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Isnull declaration stopping from subscription
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shobhaaradhya
Starting Member

35 Posts

Posted - 10/19/2012 :  06:24:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1585 Posts

Posted - 10/19/2012 :  17:39:31  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000