Automate replacing a word in .sql file
-
Hi All,
We have a Test database which gets refreshed everynight with Production back up. But the Stored Procedures in the Test database need a minor modification which I want to automate. After the refresh I have a job scheduled which creates the script for certain stored procs and leaves it the C:\SP_file.sql directory of the Database server. We use linked servers to Oracle to retrieve the data. But for Production and Test databases, The instance and login/passwords are different. So now all I want to do is replace a word in the C:\SP_file.sql file and save it and then just run in thru a sp and schedule it.
Exec master..xp_cmdshell 'isql -Usa -Psth -SsomeTestServer -d SomeDB -i"C:\SP_file.sql
In the Production Stored Procs, the following is used
"Select * from Prod_App.Mytable"
All I want to do is whereever Prod_App is used, I want to replace it with UAT_train.
"Select * from UAT_train.Mytable"
So how do I automatically replace this word in a .sql file from a stored procedure.
Any help will be really appreciated.
thanks,
Tania
-
Seems like all you are asking for is a single file search-and-replace utility.
You could use munge.exe if you've got the Windows NT Resource Kit.
I wrote a utility called frep.exe a long time ago using Turbo Pascal. It's 16-bit DOS, but still works well. It will replace all occurrences on one string with another string within a file or group of files.
I put it on my personal web site (which is not really implemented). You can download it, plus the Turbo Pascal source code for reference, in the file frep.zip.
http://mysite.verizon.net/vze3rv8g/frep.zip
Sample usage, given your example and assuming all files are in the same folder:
c:> frep sp_file.sql i "Prod_App" "UAT_train"
One warning: The original file is replaced, so keep a backup if you need to.
-
You could do it several ways depending on the size of your file.
- You could write a vbscript file to do the work and call it with xp_cmdshell using the command line to pass in arguments
- Use xp_cmdshell and the dos command type to read the entire file into a temp table, run a sql replace statement and then reoutput the file using the dos echo command
- You could use just tsql to create the fso object and manipulate the file that way
I chose the 3rd method for illustration
--from here down you should be able to cut and past into query analyzer
declare @fsoToken int, @error int
declare @tsToken int, @fToken int
declare @fileContents varchar(8000)
declare @src varchar(500), @desc varchar(500)
--first create the file manipulation object
exec @error = sp_oaCreate 'scripting.filesystemobject', @fsoToken OUT
-- a non 0 result in @error indicates failure
if @error <>0
begin
Exec sp_oaGetErrorInfo @fsoToken, @src out, @desc out
Print 'error creating fso token'
Select Error=convert(varbinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descreturn
end
--next create the object to read the file
exec @error = sp_oaMethod @fsoToken, 'OpenTextFile', @tsToken out, 'C:\Code\SQLTest\test.sql'
-- a non 0 result in @error indicates failure
if @error <>0
begin
Exec sp_oaGetErrorInfo @tsToken, @src out, @desc out
Print 'error creating ts token'
Select Error=convert(varbinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descreturn
end
--next read the file into a local variable
exec @error = sp_oaMethod @tsToken, 'readall', @fileContents out
-- a non 0 result in @error indicates failure
if @error <>0
begin
Exec sp_oaGetErrorInfo @tsToken, @src out, @desc out
Print 'error reading ts token file'
Select Error=convert(varbinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descreturn
end
--next close the text stream
exec @error = sp_oaMethod @tsToken, 'close'
-- a non 0 result in @error indicates failure
if @error <>0
begin
Exec sp_oaGetErrorInfo @tsToken, @src out, @desc out
Print 'error closing ts token file'
Select Error=convert(varbinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descreturn
end
--replace the desired text in the file
--depending on the size of your file, this could be done in a number of ways
-- if it is less than 8k then this method will work fine
-- if it is larger than 8k then you could create a temp table and use the above
--sp_oaMethod execution to populate the table with the return value
--then run the replace statment below
-- you could also read the file line by line and loop through each line until you get the eof flag
--and do a replace on each line of the file
--i think the temp table method might be faster but i am not sure
select @fileContents = replace(@filecontents, 'search value','replace value')
--next write a new file or overwrite the old one
--i have elected to create overwrite the old file in this code
exec @error = sp_oaMethod @fsoToken, 'OpenTextFile', @tsToken out, 'C:\Code\SQLTest\test.sql', '2', 'true'
-- a non 0 result in @error indicates failure
if @error <>0
begin
Exec sp_oaGetErrorInfo @tsToken, @src out, @desc out
Print 'error creating new file'
Select Error=convert(varbinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descreturn
end
exec @error = sp_oaMethod @tsToken, 'write', null, @fileContents
-- a non 0 result in @error indicates failure
if @error <>0
begin
Exec sp_oaGetErrorInfo @tsToken, @src out, @desc out
Print 'error writing ts token file'
Select Error=convert(varbinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descreturn
end
--next close the text stream
exec @error = sp_oaMethod @tsToken, 'close'
-- a non 0 result in @error indicates failure
if @error <>0
begin
Exec sp_oaGetErrorInfo @tsToken, @src out, @desc out
Print 'error closing ts token new file'
Select Error=convert(varbinary(4),@error), Source=@src, <A href="mailto
escription=@desc">Description=@descreturn
end
--destroy the objects to release resources
exec @error = sp_oaDestroy @tsToken
-- a non 0 result in @error indicates failure
if @error <>0
begin
Print 'Destroy ts token'
Exec sp_oaGetErrorInfo @tsToken
return
end
exec @error = sp_oaDestroy @fsoToken
-- a non 0 result in @error indicates failure
if @error <>0
begin
Print 'Destroy fso token'
Exec sp_oaGetErrorInfo @fsoToken
return
end
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply