Home » SQL Server

bcp magic error (SQLState = 37000, NativeError = 8105)

Hi folks,

bcp is giving me a strange error, which I cannot solve by myself. I tried to Google it without any success.

Interestingly, it works on my local machine but it does not work on others :)


Now the details. I need to bulk insert some data into the database. The challenge is that two tables have to be updated at the same time, as they are linked with one-to-one relationship. The first table's primary key is also an identity field. The second table's primary key should get the value, assigned by SQL to the first table's identity key.

So we have:

Table1( ID1(Primary, Identity), Value1)  Table2( ID2(Primary), Value2)

Input data set: (Value1, Value2)

10, 100

20, 200



After insert the tables values should be...

Table1                      Table2


1010, 10                   1010, 100

1011, 20                   1011,200

1012, 30                   1012,300

1010-1012 are the identity values assigned by SQL

We cannot target two tables in bcp, but we can target a view,
So I created a view (MyView), linking ID1 and ID2, and having two fields Value1 and Value2
now, after issuing... bcp pubs.dbo.MyView in my.txt -T -c -t ,
I am getting the following error
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]View or function 'pubs.dbo.MyView' is not
 updatable because the modification affects multiple base tables.

After bit of research, I have created a INSTEAD OF INSERT TRIGGER to catch the insert statement and redirect it to two separate statements, updating each table separately. My two insert statements look like the following...
insert into Table1 (Value1) select i.Value1 from inserted i
insert into Table2 (ID2, Value2) select scope_identity(), i.Value2 from inserted  i 
The scope_identity() function eventually returns me ID1 assigned to the first table.

The problem with those statements is that the trigger is called once, for the whole table (inserted), not per each row. So it fails, as it tries to use the same (last ID1) value for all the values of the Table2.

To solve this problem (thanks to article http://support.microsoft.com/kb/111401 ) I am using cursoring (iterating).
as I cannot use DELETE approach for 'inserted' table I am using slightly modified second approach.
However, for that to work, I modified a view to be View (ID1,Value1,Value2)
My input file would become

Note, that the only requirement to the first column, is that values must be in the ascending order.
My final trigger is...

CREATE TRIGGER MyTrigger on [dbo].[MyView]

DECLARE @row_id CHAR( 11 )
SELECT @row_id = i.ID1 from inserted i

WHILE @row_id is not null
INSERT INTO dbo.Table1 (Value1) SELECT i.value1 FROM inserted i WHERE i.ID1 = @row_id

INSERT INTO dbo.Table2 (ID2, Value2) SELECT scope_identity(), i.Value2 FROM inserted  i WHERE i.ID1 = @row_id

    SELECT @row_id = min( i.ID1 ) FROM inserted i WHERE i.ID1 > @row_id

I tested this approach on my machine and it works great either using bcp.exe utility, or BULK INSERT operator, such as
bulk insert MyView from 'c:\my.txt' with (FIELDTERMINATOR = ',', FIRE_TRIGGERS )

bcp commands are: (either works on my machine)

bcp pubs.dbo.MyView in C:\my.txt  -c  -T -t , -h "FIRE_TRIGGERS"
bcp pubs.dbo.MyView in C:\my.txt  -c  -S peterv21855 -Usa -Psa -t , -h "FIRE_TRIGGERS"

Note: we must specify -h "FIRE_TRIGGERS" option to hint a system to use the trigger. 

I was so excited about my solution, so I quickly embed it in our system and fired up the test run. Unfortunately it did not go well on real tables on different database (different server).

I went back to my test files and this time I created them on different machine. 
Strangely it did not work. I run the bcp command from that remote machine to update my test files on my machine- that worked perfectly. So bcp was not an issue. I run bcp -v and the versions were the same (Version: 8.00.194, MSSQL 2005)

The error I am getting is the following:

Starting copy...
SQLState = 37000, NativeError = 8105
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]'My_DB.dbo.MyView' is not a user table. Cannot perform SET operation.
BCP copy in failed

The only thing that differ is that my test files are under pubs database.
I run bcp with sa credentials, and with other user name (with all possible permissions, including bulkadmin and sysadmin)
Nothing helps. I did not find anything in MSDN or in Google.
The only hope is this forum.
Please help.




3 Answers Found


Answer 1

From the error  message I can say you are trying to execute a SET statement in a non-table object (through BCP). SET option can only be used on table objects.

Check whether you are trying to SET value for your IDENTITY column. Check ur DB Schema on both ur local and remote dbs.


