June 25, 2012 at 2:13 pm
I have a problem with xp_cmdshell that I am not sure how to fix it... I am trying to export the data from sql query into text file using bcp using xp_cmdshell.
I am able to export from Query Analzyer by using the query:
EXEC master..xp_cmdshell'bcp "Select * FROM MyDB.dbo.MyTable" queryout "C:\test.txt" -c -T -S MyServer'
However, I am not able to do so using the Stored Procedure that I have created... Stored Procedure does not fail when I execute, but does not create the file...
Create PROCEDURE [dbo].[ExportDataTest]
@TableNameVarchar(255),--Table Name
@FilePathVARCHAR(1000),--Full FilePath
@ResultINT = 0 OUTPUT,--Error Number
@ErrDescVARCHAR(500) = '' OUTPUT--Error Description
AS
DECLARE
@SQLCmdVarchar(max),
@ColumnsVarchar(max),
@DBNameVarchar(255),
@ServerName varchar(255)
SET NOCOUNT ON
BEGIN TRY
IF @TableName='' OR @TableName IS NULL
Begin
SET @Result = -1
SET @ErrDesc = 'Table Name can not be null'
RETURN
End
IF @FilePath='' OR @FilePath IS NULL
Begin
SET @Result = -1
SET @ErrDesc = 'File Path can not be null'
RETURN
End
--IF @ColumnDelimiter IS Null
--SET @ColumnDelimiter=','
Set @DBName=DB_NAME()
Set @ServerName = @@SERVERNAME
--Export the data in text file
Set @SQLCmd = ''
Set @SQLCmd = '''bcp ' + '"Select * FROM ' + @DBName + '.dbo.' + @TableName + '" queryout "'
+ @FilePath + '"' + ' -c ' + '-T -S ' + @ServerName + ''''
Select @SQLCmd
EXEC master.dbo.xp_cmdshell @SQLCmd
Select @SQLCmd
--EXECUTE sp_executesql @SQLCmd
Select 'TEST AGAIN!'
END TRY
BEGIN CATCH
SET @Result = Error_Number()
SET @ErrDesc = Error_Message()
RETURN
END CATCH
SET NOCOUNT OFF
SET @Result = 0
RETURN 0
Thanks in advance for taking time in going through this...
June 25, 2012 at 2:59 pm
Laura it's probably permissions;
since xp_cmdshell requires elevated permissions, try adding EXECUTE AS and see if that resolves it:
--the EXECUTE AS must be a user in the database...not a login
CREATE procedure [dbo].[ExportDataTest]
WITH EXECUTE AS 'superman' --myDomain\Lowell or YourDomain\Laura
Lowell
June 25, 2012 at 3:18 pm
Thanks Lowell... somehow I am not included in the users list in the database. I tried to add it but was not able to... I tried using With Execute as 'dbo' but that did not work...
June 25, 2012 at 3:23 pm
i end up creating a special login/user for the whole execute as stuff:
it works well for me.
--first we need a sysadmin role with no login, which will beused
--for execution context in the DDL trigger.
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'superman' AND type = 'S')
BEGIN
--create our super user
CREATE LOGIN [superman]
WITH PASSWORD=N'NotARealPassword',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'
--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [superman] DISABLE
END
GO
CREATE USER superman for LOGIN superman
Lowell
June 25, 2012 at 3:59 pm
this did not work for me... I creted SuperUser using the query above and granted Execute on xp_cmdshell... still no luck. I recreated the database using "sa" account and added my login as user and dbo priviledges... I tried Execute as 'SuperUser' and also as 'myUserAccount' .. did not work... Something I am doing wrong... Thanks a lot for your help Lowell.
June 25, 2012 at 4:03 pm
I did this too:
EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
June 25, 2012 at 7:18 pm
I am getting the error below:
Msg 916, Level 14, State 1, Procedure MyProc, Line 0
The server principal "SuperUser" is not able to access the database "MyDB" under the current security context.
I do not have SuperUser as user in MyDB at this time...
June 25, 2012 at 7:24 pm
as far as i know, the EXECUTE AS must use a user for permissions, so you'd need to add that superUser as a user so that you can use the Execute As part in your proc.
Lowell
June 25, 2012 at 7:47 pm
Do you have the xp_CmdShell proxy setup?
Also, I have to ask, where is "C:\"? On the server or on your local machine?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2012 at 8:02 pm
C drive is on the server and no I have not set-up any proxy... Thanks for looking into this guys.
June 26, 2012 at 7:57 am
It's amazing if you run it and actually look at what is contained in the error description variable.
DECLARE @Result INT,
@ErrDesc VARCHAR(500)
;
EXEC [dbo].[ExportDataTest] 'Tally','C:\Tally.txt',@Result OUT, @ErrDesc OUT
SELECT @Result,@ErrDesc
Results:
-----------------------------------------------------------------------
'bcp "Select * FROM tempdb.dbo.Tally" queryout "C:\Tally.txt" -c -T -S myservernamehere'
----------- -----------------------------------------------------------
214 Procedure expects parameter 'command_string' of type 'varchar'.
(1 row(s) affected)
So, the first fix is to declare @SQLCmd AS VARCHAR(8000) instead of VARCHAR(MAX).
The second fix is that you have one too many sets of single quotes on the BCP command.
The code with both fixes in place looks like the following...
Create PROCEDURE [dbo].[ExportDataTest]
@TableNameVarchar(255),--Table Name
@FilePathVARCHAR(1000),--Full FilePath
@ResultINT = 0 OUTPUT,--Error Number
@ErrDescVARCHAR(500) = '' OUTPUT--Error Description
AS
DECLARE
@SQLCmdVarchar(8000), --CHANGED FROM VARCHAR(MAX) TO VARCHAR(8000)
@ColumnsVarchar(max),
@DBNameVarchar(255),
@ServerName varchar(255)
SET NOCOUNT ON
BEGIN TRY
IF @TableName='' OR @TableName IS NULL
Begin
SET @Result = -1
SET @ErrDesc = 'Table Name can not be null'
RETURN
End
IF @FilePath='' OR @FilePath IS NULL
Begin
SET @Result = -1
SET @ErrDesc = 'File Path can not be null'
RETURN
End
--IF @ColumnDelimiter IS Null
--SET @ColumnDelimiter=','
Set @DBName=DB_NAME()
Set @ServerName = @@SERVERNAME
--Export the data in text file
--Set @SQLCmd = '' --DON'T NEED THIS
Set @SQLCmd = 'bcp ' + '"Select * FROM ' + @DBName + '.dbo.' + @TableName + '" queryout "'
+ @FilePath + '"' + ' -c ' + '-T -S"' + @ServerName + '"' --REMOVED EXTRA SET OF SINGLE QUOTES AND QUOTED SERVER INSTANCE NAME TO HANDLE "\"
Select @SQLCmd
EXEC master.dbo.xp_cmdshell @SQLCmd
Select @SQLCmd
--EXECUTE sp_executesql @SQLCmd
Select 'TEST AGAIN!'
END TRY
BEGIN CATCH
SET @Result = Error_Number()
SET @ErrDesc = Error_Message()
RETURN
END CATCH
SET NOCOUNT OFF
SET @Result = 0
RETURN 0
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2012 at 8:29 am
Thanks a lot Jeff and Lowell. Jeff I was looking everywhere and did not see any issues with the code, but you just made my day... this is great help. Thanks guys.
June 26, 2012 at 8:37 am
Laura_SqlNovice (6/26/2012)
Thanks a lot Jeff and Lowell. Jeff I was looking everywhere and did not see any issues with the code, but you just made my day... this is great help. Thanks guys.
You're welcome, Laura. The next thing to do would be to learn how to use xp_CmdShell in a secure manner. The first question to that end would be... do you have any users/application logins other than DBAs that have "SA" privs? If not, we can easily lock xp_CmdShell down so that it can only be executed in stored procs (stored procs could be executed by the users without the users having privs to execute xp_CmdShell directly) or by the DBAs. That's if you need to do such a thing (most people should).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply