Connecting to 32bit SQL Server Express 2008 with C#, can only compile as x86

[sdouble]

Using SQLOLEDB as a provider, I have found that when connecting to a 32 bit server, I have to compile as x86 application. I'm assuming it has something to do with the providers being 32 and 64 bit. When I compile as Any CPU or x64, the application starts but does not connect. Instead, I receive the following exception:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

When I compile as x86, it connects fine. I also found that if I compile it as Any CPU or x86 and run it on a Win7-32 installation, it works and connects. Targeting Any CPU and running it on Win7-64 will not connect. The Win7-32 computer is also the machine hosting the SQL Server.

Why will it not connect from my Win7-64 machine when targetting Any CPU but works as expected when the same executable is run on Win7-32? Alternatively, will I see any issues if I compile as x86 and try to connect to a 64 bit server?

My connection string just for S's & G's:

Provider=SQLOLEDB;Data Source=server\instance;Initial Catalog=database;Integrated Security=SSPI;

Also tried SQLNCLI11 with same result, but would rather not be so specific.

Thanks for any input you can offer!

[techgnome]

"Using SQLOLEDB as a provider," .... whyyyyyy?


you should be using the SQLClient namespace... then you wouldn't have this issue.
The reason (if I remember right) is that the OLEDB provider for SQL Server (And many of the other OLDB providers) are only available in 32-bit... The SQLClient Namespace is designed specifically for SQL Server connectivity and takes care of this issue.


-tg

[sdouble]

"Using SQLOLEDB as a provider," .... whyyyyyy?

I'm only using it because the same application also connects to an oracle database with OraOLEDB.Oracle. I've seen SQLClient but never used it, assumed it was just a way for MS to work with their own products more specifically and I was trying to recycle code. We recently upgraded all of our computers to Win7-64 so I've never experienced 32/64 issues in the past. If this takes care of this issue, I'll switch it up.

I did try this, however, and it seems to be doing the same thing. Works with x86, fails to connect with Any CPU.

SqlConnection sqlconn = new SqlConnection("Data Source=server\\instance;Initial Catalog=database;Integrated Security=SSPI;");
SqlConnection sqlconn = new SqlConnection("Server=server\\instance;Database=database;Integrated Security=true;");

Both of those provided the same result, am I doing it right?

[techgnome]

Huh... well, you got me... I've never seen this before except with Access... for which the 64-bit driver doesn't exist... but when connecting to a server, it shouldn't matter what it is, 32-bit or 64-bit... the code is running on the client... compiling it to x86 should work for now... and it should work when connecting to a 64-bit server... should... but then again, it should work just as it is...


-tg

[Nightwalker83]

Not sure if any of the solutions mentioned here would work?

[sdouble]

Oddly enough, I ported my project into WPF just to mess around a bit and after toying around with it, I noticed I was building with Any CPU and it was working fine. I hadn't even thought of it not working in WF and just went about my business without trouble.

I might just throw together a quick test from scratch with WF to see if maybe I did something to my project somehow.