Answer 2

All es equal. Same schema, same tables, same triggers.

Answer 3

I am using a temorary table now, instead of view. The data are not going there anyway, as the trigger effectively redirects it to my real tables.

It seems that bcp  does not support the views with the columns from different tables (since SQL 2005). As it happened, I had SQL 2000 on my machine, that is why it worked there.



Im Executing bcp <databasename.schema.tablename> out <destination file> -T -c but the result is always to export data from a table to a flat txt file

SQLState = S0002, NativeError = 208 Error =[Microsoft][Sql server native client 10.0][sql sevrer]Invalid object name 'database.schema.table'

Inspite of the fact that I provide an exact full table name that exists effectively within my server instance

Im running sql server 2008 32 bit in a windows server 2008 32 bit machine



The complexity resides in the simplicity

I am trying to use the BCP command to execute the following statement, but I keep getting the following error:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in
BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

This is a very small XML file and here are it's contents:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
<s:Schema id='RowsetSchema'>
 <s:ElementType name='row' content='eltOnly'>
  <s:AttributeType name='c0' rs:name='WITHDRAWAL-CODE' rs:number='1'>
   <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='6' rs:maybenull='false'/>
  <s:AttributeType name='c1' rs:name='WITHDRAWAL-SDESC' rs:number='2' rs:nullable='true'>
   <s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='30'/>
  <s:extends type='rs:rowbase'/>
 <z:row c0='00' c1='00 Withdrawn pr'/>
 <z:row c0='01' c1='01 Change grade'/>
 <z:row c0='02' c1='02 Trans-pubSam'/>
 <z:row c0='03' c1='03 Trans-nonpub'/>
 <z:row c0='04' c1='04 Moved out of'/>
 <z:row c0='05' c1='05 Moved out of'/>
 <z:row c0='06' c1='06 Left-age wit'/>
 <z:row c0='07' c1='07 Left-age wit'/>
 <z:row c0='08' c1='08 Grad'/>
 <z:row c0='09' c1='09 Grad-met IEP'/>
 <z:row c0='11' c1='11 Died'/>
 <z:row c0='12' c1='12 Excused-disa'/>
 <z:row c0='13' c1='13 Commit to co'/>
 <z:row c0='14' c1='14 Withdrawn 15'/>
 <z:row c0='15' c1='Lft Marriage'/>
 <z:row c0='16' c1='16 Expelled'/>
 <z:row c0='17' c1='Left sch Pregna'/>
 <z:row c0='18' c1='18 Withdrawn no'/>
 <z:row c0='20' c1='20 Transfer did'/>
 <z:row c0='21' c1='21 EC Withdrawa'/>
 <z:row c0='22' c1='22 Entered care'/>
 <z:row c0='23' c1='23 Kindergarten'/>
 <z:row c0='24' c1='24 Homebound'/>
 <z:row c0='25' c1='25 EC eval only'/>
 <z:row c0='26' c1='26 EC withdrawa'/>
 <z:row c0='27' c1='27 EC transitio'/>
 <z:row c0='28' c1='Not eligile 3yr'/>
 <z:row c0='29' c1='Not elig for 3y'/>
 <z:row c0='30' c1='3yr elig not de'/>
 <z:row c0='31' c1='31 Left -social'/>
 <z:row c0='32' c1='32 Left - finan'/>
 <z:row c0='33' c1='33 Left - famil'/>
 <z:row c0='34' c1='34 Left-Unknown'/>
 <z:row c0='35' c1='35 Left-Over 21'/>
 <z:row c0='36' c1='enrol post seco'/>
 <z:row c0='37' c1='37 Left- to GED'/>
 <z:row c0='40' c1='40 EOY Enrolled'/>
 <z:row c0='41' c1='41 Dropped/ReEn'/>
 <z:row c0='42' c1='42  met loc gra'/>
 <z:row c0='50' c1='50 Special ed s'/>
 <z:row c0='60' c1='No Referal'/>
 <z:row c0='61' c1='Referal to SpEd'/>
 <z:row c0='62' c1='Refer HealthCar'/>
 <z:row c0='63' c1='Refer SpEd/Heal'/>
 <z:row c0='64' c1='Refer EC Progam'/>
 <z:row c0='65' c1='Refer Parent No'/>
 <z:row c0='97' c1='Displaced NonRe'/>
 <z:row c0='99' c1='99 Enrollment S'/>
 <z:row c0='WCV' c1='DO NOT USE'/>
 <z:row c0='YE' c1='DO NOT USE'/>



And this is my SQL table in the Development database which is currently empty:

CREATE TABLE wbl_Load (xCol xml);

I am not using a format file but here is what I choose from the command prompt:



We have an application that connects to SQL 2005 thru ODBC with the following string:

driver=SQL Server;server=server1;database=db2005;uid=serveruser;pwd=pwd1;

When our administrators login on the workstation the application works well. But when ordinary users login they get the following error. (We only have 1 domain)

SQLState: 28000

SQL Server Error: 18452

Login failed for user ". The user is not associated with a trusted SQL Server connection.

Then the standard SQL Server Login window pops up asking for the Login ID and Password. On the window the 'Use Trusted Connection' is checked and the name of the user on the workstation appears on the LoginID. What we do is uncheck the 'Use Trusted Connection' then login using the credentials above.

Need help on this one please. Thanks.



I'm having trouble running jobs with my active directory (ADS) account. I've setup my SQL services to run under an ADS account, but jobs cannot seem to query ADS for user information. We're running Windows Server 2003 and SQL Server 2005 SP2.

 Here is the error message:


The job failed.  Unable to determine if the owner (ADS\me) of job eFASRtest has server access (reason: Could not obtain information about Windows NT group/user 'ADS\me', error code 0x5. [SQLSTATE 42000] (Error 15404)).


also this message in log:


[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'ADS\me, error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)


I already tested the suggested:

execute as login='ads\me' and I get the same error on both (my local installations and production)

appreciate your help


Hi People,

Am getting this strange error when i try to run my SP .

BCP Error:Unable to resolve column level collations. BCP Copyout failed

Here the Text file has been generated but the data is not dumped in the text file.

Please see the below SP that i have used.

Alter procedure sp_AOStextgeneration

set nocount on
declare @sql as varchar(8000)

declare @docid as varchar(max)
select @docid=newid()

declare @count as int
declare @lastupdate datetime

select @lastupdate=isnull(max(Dateofdelivery),CONVERT(datetime,'1900-01-01')) from ProductCart.dbo.tbl_sapsettings where active=1

set @count= (select COUNT(*) from ProductCart.dbo.[v_AOS-SAP] where datediff(mi,ProductCart.dbo.[v_AOS-SAP].InvoiceDate,@lastupdate)<= 0)
print @count
if @count>0
select @sql = 'bcp "exec ProductCart.dbo.X_sp_generateAOStoSAP_h_data ''' + @docid + '''" queryout E:\Interfaces\AOS-SAP\' + @docid + '_d.txt -c -t, -T -S '+ @@servername

exec master..xp_cmdshell @sql
--print @sql

--select @sql = 'bcp "exec pc_bak.dbo.X_sp_generateAOStoSAP_h_data ''' + @docid + '''" queryout E:\Interfaces\AOS-SAP\' + @docid + '_d.txt -c -t, -T -S ' + @@servername
--exec pc_bak..xp_cmdshell @sql
----print @sql

if @@ERROR =0
select @sql='Insert into ProductCart.dbo.tbl_sapsettings(Dateofdelivery, FileName,Active) values (getdate(),''' + @docid + '.dat'',1)'
print 'Zero Rows Copied'



When I run this,

exec master..xp_cmdshell 'bcp SoftwareReporting.dbo.t1 out C:\Test8b.txt -T -c -t "|"'

I get a query result like this. (I am using localhost):

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


This exact command works when run from a batch file. I have configured xp_cmdshell to be enabled.


I have problem with bcp, thi bcp code works fine on database wich is located on my computer, but it does not works with database wich is located on the server. I use windows autentification to connect this server.


SET @cmd = 'bcp "[main db].dbo.Company" out C:\ab\Output.csv -c -t";" -SLOCALHOST -T'EXEC

master..xp_cmdshell @cmd

What can i do for this situation?


 SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

i inserted this code in console

bcp "[main db].dbo.Company" out C:\ab\Output.csv -c -t";" -SLOCALHOST -T

 and it writes "requested by login, the login failed"



I am using BCP out command to export data to text file from SQL Server database using code below 

select @sql = 'bcp <tableName> out C:\bcp\image.txt -c -t$$ -C RAW -T -S'+ @@servernameexec master..xp_cmdshell @sql, no_output 

I am getting the following error sporadically 

SQLState = 08001, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Unable to complete login process due to delay in opening server connection

Please help on this, how to overcome this


I am using BCP out command to export data to text file from SQL Server database using code below 

select @sql = 'bcp <tableName> out C:\bcp\image.txt -c -t$$ -b 10000 -UDBA -Pxxxx -S'+@@servername

Exec master..xp_cmdshell @sql, no_output 

I am getting the following error sporadically 

SQLState = 37000, NativeError = 468
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

Please help on this, how to overcome this


Using bcp queryout, in-line queries of the following form fail with the invalid object error on the $PARTITION keyword:

bcp "SELECT S.* FROM Support_Prod.ADSK.Session S INNER JOIN ##Session #S ON (S.SessionId = #S.SessionId AND S.LastInquiryId = #S.LastInquiryId AND $PARTITION.AdskInqPF(S.LastInquiryId) = $PARTITION.AdskInqPF(#S.LastInquiryId))" queryout I:\Data2\AdskBCPout\ADSK.Session.dat -fI:\Data2\AdskBCPout\ADSK.Session.format.xml -q -t\t -r\n -SINTDEVSQL1\DEV1 -T

Here, AdskInqPF is a well-formed partition function used to partition Support_Prod.ADSK.Session.  ##Session is a table of semi-joins derived from the former, but is not partitioned.  LastInquiryID is the partitioning key on the Session entity.

The resulting error is:

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '$partition.AdskInqPF'.
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '$partition.AdskInqPF'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

The platform is SQL Server 2008 SP1 and the bcp is

BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (C) Microsoft Corporation. All Rights Reserved.
Version: 10.0.2775.0
2007.0100.2775.00 ((Katmai_SP1_QFE-CU).100430-1608 )

Any thoughts?  Workarounds?



DECLARE @cmdline VARCHAR(4000)
SELECT @cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" queryout C:\test.txt -c -T'
EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
output ------------------------------------------------------------------
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.


What to do BCP by passing a query.?



Could you help on how to catch the NativeError number from exception in T-SQL


I want to catch the SQLState and NativeError numbers in my Stored Procedure.

I am able to get the SQLState number using ERROR_NUMBER() function.

But I didn’t get any fucntion to catch the NativeError number.



Thanks & Regards,



I need to know if Network Magic.Ink is anything I need on my computer?  What is it used for?  Can I delete it successfully without losing something else?

How did the dynamicquerystring parameter solve the problem below. It works but I don't understand why?



Two tables

  table resource contains personal entries and has a primary key attribute "id"

  table projects contains a primary key attribute "id" and a foreign key attribute "ResourceID" that is constrained to an id attribute from table resource.


I wanted to create a details view for the resource table that displayed in a gridview all of the projects to which a given resource was assigned.

I used the details.aspx template as a starting point and added linq2sql and gridview controls.

The linq2sql control was a select all from table projects and became the datasource for the gridview.

I registered the gridview with the dynamicdatamanger and changed the databoundfields to dynamicdatafields.

                   The problem

Of course the gridview displayed all entries from table projects and as I didn't have vs2010 I didn't have the dynamicqueryextender. I was about to give up when I playing around added the dynamicquerystring parameter to my linq2sql datasource just for grins.

All of a sudden the gridview automagically started filtering down to only the entries that had a ResourceID that was equal to the ID of the currently selected resource.

I don't understand this and after reading the msdn documentation on the dynamicquerystring parameter it doesn't seem like this should work this way. Can someone help me to understand this because I am sure that is simply a lack of knowledge on my part.


Thank you in advance to any consideration that you can give to this post.


Here is the code.

<%@ Page Language="C#" MasterPageFile="~/Site.master" CodeFile="Details.aspx.cs" Inherits="Details" %>

<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <asp:DynamicDataManager ID="DynamicDataManager1" runat="server" AutoLoadForeignKeys="true" />

    <h2>My Custom Page Entry from table <%= table.DisplayName %></h2>

    <asp:ScriptManagerProxy runat="server" ID="ScriptManagerProxy1" />

    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <asp:ValidationSummary ID="ValidationSummary1" runat="server" EnableClientScript="true"
               HeaderText="List of validation errors"  />
            <asp:DynamicValidator runat="server" ID="DetailsViewValidator" ControlToValidate="DetailsView1" Display="None" />

            <asp:DetailsView ID="DetailsView1" runat="server"
               DataSourceID="DetailsDataSource" OnItemDeleted="DetailsView1_ItemDeleted"
                CssClass="detailstable" FieldHeaderStyle-CssClass="bold"
               AutoGenerateRows="False" DataKeyNames="ID" >
                <FieldHeaderStyle CssClass="bold" />
                    <asp:DynamicField DataField="ID"  visible="false"/>
                    <asp:DynamicField DataField="LastName" />
                    <asp:DynamicField DataField="FirstName"  />
                    <asp:CheckBoxField DataField="Active" Visible="false"  />
            <asp:LinqDataSource ID="DetailsDataSource" runat="server"
               ContextTypeName="PACSViewDataContext" EnableUpdate="True" TableName="Resources">
                   <asp:DynamicQueryStringParameter />

            <br />
            <br />
            <asp:TabContainer ID="TabContainer1" runat="server" ActiveTabIndex="0"
               <asp:TabPanel ID="TabPanel1" runat="server" HeaderText="1" CssClass="gridview" Enabled="false" >

   <asp:LinqDataSource ID="LinqDataSource1" runat="server"
      ContextTypeName="PACSViewDataContext" TableName="Projects"
      EnableInsert="True" EnableUpdate="True">
       <asp:DynamicQueryStringParameter Name="ID" />
   <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
      AllowSorting="True" DataSourceID="LinqDataSource1"
      CssClass="gridview" AutoGenerateColumns="False">
         <asp:DynamicField DataField="ID" visible="False"  />
         <asp:DynamicField DataField="hot"  />
         <asp:DynamicField DataField="Site"  />
         <asp:DynamicField DataField="ProjectType"  />
         <asp:DynamicField DataField="ResourceID"   />
         <asp:DynamicField DataField="Status"   />
         <asp:DynamicField DataField="ProjectManager"  />
         <asp:DynamicField DataField="HardwareOnSiteDate"
         <asp:DynamicField DataField="E2ETestingDate"    />
         <asp:DynamicField DataField="TechLiveDate"   />
         <asp:DynamicField DataField="RequestedLiveDate"
         <asp:DynamicField DataField="ActualLiveDate"   />
         <asp:DynamicField DataField="GON"   />
         <asp:DynamicField DataField="Notes" visible="False"  />

               <asp:TabPanel ID="TabPanel2" runat="server" HeaderText="TabPanel2" >
                     Support Cases
                  <ContentTemplate> This is a panel</ContentTemplate>
            <br />
            <br />

            <br />

                <asp:HyperLink ID="ListHyperLink" runat="server">Show all items</asp:HyperLink>



code behind

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Web.DynamicData;

public partial class Details : System.Web.UI.Page {
    protected MetaTable table;

    protected void Page_Init(object sender, EventArgs e) {


    protected void Page_Load(object sender, EventArgs e) {
        table = DetailsDataSource.GetTable();
        Title = table.DisplayName;
        ListHyperLink.NavigateUrl = table.ListActionPath;

    protected void DetailsView1_ItemDeleted(object sender, DetailsViewDeletedEventArgs e) {
        if (e.Exception == null || e.ExceptionHandled) {



We have enviroment where our intranet (based on MOSS 2007 standard) is accessed by the users with use of diffrent URL (all of then are just alternative names of the same machine):

name1 name1.domain.net name2.pl.domain.com name2

is there a way that whatever of above URL user will use he will get response from name1.domain.net ? and continue surfing on our intranet with use of name1.domain.net ?

thanks in advance


I'd like to implement a 'magic wand tool' similar to the functionality found in Paint.net and Photoshop.  Any ideas on the math or how to go about this would be appreciated.





How do the Entity Framework can benefit us while creating a very big web aplication in terms of speed and ease in programming?

Any idea please? Also, i want to know how do we implement it in the new web application? Any idea please?

I would like a few sentences as answer rather than links for the answer please? Makes easy to understand

i can't connect to sql server 2005 express. i install in windows xp
I am a Power Buidler developer using PowerBuilder version 11.2 which connect to MS Server using SQL Native Client. I got this error yesterday in one of our Production Applications:


Please advise how I can resolve this issue. Is there a driver that I can download? or ...?
Your quick reply is really highly appreciated.

In what circumstances do we get the below error. Shouldn't it be a little more descriptive?

The statement has been terminated. [SQLSTATE 01000]

This is for an SQL SERVER 2005 SP2. I am running a rebuild index using a TSQL job. The rebuild runs fine for a while giving this error later.

<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure