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

On my way to getting the latest SQL Server installed on one of my Dev PC’s- I ran across the issue of getting prompt for:

msoledbsql.msi

What the hell…

ODBC Driver 17 is what that is about. Apparently, the SQL installed is not smart enough to know that one already has ODBC 17 SQL Driver installed and runs into a major “cannot resolve” the problem. The end result is that one does not get the SQL instance installed…

But after reading lots of “not really helping” blurbs from the Internet, the Solution is simple… well mostly.

You have to uninstall the “Microsoft ODBC Driver 17 for SQL Server” – one can find it on windows 10 in the APPS & Features. Uninstall it and redo the SQL install. One should have better luck with moving forward with getting SQL installed…

~ScottGeek