BCP urgent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP urgent

Author

Topic

muhiar
Starting Member

37 Posts

Posted - 2005-11-28 : 08:11:25

i have a big problem in two parts
1- i want to create trigger that fires after insert
in that trigger for each record inserted i want to export that particular record into txt file whose name is generated automatically
i looked for the BCP but it need to be executed through command prompt?
any ideas,it is so urgent
2- i tried the BCP as follows in the MS-DOS prompt
BCP pubs..authors out c:\authors.txt -c
Passowrd :
but the following appeared in the screen
starting copy ...
SQLState=S1000,NativeError = 0
Error=[Microsoft][ODBC SQL Server dirver ][sql server]code page 720 is not supported by sql server
SQLState=S1000,NativeError = 0
Error=[Microsoft][ODBC SQL Server dirver ]unable to resolve column level collections
BCP copy out failed
any ideas how to fix it?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-28 : 11:19:10

Use xp_cmdshell to do the bcp.
see
http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html
(it does the bcp in but out is similar).

http://www.mindsdoor.net/SQLTsql/BCPAllTables.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

muhiar
Starting Member

37 Posts

Posted - 2005-11-29 : 03:53:25

hi all
i have used the following code but
declare @sql varchar(255)

set @sql='bcp "select * from ['+db_name()+']..authors "
out "c:\authors.txt" -C -S'+@@servername
exec master..xp_cmdshell @sql --, no_output
but it list the all the bcp options
some time it asks me for file type

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-29 : 04:02:39

Try this

declare @sql varchar(255)
set @sql='bcp "select * from ['+db_name()+']..authors" queryout "c:\authors.txt" -c'
exec master..xp_cmdshell @sql

Madhivanan

Failing to plan is Planning to fail

muhiar
Starting Member

37 Posts

Posted - 2005-11-29 : 05:35:25

i have tried ur solution it works but it still asks for the file type format for each field

muhiar
Starting Member

37 Posts

Posted - 2005-11-29 : 07:14:51

hi all
i have created the following trigger on the jobs table
CREATE TRIGGER insert_trg ON [dbo].[jobs]
after INSERT
AS
DECLARE @J_DESC VARCHAR(200)
DECLARE @J_ID INT
declare @FileName varchar(50),
@bcpCommand varchar(2000)

SELECT @J_DESC =JOB_DESC FROM INSERTED
SELECT @J_ID = JOB_ID FROM INSERTED

SET @FileName = REPLACE ('c:\JOBS_'+Convert(char(8),getdate(),1) +CAST (@J_ID AS VARCHAR(10))+'.txt','/','-')
set @bcpCommand ='bcp "select * from pubs..JOBS order by JOB_ID" queryout "'
set @bcpCommand = @bcpCommand + @FileName + '" -P xxx -C -f c:\JOBS.txt'
print @bcpCommand

print @filename
PRINT @J_ID
PRINT @J_DESC
exec master..xp_cmdshell @bcpCommand
PRINT 'DONE'
one problem araised it takes for ever and the system hangs
can any body tell me where is the problem???????????
it is urgent

muhiar
Starting Member

37 Posts

Posted - 2005-11-29 : 08:31:28

can i call dts package from the trigger?
how can i do it?
can i pass the file name as parameter ?
plz help me it is urgent

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-30 : 13:24:25

The trigger will be running in the same transaction as the insert statement and holding the lock on the table.
The bcp will create another connection to read the data but be blocked by the trigger and so will be stuck.

DTS would have the same problem.
You won't be able to do this synchronously.
You could have a scheduled job running every minute to do the bcp if necessary - that would get round the locking problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources