468,544 Members | 1,815 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,544 developers. It's quick & easy.

Connecting ASP to MS SQL SERVER 2000

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.
Expand|Select|Wrap|Line Numbers
  1. <%
  2. 'declare variables
  3. dim conn, rs, x
  4. 'set connection to database
  5. set conn=Server.CreateObject("ADODB.Connection")
  6. conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& Server.MapPath("../yeshti.mdb")
  7. 'create recordset
  8. set rs=Server.CreateObject("ADODB.Recordset")
  9. rs.Open "SELECT serial,make,model,eng_cap from toyota WHERE active='Y' ORDER BY Serial", conn
  10. %>
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.
Jun 18 '08 #1
4 3174
979 Expert 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. conn.Open "Provider=SQLOLEDB; Data Source=YOUR_SERVER_NAME; Initial
  2. 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
Jun 18 '08 #2
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:

Expand|Select|Wrap|Line Numbers
  2. <%
  3. 'declare variables
  4. dim conn, rs, x
  5. 'set connection to database
  6. set conn=Server.CreateObject("ADODB.Connection")
  7. conn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=yeshti; User ID=;Password=;"
  9. 'create recordset
  10. set rs=Server.CreateObject("ADODB.Recordset")
  11. rs.Open "SELECT serial,make,model,eng_cap from toyota WHERE active='Y' ORDER BY Serial", conn
  12. %>
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.....
Jun 23 '08 #3
979 Expert 512MB
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:
Expand|Select|Wrap|Line Numbers
  2. 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
Jun 23 '08 #4
wat if i m using windows authentication only
Oct 7 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Ann Marinas | last post: by
6 posts views Thread by Todd Brewer | last post: by
1 post views Thread by =?Utf-8?B?TmVpbCBQYWRkb2Nr?= | last post: by
3 posts views Thread by Me LK | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.