hi,

i'm using classic asp to try and connect to a sqlexpress database on a development server. i get the following error:

Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/dbtest.asp, line 8

I'm using the following script which runs fine against a regular SQL server (version 8) on the network.

<%@LANGUAGE="JAVASCRIPT"%>

<%
var strCon, conn, sql;

strCon = "Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=rapidHB;User Id=rapid;Password=xxx";
conn=Server.CreateObject("ADODB.Connection");
conn.Open(strCon);

sql = "SELECT product_code FROM products WHERE product_type = 1";

var results= conn.Execute(sql).GetString();
Response.write(unescape(results));
%>

I have tried changing Data Source to servername\SQLEXPRESS, changing initial catalog to master, using a user name defined on the database and changing the provider to SQLNCLI but nothing has worked.

Anyone got any idea what I'm doing wrong? Using ASP.Net is not an option.

Rgds,

lukemack

I believe this is your problem;

Provider=SQLOLEDB.1;Data Source=localhost

Try changing to this if you took the defaults

Provider=SQLNCLI; Data Source=localhost\SQLEXPRESS

thanks for the reply. I;ve tried that and now get the error:

Microsoft SQL Native Client (0x80004005)
Named Pipes Provider: Could not open a connection to SQL Server [2].

My connection string is now:

strCon = "Provider=SQLNCLI;Data Source=localhost\sqlexpress;Initial Catalog=rapidHB;User ID=sa;Password=xxx";

i have checked and the named pipes protocol is enabled for sqlexpress.

any ideas?

thanks,

lukemack.

Is SQL Express local or remote to the ASP machine?

Can you try switching to use TCP/IP?

its local. i'm running iis 5.1 on windows xp pro as a development server. how do i force a tcp/ip connection?

i can connect locally in the management console fine and remote connections are enabled. i was able to connect remotely via a management console on another machine.

anyone? i cant believe how difficult a simple local connection is from asp as compared to php and mysql.

i've noticed that netstat-a does not show specify a port number for sql server. the line is:

TCP lukem1:ms-sql-s lukem1:0 LISTENING

also, i get this error in the sqlserver error log:

The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b

Its stated elsewhere on this forum that this shouldnt be a problem but could this be involved?

thanks,

lukemack

i solved this.

instead of localhost\sqlserver or any variations thereof, i put just a "." and it works. so, for anyone else banging their head against their computer, the connection sctring should look like this:

strCon = "Provider=SQLNCLI;Data Source=.;Initial Catalog=dbName;User ID=sa;Password=xxx";

cheers,

lukemack.

Just so others reading the thread are clear, thsi works because you have installed SQL Express as a default instance as opposed to a named instance, this is not the norm. So the connection estring I supplied will work for named instances, yours will work for default

I'll definitely give this a shot. Thanks for returning to offer your solution to all the rest of us.  Your extra work is much appreciated.