Invoke-OledbSql

NAME Invoke-OledbSql



SYNOPSIS

Invokes SQL commands through an OleDB Connection.





SYNTAX

Invoke-OledbSql [-Sql] <String[]> [[-TypeName] <String[]>] [[-Connection] <Object>] [[-Timeout] <Int32>]

[-KeepOpen] [<CommonParameters>]





DESCRIPTION

When a Select is invoked, returns System.Management.Automation.PSCustomObject

objects. When an Update, Insert or Delete is invoked, it returns the affected

count.





PARAMETERS

-Sql <String[]>

SQL command to be invoked.



Required? true

Position? 1

Default value

Accept pipeline input? true (ByValue, ByPropertyName)

Accept wildcard characters? false



-TypeName <String[]>



Required? false

Position? 2

Default value

Accept pipeline input? true (ByValue, ByPropertyName)

Accept wildcard characters? false



-Connection <Object>

Any valid OleDB connection string or a System.Data.OleDb.OleDbConnection object.

Strings are passed to New-OledbConnection to be resolved.

Defaults to using the variable $Conn if set to a SQLConnection object.



Required? false

Position? 3

Default value

Accept pipeline input? false

Accept wildcard characters? false



-Timeout <Int32>

Sets the command timeout in seconds.



Required? false

Position? 4

Default value 0

Accept pipeline input? false

Accept wildcard characters? false



-KeepOpen [<SwitchParameter>]



Required? false

Position? named

Default value False

Accept pipeline input? false

Accept wildcard characters? false



<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,

ErrorAction, ErrorVariable, WarningAction, WarningVariable,

OutBuffer, PipelineVariable, and OutVariable. For more information, see

about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).



INPUTS



OUTPUTS

System.Management.Automation.PSCustomObject

A custom type can be set with TypeName parameter.





NOTES





Though this will handle more than one SQL statement in a single call, it is not recommended.



SEE ALSO

about_OledbSql



-------------------------- EXAMPLE 1 --------------------------



PS C:\\>Invoke-OledbSql 'select 1 as Ping' 'Provider=sqloledb; Data Source=ServerName; Initial

Catalog=DatabaseName;Integrated Security=SSPI;'



Tests SQL connectivity.









-------------------------- EXAMPLE 2 --------------------------



PS C:\\>$cs = 'Provider=sqloledb; Data Source=ServerName; Initial Catalog=DatabaseName;Integrated Security=SSPI;'



$c = New-OledbConnection $cs

Get-Content 'Update_statements_one_per_line.txt' | WHERE { (Invoke-OledbSql -Sql $_ -Connection $c -KeepOpen) -eq

0 } | Set-Content 'failed.txt' -PassThru

$c.Close()











RELATED LINKS