I've been working on a test system and the following UDF worked fine.
It runs in the "current" database, and references another database on
the same server called 127-SuperQuote.
CREATE FUNCTION fnGetFormattedAddress(@.WorkID int)
RETURNS varchar(130)
AS
BEGIN
DECLARE
@.Address1 As varchar(50)
@.ReturnAddress As varchar(130)
SELECT
@.Address1 = [127-SuperQuote].dbo.tblCompany.Address1
FROM
[Work] INNER JOIN
[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =
[127-SuperQuote].dbo.tblCompany.CompanyID
WHERE
[Work].WorkID = @.WorkID
IF @.Address1 IS NOT NULL
SET @.ReturnAddress = @.ReturnAddress + @.Address1 + CHAR(13)+ CHAR(10)
RETURN @.ReturnAddress
END
So now the system has gone live and it turns out that the live
"SuperQuote" database is on a different server.
I've linked the server and changed the function as below, but I get an
error both in QA and when checking Syntax in the UDF builder:
The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than the
maximum number of prefixes. The maximum is 3.
CREATE FUNCTION fnGetFormattedAddress(@.WorkID int)
RETURNS varchar(130)
AS
BEGIN
DECLARE
@.Address1 As varchar(50)
@.ReturnAddress As varchar(130)
SELECT
@.Address1 = Zen.SuperQuote.dbo.tblCompany.Address1
FROM
[Work] INNER JOIN
Zen.SuperQuote.dbo.tblCompany ON [Work].ClientID =
Zen.SuperQuote.dbo.tblCompany.CompanyID
WHERE
[Work].WorkID = @.WorkID
IF @.Address1 IS NOT NULL
SET @.ReturnAddress = @.ReturnAddress + @.Address1 + CHAR(13)+ CHAR(10)
RETURN @.ReturnAddress
END
How can I get round this? By the way, I've rather simplified the
function to ease readability. Also, I haven't posted any DDL because I
don't think that's the problem!
Thanks
Edwardteddysnips@.hotmail.com wrote:
[...]
Alias, you dolt!
Sorry if I've wasted anyone's time.
Edward|||Thanks for wasting even more of everyone's time by not indicating your mistake and having this useless thread replicated to dozens of other forums!
From http://www.developmentnow.com/g/95_...ximum-is-3-.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
