linked server problem with @@SERVERNAME

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.droptable.comThe 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
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:4c9f835ab5bd47dabc4d2b22996d8564@.SQ
droptable.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.droptable.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.droptable.com|||Ah ok, the xxx.xxx.xxx.xxx defined the ip adress, i jst thought that would
be the fout-partname of the object.
Nevertheless i would perfer the pattern as i mentioned in my previous post
with the inline set off the variable.
Jens.
"The Gekkster via droptable.com" <forum@.droptable.com> schrieb im
Newsbeitrag news:1c50dd0567b84192aea0ff47adf7d99a@.SQ
droptable.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.droptable.com