Solved

Return error messages from xp_cmdshell without using temp tables

Posted on 2007-11-15
6
242 Views
Last Modified: 2008-02-01
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            
                        


0
Comment
Question by:Pete_Burke
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20287836
>CREATE TABLE #TempTable(Message VARCHAR(250))       -- can't do this
why not?

0
 

Author Comment

by:Pete_Burke
ID: 20287860
Its a replica database and read only
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20287879
then you need to choose some alternative:
* put your procedure into another database
* try to create table table in another database explicitely
  CREATE TABLE tempdb..#TempTable(Message VARCHAR(250))    

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20287885
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.
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20287898
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.
0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20287907
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.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question