Good morning,
There is a job that runs daily to restore the production databases to a
different server for testing purposes so that the test site can have live
data on a daily basis. There is a script that runs after the database
restore to ensure views/procedures/triggers are not referencing the
production REPORTING server... however it errors out everyday. As I did not
build this job and the old DBA left months ago... I'm stumped as to how I
can resolve this.
The script is below... cphprod is the restored database (the prod db is
cph...) The error I receive everyday is: Error string: The column prefix
'cphprod' does not match with a table name or alias name used in the query.
Error source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0 Error Detail Records: Error: -2147217900
(80040E14); Provider Error: 107 (6B) ...
Any idea how I can work around and/or resolve this?
Does anyone have a better way to change all references to a prod reporting
environment?
Thanks in advance for your help...
----
--
use cphprod
go
declare @.name varchar(255), @.type char(2), @.id int
,@.contline int
,@.text varchar(8000),@.text2 varchar(8000)
,@.text3 varchar(8000)
,@.text4 varchar(8000)
,@.text5 varchar(8000)
,@.text6 varchar(8000)
,@.sql varchar(8000)
,@.char10 char(1)
set @.char10 = @.char10
declare mycursor insensitive cursor for
select distinct a.name,type, a.id
from sysobjects a
join syscomments b on a.id = b.id
where type in ('p','v','fn')
and (text like '%cph.%' or text like '%ss002repl.Billing.%' )
--and name = 'ar_oa_Invoice_Master'
--order by a.id,colid
order by type desc
open mycursor
--select max(colid) from syscomments
fetch next from mycursor into @.name, @.type, @.id
while @.@.fetch_status = 0
begin
set @.text = @.char10
set @.text2 = @.char10
set @.text3 = @.char10
set @.text4 = @.char10
set @.text5 = @.char10
set @.text6 = @.char10
set @.contline = 0
select top 1 @.text = text, @.contline = colid
from syscomments where id = @.id
while (select top 1 colid
from syscomments
where id = @.id and colid > @.contline) is not null
begin
if @.contline = 1
select top 1 @.text2 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 2
select top 1 @.text3 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 3
select top 1 @.text4 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 4
select top 1 @.text5 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
else
if @.contline = 5
select top 1 @.text6 = text , @.contline = colid
from syscomments
where id = @.id and colid > @.contline
end
set @.text = replace(@.text,'cph.','cphprod.')
set @.text2 = replace(@.text2,'cph.','cphprod.')
set @.text3 = replace(@.text3,'cph.','cphprod.')
set @.text4 = replace(@.text4,'cph.','cphprod.')
set @.text5 = replace(@.text5,'cph.','cphprod.')
set @.text6 = replace(@.text6,'cph.','cphprod.')
--ss002repl.Billing.
set @.text = replace(@.text,'ss002repl.Billing.','Billingprod.')
set @.text2 = replace(@.text2,'ss002repl.Billing.','Billingprod.')
set @.text3 = replace(@.text3,'ss002repl.Billing.','Billingprod.')
set @.text4 = replace(@.text4,'ss002repl.Billing.','Billingprod.')
set @.text5 = replace(@.text5,'ss002repl.Billing.','Billingprod.')
set @.text6 = replace(@.text6,'ss002repl.Billing.','Billingprod.')
if @.type = 'p'
begin
set @.sql = 'drop proc ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
else
if @.type = 'v'
begin
set @.sql = 'drop view ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
else
if @.type = 'fn'
begin
set @.sql = 'drop function ' + @.name
print @.sql
exec (@.sql)
print 'separation line'
print @.text
print @.text2
print @.text3
print @.text4
print @.text5
print @.text6
exec (@.text + @.char10 + @.text2 + @.char10 + @.text3 + @.char10 + @.text4 +
@.char10 + @.text5 + @.char10 + @.text6)
end
fetch next from mycursor into @.name, @.type, @.id
end
close mycursor
deallocate mycursor
go
set xact_abort on
----
-->> There is a script that runs after the database restore to ensure
I could be wrong here, but are you updating the system tables here? Can you
explain what logic is being employed here?
On a cursory glance, I see some meaningless statements like "set @.char10 =
@.char10" and excessive usage of TOP clauses in your script.
The error message suggests that you are trying to use a table name/alias
which is already replaced in the FROM clause, but still exists in some other
section of a stored procedure, view or function. Without a detailed
inspection of the code and test it out, it is hard to spot out which
procedure, view or function is the real culprint here.
Anith
