Home » SQL ServerRSS

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

30,300

 

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
0,10,100
1,20,200
2,30,300

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]
INSTEAD OF INSERT
AS
BEGIN

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

WHILE @row_id is not null
BEGIN
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
END
SET ROWCOUNT 0
END


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.

Thanks,
Peter

 

 

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.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter