Link to home
Start Free TrialLog in
Avatar of JenH2
JenH2Flag for United States of America

asked on

Executing a SQL stored procedure using PowerShell

I know how to do all this in VBScript, but I'm forcing myself to do everything in PowerShell now, cause I'll never learn it if I don't use it...

I'm simply trying to parse a list of servers (I have a DSN for each) and execute a stored procedure on them.  Can someone point out what I've got wrong?

I keep getting "Exception setting "ConnectionString": "Keyword not supported: 'dsn'."" error on line 7.

$Servers = Get-Content C:\Scripts\servers.txt

ForEach($Server in $Servers)
{
$connstr = "DSN=$Server;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SQLConnection.ConnectionString = $connstr
$SqlCommandText = "exec stored_procedure"
$SQLConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand($SqlCommandText, $SQLConnection)
$SqlCmd.ExecuteReader()
}

Open in new window

Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image


It doesn't like DSN=, syntax for the connection string is below, DSN= is not listed as an option:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

Have you tried this one?

$connstr = "Data Source=$Server;"

Chris
Avatar of JenH2

ASKER

Thanks, that got me a step further, connectionstrings.com said to use DSN=.  Now I'm running into another error on line 9 when I try to open the connection.  I've tested the DSN and know it works, so not sure what the problem is.  Here is the error:
Exception calling "Open" with "0" argument(s): "Login failed for user ''. The user is not associated with a trusted SQL Server connection."
Avatar of JenH2

ASKER

Tried using:
$connstr = "Persist Security Info=False;Integrated Security=true;Initial Catalog=eXpress;server=irvsbxds01"
Instead of a DSN and was able to run through the commands without any errors, but I'm not seeing the database reflect having actually run the command.  Here is the code that I'm working with now:
 

$Servers = Get-Content C:\Scripts\servers.txt

ForEach($Server in $Servers)
{
$connstr = "Persist Security Info=False;Integrated Security=true;Initial Catalog=eXpress;server=$server;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SQLConnection.ConnectionString = $connstr
$SqlCommandText = "exec upd_task_password @username = 'EHASH\VIeuCdf~euko`ep`MRMpJ@', @oldpassword = 'E[JSiPXZe|`p', @newpassword = 'FVZSXJDXyy{djcqUdTFFp'"
$SQLConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand($SqlCommandText, $SQLConnection)
$SqlCmd.ExecuteReader()
}

Open in new window


The SQL Part I wasn't so clear on, I rather wondered if you shouldn't be using ExecuteNonQuery, but I didn't know what the SP did so that's perhaps not correct.

Chris

Does the SP return data? I wonder if the example here adds a bit:

http://msdn.microsoft.com/en-us/library/9kcbe65k.aspx

Translating MSDN examples into PowerShell is quite easy, C# is the nearest.

Otherwise I think looking into ExecuteNonQuery would be more appropriate. It'll be the same as above except for $SqlCmd.ExecuteNonQuery() replacing ExecuteReader().

Chris
Avatar of JenH2

ASKER

That looks better than ExecuteReader, I'm really new at trying this in PowerShell.  When I used ExecuteNonQuery it returned 0, but then the database still doesn't reflect that the stored procedure actually ran.  If I open SQL Studio and run the procedure it works everytime, so its not a problem there, but somehow I can't get it to run properly from within PowerShell.

Hmm this is interesting:

http://msdn.microsoft.com/en-us/library/yy6y35y8%28VS.71%29.aspx

It implies we need this change:

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand($SqlCommandText, $SQLConnection)
$SqlCmd.CommandType = [Data.CommandType]::StoredProcedure

Then continue with the execution... worth a try :)

Chris
Avatar of JenH2

ASKER

Getting the following error now:
Exception calling "ExecuteNonQuery" with "0" argument(s): "Could not find stored procedure ''."
But I know the stored procedure is there...

If you remove Exec and just leave the SP name (and no parameters) does it work (even if execution of the SP itself fails)?

I'm sorry this is vague, I do lots with PowerShell but very little with SQL. It's just a shame the errors are coming back from the SqlClient rather than PowerShell.

Chris
Avatar of JenH2

ASKER

Made some progress today, but still no results...  Found this blog (http://bsonposh.com/archives/217) and it helped a lot on how to build the stored procedure.  The code now executes with no errors, just spits out a "0" when I run it, but then I check the database and nothing changed.
Anyone have some ideas that could help?

$connstr = "Persist Security Info=False;Integrated Security=true;Initial Catalog=databasename;server=servername"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SQLConnection.ConnectionString = $connstr
$SQLConnection.Open()
$cmd = new-Object System.Data.SqlClient.SqlCommand("upd_task_password", $SQLConnection)
$cmd.CommandType = [System.Data.CommandType]‘StoredProcedure’
$cmd.Parameters.Add("@username","EHASH\VIeuCdf~euko`ep`MRMpJ@") | out-Null
$cmd.Parameters.Add("@oldpassword","E[JSiPXZe|`p") | out-Null
$cmd.Parameters.Add("@newpassword","FVZSXJDXyy{djcqUdTFFp") | out-Null
$cmd.ExecuteNonQuery()
$SQLConnection.Close()

Open in new window

Avatar of JenH2

ASKER

Still not sure what to do about this one.  As a work around I made a script that ran the 5 actions that the stored procedure ran individually.  It would still be nice to know why exactly I ran into the problems I did...

My thoughts:  The stored procedure in question ran 5 different update tasks against a database, so I'd get 5 different feedbacks from the procedure telling me the number of records updated by each of the updates.  I think that for some reason PowerShell would run the stored procedure, get the first task update (usually 0) and then quit, leaving the 4 other tasks within the stored procedure undone.  It doesn't make sense though, because the stored procedure runs on the SQL side, PowerShell just executes it.  I'm stuck on it.  If I run the stored procedure from SQL Studio it works fine, but I need to run the stored procedure on 45 different servers, so I wanted to script running them.  I have a work around (run the 5 updates as ExecuteNonQuery individually works), sure, but I'm still curious why this wouldn't work.
ASKER CERTIFIED SOLUTION
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JenH2

ASKER

Still not working, returned "False", which is new.  Oh well, I've got the work around for now.  I've got some other scripts that will have stored procedures that return recordsets coming up soon, so I'll tackle this again then.  Thanks for the help Chris.
Avatar of JenH2

ASKER

Never quite got there, but good effort.  :)