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```
11
  • The correct syntax for inserting data from an exec is INSERT INTO... EXEC; not INSERT INTO ... SELECT ... FROM EXEC. Commented Mar 29, 2022 at 9: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. Commented Mar 29, 2022 at 9: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? Commented Mar 29, 2022 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. Commented Mar 29, 2022 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. Commented Mar 29, 2022 at 11:34

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.