Set output value to variable using 'execute' with a linked server in t-sql?

• lassell

  Ten Centuries

  Points: 1215

  More actions

  May 6, 2016 at 5:27 pm

  #326115

  Hi,

  I wonder if anyone can help?

  I am trying to set an output value to variable using 'execute' with a linked server. However, I seem only to be able only to select the value not store it in a variable and then therefore make use of it?

  The code so far is:

  DECLARE @Script nvarchar(max) =

  N'

  DECLARE @output TABLE

  (

  AvailMbytes VARCHAR(max)

  )

  DECLARE @sql NVARCHAR(2000)

  SET @sql = ''powershell.exe Get-Counter ''''\Memory\Available MBytes''''''

  INSERT @output

  EXEC xp_cmdshell @sql

  UPDATE @output

  SET AvailMbytes = REPLACE(RTRIM(LTRIM(AvailMbytes)), '''', '''')

  DELETE @output

  WHERE AvailMbytes IS NULL

  OR AvailMbytes = ''''

  OR AvailMbytes LIKE ''—-%''

  OR AvailMbytes LIKE ''timestamp%''

  OR AvailMbytes LIKE ''%\\%''

  OR AvailMbytes = ''-----------------------''

  OR AvailMbytes = ''--------- --------------''

  begin select AvailMbytes from @output

  end; ';

  --EXECUTE (@Script) AT SQL_11 -- This works, but only selects the output

  declare @AvailMb varchar(50)

  EXECUTE (@Script, @AvailMb OUTPUT) AT SQL_11 -- This doesnt work, it just returns NULL

  select @AvailMb

  Any help appreciated. txtPost_CommentEmoticon(':-D');

  Thanks, Phil

• lassell

  Ten Centuries

  Points: 1215

  More actions

  May 6, 2016 at 9:00 pm

  #1877300

  Please ignore I worked it out in the end.... All I needed to do was perform an insert into a temp table. Simple!

  INSERT @AvailMbytes EXEC (@Script) AT SQL_11

  If anyone interested the solution is below:

  DECLARE @Script nvarchar(max) =

  N'

  DECLARE @output TABLE

  (

  AvailMbytes VARCHAR(max)

  )

  DECLARE @sql NVARCHAR(2000)

  SET @sql = ''powershell.exe Get-Counter ''''\Memory\Available MBytes''''''

  INSERT @output

  EXEC xp_cmdshell @sql

  UPDATE @output

  SET AvailMbytes = REPLACE(RTRIM(LTRIM(AvailMbytes)), '''', '''')

  DELETE @output

  WHERE AvailMbytes IS NULL

  OR AvailMbytes = ''''

  OR AvailMbytes LIKE ''—-%''

  OR AvailMbytes LIKE ''timestamp%''

  OR AvailMbytes LIKE ''%\\%''

  OR AvailMbytes = ''-----------------------''

  OR AvailMbytes = ''--------- --------------''

  select AvailMbytes from @output

  '

  DECLARE @AvailMbytes TABLE

  (

  AvailMbytes VARCHAR(max)

  )

  INSERT @AvailMbytes EXEC (@Script) AT SQL_11

  SELECT * FROM @AvailMbytes