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.
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 = @@ROWCOUNTif @Count > 0EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'xp_cmdshell', 1RECONFIGUREset @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 ' + @@SERVERNAMEBEGIN TRANEXEC master..xp_cmdshell @CommandSQLIF @@ERROR <> 0 and @@TRANCOUNT <> 0ROLLBACK TRANELSE IF @@ERROR = 0 and @@TRANCOUNT > 0COMMIT 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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! |
|
|
|
|
|
|
|