Vista - Powershell 32 vs. 64 when using ADODB

Hi Group,

I've been trying to create a simple script to extract data from an SQL2005
database using PowerShell.

However, when trying to use ADODB in powershell (from a Win2K3R2SP1 server)
I get the following problem:

Script:
$objConnection = New-Object -comobject ADODB.Connection
$objConnection.Set_ConnectionString("Provider=SQLOLEDB; Data
Source=servername\instancename; Initial Catalog=master; Integrated
Security=SSPI")

On PowerShell 32bit, there are no problems, but the 64bit version gives the
following result:

Method invocation failed because
[System.__ComObject#{00000550-0000-0010-8000-00aa006d2ea4}] doesn't contain a
method named 'Set_ConnectionString'.
At line:1 char:36
+ $objConnection.Set_ConnectionString( <<<< "Provider=SQLOLEDB; Data
Source=servername\instancename; Initial Catalog=master; Integrated
Security=SSPI")

How do I solve this issue ??

As PowerShell is .net based why not use the .net providers eg

#init the command and connection
$connString = "server=.;Integrated Security=SSPI;database=adventureworks"
$cn = new-object "System.Data.SqlClient.SqlConnection" $connString

# create the command
$cmd = new-object "System.Data.SqlClient.SqlCommand"
$cmd.CommandType = [System.Data.CommandType]"Text"
$cmd.CommandText = "SELECT * FROM Production.Product"

#get the data
$dt = new-object "System.Data.DataTable"
$cmd.Connection = $cn
$cn.Open()
$rdr = $cmd.ExecuteReader()
$dt.Load($rdr)
$cn.Close()
$dt | Format-Table


--
Richard Siddaway

Please note that all scripts are supplied "as is" and with no warranty


"Jakob Bindslet" wrote:

> Hi Group,
>
> I've been trying to create a simple script to extract data from an SQL2005
> database using PowerShell.
>
> However, when trying to use ADODB in powershell (from a Win2K3R2SP1 server)
> I get the following problem:
>
> Script:
> $objConnection = New-Object -comobject ADODB.Connection
> $objConnection.Set_ConnectionString("Provider=SQLOLEDB; Data
> Source=servername\instancename; Initial Catalog=master; Integrated
> Security=SSPI")
>
> On PowerShell 32bit, there are no problems, but the 64bit version gives the
> following result:
>
> Method invocation failed because
> [System.__ComObject#{00000550-0000-0010-8000-00aa006d2ea4}] doesn't contain a
> method named 'Set_ConnectionString'.
> At line:1 char:36
> + $objConnection.Set_ConnectionString( <<<< "Provider=SQLOLEDB; Data
> Source=servername\instancename; Initial Catalog=master; Integrated
> Security=SSPI")
>
> How do I solve this issue ??
>
>

On Fri, 29 Dec 2006 06:58:01 -0800, RichS
<RichS@discussions.microsoft.com> wrote:

>As PowerShell is .net based why not use the .net providers eg
>
>#init the command and connection
>$connString = "server=.;Integrated Security=SSPI;database=adventureworks"
>$cn = new-object "System.Data.SqlClient.SqlConnection" $connString
>
># create the command
>$cmd = new-object "System.Data.SqlClient.SqlCommand"
>$cmd.CommandType = [System.Data.CommandType]"Text"
>$cmd.CommandText = "SELECT * FROM Production.Product"
>
>#get the data
>$dt = new-object "System.Data.DataTable"
>$cmd.Connection = $cn
>$cn.Open()
>$rdr = $cmd.ExecuteReader()
>$dt.Load($rdr)
>$cn.Close()
>$dt | Format-Table

Yes, that would be a solution. But I would still like to know what
differences there are between PowerShell 32 and 64.

Has anybody compared speed or functionality between the two versions ?


--
Jakob Bindslet

Jakob Bindslet wrote:

>On Fri, 29 Dec 2006 06:58:01 -0800, RichS
><RichS@discussions.microsoft.com> wrote:
>
>>As PowerShell is .net based why not use the .net providers eg
>>
>>#init the command and connection
>>$connString = "server=.;Integrated Security=SSPI;database=adventureworks"
>>$cn = new-object "System.Data.SqlClient.SqlConnection" $connString
>>
>># create the command
>>$cmd = new-object "System.Data.SqlClient.SqlCommand"
>>$cmd.CommandType = [System.Data.CommandType]"Text"
>>$cmd.CommandText = "SELECT * FROM Production.Product"
>>
>>#get the data
>>$dt = new-object "System.Data.DataTable"
>>$cmd.Connection = $cn
>>$cn.Open()
>>$rdr = $cmd.ExecuteReader()
>>$dt.Load($rdr)
>>$cn.Close()
>>$dt | Format-Table
>
>Yes, that would be a solution. But I would still like to know what
>differences there are between PowerShell 32 and 64.

The difference (in this case at least) is not PowerShell. The difference
is that ADO only comes in 32-bit. I'm surprised that you can even
instantiate an ADO COM object in 64-bit!

--
Steve Foster [SBS MVP]
---------------------------------------
MVPs do not work for Microsoft. Please reply only to the newsgroups.

On Fri, 29 Dec 2006 15:20:43 -0800, "Steve Foster [SBS MVP]"
<steve.foster@picamar.co.uk> wrote:

>>On Fri, 29 Dec 2006 06:58:01 -0800, RichS
>><RichS@discussions.microsoft.com> wrote:
>>
[snip]
>>
>>Yes, that would be a solution. But I would still like to know what
>>differences there are between PowerShell 32 and 64.
>
>The difference (in this case at least) is not PowerShell. The difference
>is that ADO only comes in 32-bit. I'm surprised that you can even
>instantiate an ADO COM object in 64-bit!

Where are you getting this information?

According to:
http://msdn2.microsoft.com/en-us/library/ms676506.aspx

"ADO 2.7 introduces support for 64-bit processors."

I've used ADO from C# on an x64 machine to connect to my company's
64-bit OLE DB provider, so I know it works.

Chuck

Chuck Heatherly wrote:

>On Fri, 29 Dec 2006 15:20:43 -0800, "Steve Foster [SBS MVP]"
><steve.foster@picamar.co.uk> wrote:
>
>>>On Fri, 29 Dec 2006 06:58:01 -0800, RichS
>>><RichS@discussions.microsoft.com> wrote:
>>>
>[snip]
>>>
>>>Yes, that would be a solution. But I would still like to know what
>>>differences there are between PowerShell 32 and 64.
>>
>>The difference (in this case at least) is not PowerShell. The difference
>>is that ADO only comes in 32-bit. I'm surprised that you can even
>>instantiate an ADO COM object in 64-bit!
>
>Where are you getting this information?

Ah, um, I might have been getting ADO mixed up with DAO.


>According to:
>http://msdn2.microsoft.com/en-us/library/ms676506.aspx
>
>"ADO 2.7 introduces support for 64-bit processors."
>
>I've used ADO from C# on an x64 machine to connect to my company's
>64-bit OLE DB provider, so I know it works.

ADO or ADO.NET?

--
Steve Foster [SBS MVP]
---------------------------------------
MVPs do not work for Microsoft. Please reply only to the newsgroups.

On Fri, 05 Jan 2007 16:59:16 -0800, "Steve Foster [SBS MVP]"
<steve.foster@picamar.co.uk> wrote:

>Chuck Heatherly wrote:
>
>>I've used ADO from C# on an x64 machine to connect to my company's
>>64-bit OLE DB provider, so I know it works.
>
>ADO or ADO.NET?

ADO, also referred to by its library name ADODB, as in the title of
this thread. The OLE DB provider I'm using in my case doesn't support
SQL, so ADO.NET is useless.

Chuck