Return error messages from xp_cmdshell without using temp tables
Hi Guys n Gals,
I've been asked to develop an SP that can run dos commands and isqlw. The SP has to return any error messages from the operating system or SQL Server. But I can't create temporary tables in the SP to store the error messages. The bare bones SP looks like this so far ... any ideas?
Cheers,
Pete
-- This SP is for running xp_cmdshell and reporting back and operating system error messages
CREATE proc sp_RunCmd
@MyCmd varchar (50), @Result varchar(250) OUTPUT
AS
-- @MyCmd - This will be a DOS cmd or isqlw statement e.g. @MyCmd = 'mkdir MyDir ' Or what ever
-- Normally I'd do this but we can't create temp tables
CREATE TABLE #TempTable(Message VARCHAR(250)) -- can't do this
INSERT #TempTable
EXEC master.dbo.xp_cmdshell @MyCmd
SELECT @Result = Message FROM #TempTable
I've been asked to develop an SP that can run dos commands and isqlw. The SP has to return any error messages from the operating system or SQL Server. But I can't create temporary tables in the SP to store the error messages. The bare bones SP looks like this so far ... any ideas?
Cheers,
Pete
-- This SP is for running xp_cmdshell and reporting back and operating system error messages
CREATE proc sp_RunCmd
@MyCmd varchar (50), @Result varchar(250) OUTPUT
AS
-- @MyCmd - This will be a DOS cmd or isqlw statement e.g. @MyCmd = 'mkdir MyDir ' Or what ever
-- Normally I'd do this but we can't create temp tables
CREATE TABLE #TempTable(Message VARCHAR(250)) -- can't do this
INSERT #TempTable
EXEC master.dbo.xp_cmdshell @MyCmd
SELECT @Result = Message FROM #TempTable
ASKER
Its a replica database and read only
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello Pete_Burke,
you can create temp tables as physically they are created in the tempdb. and there is no other way other than using temp tables / table variables
now the wn is , if this is a read only db, how can u create an sp there
Aneesh R.
you can create temp tables as physically they are created in the tempdb. and there is no other way other than using temp tables / table variables
now the wn is , if this is a read only db, how can u create an sp there
Aneesh R.
Any Why you dont want to create #Temp tables? There is no other way round to deal with this is to redirect the output to a file and read that file from SP.
Sorry a statement error in my comments.
Any Why you dont want to create #Temp tables? There is no other way round to deal with this except that you redirect the output to a file and read that file from SP and parse the text.
Any Why you dont want to create #Temp tables? There is no other way round to deal with this except that you redirect the output to a file and read that file from SP and parse the text.
why not?