Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am new to powershell. Even I dont think I know the basics..But now I am assigned a work to do the execution of sql scripts residing in a remote server.

1) we have to connect to that particular database/server using ip and the credentials--For this I need powershell to ask user to give the required parameters(such like server/database name/ip and credentials)

2) The next step is to get the sql scripts from 3 different folders based on 3 different inputs from user--Here I need powershell to ask user to enter the parameters(Eg:a=1st folder, m=2nd folder and x=3rd folder)

3) Then the execution of the script.After the execution, then it should again ask user to continue or exit.

The 3 steps described above is my requirement. Someone please help on this..Any help would be really appreciated..

Thanks in advance..

share|improve this question

closed as off topic by Mat, dezso, StanleyJohns, RolandoMySQLDBA, Max Vernon Jun 17 '13 at 18:15

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
This question seems overly broad for dba, please read dba.stackexchange.com/help/on-topic –  James Jenkins Jun 17 '13 at 11:31
    
@JamesJenkins Not sure I can agree with that comment. The OP is quite explicit in what he/she is looking for and wants to do. –  Thomas Stringer Jun 17 '13 at 13:13
add comment

1 Answer 1

up vote 1 down vote accepted

Note: Before I get into sample code on one way to go about this, I recommend you make every attempt at grasping the programmatic interaction between SQL Server and PowerShell. It is not hard, and by muscling through a few scripts from given requirements like the ones above your proficiency will exponentially grow.

1) we have to connect to that particular database/server using ip and the credentials--For this I need powershell to ask user to give the required parameters(such like server/database name/ip and credentials)

Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServerName = Read-Host "Server name"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

$SqlServer.ConnectionContext.LoginSecure = $false

$SqlServer.ConnectionContext.Login = Read-Host "Login"
$SqlServer.ConnectionContext.SecurePassword = Read-Host "Password" -AsSecureString

2) The next step is to get the sql scripts from 3 different folders based on 3 different inputs from user--Here I need powershell to ask user to enter the parameters(Eg:a=1st folder, m=2nd folder and x=3rd folder)

$SqlScripts = @()

while ($true) {
    $ScriptLocation = Read-Host "Enter location (""x"" if no more needed)"
    if ($ScriptLocation.ToLower() -eq "x") {
        break
    }
    $SqlScripts += $ScriptLocation
}

3) Then the execution of the script.After the execution, then it should again ask user to continue or exit.

foreach ($SqlScript in $SqlScripts) {
    try {
        $SqlServer.Databases["master"].ExecuteNonQuery((Get-Content -Path $SqlScript))
    }
    catch {
        Write-Error $_.Exception
    }
}

As for your last statement "After the execution, then it should again ask user to continue or exit", I'm not quite sure what you're looking to do there. Ask the user to continue doing what? Regardless, the above sample code should give you quite a head start on your requirement. Test this logic out in a non-production environment to ensure it does what you want and rework accordingly. There are a couple of assumptions here (such as you're not looking for a return from the script, which is why I used ExecuteNonQuery(), I don't know your databases layout so it's going to execute in the context of master, so on and so forth).

share|improve this answer
    
Many thanks for your valuable response and sample code. Really it gave me an idea on how to do it..Many Thanks Again.. After the execution in the sense,I have 3 folders having 3 types of sql scripts.I need to execute scripts inside 1st folder.Then I need powershell to show me errors(if any) and then ask user to continue with the next folder scripts.Hope this makes you sense..Also it should automatically sava a text file with those errors(if any).. Any idea on how to achieve this?? –  Vysakh Jun 18 '13 at 8:00
    
In order to save the errors to a text file, instead of having Write-Error $_.Exception in the CATCH{} block, just have an Out-File cmdlet call and append to an error log of your choice. –  Thomas Stringer Jun 19 '13 at 13:12
    
Thanks thomas..When I try to execute the code it shows Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: make sure the assembly containing this type is l oaded. Iam trying to load the dll like this.. [System.Reflection.Assembly]::LoadWithPartialName('D:\VysakhTest\Microsoft.SqlSe‌​rver.Management.Smo.Server') | out-null Am I doing anything wrong??There is no smo dll found in my server so i couldnt test my code.Please help me out on this.. –  Vysakh Jun 20 '13 at 8:50
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.