Avatar of JenH2
JenH2
Flag 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

Powershell

Avatar of undefined
Last Comment
JenH2

8/22/2022 - Mon
Chris Dent


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
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."
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Chris Dent


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
Chris Dent


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
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Dent


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
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...
Chris Dent


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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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

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
Chris Dent

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JenH2

ASKER
Never quite got there, but good effort.  :)