help with returning value from serverproperty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with returning value from serverproperty

Author

Topic

leoc50
Yak Posting Veteran

54 Posts

Posted - 2012-03-08 : 14:48:05

I'm walking thru a list of link servers collecting info such as servername, instance, etc...
This code doesn't work but I'm out of ideas on this issue

declare @server nvarchar(20), @cmd nvarchar(300)
--select @cmd= 'set @server = SELECT * FROM OPENQUERY(LinkServer,''SELECT SERVERPROPERTY(''''ServerName'''')'')'
select @cmd = 'set @server= LinkServer.master.dbo.sp_executesql N''SELECT SERVERPROPERTY(''''ServerName'''')'''
exec sp_executesql @cmd, N'@server nvarchar', @server output
print @cmd
print @server

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT SERVERPROPERTY('ServerName')'.

thanks for any ideas!

- lec

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 14:55:52

whats the need of dynamic sql here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

leoc50
Yak Posting Veteran

54 Posts

Posted - 2012-03-08 : 15:31:28

We have a good number of reports built on the fly from the linked servers list, I'm trying to get 'running/not-running services' for all servers; this serverproperty is just a small part, BUT I need to be able to capture ina variable the return value (not code)!

- lec

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-08 : 15:42:14

Unless I'm missing something:

SELECT data_source FROM sys.servers WHERE is_linked=1

Should give you the list of all your linked servers with server name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources