Sunday, February 19, 2012

...Contains more than the maximum number of prefixes. The maximum is 3.

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

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

No comments:

Post a Comment