sql server does not exist - oh but it does!

[thebloke]

Guys

I have a couple of lines of code:

if request.servervariables("HTTP_HOST") = "localhost" Then
    strConnex = "Provider=sqloledb;SERVER=MyLapTop;UID=MyUID;PWD=MyPWD;DATABASE=MyDB"
else
    strConnex = "Provider=sqloledb;SERVER=mywebserver;UID=MyUID;PWD=MyPWD;DATABASE=MyDB"
End if

which, on my laptop (MyLapTop) works just fine. I have another machine, however, sitting right next to me on the same network that can open and query the database on my laptop from Enterprise Manager/Query Analyser however when I try to use the bit of code above I get the error:

Microsoft OLE DB Provider for SQL Server (0x80004005) [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

This is when I try to open the connection using:

Set counterConn = Server.CreateObject("ADODB.Connection")
            counterConn.open strConnex

If, however, I comment out the test for localhost and go straight out the the webserver database then that connects ok.

Any ideas?!

Incidentally, I'm pretty sure that this worked before I went on holiday. I don't know, leave a network engineer in charge of things for 2 weeks and look what happens......!

[vb_dba]

Are you defining the server in your connection string to be "SERVER=(local)" when you are on your laptop, or "SERVER=ComputerName"?

I'm assuming that the machine next to you is NOT your webserver. If that's the case and you have the connection string defined as "SERVER=(local)", then you'll get that error if SQL Server isn't running on that machine.

[thebloke]

The connection string on both machines is the same; both define the local server as the computer name of the laptop MyLapTop where the database is....

[techgnome]

if request.servervariables("HTTP_HOST") = "localhost" Then

I think that's what's failing you.
Given that code, I'm assuming you are running an ASP page, right? When you run it on the laptop, is it actualy running on the laptop, or from the other machine? And when running it from the other machine, is it running on that machine, or on the laptop?

In other words, when you run the page, how are you accessing it? http://localhost ?? or http://some.ip.addr.here or ??????

I'm wondering if it thinks that the server variable in both cases is localhost and tries to use the first connectionstring.

Have you tried to response.write the connection string before opening it? That might give some clue as to what connectionstring it really is using.

Tg

[thebloke]

No, it's 2 different pages. Each running on it's own machine. That bit of the code is correct. It's just so that when I stick it live I don't have to mess around changing connection strings.

[techgnome]

So, do you have SQL Server on both machines?
It sounds like you have PageA on MachineA and PageB on MachineB.... both of which think they are "localhost"..... and so try to connect to "local" server. But you want PageB to connect to database on MachineA. So checking for localhost isn't quite going to work.

Tg

[thebloke]

pageA is on machineA and pageB is on machineB, yes. However I need pageB to connect to the database on machineA if pageB is running on machineB and not on my live web server (machineC, if you like).

[vb_dba]

Have you tried to response.write the connection string before opening it? That might give some clue as to what connectionstring it really is using.

Tg

Have you verified that on machineB it is using the correct connection string?

[techgnome]

D'uh.... Gotcha now.... sorry I was being a bit thick.....

Still, have you tried to print out the connection string before opening the connection? --just to make sure it is in fact connecting to the machine you think it is...


Tg

[thebloke]

Yup, I've done that.

[Ecniv]

Sorry to butt in....

Do you have a firewall on the maachine with the database?
Just wondering if it is (for some reason) blocking the connection...

You did say direct connection works, but that may be ok through the firewalll if you have one, where as the HTML/IIS link might need a specific permission, like when you allow people to view folders with actions etc ...

Just a thought, sorry if it has nothing to do with it.

[thebloke]

You know what? You're a star! Some munter appears to have switched my windows firewall on on my laptop while I was sunning myself in Spain. Just switched it off and job's a good 'un.

Good man, yourself.

Thanks guys for all of your help.

[techgnome]

You know, I thought about that, but when it was mentioned that a direct connect worked, I dicounted that.... That'll learn me. Sometimes it's the simplest of things.

Tg