?
Solved

ASP to connect to SQL Server

Posted on 2003-03-08
18
Medium Priority
?
527 Views
Last Modified: 2008-02-01
In ASP I'm trying to connection to a SQL Server 2000 and need to know the syntax - I did as the Microsoft Documents said but I can't connect to it - I keep getting errors like the following:
"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied."

I created a system dsn which when I test it - it does connect!
 
Here are the best three attempts at this I've tried:

Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "Driver={SQL Server};Server=local;Uid=sa;Pwd=mypw;Database=theweb"

Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=THEWeb;UID=sa;PWD=mypw;"

Set oConn = Server.CreateObject( "ADODB.Connection" )
   Call oConn.Open( "theweb" )
0
Comment
Question by:dylanone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +7
18 Comments
 
LVL 4

Expert Comment

by:Wakie
ID: 8096710
It looks fine to me, perhaps you should go on the generated error:

1. Has the user got access?
2. Does the SQL Server exist?
0
 

Author Comment

by:dylanone
ID: 8096767
FYI:

1) The user does exist
2) The db does exist as well -

3) If I logon to the db with the credential's I'm passing in - I authenticate ? - It's just via the code I get that error -

 

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Expert Comment

by:sybe
ID: 8097033
I use a string like this to connect to sql server:


PROVIDER=SQLOLEDB;DATA SOURCE=computername;USER ID=myusername;PASSWORD=mypassword;DATABASE=database;


>> oConn.Open "Driver={SQL Server};Server=local;Uid=sa;Pwd=mypw;Database=theweb" <<

The part "Server=local" does not sound right. SHouldn't it be : "Server=localhost"
0
 
LVL 2

Expert Comment

by:prokni
ID: 8097117
What about this
oConn.Open "THEWeb","sa","mypw"
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 8097883
Does the IUSR_ account have permission to READ/WRITE to the Database?

Fritz the Blank
0
 

Author Comment

by:dylanone
ID: 8098091
I tried both these strings - have me the following error:

Questions:
1) When I look in SQL Server Group - I see a server which has the name of the database I'm passing in - should that be the servername instead? because off of that is a directory called databases which has my dbname.
2) How do I know what the permissions are on my db -
I only see two files with my db name one is mydbname_data and mydbname.log - thus I have set in the manage section the IUSER_Internet User to read/write?

oConn.Open "Driver={SQL Server};Server=localhost;DSN=myDSN;Uid=sa;Pwd=myPW;Database=MYDBNAME;"

oConn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=localhost;USER ID=sa;PASSWORD=myPW;DATABASE=MYDBNAME;"

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'MYDBNAME'. Login fails.
0
 

Expert Comment

by:subgenius
ID: 8098521
What do you see as you SQL Server instance name?  This should be right under "SQL Server Group" on the left side in Enterprise Manager.  In my case it is the computername\instancename and I use that in my connect string.  I also use sa as the login name and no password.  Here is my connect string (computer and instance names have been changed to protect the innocent).  In this case I'm using the pubs database as you see in the Initial Catalog part of the connect string.

     dim cnn,rst
     
     set cnn = Server.CreateObject("ADODB.Connection")
     cnn.open "Provider=SQLOLEDB; Data Source=machinename\sqlserverinstancename; Initial Catalog=pubs; User Id=sa; Password="
     set rst = Server.CreateObject("ADODB.RecordSet")

Also try this.  Right click on your SQL server instance in Enterprise Manager (right under "SQL Server Group").  Select Properties then the Security tab. Under Authentication, select the "SQL Server and Windows" radio button.  I was getting the same error and this is what finally fixed it.
0
 

Expert Comment

by:How2do
ID: 8098566
Try ADO method (without any ODBC entry) to connect to your SQL 2000 server:

// Java-Script code
myConn = Server.CreateObject("ADODB.Connection");
myConn.Open("Provider=SQLOLEDB; Data Source=Your_Server_Name; Initial Catalog=pubs; User ID=sa; Password=your_password");


If you are not sure what username/password you should use, try to use Query Analyser or Enterprise Manager, you will be asked to login. By default, the SQL server system username is sa and no password.

Also try first to connect to the default SQL database pubs and then if it works, to your own.
0
 
LVL 28

Expert Comment

by:sybe
ID: 8098589
>>
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'MYDBNAME'. Login fails.

<<

at least you have a different error now. I suspect you are a bit further now.

Is there a database called "MYDBNAME" ??
Is the password really "myPW" ??
0
 

Author Comment

by:dylanone
ID: 8098682
Sub-genius - as the instance name I see just the name:
in this case it's HACFSOURCE :

using your methodology for a connect string - I get the following error:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Finlly, I had previously checked that setting in
authenticataion and it is "SQL_Server and Windows"



0
 

Author Comment

by:dylanone
ID: 8098710
How2do -

Interesting - I get the same error I just posted when I try and connect to the pubs db

0
 

Author Comment

by:dylanone
ID: 8098716
sybe -

No these are just placeholder names
0
 
LVL 15

Accepted Solution

by:
gladxml earned 1000 total points
ID: 8100031
0
 
LVL 4

Expert Comment

by:whammy
ID: 8100068
If that doesn't help, I'll provide you with another option tomorrow.

None of these connection strings look remotely like what I use to access SQL Server at work, which is kind of strange. :-\

If you were able to check the DSN, and you connected successfully, then hopefully it will work. :-)
0
 

Author Comment

by:dylanone
ID: 8100494
This one did the trick - it was that easy! Only took me several zillion hours and a lot of help - argh

To connect to SQL Server running on the same computer

oConn.Open "Driver={SQL Server};" & _
           "Server=(local);" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"
 
0
 
LVL 4

Expert Comment

by:ETC-Staff
ID: 13625736
Unfortunately, this didn't work for me.  I have my web server on one box and SQL server on another.

I can:

Create a dsn on the web server that connects to the SQL server
Connect to the SQL server via MS Access

I can't create a web page using ASP that will connect to the SQL server and gets the data.
0
 

Expert Comment

by:How2do
ID: 13821057
FYI:

If you are using an SQL 2000 Name Instance, to connect to a named instance using:

1) JScript, use this format: '<computername>\\<instancename>'
2) VBScript, use this format: '<computername>\<instancename>'

For example,

// Java-Script code
myConn = Server.CreateObject("ADODB.Connection");
myConn.Open("Provider=SQLOLEDB; Data Source=Your_Server_Name\\instancename; Initial Catalog=pubs; User ID=sa; Password=your_password");
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question