Sql – Exec sproc from Powershell

execpowershellpowershell-2.0sqlstored-procedures

I would like to execute a stored procedure from Powershell (v2) against a SQL Server 2008 database. Coming from using C# as my primary language, I'm doing it in that fashion. For example, when I need to run a sproc that doesn't return results, here is what I'm doing now:

$con = new-object System.Data.SqlClient.SqlConnection($connectionString)
$cmd = new-object System.Data.SqlClient.SqlCommand("exec MySproc", $con)
$con.Open()
$cmd.ExecuteNonQuery()
$cn.Close()

While perhaps TMTOWTDI, I'd like to know the best way.

I should mention that I'm already familiar with T-SQL and the System.Data namespace. This is really a question about Powershell.

Best Answer

For straight PowerShell I would go with code like you and Andomar have written. However if you are using the PowerShell Community Extensions there are some cmdlets for working with ADO e.g.:

$conn = 'Data Source=.\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI'
$ds = Invoke-AdoCommand -ProviderName SqlClient -ConnectionString $conn `
          -CommandText 'Select * from Authors' -AsDataSet
$ds.Tables


au_id    : 172-32-1176
au_lname : White
au_fname : Johnson
phone    : 408 496-7223
address  : 10932 Bigge Rd.
city     : Menlo Park
state    : CA
zip      : 94025
contract : True

...

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Best Answer

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

Related Topic