Connection string for passthrough query on windows 7 64 bit???

[mat]

Using Access 2003 I've been able to use passthrough queries to sql
server 2005 for a long time. In setting up a windows 7 64 bit
workstation I'm having issues that I can't resolve so far.

Normally a connections string like this would work:

ODBC;Driver={SQL Native Client};Server=blink
\sqlexpress;Database=mydb;UID=test_login;PWD=secret

That connection uses sql auth. All I've changed is the server name (old
instance and new instance both run against local copies of sql express).

I suspect that it's the 64 bit vs 32 bit driver thing, which I don't
really have a handle on. When I'm using a dsn I have to start the 32 bit
version of the odbc connection manager to get dsns that Access 2003 can
'see' when linking. Is something like this in play here? I've searched
but not found any solution for this yet.

 

[Dennis]

This is probably completely WRONG, but it's something I discovered only a
week or two ago. I attempted to set up a new ODBC connection with the "SQL
Server" type. That failed completely. After some research, I determined that
I had to install the "SQL Server Native Client 10.0" type. Once I did that,
the new ODBC connection worked perfectly. Again, this probably has NOTHING to
do with your issue, but.... You know...?

 

[mat]

Thanks for the reply...I do think it's in another direction though. The
driver you installed ought to server sql 2008, and I'm working with
2005. I don't have other odbc connectivity issues with this workstation
or server, using dsn, other than needing to use the 32 bit odbc
management tool.

It would seem to be a fair guess that {SQL Native Client} points to the
64 bit version of the odbc driver on a 64 bit windows os, but even if
it's a good guess I don't know how to beat it, and have found nothing re
this on the net so far.

 

[Stefan Hoffmann]

hi Mat,

Using Access 2003 I've been able to use passthrough queries to sql
server 2005 for a long time. In setting up a windows 7 64 bit
workstation I'm having issues that I can't resolve so far.

Try a reinstall/repair of the client tools.

Normally a connections string like this would work:

ODBC;Driver={SQL Native Client};Server=blink
\sqlexpress;Database=mydb;UID=test_login;PWD=secret

http://connectionstrings.com/sql-server-2005#p3

There is imho the version (10.0) missing in your string.


mfG
--> stefan <--

 

[mat]

hi Mat,


Try a reinstall/repair of the client tools.

http://connectionstrings.com/sql-server-2005#p3

There is imho the version (10.0) missing in your string.


mfG
--> stefan <--

But I'm not working with 10.0, it's 9.0, and for that version the
integer has never been required. Not sure if it would even work.

What client tools are you referring to? The Access db has been repaired
etc.

 

[Stefan Hoffmann]

hi Mat,

What client tools are you referring to? The Access db has been repaired
etc.

From the SQL Server CD/DVD-ROM...


mfG
--> stefan <--

 

[Alex Dybenko]

Hi,
Also try to use {sql server} instead of {SQL Native Client}

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[mat]

Hi,
Also try to use {sql server} instead of {SQL Native Client}

That's doesn't seem to help. And I don't see why it would...if the
connections string is having issues because the request is being routed
to a 64 bit version of the odbc driver, I'd expect all of the drivers to
fail in a similiar manner. I'm not sure that I'm guessing right about
the nature of the problem, of course.

Is anyone else facing this issue? It seems odd that what I'm reporting
is 'news'...64 bit workstations using Access 2003 and dsn-less
connections might be new to me but certainly not to the group.

 

[Paul Shapiro]

That's doesn't seem to help. And I don't see why it would...if the
connections string is having issues because the request is being routed
to a 64 bit version of the odbc driver, I'd expect all of the drivers to
fail in a similiar manner. I'm not sure that I'm guessing right about
the nature of the problem, of course.

Is anyone else facing this issue? It seems odd that what I'm reporting
is 'news'...64 bit workstations using Access 2003 and dsn-less
connections might be new to me but certainly not to the group.

I don't use dsn-less connections, but I think Access only uses 32-bit
drivers since it's a 32-bit application. Maybe check the 32-bit
configuration for SQ Server? In SQL 2008 (and I think SQL 2005 too) the SQL
Server Configuration Manager has separate configurations for 32-bit and
64-bit clients.

 

