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```
INSERT INTO... EXEC; notINSERT INTO ... SELECT ... FROM EXEC.xp_cmdshellat all here. Honestly, it seems like you should be using something else to consume your log file data andINSERTit into your table; T-SQL isn't do anything scripting language, and trying to use it like one will always end up with difficulties.IP_LINKED_SERV.master.sys.xp_cmdshell. I second the question: why would you ever want to do this?xp_cmdhsellis being used against a file. Are you saying it's run against 700 different files? All the more reason to use a proper ETL tool or middleware.