Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1540
  • Last Modified:

How to try and catch in Powershell with oledb

Hello,

I was wondering how I could Try and Catch or Trap an error when using oledb in Powershell. I have a problem with the following line :

$command = New-Object System.Data.OleDb.OleDbCommand $sqlcommand,$connection

For some reasons, if I change manually the $sqlcommand to my actual SQL command the rest of the script works fine. If I print the value of $sqlcommand, I get the right thing (the actual SQL command I entered manually to make it work) so I know its value is OK.

Any idea how I could try and catch this ?
Function Global:SQL\Invoke-Command ([string] $DataSource, [string] $Database, [string] $SQLCommand, [System.Management.Automation.PsCredential] $Credential) {
# // Prepare the authentication information. By default, we pick Windows authentication
$authentication = Integrated Security=SSPI;
# // If the user supplies a credential, then they want SQL authentication
if($credential)
{
$plainCred = $credential.GetNetworkCredential()
$authentication =
(uid={0};pwd={1}; -f $plainCred.Username,$plainCred.Password)
}
# // Prepare the connection string out of the information they provide
$connectionString = Provider=sqloledb;  +
Data Source=$dataSource;  +
Initial Catalog=$database;  +
$authentication; 
# // Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlcommand,$connection
$connection.Open()
# // Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataset)
$connection.Close()
# // Return the dataset
Return $dataset

Open in new window

0
tech_ci_sherbe
Asked:
tech_ci_sherbe
  • 2
1 Solution
 
VeeNashiCommented:
try this..


trap [Exception]
      {
            write-error $("TRAPPED: " + $_.Exception.Message);
            continue;
      }

Function Global:SQL\Invoke-Command ([string] $DataSource, [string] $Database, [string] $SQLCommand, [System.Management.Automation.PsCredential] $Credential) { 
trap [Exception] 
	{
		write-error $("TRAPPED: " + $_.Exception.Message);
		continue;
	}
 
# // Prepare the authentication information. By default, we pick Windows authentication 
$authentication = Integrated Security=SSPI; 
# // If the user supplies a credential, then they want SQL authentication 
if($credential) 
{ 
$plainCred = $credential.GetNetworkCredential() 
$authentication = 
(uid={0};pwd={1}; -f $plainCred.Username,$plainCred.Password) 
} 
# // Prepare the connection string out of the information they provide 
$connectionString = Provider=sqloledb;  + 
Data Source=$dataSource;  + 
Initial Catalog=$database;  + 
$authentication;  
# // Connect to the data source and open it 
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString 
$command = New-Object System.Data.OleDb.OleDbCommand $sqlcommand,$connection 
$connection.Open() 
# // Fetch the results, and close the connection 
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command 
$dataset = New-Object System.Data.DataSet 
[void] $adapter.Fill($dataset) 
$connection.Close() 
# // Return the dataset 
Return $dataset

Open in new window

0
 
tech_ci_sherbeAuthor Commented:
Thanks, that's exactly what I was looking for. For some odd reasons, my script started working even though I let the $sqlcommand. It still helps me troubleshoot further though. Thanks a lot I should have asked earlier instead of wasting plenty of time :P <3 expert exchange.
0
 
tech_ci_sherbeAuthor Commented:
Just wanted to mention that I solved the mystery behind the SQL command. The single quotes between the variable weren't the good one.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now