What might be the best/most efficient way to have a SQL query run on a
remote machine? ADO.NET? ODBC? Other?

The remote machine will be running MSDE or possibly MS-SQL. Dunno if
there's a stored procedure I could use, but let's assume not.

Might the answer depend on the SQL query?



Marco

Marco Shaw wrote:
> What might be the best/most efficient way to have a SQL query run on a
> remote machine? ADO.NET? ODBC? Other?
>
> The remote machine will be running MSDE or possibly MS-SQL. Dunno if
> there's a stored procedure I could use, but let's assume not.
>
> Might the answer depend on the SQL query?
>
> Marco

Hi Marco,
since powershell is heavily .NET oriented i'd suggest you go with ADO.NET as you can allso apply your knowledge of ADO.NET to .NET programming in general.
Powershell allso knows out of the box how to format ADO.NET objects in the pipeline, for instance a cmdlet returning a DataTable to the pipeline will result in a format-table output with the columns in the datatable, on the contrary returning an ADODB Recordset results in a format-list of the properties of the Recordset object and you'll have to create custom formatting to display that nicely.

Ado.NET comes two general purpose data providers: ODBC or OLEDB, which one to use depends on your database driver, MSSQL and mysql can be accessed via ODBC, but in the case of MS SQL Server there are specialized classes in the System.Data.SqlClient namespace, and for Oracle there's the System.Data.OracleClient .

The type of query will allow you to optimize the flow of data using Execute... method of an Ado.NET DbCommand if you need the results in a certain format (1e one valye, one row or a DataReader) or no results at all , i'll paste some info from .NET Framework 2.0 Documentation:

ExecuteScalar Executes the query and returns the first column of the first row in the result set returned by the query. All other columns and rows are ignored.

You can use ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database by executing UPDATE, INSERT, or DELETE statements.
Although ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

hth

Joris

Definitely ADO.NET if at all possible. I'll send you an example
--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk


"Marco Shaw" wrote:

> What might be the best/most efficient way to have a SQL query run on a
> remote machine? ADO.NET? ODBC? Other?
>
> The remote machine will be running MSDE or possibly MS-SQL. Dunno if
> there's a stored procedure I could use, but let's assume not.
>
> Might the answer depend on the SQL query?
>
> Marco
>
>
>