2

Hopefully, this is not a duplicate. I aggregated numerous solutions I came across from the last year or so to get where I am. This is all relatively new to me and I am looking for the most secure and effective solution. When I run this, nothing happens. The intended result is to execute the stored procedure.

$Server = 'Server Name'    
$database = 'DBName'
$userName = 'un'
$password = 'pw'
$Name = 'Name'
$Job = '15'
$Logs = Get-Content -Path $global:LOGFILE
$StartTime ='time'
$End = 'End'
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "Server=$('$Server');Database=$('$Database');trusted_connection=true;User Id=$('$userName');Password=$('$password')"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText ="EXEC dbo.UpdateOutput @Name,@Job,@StartTime,@End,@Status,@Logs"
$Command.Parameters.AddWithValue("@Name", $Name)| Out-Null
$Command.Parameters.AddWithValue("@Job", $Job)| Out-Null
$Command.Parameters.AddWithValue("@Start", $StartTime)| Out-Null
$Command.Parameters.AddWithValue("@End", $End)| Out-Null
$Command.Parameters.AddWithValue("@Status", $Status)| Out-Null
$Command.Parameters.AddWithValue("@Logs", $Logs)| Out-Null
$Command.ExecuteNonQuery()
$Connection.Close()
robdigm
  • 143
  • 2
  • 16
  • 1
    https://www.mssqltips.com/sqlservertip/5927/execute-sql-server-stored-procedures-from-powershell/ – Brad May 14 '19 at 17:51
  • Hey @Brad, that is where I got most of the syntax for what I have written. – robdigm May 14 '19 at 17:57
  • @robdigm Are you _certain_ that the SP doesn't execute? Have you run a SQL trace in Profiler to double check? You could spend a lot of time fixing a problem in the script that doesn't actually exist – Martin May 14 '19 at 18:00
  • @Martin no I did not. Let me research how that is done. – robdigm May 14 '19 at 18:03
  • I ran the tracer and it did not even hit the db. – robdigm May 14 '19 at 18:13
  • No messages in the output either? Also to confirm, the username/password is not YOUR username and password, right? It is a SQL Authentication account? – UnhandledExcepSean May 14 '19 at 18:49
  • here is the message: – robdigm May 14 '19 at 18:51
  • Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed." – robdigm May 14 '19 at 18:54
  • Had to shorten it, but hopefully you get the gist – robdigm May 14 '19 at 18:54
  • The account is accurate. – robdigm May 14 '19 at 18:55

1 Answers1

0

Get rid of the apostrophes in the ConnectionString line as such:

$Connection.ConnectionString = "Server=$($Server);Database=$($Database);trusted_connection=true;User Id=$($userName);Password=$($password)"
UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
  • I get this now: Exception setting "ConnectionString": "Format of the initialization string does not conform to specification starting at index 70." – robdigm May 14 '19 at 19:03
  • @robdigm https://stackoverflow.com/questions/8243008/format-of-the-initialization-string-does-not-conform-to-specification-starting-a seems like the connection string doesn't match the database maker and version – UnhandledExcepSean May 14 '19 at 19:05
  • Ill verify with my DBA – robdigm May 14 '19 at 19:08
  • @robdigm The only thing I can tell you is that the connection string as-is works fine for me connecting to a SQL Server 2014 with a generic sql being executed. – UnhandledExcepSean May 14 '19 at 20:22
  • Ill have the dba take a look at the script and tell me what he thinks because it should work perfect. – robdigm May 14 '19 at 22:06