Stored procedure to find and replace text in a text file

  • Comments posted to this topic are about the item Stored procedure to find and replace text in a text file

  • @servername is the text you want to change

    @newservername is what you want to change it too.

    orginanally used to change server name in generated sql files for DR where servername is different.

  • what if the usage of xp_cmdshell is restricted? .. Any workaround to still be able to do this?

  • You could also use a CLR assembly with code to do that but it depends on the environment and the security of the server. It's a bad idea to leave 'xp_cmdshell' enabled in a production environment.

    Why not use a programming language to do what you need? I don't think T-SQL is the right tool to use here but I could be wrong.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • you can always enable the command shell and then disable it in the stored procedure. That way its only on for a moment.

  • I keep getting system cannot find the specified file. Though i know my .sql file exists. The new file was created but with 0kb

  • what is the exact syntax you are using?

  • Top marks for the stored procedure name!! As for the rest, there's not much sql in it! More a lesson on how to issue dos type commands via sql.

    But if it works....why not!

  • I found this interesting, but have not tried it out thus far.

    What would be nice is if the script had commented explanations of exactly what each piece of code is doing and why.

    The @servername & @newservername should be changed as well - if it's adapted from another purpose then thats cool, but it has a new purpose now so not too hard to FART (find & replace those) parameters.

    - Gav.

  • Every script has value no matter how you slice it. Every time i see a new way of doing something, i like to share it. this script had one purpose to change a server name, but you can rename the parameters what you wish. I just thought someone might like to see this example of manipulating text files.

  • @GrassHopper : I followed the script to the letterm I guess the server name is just another string in the .sql file and I can use it as anything.

    I am trying to auto encrypt my files.

    I place a line with a comment on development environment

    --With encryption

    I script out all the objects into a .sql file

    I use your script to do a ctrl find replace

    "--with encryption" to just "with encryption"

    All I simply do is the set @servername to "--with encryption" and @newservername to "with encryption"

    Everyother thing remains the same.

    The script builds the batch file, I see it in the folder. It also creates the new file.sql but nothing is contained in the new file though it calls it the exact name I asked it to.

    Pls help. Thanks

  • There are some special characters that this script will not handle because they are valid DOS command operators such as | pipe and > and < etc... but if you are in a bind here is what you need to do. There is a free tool you can download called file monkey, get the free version. After you install it, there is a find and replace file in there somewhere I think its called dossr.exe and if you get that file and use it, its a stand alone file you can copy to any server and run it with xp_cmdshell passing the parameters , then it will just about anything , beyond what this script will do. it does not have the limitations of a batch file.

  • Hmmmm, thanks. Although the only special character I have is '-' that's dash.

  • ok then put "around text to replace"

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply