Ошибка sqlstate s1000

I am trying to create a format file for use with bcp exporting and bcp importing.  I am actually just practicing to make sure I have this bcp stuff down before attempting to use it for copying a large database.  The command I am using is

!!bcp «OLDAdventureWorks2008R2.dbo.AWBuildVersion» format nul -f «c:OLDAdventureWorks2008R2_dbo_AWBuildVersion.xml» -x -q -N -T -S Netsql1SQL2008_APPS

The error I get when I run the above (in SSMS Query Editor running in SQLCMD mode) is:

SQLState = S1000, NativeError = 0

Error = [Microsoft] [SQL Native Client] Format File : Invalid Field Index

The format file «OLDAdventureWorks2008R2_dbo_AWBuildVersion.xml» gets created, but contains no data.

I don’t get this error for any other table in the OLDAdventureWorks2008R2 database, only this on (i.e. AWBuildVersion).  Note also that database OLDAdventureWorks2008R2 is a copy of AdventureWorks2008R2.

The other day one job started to fail every time it ran. The error was

Server: FancyServerName 
Job: SomeJobName 
Step: Step 3) SomeStepName 
Message: Executed as user: SomeDomainSomeServiceAccount. Starting copy… SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 11 for SQL Server]I/O error while writing BCP data-file 19 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 Average : (19000.00 rows per sec.). Process Exit Code 1. The step failed. 
Time: 2015-10-06 08:16:20

This was very interesting since the file we were writing had only 19 rows and this was very small, less than a kilobyte. So how do you investigate what the error is? It could be all kinds of things:

Permissions on the drive
Problems with the drive
Memory issues
Issue with a format file

If you do a search you will get all kinds of things back. However the first thing you need to run is the following

Here is what I saw after running xp_fixeddrives

drive      MB free
C             235644
D             1479234
E              10123
F              10123
G             10123
L              10123
S              117706
T              10123
Z              0

Take a look at the Z drive do you see that? The Z drive is full, there is no space left. It would be nice if the error message indicated that it couldn’t write to the file because there was no space left. In a production environment this should not happen because hopefully you have something in place that sends out an alert if the drive is 90% full.

So, if you ever get this error, first thing you have to do is if you have space left on the drive.

I also want to point out that the xp_fixeddrives stored procedure is undocumented so there is no guarantee it will be available in a future version of SQL Server.

I am trying to execute the below BCP query in my TEST Server, where BCP has a connection string to the production DB, so my destination folder is in my Test Server and I am getting an error, I think it’s a permission related issue.

DECLARE @str NVARCHAR(2000), @path NVARCHAR(200)='Z:SQL' , @TableName NVARCHAR(100)='tblUsers' Set @str = 'BCP IDB.dbo.'+@TableName+ ' OUT '+@path +''+@TableName+'.csv -c -t^| -T -S ProductionServerNameSQL2012' Exec xp_cmdshell @str

Print (@str) = BCP IDB.dbo.tblUsers OUT Z:SQLtblUsers.csv -c -t^| -T -S ProductionServerNameSQL2012

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

I shared my Test Server folder to the Production User account. Also I have mapped the shared folder drive as a network share in production and run the below SQL statements:

EXEC XP_CMDSHELL 'net use Z: \TestServevrNameTest'

EXEC XP_CMDSHELL 'Dir Z:'

Still I am facing the same Issue.

Please guide me what steps are required to fix this permissions issue.