ASP-DB Connection



I'm getting this error

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

''' app database
dim cn
set cn = Server.CreateObject("ADODB.Connection")
appDB = "PROVIDER=SQLOLEDB;SERVER=serverip;UID=username;PWD=password;DATABASE=dbname"
cn.Open appDB <--this line errors
ssaville24Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
Try this:

Provider=SQLOLEDB;Data Source=serverip;User ID=username;Password=password;Initial Catalog=dbname;"

Does it still error?
0
ssaville24Author Commented:
Yes unfortunately, same error.
0
muzzy2003Commented:
OK. What version of SQL Server?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

ssaville24Author Commented:
SQL Server 2000
Windows 2003
IIS6

it works fine with SQL Server 7, Windows 2000, and IIS5
0
lengreenCommented:
Hi

Take a look at

http://www.connectionstrings.com/

cheers

Len
0
muzzy2003Commented:
Sure the username and password is still valid for the SQL Server 2000 box? Can you ping the SQL Server's IP address from the web server? Is the SQL Server running (sorry, you have to ask these questions sometimes, please don't take offence)?
0
GoofyDawgCommented:
Have you upgraded your MDAC as of late? It typically comes with SQL Server service packs, but sometimes, the update doesn't happen. Go to:

http://www.microsoft.com/downloads/results.aspx?productID=&freetext=mdac&DisplayLang=en

and download MDAC 2.8.

I've found that doing this solves a lot of connection problems. Earlier versions of the MDAC don't recognize OLE DB connection strings.

GoofyDawg
0
ssaville24Author Commented:
Thanks GoofyDawg, but I installed the MDAC 2.8 and same error.  

Yes the SQL is running, because it works for different sites and databases.

And muzzy2003 no offense taken.
0
muzzy2003Commented:
Good. :)

Does it work for different sites on the same web server connecting to different databases on the same SQL server? Are you sure the login you are using actually has permissions on the database you are trying to access?
0
Yrag1Commented:
Hi look at this question it should help you.

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21192995.html

my instruction on creating a universal data link are near bottom.

Gary
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ssaville24Author Commented:
Yes on different webpages, it works to connect to a seperate database on the same SQL Server.  Under SQL Enterprise Manager both users are listed in the Users in the seperate databases.  
0
muzzy2003Commented:
Well, this is getting me stumped. Can you copy and paste a working connection string from another page and yours into a reply to this? Just XXX out the password and, if you really feel you need to, the first octet of the IP address.
0
GoofyDawgCommented:
Have you tried using the older connection string method... pre-OLEDB? I've found that sometimes that works:

Provider=MSDASQL; Driver={SQL Server}; Server=server_name_or_address; Database=database_name; UID=username; PWD=password;

This is a DSN-less connection. Also, have you looked at the rights you've assigned to your user accounts?

GoofyDawg
0
ssaville24Author Commented:
Const CONNECTION_PROVIDER   = "SQLOLEDB"

Const CONNECTION_SERVER     = "xxx.xxx.xxx.xxx"
Const CONNECTION_UID        = "username"
Const CONNECTION_PW         = "password"
Const CONNECTION_DATABASE   = "dbname"
Const ADO_CONNECTION_CLASS = "ADODB.Connection"

Function GetOpenConnection()
      Dim objConn
      Set objConn = Server.CreateObject(ADO_CONNECTION_CLASS)
      
      objConn.ConnectionString = "PROVIDER=" & CONNECTION_PROVIDER &_
                                     ";SERVER=" & CONNECTION_SERVER &_
                                     ";UID=" & CONNECTION_UID &_
                                     ";PWD=" & CONNECTION_PW &_
                                             ";DATABASE=" & CONNECTION_DATABASE      
      
      objConn.open
      Set GetOpenConnection = objConn
End Function


And GoofyDawg the rights are set up as public, db_owner.
0
ssaville24Author Commented:
Gary Thanks for your help, I followed those instructions, and it gave me a working connection string !  

Here's what worked...

Provider=SQLOLEDB.1
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.