[mat]

I don't use dsn-less connections, but I think Access only uses 32-bit
drivers since it's a 32-bit application. Maybe check the 32-bit
configuration for SQ Server? In SQL 2008 (and I think SQL 2005 too) the SQL
Server Configuration Manager has separate configurations for 32-bit and
64-bit clients.

Yes, all of that I know about. It's not too hard to use the two
different interfaces for the 32 and 64 bit dsns and they work fine on
this computer.

 

[Sylvain Lafontaine]

After having read this thread, it's not clear if your problem is related
only to DSN-Less connection or if you cannot connect even when using a DSN
created with the 32 bit version of the ODBC control panel.

First, Access is a 32 bit application, so it can see only DSN that have been
created the 32 bit version of the ODBC panel and it can use 32 bit drivers
only (with or without a DSN). Normally, both the 32 bit and the 64 bit
versions of the ODBC SQL drivers should have been installed, so you
shouldn't have any problem connecting from Access. Also, when creating the
DSN, the result should be successful if you test it.

There are two versions of the ODBC Native Driver for SQL-Server; one for
2005 and one for 2008 and for the 2008 version, you must add the number 10
after it: {SQL Server Native Client 10.0} instead of {SQL Server Native
Client}; so maybe this is your problem. (See
http://www.connectionstrings.com/ ).

Try reinstalling both the 2005 and the 2008 Native Drivers:

http://www.microsoft.com/downloads/...60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

http://www.microsoft.com/downloads/...EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

I don't remember if you need to install the 64 bit packages only or if you
will have to install the 32 bit package, too. However, it should be easy to
check using the 32 bit version of the ODBC panel after the installation of
the 64 bit package.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[mat]

After having read this thread, it's not clear if your problem is related
only to DSN-Less connection or if you cannot connect even when using a DSN
created with the 32 bit version of the ODBC control panel.

First, Access is a 32 bit application, so it can see only DSN that have been
created the 32 bit version of the ODBC panel and it can use 32 bit drivers
only (with or without a DSN). Normally, both the 32 bit and the 64 bit
versions of the ODBC SQL drivers should have been installed, so you
shouldn't have any problem connecting from Access. Also, when creating the
DSN, the result should be successful if you test it.

There are two versions of the ODBC Native Driver for SQL-Server; one for
2005 and one for 2008 and for the 2008 version, you must add the number 10
after it: {SQL Server Native Client 10.0} instead of {SQL Server Native
Client}; so maybe this is your problem. (See
http://www.connectionstrings.com/ ).

Try reinstalling both the 2005 and the 2008 Native Drivers:

http://www.microsoft.com/downloads/...60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

http://www.microsoft.com/downloads/...EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

I don't remember if you need to install the 64 bit packages only or if you
will have to install the 32 bit package, too. However, it should be easy to
check using the 32 bit version of the ODBC panel after the installation of
the 64 bit package.

There are a places where I did mention that dsn odbc is fine on the 64
bit box like "I don't have other odbc connectivity issues with this
workstation or server, using dsn, other than needing to use the 32 bit
odbc management tool." And someone else started focusing on sql native
client 10.0, and I told him that I'd explicitly said this was for a sql
2005 db. Anyways it's a long thread and not surprising if you missed
some of the back and forth.

What would be very intersting to hear, and which I've mentioned a couple
of times, is if any of you are able to use dsn-less connections with
regular connections strings on a 64 bit os without taking any extra
steps. I can only imagine that many have tried since 64 bit is not
exactly new. If anyone stated that they can use the exact same
connection string on 32 and 64 bit os to sql server 2005, it'd seem to
indicate the issue lies elsewhere; I already stated a couple times that
I'm not sure it's 64 bit that is the issue, only that it seemed to be a
reasonable point to explore. Reasonable because 64 bit os supports two
separate stacks of odbc drivers, and how can the simple connection
string I'm using inform the OS re which to use? Maybe because it's
called by a 32 bit process, I don't know, but you can see why I an
interested in this angle. Nothing else that I'm aware of is different on
this client; that connection string works fine on a lot of workstations,
to date all of which have been 32 bit.

 

[Sylvain Lafontaine]

There are a places where I did mention that dsn odbc is fine on the 64
bit box like "I don't have other odbc connectivity issues with this
workstation or server, using dsn, other than needing to use the 32 bit
odbc management tool." And someone else started focusing on sql native
client 10.0, and I told him that I'd explicitly said this was for a sql
2005 db. Anyways it's a long thread and not surprising if you missed
some of the back and forth.

What would be very intersting to hear, and which I've mentioned a couple
of times, is if any of you are able to use dsn-less connections with
regular connections strings on a 64 bit os without taking any extra
steps. I can only imagine that many have tried since 64 bit is not
exactly new. If anyone stated that they can use the exact same
connection string on 32 and 64 bit os to sql server 2005, it'd seem to
indicate the issue lies elsewhere; I already stated a couple times that
I'm not sure it's 64 bit that is the issue, only that it seemed to be a
reasonable point to explore. Reasonable because 64 bit os supports two
separate stacks of odbc drivers, and how can the simple connection
string I'm using inform the OS re which to use? Maybe because it's
called by a 32 bit process, I don't know, but you can see why I an
interested in this angle. Nothing else that I'm aware of is different on
this client; that connection string works fine on a lot of workstations,
to date all of which have been 32 bit.

The general impression from your first post was that you were unable to
connect from an Access installation running on a 64 bit OS. Now, it looks
that you are able to connect but that you question is about some "extra
step".

What's exactly this extra step that you are talking about and what are the
connection strings that you are actually using on the 32 bit OS and on the
64 bit OS?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[mat]

The general impression from your first post was that you were unable to
connect from an Access installation running on a 64 bit OS. Now, it looks
that you are able to connect but that you question is about some "extra
step".

What's exactly this extra step that you are talking about and what are the
connection strings that you are actually using on the 32 bit OS and on the
64 bit OS?

It's not really a complicated question and I've outlined the issue many
times in this thread, including in my prev response to you. The actual
connection string I am not going to post on a public forum, complete
with password etc, but the original post contains a munged, completely
ordinary connections string that works on 32 bit. The 64 bit conn string
is the same; and fails. That's the question, why does it fail?

 

[Sylvain Lafontaine]

Well, I would say that if your previous posts were clear, you would have
received a clear answer from me or from someone else around here. A
question well stated is a question half solved.

You have made a mention about beeing obligated to make an extra step to make
it work on a 64 bit OS. However, you don't say what exactly is this extra
step and you didn't post an example of a working connection string; so it's
nearly impossible for us to make any call on this. BTW, when we speak about
posting a connection string, we really don't care about seeing the username
and the password and you can safely remove them before posting the rest (but
without forgetting to mention if you are using the Integrated Security of
Windows or a SQL-Server authentication login).

Finally, don't forget that you are the only one in front of your machine,
knowing what things you have installed on it and capable to perform some
basic tests on it.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[mat]

Well, I would say that if your previous posts were clear, you would have
received a clear answer from me or from someone else around here. A
question well stated is a question half solved.

You have made a mention about beeing obligated to make an extra step to make
it work on a 64 bit OS. However, you don't say what exactly is this extra
step and you didn't post an example of a working connection string; so it's
nearly impossible for us to make any call on this. BTW, when we speak about
posting a connection string, we really don't care about seeing the username
and the password and you can safely remove them before posting the rest (but
without forgetting to mention if you are using the Integrated Security of
Windows or a SQL-Server authentication login).

Finally, don't forget that you are the only one in front of your machine,
knowing what things you have installed on it and capable to perform some
basic tests on it.

The extra step needed to get a dsn-less connection working on a 64 bit
OS is what I am looking for, if there is such a thing. No one has
responded to my many requests for input on that. Does the same
connection string work for anyone else on both 32 and 64 bit os? I've
asked that many times here. The extra config options that one must got
through on a 64 bit OS when setting up a dsn is well known to me, as
I've written. The question is, is there anything parallel to that with a
dsn-less connection.

Honestly I've gotten a ton of help from this ng over the years. The
question I've posed here is pretty simple, and shouldn't be esoteric.
Yet I've gotten zilch input. I'm not interested in sparring with you
over whether I've posted a question that is up to your standards. If you
can't understand what I'm asking about after all of this communication,
or simply don't know the answer, then please don't worry about it.

I even asked Mary Chipman about it and she does not seem to know either.
64 bit os are mainstream now, so I don't get why this is such a tough
question.

Simply if someone could tell me that they know from direct experience
that there is or isn't any need for a connection string to sql server
2005 express to be different between a 32 bit and 64 bit os, then at
least I'd know if I was barking up the right tree or not.

 

[Sylvain Lafontaine]

Even on a 64 bit OS, Access is running under the 32 bit emulation mode
because it's a 32 bit application and therefore, the same connection string
should always work for both 32 and 64 bit OS to connect to an instance of
SQL-Server from Access.

Furthermore, if I remember correctly, the Express edition of SQL-Server 2005
can only be installed under the 32 bit mode; contrary to the Express edition
of SQL-Server 2008 which can be installed to run natively under the 64 bit
mode.

Second, you should check the protocol that you are using. With SQL-Server,
three different protocols can be used: the Shared Memory protocol, the Named
Pipe protocol or TCP/IP. Maybe you have a configuration problem at this
level. For example, if TCP/IP is set to be the default protocol to be used
but that it has been inactivated in the configuraton of the SQL-Server, then
you won't be able to connect. You should check your configuration to see
what protocols have been activated.

Finally, when you have a connection problem, you should tell us what your
exact situation. For example, if you can connect using a DSN connection,
then you should told us as this can eliminate some possibilities. Same
thing if you can connect using another program such as SQL Server Management
Studio (SSMS) or if you can connect or not from another machine. Make sure
also that the SQL-Server Service is running properly. If it has not be
started, you won't be able to connect to it from Access. While checking the
service, take the time to verify that its name is SQLExpress and not
something else and an unnamed instance.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[mat]

Even on a 64 bit OS, Access is running under the 32 bit emulation mode
because it's a 32 bit application and therefore, the same connection string
should always work for both 32 and 64 bit OS to connect to an instance of
SQL-Server from Access.

Furthermore, if I remember correctly, the Express edition of SQL-Server 2005
can only be installed under the 32 bit mode; contrary to the Express edition
of SQL-Server 2008 which can be installed to run natively under the 64 bit
mode.

Second, you should check the protocol that you are using. With SQL-Server,
three different protocols can be used: the Shared Memory protocol, the Named
Pipe protocol or TCP/IP. Maybe you have a configuration problem at this
level. For example, if TCP/IP is set to be the default protocol to be used
but that it has been inactivated in the configuraton of the SQL-Server, then
you won't be able to connect. You should check your configuration to see
what protocols have been activated.

Finally, when you have a connection problem, you should tell us what your
exact situation. For example, if you can connect using a DSN connection,
then you should told us as this can eliminate some possibilities. Same
thing if you can connect using another program such as SQL Server Management
Studio (SSMS) or if you can connect or not from another machine. Make sure
also that the SQL-Server Service is running properly. If it has not be
started, you won't be able to connect to it from Access. While checking the
service, take the time to verify that its name is SQLExpress and not
something else and an unnamed instance.

Yes the server is started; dsn connections work fine as I noted many
times. tcp/ip. Names in the string are good.

 

[Sylvain Lafontaine]

Yes the server is started; dsn connections work fine as I noted many
times. tcp/ip. Names in the string are good.

The fact that you can create and use a DSN to connect to the sql-server
eliminates many possibilities but I don't remember seeing a case where you
can connect using a DSN but not using a DSN-less connection. (But the
contrary - to be able to connect with a DSN-less conection but not with a
DSN - is quite frequent.)

I would suggest that you create aliases in order to precisely test for the
protocol to be used. Create both a DSN and DSN-Less connections using each
time the same alias and probably that you will find where the error is.

You can create aliases using the Configuration Tool of SQL-Server 2005.

Also, double check to be sure that you are really using the very same ODBC
provider each time.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)