Powershell script changing characters in file -

Powershell script changing characters in file -

disclaimer: finish powershell rookie.

i have powershell script utilize compile big number of sql scripts 1 delivery team migrations. works great, wrapping in transactions , rolling whole thing if errors out, ran issue today.

one of stored procedures has several columns named like

[firstpart-secondpart]

but when re-create them new file changed to

[firstpart?secondpart]

this line concat:

get-childitem -path $inpath -recurse | ?{ ! $_.psiscontainer } | ?{($_.name).contains(".sql")} | sort fullname | %{ out-file -filepath $outpath -inputobject ($commentwrapper + $newline + "-- script: " + $_.fullname + $newline + $commentwrapper + $newline + $newline + "print n'executing script " + $_.basename + "'" + $newline + $scriptheaderpt1 + $_.basename + $scriptheaderpt2 + [system.io.file]::readalltext($_.fullname) + $newline + "go" + $newline + $scriptfooter + $newline ) -append}

here's entire script:

##sqlconcat.ps1## function insert-content { param ( [string]$path ) process { $( ,$_; get-content $path -ea silentlycontinue) | out-file $path } } $newline = "`r`n" $inpath = [environment]::currentdirectory=(get-location -psprovider filesystem).providerpath $outpath = $inpath + "\sql_package.sql" $commentwrapper = "-- ========================================================================================== " ################################### $logtable = "if not exists (select * sys.objects object_id = object_id(n'[dbo].[log_scriptexecution]') , type in (n'u'))" + $newline + "begin" + $newline + " print n'creating log_scriptexecution table.'" + $newline + " set ansi_nulls on" + $newline + " set quoted_identifier on" + $newline + " set ansi_padding on" + $newline + " create table [dbo].[log_scriptexecution] (" + $newline + " [id] int identity(1, 1) not null," + $newline + " [script] varchar(100) collate sql_latin1_general_cp1_ci_as not null," + $newline + " [messages] varchar(max) collate sql_latin1_general_cp1_ci_as null," + $newline + " [executiondate] datetime not null," + $newline + " [executedby] varchar(50) collate sql_latin1_general_cp1_ci_as not null," + $newline + " primary key clustered ([id] )" + $newline + " ( pad_index = off," + $newline + " fillfactor = 100," + $newline + " ignore_dup_key = off," + $newline + " statistics_norecompute = off," + $newline + " allow_row_locks = on," + $newline + " allow_page_locks = on," + $newline + " data_compression = none )" + $newline + " on [primary]" + $newline + " )" + $newline + " on [primary];" + $newline + "end" + $newline + "go" + $newline ################################### $tmperrors = "if exists(select * tempdb..sysobjects id=object_id('tempdb..#tmperrors'))" + $newline + " drop table #tmperrors" + $newline + "go" + $newline + "create table #tmperrors (error int)" + $newline + "go" + $newline + "set xact_abort on" + $newline + "go" + $newline + "set transaction isolation level serializable" + $newline + "go" + $newline + "begin transaction" + $newline + "go" + $newline ################################### $scriptheaderpt1 = "insert log_scriptexecution( script ,messages , executiondate ,executedby)" + $newline + "values ( '" $scriptheaderpt2 = "' ,'executing script' , getdate() ,system_user)"+ $newline + "go" + $newline ################################### $scriptfooter = "if @@error<>0 , @@trancount>0" + $newline + "begin" + $newline + " rollback transaction" + $newline + "end" + $newline + "go" + $newline + "if @@trancount= 0 begin insert #tmperrors(error) select 1 begin transaction end" + $newline + "go" + $newline ################################### $filefooter = "if exists (select * #tmperrors) rollback transaction" + $newline + "go" + $newline + "if @@trancount>0 begin" + $newline + " print 'the database update succeeded.'" + $newline + " commit transaction" + $newline + "end" + $newline + "else print 'the database update failed'" + $newline + "go" + $newline + "drop table #tmperrors" + $newline + "go" + $newline ################################### # # delete existing sql_package.sql file # ################################### write-output "deleting existing file..." if(test-path $outpath) { remove-item $outpath } ################################### # # prompt user target db # ################################### $dbname = read-host 'what target db name?' ################################### # # compile script(s) # ################################### write-output "compiling script..." get-childitem -path $inpath -recurse | ?{ ! $_.psiscontainer } | ?{($_.name).contains(".sql")} | sort fullname | %{ out-file -filepath $outpath -inputobject ($commentwrapper + $newline + "-- script: " + $_.fullname + $newline + $commentwrapper + $newline + $newline + "print n'executing script " + $_.basename + "'" + $newline + $scriptheaderpt1 + $_.basename + $scriptheaderpt2 + [system.io.file]::readalltext($_.fullname) + $newline + "go" + $newline + $scriptfooter + $newline ) -append} ################################### # # rid of utilize [...] statements # ################################### write-output "removing utilize statements..." (get-content $outpath) | foreach-object { $_ -replace 'use \[(\w+)\]', ''} | set-content $outpath ################################### # # add together proper utilize statement , error table check origin of file # , file footer end of # ################################### write-output "updating file..." $usestatement = $newline + "use [" + $dbname + "]" + $newline $usestatement + $newline + $logtable + $tmperrors | insert-content $outpath $filefooter | out-file $outpath -append write-output "finished." ##sqlconcat.ps1##

powershell