Posted by Erland Sommarskog on 01/11/06 00:45

(bsandell@gmail.com) writes:
> Thanks, I am using the -h "FIRE TRIGGERS". The trigger is definitely
> being executed. I have added the following line to my trigger code -
>
> SELECT inserted.* into pubs..dummy from inserted
>
> My input file looks like this -
> aaa,aaa,aaa
> bbb,bbb,bbb
> ccc,ccc,ccc
>
> but when the code above runs in my trigger, the output is -
> NULL,aaa,aaa
> NULL,bbb,bbb
> NULL,ccc,ccc
>
> so it looks like bcp has somehow determined that the first column in my
> view does not have a 1-to-1 mapping with a column on the database
> (because it's based on a udf) and set it to null? I'd like to find a
> way to work around this if possible, without changing the view
> definition or the underlying table.

Smells bug to me. To wit, it works with BULK INSERT, which uses OLE DB
in difference of BCP which uses ODBC. The behaviour is the same in
SQL 2000 and SQL 2005, so I've field a bug for it,
http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK43711
You can vote for it, if you like.

The repro below is also in the bug report:

USE tempdb
go
CREATE TABLE sandell (col1 int NULL,
col2 int NULL,
col3 int NULL)
go
CREATE FUNCTION myudf(@c int) RETURNS int AS
BEGIN
RETURN (SELECT 80 - @c)
END
go
CREATE VIEW sandell_vy AS
SELECT col1 = dbo.myudf(col1), col2, col3
FROM sandell
go
CREATE TRIGGER sandell_tri ON sandell_vy INSTEAD OF INSERT AS
INSERT sandell(col1, col2, col3)
SELECT 80 - col1, col2 + 20, col3 + 20 FROM inserted
go
INSERT sandell_vy (col1, col2, col3)
SELECT 12, 98, 23
go
SELECT * FROM sandell_vy
go
EXEC master..xp_cmdshell 'ECHO 9, 2, 98 > C:\TEMP\bulk.csv', 'no_output'
EXEC master..xp_cmdshell 'ECHO 76, 23, 87 >> C:\TEMP\bulk.csv', 'no_output'
go
BULK INSERT sandell_vy FROM 'C:\temp\bulk.csv'
WITH (FIELDTERMINATOR = ',',
DATAFILETYPE = 'char',
FIRE_TRIGGERS)
go
DECLARE @bcp nvarchar(4000)
SELECT @bcp = 'bcp tempdb..sandell_vy in C:\temp\bulk.csv -T -c -t, -h "FIRE_TRIGGERS" -S ' + @@servername
EXEC master..xp_cmdshell @bcp
go
SELECT * FROM sandell_vy
go
DROP VIEW sandell_vy
DROP FUNCTION myudf
DROP TABLE sandell
EXEC master..xp_cmdshell 'DEL C:\temp\bulk.csv', 'no_output'



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]