Menu
I have a large CSV file and I want to execute a stored procedure for each line.
What is the best way to execute a stored procedure from PowerShell?
612
In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases. Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure.
You can call an SQL stored procedure with the execute, open, or get statement; in each case, you use the #sql directive. A stored procedure is a set of instructions for a database, like a function in EGL.
Use Win + R , on your Windows computer, and type PowerShell to launch a new Windows PowerShell session. SQL Server provides a PowerShell module named SqlServer. You can use the SqlServer module to import the SQL Server components (SQL Server provider and cmdlets) into a PowerShell environment or script.
This answer was pulled from http://www.databasejournal.com/features/mssql/article.php/3683181
This same example can be used for any adhoc queries. Let us execute the stored procedure “sp_helpdb” as shown below.
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "sp_helpdb" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() $DataSet.Tables[0] If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
