December 20, 2011 at 2:13 pm
Comments posted to this topic are about the item Stored procedure to find and replace text in a text file
December 27, 2011 at 6:42 am
@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.
January 11, 2012 at 2:56 pm
what if the usage of xp_cmdshell is restricted? .. Any workaround to still be able to do this?
January 11, 2012 at 3:00 pm
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
January 11, 2012 at 5:22 pm
you can always enable the command shell and then disable it in the stored procedure. That way its only on for a moment.
January 18, 2012 at 3:28 am
I keep getting system cannot find the specified file. Though i know my .sql file exists. The new file was created but with 0kb
January 18, 2012 at 6:36 am
what is the exact syntax you are using?
January 23, 2012 at 1:24 am
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!
January 23, 2012 at 3:26 am
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.
January 25, 2012 at 8:57 pm
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.
January 25, 2012 at 9:06 pm
@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
January 28, 2012 at 6:42 pm
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.
January 28, 2012 at 11:10 pm
Hmmmm, thanks. Although the only special character I have is '-' that's dash.
January 29, 2012 at 7:05 am
ok then put "around text to replace"
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply