Skip to content

Quick test to see if the bcp command works on executing an SQL script in SQL Management Studio

November 10, 2015

Today, I spent the majority of the afternoon, debugging and fixing the bcp command in SQL to see if it can generate the headers of a view table in the local C drive in the archive folder. As instead of hardcoding the file path into the SQL script, I would like to pass in the file path as an SQL parameter as described with the method from the previous post I made today. See https://mashupweb.wordpress.com/2015/11/10/setup-and-select-a-full-path-based-on-a-dialog-box-in-c/

The problem was that it now didn’t create this header csv file for some strange reason whilst it did beforehand. Therefore I had to debug the SQL script by hardcoding the @path to see where it went wrong.

This is what I wrote.

declare @path nvarchar(MAX) = null
set @path = ‘C:\\archive\\test.csv’
declare @sqlbefore varchar(8000)
select @sqlbefore = ‘bcp “DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ‘+”’,”’+’ , ””) + column_name from dbName.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=”vw_viewname”; select @colnames;” queryout ‘ + @path + ‘-HeadersOnly.csv -c -T -S ‘ + @@servername
exec master..xp_cmdshell @sqlbefore

This worked perfectly with the output shown: –

NULL
Starting copy…
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 94 Average : (10.64 rows per sec.)
NULL

But when I created the same folder, but in the desktop, it didn’t work which was quite strange.

declare @path nvarchar(MAX) = null
set @path = ‘C:\\Users\\lee_admin\\Desktop\\archive\\test.csv’
declare @sqlbefore varchar(8000)
select @sqlbefore = ‘bcp “DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ‘+”’,”’+’ , ””) + column_name from dbName.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=”vw_viewname”; select @colnames;” queryout ‘ + @path + ‘-HeadersOnly.csv -c -T -S ‘ + @@servername
exec master..xp_cmdshell @sqlbefore

This gave the output.

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file
NULL

And this occured for folders that didn’t exist which was fine, you had to manually create the fodler but for folders that did exist, this shouldn’t of happened. So I read up on the web like http://www.sqlservercentral.com/Forums/Topic237238-8-1.aspx but there was no direct answer, after doing the specific test several times, it turns out that if you add permissions for the fodler you create in Desktop with fill admin for the user group Users, it worked! So it is a file permission problem!

Lesson learnt – It is hard to debug why the bcp error doesn’t execute properly, you need to do a hardcoded test like above to see what it is output  in the Message window of SQL Server Management Studio.

And for bcp, if you get the Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file error, it may be a file permission issue to unable to save the file.

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: