linked server problem with @@SERVERNAME

14 May 2005 7:33 AM

The Gekkster via SQLMonster.com

Hey all,

In a stored procedure that retrieves data from a linked server, I want to
do a simple 'test' for availability before attempting to carry out the SP's
tasks: The linked server is 'defined' as its IP.

DECLARE @serverUP varchar(100)
SELECT @serverUP = ( SELECT @@SERVERNAME
FROM [xxx.xxx.xxx.xxx] )

BEGIN
IF LEN(@serverUP) > 0

I can use the IP like this in SELECTs when a four-part name is used, like
[xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
"Invalid object name 'xxx.xxx.xxx.xxx'."

If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME for
the linked server. So I must be missing something here about the reference
in the FROM clause; or can I not even use @@SERVERNAME like this?

Any ideas, or suggestions for a better way to check for 'availability'?

Thanks.

--
Message posted via http://www.sqlmonster.com

14 May 2005 7:52 AM

Jens Süßmeyer

The brackets should be used t identify a single expression in the four part
name becasue sql server will else assume that thi is a local object which
name is [xxx.xxx.xxx.xxx], so you hould go for that:

xxx.xxx.xxx.xxx or [xxx].[xxx].[xxx].[xxx]

Furthmore I would write the query as below:

SELECT @serverUP = @@SERVERNAME FROM [xxx.xxx.xxx.xxx]

HTH, Jens Suessmeyer.

----
http://www.sqlserver2005.de
---

Show quoteHide quote

"The Gekkster via SQLMonster.com" <forum@nospam.SQLMonster.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@SQLMonster.com...
> Hey all,
>
> In a stored procedure that retrieves data from a linked server, I want to
> do a simple 'test' for availability before attempting to carry out the
> SP's
> tasks: The linked server is 'defined' as its IP.
>
> DECLARE @serverUP varchar(100)
> SELECT @serverUP = ( SELECT @@SERVERNAME
> FROM [xxx.xxx.xxx.xxx] )
>
> BEGIN
> IF LEN(@serverUP) > 0
>
> I can use the IP like this in SELECTs when a four-part name is used, like
> [xxx.xxx.xxx.xxx].catalog1.dbo.table1 but the above generates an error:
> "Invalid object name 'xxx.xxx.xxx.xxx'."
>
> If I run 'EXEC sp_linkedservers' locally it returns the proper SVR_NAME
> for
> the linked server. So I must be missing something here about the reference
> in the FROM clause; or can I not even use @@SERVERNAME like this?
>
> Any ideas, or suggestions for a better way to check for 'availability'?
>
> Thanks.
>
> --
> Message posted via http://www.sqlmonster.com

14 May 2005 8:28 AM

The Gekkster via SQLMonster.com

Thanks, Jens. With some more trial and error I found that this works to get
what I was after:

SELECT @serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )

I appreciate your feedback.

--
Message posted via http://www.sqlmonster.com

14 May 2005 11:56 AM

Jens Süßmeyer

Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-part´name of the object.

Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.

Jens.

Show quoteHide quote

"The Gekkster via SQLMonster.com" <fo***@SQLMonster.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@SQLMonster.com...
> Thanks, Jens. With some more trial and error I found that this works to
> get
> what I was after:
>
> SELECT @serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
>
> I appreciate your feedback.
>
> --
> Message posted via http://www.sqlmonster.com