Connecting to SQLExpress from classic ASP.

Migrating a site to a new server. Moved data to SQLExpress. Changed connection string to point to SQLExpress and now I am getting this error.

Microsoft OLE DB Provider for SQL Servererror '80004005'

Cannot open database "PHCSQL" requested by the login. The login failed.

/func/inc_DatabaseFunctions.inc, line 5
Do I need to do permissions differently than MSDE?
Did connection strings change?
Help?

Hi,
Download the full eval version and install it as a named instance and register the Express. How install the full version management studio which is a separate install and right click at the top to register the Express. Then create permissions for Asp in the security section of management studio. Another option is to just restore the backup .bak file of the MSDE with the backup and restore wizard and choose the restore from a device option. Try the links below to download the eval verion and connection strings including old style connection strings. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

http://www.carlprothman.net/Default.aspx?tabid=81

connecting to sqlexpress from classic asp

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

Connecting to SQL Server 2005 from Classic ASP

I am trying to return multiple recordsets to a classic ASP web page from SQL Server 2005 and then use GetRows() in ASP to fill 2 arrays with the data. I'm using a command object to run a stored procedure which performs 2 simple selects in SQL Server. The stored procedure works fine in SQL Server Management Studio so I'm guessing it's an ADO issue.

The ASP code looks like this:

strConnect = "DRIVER={SQL Native Client};SERVER=MyServer.IsAtMyIsp.com;DATABASE=MyDb;UID=Me;PWD=MyPwd; MARS Connection=True;"

Set conn = Server.CreateObject("ADODB.Connection")

conn.ConnectionString = strConnect

conn.Open

Set objCommand = Server.CreateObject("ADODB.Command")

Set objRecordset = Server.CreateObject("ADODB.Recordset")

Set objRs = Server.CreateObject("ADODB.Recordset")

With objCommand

.ActiveConnection = conn

.CommandText = "sp_GetShowList"

.CommandType = adCmdStoredProc

If Len(strBeginDate) And IsDate(strBeginDate) Then

.Parameters.Append .CreateParameter("@.i_DateStart",adDate,adParamInput,,strBeginDate)

If Len(strEndDate) And IsDate(strEndDate) Then

.Parameters.Append .CreateParameter("@.i_DateEnd",adDate,adParamInput,,strEndDate)

End If

End If

End With

Set objRs = objCommand.Execute

arrEvents = objRs.GetRows()

objRs.NextRecordset

arrSched = objRs.GetRows()

The error message below occurs at the 2nd GetRows() command.

Operation is not allowed when the object is closed.

Can anyone tell me what I might be doing wrong?

-Dan

You don't assign the second recordset returned by NextRecordset to the recordset variable

Try

Set objRs = objCommand.Execute
arrEvents = objRs.GetRows()
objRs = objRs.NextRecordset
arrSched = objRs.GetRows()

|||

That was so obvious I feel like an idiot. Thanks for the response!

-Dan