BCP thru xp_cmdshell from stored procedure:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE
EXEC xp_cmdshell 'bcp database.dbo.table in c:\scheduled.csv -S SERVER\SQLEXPRESS -T -t, -r\n -c -e "error.txt"';
This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.
=================================================================
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
=================================================================
Error message when using BULK INSERT as follows:
BULK INSERT database.dbo.table from 'c:\unscheduled.csv' with
(FIELDTERMINATOR = ',', ERRORFILE = 'c:\error.txt');
Returns the following error message:
=================================================================
Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9
Cannot bulk load because the file "c:\error.txt" could not be opened. Operating system error code 80(The file exists.).
Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9
Cannot bulk load because the file "c:\error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).
=================================================================
The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.
EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2
bcp or bulk insert will not overwrite or append to the error file. If you get an error the first time, and want to rerun your command, you need to either delete these files, or specify a new location for the error file.
|||
Unfourtunetly I am deleting the file, I'm still doing so by hand in testing.
On bcp thru xp_cmdshell it does not generate an errorfile at all (this actually works just fine from the command line just not thru a stored procedure), with bulk insert it generates a error file which is completely blank (even tho I know there is 6 rows that cannot be imported) of size 0kb, so totally empty. Also it does not actually execute the insert.
Most likely it is a security setting. I can not find it however. Another possiblity is maybe I disabled a required service.
Anyways, thanks for the reply.
Running SQL Express 2005 on WINXP PRO SP2.
|||
Does the service account which SQL Server is running under have write permissions to the disk?
According to the bulk insert error message, it does (your message says the file exists), but you say in the next post you're deleting it?
|||
Ah your the bomb.
SQLExpress service was running under network authority not local system account. I switched it to local system and now the bulk import with errorfile switch is working from sqlcmd. Write permissions or something, anyways I know where to look now, thanks!
