October 21, 2003 at 7:21 am
I am trying to find out the ms-sql product versions for each linked server I have. This is the following statement I would like to run and generate a report from.
SELECT SERVERPROPERTY('productversion'),
SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
What is the best way of doing this? OpenQuery, or creating a table of all my servers and using a cursor. Any suggestions?
October 21, 2003 at 4:00 pm
I think you'll need to use OPENQUERY and a cursor (and a temporary table).
CREATE TABLE #ls(
Srv_Name varchar(128),
Srv_ProviderName varchar(128),
Srv_Product varchar(128),
Srv_DataSource varchar(4000),
Srv_ProviderString varchar(4000),
Srv_Location varchar(4000),
Srv_Cat varchar(128))
CREATE TABLE #LSRept(
ServerName varchar(128),
ProductVersion varchar(128),
ProductLevel varchar(128),
Edition varchar(128))
INSERT #ls
EXEC sp_linkedservers
DECLARE @Name varchar(128), @sql varchar(900)
DECLARE cur CURSOR FOR
SELECT Srv_Name
FROM #ls
WHERE Srv_Product = 'SQL Server'
--AND Srv_Name <> @@SERVERNAME
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT *
FROM OPENQUERY(' + @Name + ',''SELECT
SERVERPROPERTY(''''SERVERNAME''''),
SERVERPROPERTY(''''PRODUCTVERSION''''),
SERVERPROPERTY(''''PRODUCTLEVEL''''),
SERVERPROPERTY(''''EDITION'''')'')'
INSERT #lsRept
EXEC(@sql)
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur
SELECT *
FROM #lsrept
DROP TABLE #ls
DROP TABLE #lsrept
--Jonathan
--Jonathan
October 22, 2003 at 7:46 pm
You might enjoy trying this too:
DECLARE @missingLink varchar(1600)
SELECT @missingLink = coalesce( @missingLink, ' ' ) + '
INSERT @tbl ( lnkservername, productdata )( SELECT * FROM OPENQUERY( ' + srvname
+ ' , ''SELECT ''''' + srvname + ''''', ''''ver: ''''
+ Cast( SERVERPROPERTY(''''productversion'''') as char(15)) + ''''lvl: ''''
+ Cast( SERVERPROPERTY (''''productlevel'''') as char(10))
+ ''''ed: '''' + Cast( SERVERPROPERTY (''''edition'''') as varchar(20))'')' + ') '
FROM master..sysservers
WHERE srvid > 0 --AND isremote = 1 AND rpcout = 1
AND dataaccess = 1
SET @missingLink = 'DECLARE @tbl TABLE ( lnkservername varchar(20), productdata varchar(60) null )
SET NOCOUNT ON ' + @missingLink + '
SELECT lnkservername, productdata FROM @tbl'
EXEC( @missingLink )
HTH 😉
Edited by - Mongo_KS on 10/22/2003 8:55:29 PM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply