BCP and Clustered SQL Oh the Errors

I got a curious message concerning problems that someone from work was having issues getting a relatively simple BCP (Bulk Copy for SQL) to work.

So here we go:

bcp "select top 5 [Data] from [ADataBase].[dbo].[ATable]  where [ProcessedTime] <= GetDate()-1 and MessageType=''SomeProduct'' order by [ProcessedTime] desc for xml raw, root(''MyexportRoot'')"' +
    ' QUERYOUT ""B:\BCPBatch\Data\sample.xml"" -T -c  -S TheSQL\Instance';

It’s a little complicated… Basically pull data from a table in DB on a SQL Instance… and output as xml to an xml file.
After spending, a few hours, getting all kinds of errors about drive not found, path not found, can’t open the file… bah bah bah. It’s like what the hell?!

You take that statement and execute it on a console window or powershell window… and no problems, but you get into SSMS, and in a query window… and nothing but errors… So something like this:

EXECUTE sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO  

  declare @cmd nvarchar(512);
-- Please note that the fully qualified table name is needed
select @cmd = 'bcp "select top 5 [Data] from [ADataBase].[dbo].[ATable] where [ProcessedTime] <= GetDate()-1 and MessageType=''SomeProduct'' order by [ProcessedTime] desc for xml raw, root(''MyexportRoot'')"' + ' QUERYOUT ""B:\BCPBatch\Data\sample.xml"" -T -c -S TheSQL\Instance';
exec xp_cmdshell @cmd;
go

EXECUTE sp_configure 'xp_cmdshell', 0;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO  

EXECUTE sp_configure 'show advanced options', 0;  
GO

And let the errors happen… and they do. What’s the issue here… well nothing really with the code, but with the fact that my TheSql\Instance was in fact Clustered!

So why is that a problem? It’s this part here:

QUERYOUT “”B:\BCPBatch\Data\sample.xml”” – the syntax is correct, but on a clustered server file locations are problematic! Meaning, the cluster does not normally have full access to all of the drives. Now, will a network share work? Not sure. I didn’t test that.

The solution I suggested was to move to another SQL instance that was not clustered, and the script worked.
~SG

Leave a Reply Cancel reply