?
Solved

ASP to connect to SQL Server

Posted on 2003-03-08
18
Medium Priority
?
531 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
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

578 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