Results 1 to 4 of 4

Thread: Trigger

  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Unanswered: Trigger

    I'm trying to execute the bcp command using the xp_cmdshell from a trigger which is an Insert Trigger. Thus, when ever I try to insert a row to the table nothing happens it just stays still saying executing query. I Insert the record from query analyzer. Here is the trigger codeas below:

    CREATE TRIGGER [trig_repl_uploads] ON [dbo].[reginet_replication_uploads]
    FOR INSERT
    AS
    -- get the srocode and filename from the table

    DECLARE @srocode varchar(4)
    DECLARE @filename varchar(255)
    DECLARE @tablename varchar(255)
    DECLARE @sqlstr varchar(255)
    DECLARE @bcpcmd varchar(255)

    SELECT @srocode = (SELECT sro_code FROM Inserted)
    SELECT @filename = (SELECT filename FROM Inserted)
    SELECT @tablename = 'replication_commands_' + @srocode

    SELECt @sqlstr = 'CREATE TABLE ' + @tablename + ' (repl_command [text] ,cmd_type [char] (1) ,priority [int] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
    exec (@sqlstr)

    -- bulk copy the data from the file to the table

    --SELECT @bcpcmd = 'bcp igreg_new.dbo.' + @tablename +' out e:\' + @filename + '-n -T'
    SELECT @bcpcmd = 'bcp igreg_new.dbo.replication_commands_1 in f:\Adyar_30-08-2003.bcp -n -S AMBALAm -T'
    Print @bcpcmd
    exec master..xp_cmdshell @bcpcmd

    If somebody can help me it will be great.

    thanx & regards,

    kamal

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    A trigger is executed inside a transaction.
    The the table created inside that transaction will be locked.
    The bcp command creates a new connection - which cannot access that table as it is locked.

    Doing a create table from code is not a good idea (and especially inside a trigger).
    Doing a bcp inside a trigger (or any xp_cmdshell) is also not a good idea.

    You could have the trigger insert the commend into another table and have a scheduled task polling this table to execute th bcp's (try bulk insert instead).
    This would enable you to create the table and bcl into it.
    I would suggest having a permanent table to receive the data. bcp into a global temp table and then copy to the permanent table including the identifier for the import.
    The global temp table will be automatically cleared up if there are problems.

  3. #3
    Join Date
    Jul 2003
    Posts
    4
    Originally posted by nigelrivett
    A trigger is executed inside a transaction.
    The the table created inside that transaction will be locked.
    The bcp command creates a new connection - which cannot access that table as it is locked.

    Doing a create table from code is not a good idea (and especially inside a trigger).
    Doing a bcp inside a trigger (or any xp_cmdshell) is also not a good idea.

    You could have the trigger insert the commend into another table and have a scheduled task polling this table to execute th bcp's (try bulk insert instead).
    This would enable you to create the table and bcl into it.
    I would suggest having a permanent table to receive the data. bcp into a global temp table and then copy to the permanent table including the identifier for the import.
    The global temp table will be automatically cleared up if there are problems.

    Hi Thanx for your suggestions, I guess I should do as suggested by you. Bu t please tell me how can I make a schedule Job poll on a particular Table

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Look at the agent. Add a new job to call an SP and schedule it to run periodically.
    The SP checks the table to see if there is anything to do -
    if exists(select * from tbl) if the entry is deleted when actioned.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •