Connecting ASP to MS SQL SERVER 2000

[giandeo]

Frankly speaking, I got much help from Experts CrowCrew, DrBuchman and others when I was learning ASP with MS ACCESS as backend.

When I was using ASP With Access Database, I was using the following codes for connection with the Access Database which resides in the inetpub/wwwroot folder.

<%
'declare variables
dim conn, rs, x
'set connection to database
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& Server.MapPath("../yeshti.mdb")
'create recordset
set rs=Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT serial,make,model,eng_cap from toyota WHERE active='Y' ORDER BY Serial", conn
%>
 

Now I wish to replace MS Access for MS SQL as backend. I have successfully installed MS SQL Server 2000 on my PC running Windows XP.

I have used the Data Transformation Service Import/Export Wizard to import the data from MS Access to MS SQL Server.

I have put login and password for user authentication at the ASP program level. I do not want to put any restriction at the server level.

Could any one please help me connect to the SQL Server and display the information found in the table I have chosen.

[DrBunchman]

Hi giandeo,

Rather than connecting to a database file as you do with Access you need to connect to your server and specify the database which you are going to use. Let's change your connection string to something like the following:

conn.Open "Provider=SQLOLEDB; Data Source=YOUR_SERVER_NAME; Initial
Catalog=your_database_name; User ID=your_username; Password=your_password"

For more information about connection strings with MS SQL take a look at this.

The general syntax for the SQL is very similar between Access and MS SQL and you should find your query above works without any problems.

Let us know if you are able to connect using the above connection string as an example. If it fails then please print the errors here so we can take a look.

Hope this helps,

Dr B

[giandeo]

Hello Dr. B

Sorry Sir, I could not reply for so long because I was ill.

I have tried the codes you suggested, but unfortunately, I could not create a connection to the MS SQL Server.

Here are my codes:

 
<%
'declare variables
dim conn, rs, x
'set connection to database
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=yeshti; User ID=;Password=;"
 
'create recordset
set rs=Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT serial,make,model,eng_cap from toyota WHERE active='Y' ORDER BY Serial", conn
%>
 

I have not put any password or login at the Server Level.

The following errors were reported:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Invalid authorization specification
/shv/test.asp, line 6

Please give me your suggestion.....
Thanks

[DrBunchman]

When you say that you have not put any password or login at the Server Level, does that mean you are using Windows Authentication to log on rather than SQL Server Authentication?

If so, or if you are not sure, then try the following:

 
conn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=yeshti; Integrated Security=SSPI;"
 

You'll need to disable Anonymous Access on your webpage through the IIS and enable Windows Authentication. Doing this means that the IIS will pass your log in credentials to the SQL Server.

Let me know if this works.

Dr B

[vivek shah]

wat if i m using windows authentication only