Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 bcp command hangs stored procedure

Author  Topic 

Supriya Krishna
Starting Member

1 Post

Posted - 2013-09-30 : 17:29:46
I have a problem where bcp command is called and executed successfully sometimes but fails randomly.

My stored procedure looks like this:

select @SQL = 'select * from [' + @RepositoryName + '] where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')'

execute(@SQL)

set @Count = @@ROWCOUNT

if @Count > 0


EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

set @CommandSQL= 'bcp "SELECT * FROM ['+DB_NAME()+'].[dbo].[' + @RepositoryName + '] where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')' " queryout "C:\Export\' + @ExportFileName + '.csv" -c -t, -T -S ' + @@SERVERNAME

BEGIN TRAN
EXEC master..xp_cmdshell @CommandSQL

IF @@ERROR <> 0 and @@TRANCOUNT <> 0
ROLLBACK TRAN

ELSE IF @@ERROR = 0 and @@TRANCOUNT > 0
COMMIT TRAN

set @SQL='delete from [' + @RepositoryName + '] with (Rowlock) where ( IsNumeric([' + @PhoneField + ']) = ' + CONVERT(varchar(1), @FALSE) + ' OR LEN([' + @PhoneField + ']) <> ' + CONVERT(varchar(2), @ValidLength) + ')'

execute(@SQL)


The above procedure is called 4 times from another procedure by passing appropriate parameters. Most of the time, it executes successfully for 2 times before failing. When I try to debug, it always hangs at EXEC master..xp_cmdshell @CommandSQL. The task manager shows bcp.exe as running at this point. Interestingly, the required rows are exported using the bcp command, but the stored procedure does not move further until bcp.exe is manually stopped from the task manager.

Any help is appreciated, thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-30 : 19:14:51
You need to wait until it finishes. It will move to the next command in the stored procedure when bcp.exe is done exporting the data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Maa421S
Starting Member

3 Posts

Posted - 2014-06-09 : 11:10:14
OP did you ever figure this out? I am having the same issue. Running BCP from SP and it randomly fails and hangs.

Thanks!
Go to Top of Page
   

- Advertisement -