Linked servers, results query xp_cmdshell to table migration SQL Server 2005 to SQL Server 2019

322 Views Asked by At

I'm migrating procedures from Microsoft SQL Server 2005 to Microsoft SQL Server 2019 and I got stuck while trying insert query result xp_cmdshell in linked servers to table

I'm out of ideas

Old solution in Microsoft SQL Server 2005:

INSERT INTO LOGTABLE (ShopNo,Line) SELECT 1, OUTPUT FROM openquery ([IP_LINKED_SERV],'set fmtonly off; exec master..xp_cmdshell ''type d:\log\file.log'' ')

Microsoft SQL Server 2019 gives me error:

Msg 11519, Level 16, State 1, Procedure
sp_describe_first_result_set, Line 1 [Batch Start Line 0] The metadata could not be determined because statement 'exec master..xp_cmdshell 'type d:\log\file.log'' invokes an extended stored procedure.`

I found a way how to do xp_cmdshell in SQL Server 2019 at linked servers

EXEC ('set fmtonly off;exec master..xp_cmdshell ''type d:\log\file.log'' ') AT [IP_LINKED_SERV]

However I can't insert this results in table

INSERT INTO LOGTABLE (ShopNo,Line) SELECT '998', OUTPUT FROM EXEC ('set fmtonly off;exec master..xp_cmdshell ''type d:\log\file.log'' ') AT [IP_LINKED_SERV]

Incorrect syntax near the keyword 'EXEC'.

part of the procedure in sql2005:

DECLARE TableCursor CURSOR FOR
SELECT IP, SqlUser, SqlPass, Object   FROM ..ObjectInfo

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Ip, @SqlUser, @SqlPass, @Object
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Object
SELECT @PARAMS = ' @tmp_object varchar(5) OUTPUT'
set @SQL = 'INSERT INTO LOGTABLE (Object,Line) SELECT @tmp_object, output FROM  openquery (['+@Ip+'],''set fmtonly off;
exec master..xp_cmdshell ''''type d:\log\file.log''''
'')' 

BEGIN TRY
EXECUTE sp_executesql @SQL,@PARAMS, @tmp_object = @Object OUTPUT
END TRY
BEGIN CATCH
INSERT INTO LOGTABLE (Object, Line) VALUES(@Object, '-error')
END CATCH```
0

There are 0 best solutions below