Linked Servers and SERVERPROPERTY

Question

Post reply

Linked Servers and SERVERPROPERTY

applebyte

Ten Centuries

Points: 1028
More actions
November 29, 2010 at 8:34 am

#231750

Hi folks,
I am trying to use the “SERVERPROPERTY” function in a stored procedure to get data from a linked server and then save that data in a temp table.
My temp table:
CREATE TABLE #Serverproperties
( PropertyName nvarchar(50)NOT NULL
,PropertyValue nvarchar(250)
)
----------------------------------------------------------------
My procedure:
CREATE PROCEDURE dbo.usp_LinkedServer_Qry1
(
@LinkedServer varchar(50)
,@port varchar (10)
)
AS
BEGIN
DECLARE @sql varchar (1000)
SET @SQL = 'INSERT #Serverproperties SELECT (PropertyName,PropertyValue)
FROM OpenQuery([' + @LinkedServer + ',' + @Port + '],SELECT SERVERPROPERTY(''MachineName'') as "machine_Name"'
EXEC @SQL
END
-------------------------------------------------------------------------
Parameters for the stored procedure are servername and port#:
EXEC dbo.usp_LinkedServer_Qry1 SERVERX15,1688
Execution results in this error:
Msg 203, Level 16, State 2, Procedure usp_LinkedServer_Qry1,
The name ' SELECT (PropertyName,PropertyValue)
FROM OpenQuery([SERVERX15,1688],SELECT SERVERPROPERTY('MachineName') as "machine_Name"' is not a valid identifier.
-----------------------------------------------------------------------------------
I’m not sure what this error is telling me. Any assistance would be greatly appreciated.
thx
-jon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 1

You've incorrectly built your command.

Basically you want to execute this:

INSERT #Serverproperties

SELECT 'MachineName', SERVERPROPERTY('MachineName')

To do this on a linked server, look at this: http://qa.sqlservercentral.com/Forums/Topic584711-145-1.aspx

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

applebyte Ten Centuries Points: 1028 More actions · Answer 2

Thank you.

I was able to retreive data from the openquery statement using "SELECT *" which returns one value.

I'm having problems inserting data into my temp table because the table has two columns: PropertyName and PropertyValue. The code will work if I take it out of the @sql block and execute it from a Query window.

Any suggestions? I'm sure it's something simple but I just don't see what is wrong.

This code produces an error: Incorrect syntax near 'productversion'.

CREATE PROCEDURE dbo.usp_LinkedServer_Qry1

(

@LinkedServer varchar(50)

,@port varchar (10)

)

AS

BEGIN

declare @sql nvarchar (1000)

set @sql = N'INSERT #Serverproperties

SELECT 'productversion'

,Version

FROM openquery ([' + @linkedServer + ',' + @port + '], ' + '''SELECT SERVERPROPERTY (''''ProductVersion'''') AS [Version]'')'

EXEC (@SQL)

END

------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 3

print out your SQL, look at it, execute it in stages. You are getting a syntax error here.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

applebyte Ten Centuries Points: 1028 More actions · Answer 4

My code is working. Thanks very much. Quotation marks will be the death of me...

-jon

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 5

Quotes are hard. It's why printing out the string is a good idea.

Glad it's working.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com