Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Create text file and ftp with trigger

    Good day,

    I hope someone can help me.

    Question 1:
    Is it possible to create a text file from a sql server trigger?

    Question 2:
    Is it possible to ftp a file from a sql server trigger?

    Please if anyone can help I would appeciate it.

    Thanks

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    well there is something called xp_cmdshell which allows you to issue OS commands from TSQL....

    for more details check here

    http://www.databasejournal.com/featu...le.php/3372131

    include this in you SQL

    and you can create both the file and issue ftp commands......

    however many may argue that this represents a security risk but i guess you don't have any other option other than this to do an ftp.....

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    For SQL Server 2005 you could create a .NET trigger to perform the task.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    and... this not a good idea. anyone care to guess why?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    What is not a good idea....wha tI want to do or the .NERT trigger roac suggested?

    By the way I should have mentioned I am using SQL Server 2000.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you can ftp a file from a trigger, but if there is something wrong with the ftp service this transaction will just hang. if you have several of these you can hang up your database server. I would either stick this in the application code or perhaps write a job that does this at certain intervals but making it part of your transactional process could concievably hang up your database server.

    but what do i know?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You know a lot.

    The scope of a trigger should be limited to its own database. They should not access other databases or the operating system or file system.

    The best method to handle something like this is to have the trigger populate a staging table within the database, and then have a schedule job poll the staging table periodically to process the data and ftp your files.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    how about popping a message box from the trigger to ask the user if they want to save the insert to a file? would that be ok?


  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Jez, we already figured out that people can't tell when you are joking, based upon your LTRIM/RTRIM priority post...
    Make sure you load these gems up with LOTS of smilies.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ouch, that stings!

  11. #11
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by blindman
    The best method to handle something like this is to have the trigger populate a staging table within the database, and then have a schedule job poll the staging table periodically to process the data and ftp your files.
    Or, if using SQL Server 2005, use the Service Broker. Let the trigger begin a new service broker conversation and have the service receiving the message call a SQLCLR procedure that creates and ftps the file.

Posting Permissions

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