StoredProcedure.Alter() not working

we have situation have live sql database , due complexity of our application need use desensitised copy in our test environments rather start blank copy each time. our live database communicates number of other system databases on other servers means there references servers in our functions , sps, so part of our desentisiation need find , replace on these either its respective test environments server or dummy server may not exist.

from googling around found helpful examples produced script below uses smo alter stored procedures, triggers and functions. reason doing rather running an altered script change objects because try , communicate those servers ensure exist before allowing script references applied. testing on small database locally , on few test environments has been fine seems on of our test environments throwing the error:

exception calling "alter" "0" argument(s): "alter failed storedprocedure 'dbo.sutilmigrateworkflowfromrrp'. "
@ c:\temp\sqlalter\amenddbobjects.ps1:136 char:18
+             $proc.alter <<<< ();
    + categoryinfo          : notspecified: (:) [], methodinvocationexception
    + fullyqualifiederrorid : dotnetmethodexception

to see location of error in script below search #errors here#

all of our sql server servers are supposedly set same virtual machines running on hyper v , based templates little odd happening. unable find reason/solution anywhere my last hope :(

the issue feels permission related when working smo but have no idea of start look.

script

to make script fit in 60,000 characters restriction of post (although sure did not exceed) including process_storedproc function same logic used views/trigger , functions except using respective $db. property object.

param(  [string]$server = "",  # target sql server instance name  [string]$database = "", # target database name  [array]$matchtext = $null,  [array]$replacetext = $null,  [string]$alter = "",  # y/n  [string]$outputfolder = "", # target scripts in , out (rollback) scripts  [int]$manualorauto = 0	 # 0/1 - 0 means manual, 1 means auto(job)  )      #***************************************************  # variables used debug  #***************************************************    # sql server instance name   $server = "server1\sqlinst1"   # database name   $database = "dbname"   $matchtext = ("server2\\sqlinst1","sirius_prd","server3\\sqlinst2","server4\\sqlinst3","server5")    $replacetext = ("server6\sqlinst1","sirius_fix1", "server7\sqlinst2","server7\sqlinst2","server8\sqlinst2" )   # set true if want script alter database objects   $alter = "y"   # location of in , out (rollback) scripts   $outputfolder = "c:\temp\sqlalter\"    #***************************************************  # functions  #***************************************************    # select folder  function select-folder($message='select folder', $path = 0)   {    $object = new-object -comobject shell.application       $folder = $object.browseforfolder(0, $message, 0, $path)    if ($folder -ne $null)    {     $folder.self.path    }   }    # process stored procedures   function process_storedproc  {   if($manualorauto -eq 0)  	{   		write-host "begin processing stored procs"  	}   [array]$matched = $null   foreach($proc in $db.storedprocedures)   {   [bool]$match = $false   foreach( $tempmatchtext in $findandreplace.keys )   {   	if($proc.textbody -match $tempmatchtext)   	{    $matched += $proc    $match = $true    }    if($match)    {    break    }     }   }     # each stored procedure in database   for([int]$i = 0;$i -lt $matched.count; $i++)   {   $proc = $matched[$i]      #define proc in , out file names   $infile = ($changefolder + 'sp_' + $filenamepart1 + [string]$proc.name + '_' + $formatteddate + "_in.sql")   $outfile = ($backupfolder + 'sp_' + $filenamepart1 + [string]$proc.name + '_' + $formatteddate + "_out.sql")     if($manualorauto -eq 0)  	{    	write-host "processing proc: " $proc.name;   }   # creating drop script sp   $spdrop = "if object_id(n'[dbo].["+$proc.name.tostring()+"]') not null  begin  	drop procedure [dbo].["+$proc.name.tostring()+"]  end  go    set ansi_nulls on  set quoted_identifier on  go   "   $ddlend = " go "+" --modifiedbyscript "   $content = ""   $original = ""   	$content = $proc.script()   	$content = $content -replace "set ansi_nulls on", " "   	$content = $content -replace "set quoted_identifier on", " "   $original = $content   $temptextbody = $proc.textbody   foreach( $tempmatchtext in $findandreplace.keys )   {       	if($proc.textbody -match $tempmatchtext)   	{    $tempreplacewith = $findandreplace[$tempmatchtext]    $content = $content -replace $tempmatchtext, $tempreplacewith    $temptextbody = $temptextbody -replace $tempmatchtext, $tempreplacewith    }   }     	# if set true change procedure definition on server!   	if($alter -eq "y")   	{     		$proc.textbody = $temptextbody;   		$proc.alter(); #errors here#       	if($manualorauto -eq 0)  		{   		write-host "altered " $proc.name;   		}  	}   # adding change ddl     if($manualorauto -eq 0)  	{   write-host "adding change ddl:- $ddlin"  	}   out-file -inputobject $spdrop -filepath $ddlin -encoding "default" -append   out-file -inputobject $content -filepath $ddlin -encoding "default" -append   out-file -inputobject $ddlend -filepath $ddlin -encoding "default" -append      # adding backout ddl     if($manualorauto -eq 0)  	{   write-host "adding backout ddl:- $ddlout"  	}   out-file -inputobject $spdrop -filepath $ddlout -encoding "default" -append   out-file -inputobject $original -filepath $ddlout -encoding "default" -append   out-file -inputobject $ddlend -filepath $ddlout -encoding "default" -append     	# creating backout proc definition   if($manualorauto -eq 0)  	{   write-host "creating backout proc:- $outfile"  	}   	out-file -inputobject $spdrop -filepath $outfile -encoding "default" -append;   out-file -inputobject $original -filepath $outfile -encoding "default" -append   out-file -inputobject $ddlend -filepath $outfile -encoding "default" -append     	# create change proc definition     if($manualorauto -eq 0)  	{   write-host "creating change proc:- $infile"  	}   out-file -inputobject $spdrop -filepath $infile -encoding "default" -append   out-file -inputobject $content -filepath $infile -encoding "default" -append   out-file -inputobject $ddlend -filepath $infile -encoding "default" -append   }     if($manualorauto -eq 0)  	{   	write-host "completed processing stored procs"  	}  }  #***************************************************  # constants  #***************************************************  $emptystring = ""  #***************************************************  # process logic  #***************************************************  if($server -eq $emptystring)  {     {   $server = read-host "enter sql server name. eg: localhost\sqlinst1"   }   until ($server -ne $emptystring)  }  if($database -eq $emptystring)  {     {   $database = read-host "enter database name. eg: dbname"   }   until ($database -ne $emptystring)  }  if($alter -eq $emptystring)  {     {   $alter = read-host "do want change part of script? (y/n)"   }   until ($alter -eq "y" -or $alter -eq "n")  }    [hashtable]$findandreplace=@{}    if($matchtext -eq $null)  {     {   $tempmatchtext = $emptystring   $tempreplacetext = $emptystring     {    $tempmatchtext = read-host "enter text search for. (enter c cancel)"    }   until($tempmatchtext -ne $emptystring)   if ($tempmatchtext -ne "c")   {       {    $tempreplacetext = read-host "enter text replace:- $tempmatchtext. (enter c cancel)"    }    until($tempreplacetext -ne $emptystring -or $tempreplacetext -eq "c")   }   if($tempreplacetext -ne "c" -and $tempmatchtext -ne "c")   {    $findandreplace[$tempmatchtext] = $tempreplacetext   }   $continue = read-host -prompt "add search pair? (y/n)"   }   until($continue -eq “n”)  }  else  {   for([int]$i = 0;$i -lt $matchtext.count; $i++)  	{   	$findandreplace[$matchtext[$i]] = $replacetext[$i]  	}  }    if ($outputfolder -eq $emptystring)  {     {   $folder = select-folder   $outputfolder = $folder + "\"   }   until ($outputfolder -ne $emptystring)  }      $date = get-date    # formate date used in names  $formatteddate = $date.year.tostring()+"_"+$date.month.tostring()+"_"+$date.day.tostring()+"_"+$date.hour.tostring()+"_"+$date.minute.tostring()+"_"+$date.second.tostring()   # change script folders. need \ (back slash) on end  $backupfolder = "$outputfolder\backup_"+$formatteddate +"\";	     # 1 file per object, backup & change folders  $changefolder = "$outputfolder\change_"+$formatteddate +"\";		         # load sql management objects assembly (pipe out-null supresses output)  [system.reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo") | out-null     # create our smo objects  $srv = new-object "microsoft.sqlserver.management.smo.server" $server;  $db = new-object ("microsoft.sqlserver.management.smo.database");     # database  $db = $srv.databases[$database];    # create array of directories required created if not exist output files  [array]$outputfolders = $null  $outputfolders += $backupfolder  $outputfolders += $changefolder    # create working folders  for( [int]$i = 0; $i -lt $outputfolders.count; $i++ )  {   if((test-path($outputfolders[$i])) -eq $false)   {     if($manualorauto -eq 0)  	{   write-host "creating directory "$outputfolders[$i]  	}   $newfolder = new-item -path $outputfolders[$i] -type directory   }  }    #create parts 1 , 2 of scriptfilename  $filenamepart1 = ([string]$srv.name -replace("\\", "_")) + "_" + [string]$db.name + "_"    # create in , out ddl file names  $ddlin = $changefolder + "ddl_" + $filenamepart1 + $formatteddate + "_in.sql"  $ddlout = $backupfolder + "ddl_" + $filenamepart1 + $formatteddate + "_out.sql"    process_storedproc     if($manualorauto -eq 0)  	{   	write-host "finished processing $database on $server.";  	}  
 

any appreciated

i have found source of problems after traping exception thrown alter method , looking depths of (see bottom of post exception details).

i had assumed working smo able bypass parsing of changes had made, know theory dangerous , bad practise there situations our test environments did not have respective server still wanted them changing other live servers and i thought method would it. looking @ inner exception of inner exception, turns out understanding wrong , still parse object before amending database cunning plan thwarted :(

i guess next question is there way of bypassing sql parsing of object somehow?

exception

the alter failed snameofsp following error:
- error type:        microsoft.sqlserver.management.smo.failedoperationexception
- error message:     alter failed storedprocedure 'dbo.snameofsp'.
- error target site: void alterimpl()
- error trace:          @ microsoft.sqlserver.management.smo.sqlsmoobject.alterimpl()
   @ microsoft.sqlserver.management.smo.storedprocedure.alter()
   @ alter(object , object[] )
   @ system.management.automation.dotnetadapter.auxiliarymethodinvoke(object target, object[] arguments, methodinformation methodinformation, object[] originalarguments)
-- inner error type   :     microsoft.sqlserver.management.common.executionfailureexception
-- inner error message:     exception occurred while executing transact-sql statement or batch.
-- inner error target site: int32 executenonquery(system.string, microsoft.sqlserver.management.common.executiontypes)
-- inner error trace:          @ microsoft.sqlserver.management.common.serverconnection.executenonquery(string sqlcommand, executiontypes executiontype)
   @ microsoft.sqlserver.management.common.serverconnection.executenonquery(stringcollection sqlcommands, executiontypes executiontype)
   @ microsoft.sqlserver.management.smo.executionmanager.executenonquery(stringcollection queries)
   @ microsoft.sqlserver.management.smo.sqlsmoobject.alterimplfinish(stringcollection alterquery, scriptingoptions so)
   @ microsoft.sqlserver.management.smo.sqlsmoobject.alterimplworker()
   @ microsoft.sqlserver.management.smo.sqlsmoobject.alterimpl()
-- inner inner exception:       system.data.sqlclient.sqlexception: not find server 'servername\sqlinst1' in sys.servers. verify correct server name specified. if necessary, execute stored procedure sp_addlinkedserver add server sys.servers.
   @ microsoft.sqlserver.management.common.connectionmanager.executetsql(executetsqlaction action, object execobject, dataset filldataset, boolean catchexception)
   @ microsoft.sqlserver.management.common.serverconnection.executenonquery(string sqlcommand, executiontypes executiontype)

Windows Server  >  Windows PowerShell