0

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```
wielebny
  • 9
  • 2
  • The correct syntax for inserting data from an exec is `INSERT INTO... EXEC`; not `INSERT INTO ... SELECT ... FROM EXEC`. – Thom A Mar 29 '22 at 09:31
  • 2
    Of course, the real question is, why are you trying to use `xp_cmdshell` at all here. Honestly, it seems like you should be using something *else* to consume your log file data and `INSERT` it into your table; T-SQL isn't do anything scripting language, and trying to use it like one will always end up with difficulties. – Thom A Mar 29 '22 at 09:33
  • 1
    Another way to execute it remotely is `IP_LINKED_SERV.master.sys.xp_cmdshell`. I second the question: why would you ever want to do this? – Charlieface Mar 29 '22 at 10:03
  • I need use SELECT because the query is run on 700 objects (AT ['+@Ip+']), so I enter in first column the object number and in the second column result xp_cmdshell on object (the contents of the log file). This is part of the procedures. Maybe there are some other ways to load the contents of the file on the linked servers? SQL EXPRESS version on objects and I cannot add task scheduler locally in Windows. On a Central Server i have a standard edition and administrator rights in Windows. – wielebny Mar 29 '22 at 11:33
  • What do you mean the query is run on 700 objects? `xp_cmdhsell` is 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. – Thom A Mar 29 '22 at 11:34
  • I have one Central Server (SQL Standard) and 700 clients (other computers with SQL EXPRESS) which are monitored. In Central Server a Linked this 700 objects and I need to get information from logs. – wielebny Mar 29 '22 at 11:46
  • with `BULK INSERT` you can load a file over NFS. you receive all lines of the file directly in a table to analyse – jjdesign Mar 29 '22 at 12:46
  • How can I use BULK INSERT to linked servers ? BULK INSERT LogTable FROM 'd:\log\file.log' WITH ( DATA_SOURCE = 'IP') Did not work I also use the mechanisms for service control, here I need the result EXEC xp_servicecontrol N'querystate ', N'servicename' – wielebny Mar 31 '22 at 08:44
  • no linked servers. with UNC name `BULK INSERT logTable FROM '\\serverName\ShareName\Path\FileName'`. you can have it executed with SQL Agent with any windows login that would have access to all your shared folders – jjdesign Apr 01 '22 at 09:45
  • I'm not connected to linked servers from Central server with UNC, only MSSQL :( – wielebny Apr 02 '22 at 10:14
  • if you want to make it work, you need to first INSERT INTO #someTempTable(ShopNo,Line) EXEC ... and then copy it to your main table together with your select 998 id – siggemannen Jul 30 '23 at 15:43

0 Answers0