BCP and cmd_shell or CmdExec

Transact-SQL
1

We have a program that uses a custom stored procedure to import data from a flat file. It uses xp_cmdshell and the bcp utility. The software has recently been upgraded and we are doing testing on this process and had to set up the proxy on the new SQL server so users can run this procedure. The account used is not a domain or local administrator. Still I would prefer not to use this method so I wanted to use a job and CmedExec. Below is my command string. The problem is I am not sure how to pass the variables into the job.

select @cmdstr=	'bcp '+db_name()+'..stored procedure in '+ @DataPath + @FileName + ' -S'+@@ServerName+' -UUsername -Ppassword -f '+@FMTFILE
EXEC @retval=master..xp_cmdshell @cmdstr

The filename will change each time it is run by the user and the location of the format file and server could change so I don't want to hard code anything into the statement.

Is this possible or is my best option to use a secure xp_cmdshell proxy, if there is such a thing.

2

Could you use BULK INSERT (i.e. 100% within SQL) instead of having to xp_cmdshell to run BCP ?

3

Thanks Kristen - There was some office politics involved because a co-worker had programmed it with xp_cmdshell. But I just went ahead and changed it to Bulk Insert. Xp_cmdshell was also being used to make sure the file exists in the proper location so I replaced that as well with xp_fileexist.

1 Like
4

You've booted the use of xp_CmdShell so let me ask... are you aware that, as a default, both BCP and BULK INSERT default to allowing up to 10 errors prior to failure or alert of failure? Otherwise, it'll just "consume" the error. The output that contains the error will be lost. What are you doing about that little nuance?

And, yes... you can set BULK INSERT to capture the errors but the results are cryptic.

Should be a great subject for the "office politics" because there is actually a way to capture and analyze the human readable output using xp_CmdShell and SQLCMD.