Text file output for few sql entries

21

what happens when you run this?

if exists(select 1 from sys.tables where name = 'bamboo')	
	drop table bamboo;

create table bamboo(leaf varchar(50));
go

create trigger test on bamboo
for insert
as
declare @sql varchar(8000)

SELECT @sql = 'bcp "select * from inserted" queryout c:/test.txt -c -t -T -S localhost\SQLEXPRESS'

exec xp_cmdshell @sql

go

insert into bamboo
select 'Kaching'
22

I am getting error as below.

output
NULL
Starting copy...
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'inserted'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to resolve column level collations
NULL
BCP copy out failed
NULL

23

I have changed it to my live sql database and found the text file is created whenever there is a new insert. But the file is empty.

24

What do you get when you do

Select * from inserted in ssms

25

Same message invalid object name inserted

26

Try this

create trigger test on bamboo
for insert
as
declare @sql varchar(8000)
Select * into #inserted from inserted
SELECT @sql = 'bcp "select * from #inserted" queryout c:/test.txt -c -t -T -S localhost\SQLEXPRESS'

exec xp_cmdshell @sql

1 Like
27

Same message

output
NULL
Starting copy...
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name '#inserted'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to resolve column level collations
NULL
BCP copy out failed
NULL

28

hi subbu

if you have any remote desktop software .. google search
"AnyDesk" is there
image

you install it
i also install it

then i can connect to your machine and see and do after you give me permission

29

Anydesk ID - 640450383