|
|
Posted by teddysnips on 10/10/05 17:23
SQL Server 2000 8.00.760 (SP3)
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
Edward
Navigation:
[Reply to this message]
|