windows - Executing a .ps1 file from a .bat file -
i have .bat file that executes list of things no issues. powershell execute line: powershell.exe "y:\data\fs01\notouch_deploy.ps1"
i following error: warning: failed load 'sqlas' extension: exception in smo while trying manage service. --> failed retrieve data request. --> invalid class
when run same script through gui, runs no issues. have tried , running simple powershell script in same spot (the test 1 throws windows message box, let me know opening , executing) works well. once add in regular .ps1 , run .bat file error. script included below. quick summary of script: sets few variables creates 2 functions (bcp, , logwrite error trapping) renames tables , fires sql stored procedure. executes bcp function move data server server, executes 1 more procedure.
cls [system.reflection.assembly]::loadwithpartialname("system.windows.forms") ## test msgbox## [system.windows.forms.messagebox]::show("we proceeding next step." , "status") $erroractionpreference = "stop" [void][system.reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo") [void][system.reflection.assembly]::loadwithpartialname("system.data") add-pssnapin sqlserverprovidersnapin100 -erroraction silentlycontinue add-pssnapin sqlservercmdletsnapin100 -erroraction silentlycontinue import-module sqlps -disablenamechecking -erroraction silentlycontinue #import-module sqlas -disablenamechecking -erroraction silentlycontinue set-location 'c:\' # set variables (make table driven final tool creation) $sourceserver = "devserv" $sourcedb = "devdb" $sourceobject = "devtable" $targserver = "prodserv" $targdb = "proddb" $targobject = "prodtable" $containerdb = 'maindb' ################################################################################################################ ## begin function logwrite ## ##################################################################################################### ########### function logwrite () { param ( [string]$logstr, [string]$table, [string]$server, [string]$database ) trap{continue} #$datetime = get-date -uformat "%y:%mm:%d:%h:%m:%s" $loginsquery = "insert dbo.deploy_log ([tablename], [logtext],[datestamp]) values ('" + $table + "', '" + $logstr + "',getdate())" invoke-sqlcmd -serverinstance $sserver -database $sdb -query $loginsquery } ################################################################################################################ ## begin function bcp ################################################################################################################ function bcp () { param ( [string]$sourceserver, [string]$sourcedb, [string]$sourceobject, [string]$targserver, [string]$targdb, [string]$targobject ) $newserver try { $sourcecon = new-object data.sqlclient.sqlconnection $sourcecon.connectionstring = "data source=$sourceserver;database=$sourcedb;integrated security=true" logwrite "$targobject -- beginning bcp transfer process" $sourceobject $targcon = new-object data.sqlclient.sqlconnection $targcon.connectionstring = "data source=$targserver;database=$targdb;integrated security=true" $targcon.open() #[long]$startcount = (invoke-sqlcmd -serverinstance $sourceserver -query "select count(*) 'ct' $targdb.dbo.$targobject")[0] $bcp = new-object data.sqlclient.sqlbulkcopy($targcon.connectionstring, [system.data.sqlclient.sqlbulkcopyoptions]::keepidentity) $fieldsserver = new-object "microsoft.sqlserver.management.smo.server" $targserver $devserver = new-object "microsoft.sqlserver.management.smo.server" $sourceserver $selectstring = 'select ' $devserver.databases[$targdb].tables | ?{$_.name -eq $targobject} | %{$_.columns | %{$selectstring += "[$($_.name)],"}} $selectstring = $selectstring.substring(0, ($selectstring.length - 1)) # remove trailing comma $selectstring += " dbo.$sourceobject" $sourcecon.open() $sqlcmd = new-object "data.sqlclient.sqlcommand" -argumentlist $selectstring, $sourcecon [data.idatareader]$datareader = $sqlcmd.executereader() $bcp.bulkcopytimeout = 0 $bcp.destinationtablename = "dbo.$targobject" $bcp.writetoserver($datareader) $sqlcmd = $null $datareader = $null $sourcecon.close() $bcp.close() $targcon.close() logwrite "$targobject -- transfer complete" $sourceobject; } catch { logwrite "error in bcp subroutine -- $_" $sourceobject; } } ################################################################################################################ ## begin main code section end of functions ################################################################################################################ try { $sqlquery = "sp_rename 'tablename','tablename_deploy'" invoke-sqlcmd -serverinstance $sserver -database $sdb -query $sqlquery $sqlquery ="" # runs sproc on fld server make sure _deploy table exists, empty , indexed. $deploytablesproc = "execute [dbo].[sp_notouch_staging_build]" invoke-sqlcmd -serverinstance $targserver -database $containerdb -query $deploytablesproc # runs bcp process copy records feild new prepared table. bcp $sserver $sdb $sourceobject $targserver $targdb $targobject $sqlquery = "execute [maindb].[dbo].[notify_notouch] @emailtype = 6" invoke-sqlcmd -serverinstance $sserver -database $sdb -query $sqlquery $sqlquery ="" logwrite "no touch table transfer complete table renamed , indexed" $sourceobject; } catch { logwrite "error in transfer process" $sourceobject; } i tried add #import-module sqlas -disablenamechecking -erroraction silentlycontinue did not help. ideas? thanks
since want run script via task scheduler, go ahead , create task on server want on. in actions tab, set following criteria:
program/script field
c:\windows\system32\windowspowershell\v1.0\powershell.exe add arguments field:
-noprofile -executionpolicy unrestricted -nologo -command "&{\\path\to\your\files\filename.ps1 -silent:$true}" if want test batch file, have code this:
schtasks /run /s "servername" /tn "scheduled tasks named" | out-string
Comments
Post a Comment