Running SQL via powershell

All replies

• 

  

  2

  Sign in to vote

  This is what I use for returning SQL queries:

  $sqlsvr = 'server'
  $database = 'database'
  $sqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlsvr)
  $db = $sqlserver.databases[$database]
  $db.ExecuteWithResults("Select * FROM BaselineSoftware").tables

  Tuesday, August 10, 2010 4:43 PM
• 

  

  4

  Sign in to vote

  Yes, the default timeout is 30 seconds. You should be able to set the timeout to infinite by changing it to zero, but there seems to be a bug there (I have a Connect item open). Instead, one possibility is to set it to the max value, 65535. Another option was proposed by Chad Miller, to create your own Invoke-Sqlcmd:

  function Invoke-Sqlcmd2
  {
  param(
  [string]$ServerInstance,
  [string]$Database,
  [string]$Query,
  [Int32]$QueryTimeout=30
  )

  $conn=new-object System.Data.SqlClient.SQLConnection
  $conn.ConnectionString=”Server={0};Database={1};Integrated Security=True” -f $ServerInstance,$Database
  $conn.Open()
  $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
  $cmd.CommandTimeout=$QueryTimeout
  $ds=New-Object system.Data.DataSet
  $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
  [void]$da.fill($ds)
  $ds.Tables[0]
  $conn.Close()
  }

   

  • Proposed as answer by Marco Shaw Wednesday, August 11, 2010 12:45 AM

  Tuesday, August 10, 2010 5:57 PM
• 

  

  0

  Sign in to vote

  Thanks for the answer, do you have an example of the powershell script in action. lets assume we want to run sp_helpdb

   

  Wednesday, August 11, 2010 9:30 AM
• 

  

  0

  Sign in to vote

  Grant,

  I think that the bug and the Connect item are irrelevant here because they are only applicable to Invoke-SqlCmd, and the OP is not using Invoke-SqlCmd. I'm just clarifying it here for the OP. However your Invoke-Sqlcmd2 function should be able to address OP's needs.

  Wednesday, August 11, 2010 10:38 AM
• 

  

  1

  Sign in to vote

  MrFlinstone,
  
  to change the timeout you can set CommandTimeout property like this:
  $SqlCmd.CommandTimeout = 0
  
  So that your example become this:
  
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = " sp_helpdb"
  $SqlCmd.Connection = $SqlConnection
  $SqlCmd.CommandTimeout = 0
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd
  $DataSet = New-Object System.Data.DataSet
  $SqlAdapter.Fill($DataSet)
  $SqlConnection.Close()
  $DataSet.Tables[0]
  
  The above will work.
  
  To capture info messages (result of print statements) you need to subscribe to a .net event (SqlConnection.InfoMessage). You can download this project: http://powershell4sql.codeplex.com/ for an example of how to do this. If you don't need ado.net datasets you might even be able to *use* powershell4sql unchanged for your needs.
  
  Also this project could be of interest to you: http://sqlpsx.codeplex.com/
  
  Andrew

  • Marked as answer by Mervyn Zhang Friday, August 13, 2010 9:52 AM

  Wednesday, August 11, 2010 10:53 AM
• 

  

  0

  Sign in to vote

  Probably true. I can't help but mention it. It's a pain that you can't set the timeout.

  I think the OP's issue was simply the timeout, but we'll see.

  Wednesday, August 11, 2010 11:30 AM
• 

  

  0

  Sign in to vote

  ok thanks guys, the issue was indeed related to timeout. I have set this to 0 i.e unlimited.

  As for the ability to get print messages written to a text file. I already tried to do this, but what i find is that i am not getting the full message, in my stored procedure proc1 calls proc 2, i notice that i am getting print messages from just proc 1 but not proc2.

  As for using a method to pipe out the results, i am looking for a solution that will not invlve any form of binary isntallation. is this possible ?

  Wednesday, August 11, 2010 12:26 PM
• 

  

  0

  Sign in to vote

  Hello, I'm not sure why you are talking about binary installation, I don't think I suggested that. Anyway here is and example that runs a query and captures the messages. I create two stored procedures sp1 and sp2 each outputs a message and one is called from the other. I can see both messages in the resulting file.

  Here is the code:

  function WriteConnectionEvents($currentEventID) {
      Get-Event | % {
          if ($_.SourceIdentifier -eq $currentEventID) {
              $CurrentEventIdentifier = $_.EventIdentifier;   
              $info = $_.SourceEventArgs   
              Remove-Event -EventIdentifier $CurrentEventIdentifier
              $info.Message
          }
      }
  }
  
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
  
  $eventID = "Connection.Messages."+[datetime]::Now.Ticks;
  Register-ObjectEvent -inputObject $SqlConnection -eventName InfoMessage -sourceIdentifier $eventID
  
  $sp2 = @"
  CREATE PROCEDURE dbo.sp2
  AS
  BEGIN
      print 'Hello from sp2'
      SELECT * from sys.tables
  END
  "@
  
  $sp1 = @"
  CREATE PROCEDURE dbo.sp1
  AS
  BEGIN
      print 'Hello from sp1'
      Exec dbo.sp2
  END
  "@
  
  
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.Connection = $SqlConnection
  $SqlCmd.CommandTimeout = 0
  $SqlCmd.Connection.Open()
  
  $SqlCmd.CommandText = $sp2
  $SqlCmd.ExecuteNonQuery()
  
  $SqlCmd.CommandText = $sp1
  $SqlCmd.ExecuteNonQuery()
  
  $SqlCmd.CommandText = "exec dbo.sp1"
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd
  $DataSet = New-Object System.Data.DataSet
  $SqlAdapter.Fill($DataSet)
  
  $DataSet.Tables[0] > c:\result.txt
  WriteConnectionEvents $eventID >> c:\result.txt
  
  $SqlCmd.CommandText = "drop procedure dbo.sp1"
  $SqlCmd.ExecuteNonQuery()
  
  $SqlCmd.CommandText = "drop procedure dbo.sp2"
  $SqlCmd.ExecuteNonQuery()
  $SqlConnection.Close()
  

  Wednesday, August 11, 2010 8:14 PM
• 

  

  0

  Sign in to vote

  thanks,

  the Timeout = 0 worked great.

  Tuesday, February 9, 2016 2:00 PM