connectivity issue

hey there

I hope I have posted in correct forum - please let me know

I have 1 XP box - SQL Server Standard 2005 - running IIS (Server 1)

I have 1 XP box - SQL Server Express - running IIS (Server 2)

script called (test)

Server 1 can host web pages eg aspx and collect data from Server 2 (noted in web.config)

http:\localhost\test or http:\Server 1\test - either collect data and display

Server 1 cannot go to http:\Server 2\test - error default settings do not allow remote connections.

Server 2 cannot host web pages and collect data from Server 1 - error default settings do not allow remote connections.

Is this because Server 2 has Express Edition?

thanks

Dianne

Hi JewelFire,

Try following the steps on this page to enable remote connections on your server: http://www.cryer.co.uk/brian/sqlserver/hresult_0x2%20npp_error_establishing_connection.htm

Thanks

Waseem

|||

thanks for this but at the moment

we have turned the firewall off on Server 2?

all services are enabled.

any thing else I should check?

Dianne

sqlsql

Connection string for default instance of SQL Server 2005, on a box also running a named i

The box I am trying to connect to is running two instances of SQL Server.

There is a SQL Server 2005 instance which is the default. There is a SQL Server 2000 instance which is named 'SQLSERVER'.

I can connect to the SQL Server 2000 instance no problem:

<addkey="ConnectionString"value="server=MYPC\SQLSERVER;database=mydatabase;user id=****;password=****"/>

However, I am having trouble connecting to the Default SQL Server 2005 instance. I have tried:

<addkey="ConnectionString"value="server=MYPC;database=mydatabase;user id=****;password=****"/>

but it doesn't work. I have tried explicitly setting SQL Server 2005 to use port 1434 (as SQL Server 2000 is running on port 1433), and then used:

<addkey="ConnectionString"value="server=MYPC,1434;database=mydatabase;user id=****;password=****"/>

but this doesn't work either.

Am I mssing something here? Any help much appreciated

Thanks...

Since both SQL Server 2000 and 2005 are running on the same machine, definitely one of the instance will be the named instance.

I guess SQL 2005 is a named instance. So the connection should be as follows:

server=MYPC\<Instance name>;database=mydatabase;user id=****;password=****

|||

Hi, thanks for the reply.

No, SQL Server 2000 was the named instance. SQL Server 2005 was the default. It was the default instance (SQL Server 2005) that I was having problems connecting to. Ineed, I was having trouble connecting to it with anything other than Enterprise Manager.

However, I have uninstalled / reinstalled SQL Server 2000 and SQL Server 2005, and made them both named instances. I can now connect to them both no problem.

I suspect that had I reinstalled with one or other of them being a default instance, I also would have had no problems. I had noticed a number of other strange behavioural issues in SQL Server 2005 and I suspect something had gone awry in general.

Connection String for "local" connection

I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
using a "localhost" type of connection would be faster than referring to the
server name and thus invoking the DNS lookup. The connection string I am
using as an ASP include is:
Dim Connect
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.open "DRIVER={SQL
Server};SERVER=SERVERName;DATABASE=DBname;UID=USER id;PWD=Password;"
Thanks
Something even better would be to use OLEDB instead of ODBC. You can also
use (local) (with the parenthesis) instead of localhost to use named pipes
instead of TCP/IP but I'm not really sure is this will impact the
performance.
Replace localhost with 127.0.0.1 if you want to make really sure that the
DNS lookup has been shortcutted.
Finally, be default, the connections are pooled, so you shouldn't see any
improvement about shortcutting the DNS lookup.
S. L.
"MICHAEL SHIFFMAN" <mettaworks@.verizon.net> wrote in message
news:rSfAd.7050$1U6.2738@.trnddc09...
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string I
> am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBname;UID=USER id;PWD=Password;"
> Thanks
>
|||MICHAEL SHIFFMAN (mettaworks@.verizon.net) writes:
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string
> I am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBname;UID=USER id;PWD=Password;"
To echo what Sylvain said: change DRIVER=(SQL Server) to Provider=SQLOLEDB.
Changing providers is not entirely painless - some things may broke. But
the default OLEDB-over-ODBC driver is deprecated for SQL Server, and since
it means one extra layer, there is definitely an extra overhead.
The gain in performance you may get by chaning the SERVER parameter is
probably neglible. However, as long as you run SQL Server on the web
server - which is dubious practice - you gain some maintainability by
dropping SERVER altogther. If the server changes name, you don't have
to change the connection string. Then again, it is not unlikely that you
move the SQL Server to a separate box one day - in which case your
question becomes moot.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Connection String for "local" connection

I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
using a "localhost" type of connection would be faster than referring to the
server name and thus invoking the DNS lookup. The connection string I am
using as an ASP include is:
Dim Connect
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.open "DRIVER={SQL
Server};SERVER=SERVERName;DATABASE=DBnam
e;UID=USERid;PWD=Password;"
ThanksSomething even better would be to use OLEDB instead of ODBC. You can also
use (local) (with the parenthesis) instead of localhost to use named pipes
instead of TCP/IP but I'm not really sure is this will impact the
performance.
Replace localhost with 127.0.0.1 if you want to make really sure that the
DNS lookup has been shortcutted.
Finally, be default, the connections are pooled, so you shouldn't see any
improvement about shortcutting the DNS lookup.
S. L.
"MICHAEL SHIFFMAN" <mettaworks@.verizon.net> wrote in message
news:rSfAd.7050$1U6.2738@.trnddc09...
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string I
> am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBnam
e;UID=USERid;PWD=Password;"
> Thanks
>|||MICHAEL SHIFFMAN (mettaworks@.verizon.net) writes:
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string
> I am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBnam
e;UID=USERid;PWD=Password;"
To echo what Sylvain said: change DRIVER=(SQL Server) to Provider=SQLOLEDB.
Changing providers is not entirely painless - some things may broke. But
the default OLEDB-over-ODBC driver is deprecated for SQL Server, and since
it means one extra layer, there is definitely an extra overhead.
The gain in performance you may get by chaning the SERVER parameter is
probably neglible. However, as long as you run SQL Server on the web
server - which is dubious practice - you gain some maintainability by
dropping SERVER altogther. If the server changes name, you don't have
to change the connection string. Then again, it is not unlikely that you
move the SQL Server to a separate box one day - in which case your
question becomes moot.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp