Problem with "SQL Server does not exist" error

I have a couple of databases developed and being used in MSDE. The
db's are used and maintained from a couple of programs written in
Visual Basic 6 (using an ADO connection) (these programs run on a
separate computer).
I've just set up a new computer and installed SQL Server Express 2005
on it. I copied the .MDF files from the MSDE unit and attached them to
SQLExpress.
Now, when I try to connect to the databases, I get the "SQL Server
does not exist or access is denied" error. I have, of course, modified
the connection string such that the Data Source property is using the
correct computer name.
I've looked at everything I can think of but obviously I'm missing
something fundamental here. Any suggestions as to what I might be
doing wrong?
Thanks.
On Mon, 20 Nov 2006 16:22:39 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Martin,
>Martin wrote:
>please verify the instance name is correct (in the form
>ComputerName\InstanceName), the remote computer can access the lan and the
>remote SQLExpress installed computer, firewall (both) have been set with the
>appropriate exception(s) allowing connectivity and trafic (both the DBEngine
>used port and the SQLBrowser, if used), the SQLExpress network protocols
>have been enabled, the Surface Area Configuration ahs been set to allow
>remote connections...
Thanks, Andrea. I've gone thru your list but still no luck.
* the name of the new computer is "CATServer" (it's not actually a
"server" - it's running WinXP Pro with all current service packs and
updates). I've tried setting the Data Source to both "CATSERVER" and
"CATSERVER\SQLEXPRESS". Neither way seemed to make any difference.
* the remote computer CAN access CATServer. I have one folder on
CATServer shared and it can be viewed in Windows Explorer. Also, I can
ping CATServer successfully.
* there are no firewalls running on either computer.
* the network protocols: Shared Memory, Named Pipes and TCP/IP are
enabled; VIA is not enabled.
* the Surface Area Configuration was something I did not know anything
about. Remote Connections were set to "Local Connections Only". I
changed that to "Local and Remote" (for both TCP/IP and Named Pipes)
and then stopped and restarted the server service. It made no
difference. I also re-booted the computer but that, too, made no
difference.
Any other thoughts?
|||On Mon, 20 Nov 2006 18:20:18 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Martin,
>Martin wrote:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;328306&Product=sql
Thanks again. I've gone through that fairly well but still haven't
been able to get connected.
I did stumble on to something though: I started the "SQL Server
Browser". Now, when my VB6 program tries to connect, I'm getting a
different error message: "Login failed for user 'userid'" (where
userid is the userid that's in my connect string).
Does this mean that SQL Server Browser has to be running in order for
my program to connect?
Also, when I posted earlier, I was using msado 2.5 in my VB program.
One of the things in the page you referenced mentioned that I needed
at least version 2.6. I have 2.7 (and 2.8) on the machine where VB is
running so I changed the reference to 2.7. That did not seems to make
any difference however. Does that shed any light on the problem I
might be up against?
|||On Mon, 20 Nov 2006 07:58:39 -0700, Martin <martinvalley@.comcast.net>
wrote:

>I have a couple of databases developed and being used in MSDE. The
>db's are used and maintained from a couple of programs written in
>Visual Basic 6 (using an ADO connection) (these programs run on a
>separate computer).
>I've just set up a new computer and installed SQL Server Express 2005
>on it. I copied the .MDF files from the MSDE unit and attached them to
>SQLExpress.
>Now, when I try to connect to the databases, I get the "SQL Server
>does not exist or access is denied" error. I have, of course, modified
>the connection string such that the Data Source property is using the
>correct computer name.
>I've looked at everything I can think of but obviously I'm missing
>something fundamental here. Any suggestions as to what I might be
>doing wrong?
>Thanks.
I finally got this working - sorta...
I had to do three things: I added "\SQLExpress" to the Data Source
name in the client program's connection string. Also in the client
program, I upgraded to version 2.8 of MSADO. On the server side of
things, I added the Login that I was trying to use from the client
side.
With these changes, I am now able to access the data on the server as
needed from the client programs.
With one major exception:
After I got everything working, I recompiled one of the VB programs
and installed it on another computer (call it computer B for
reference). But, when I run it, it encounters the exact same error
condition that I had originally! IOW, this thing works from computer A
but not from computer B.
Yes, computer B has version 2.8 of MSADO on it (I downloaded and
installed a verification tool and checked it out). Computer B has
access to the server computer where SQL Express is running (they're
all on the same LAN).
I'm stumped. Anyone have any ideas what the problem might be?
|||>I finally got this working - sorta...
>I had to do three things: I added "\SQLExpress" to the Data Source
>name in the client program's connection string. Also in the client
>program, I upgraded to version 2.8 of MSADO. On the server side of
>things, I added the Login that I was trying to use from the client
>side.
>With these changes, I am now able to access the data on the server as
>needed from the client programs.
>With one major exception:
>After I got everything working, I recompiled one of the VB programs
>and installed it on another computer (call it computer B for
>reference). But, when I run it, it encounters the exact same error
>condition that I had originally! IOW, this thing works from computer A
>but not from computer B.
>Yes, computer B has version 2.8 of MSADO on it (I downloaded and
>installed a verification tool and checked it out). Computer B has
>access to the server computer where SQL Express is running (they're
>all on the same LAN).
>I'm stumped. Anyone have any ideas what the problem might be?
>
OK, got it going...
Apparently, I have to have the SQL Server Browser service running in
order to connect from computer "B".
Why the Browser service is not needed by computer "A", I have no idea.
|||On Thu, 23 Nov 2006 12:09:01 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Martin,
>Martin wrote:
>SQLBrowser is not needed for local connections as they are not resolved over
>the TCP/IP stack but via shared memory and the port is directly resolved via
>local access...
>try modifying the network protocol (SQL Server Management Studio Express,
>connection dialog, "Options", "Network Protocol" = TCP/IP... you will not
>ba able to connect from local client as well..
I assume by "local connections" you mean "on the same physical
computer where SQL Server is running"? If so, then that is NOT the
case here. Both computer "A" and computer "B" in my situation are
"remote" - that is, they are separate computers on the LAN.
Again, it would seem to me that if one can connect, then the other
should be able to also.