ASP to SQL Server connection string

[ggarman]

I know this is a simple question, but I have never done this before and the connection string I'm using doesn't work, I keep getting 'Client unable to establish connection' What am I doing wrong? I'm writing a ASP page using VBScript. I've tried with and without the braces around the driver.

Here's my string: "Driver={SQL Server};server=edl-esdiii01;uid=sa;pwd=;database=licdb"

Thanks,
Greg

[robert2504]

That looks very different than what I am used to using. Here is what I use as my connection string.

"Provider=SQLOLEDB;Server=MYSERVER;Database=mydata base;UID=me;PWD=password"

I am using SQL Server 2000.

[ggarman]

Robert, thanks for your string, but it still doesn't work. I've had tried one similar to yours earlier, but I was hoping yours would have something I was missing.

Is your ASP page hosted on the same server as your SQL server? Mine is not, so it is a cross-intranet, cross-server connection that I am trying to make. I'm pretty sure the server is setup correctly, because I am able to get to manipulate the table via VB6 code. It just won't play with ASP/VBS.

Any other ideas?

Thanks,
Greg

[Stan]

What type of connection are you using?
Try OLEDB, You will need to make registry entries along these lines:

"DataPath"="NAME_OF_YOUR_SQL_SERVER\\NAME_OF_SQL_I NSTANCE"
"Provider"="SQLOLEDB.1"
"Database"="SQL"
"InitialCatalog"="NAME_OF_YOUR_DATABASE"

Then your connection string should look something like this:

MYDB.dbDBType="SQL"
MYDB.dbDSN = "provider=SQLOLEDB; data source=NAME_OF_YOUR_SQL_SERVER\NAME_OF_SQL_INSTANC E
; Initial Catalog = NAME_OF_YOUR_DATABASE; User ID= sa; Password = YOUR_SA_PASSWORD"
MYDB.dbRSCursor=1

The last line, mydb.dbrscursor=1 can prevent some functionality problems that might occur.

[ggarman]

Thanks for the suggestions Stan, but this is on an intranet so all of the people hitting my page won't have a DSN set up. The connection needs to be client independent. The ASP page should be the only thing supplying the credentials needed to access the data.

Thanks,
Greg

[Stan]

You said you'd never done this before.
My app is hosted on a load balanced web server cluster, my data base resides on a separate SQL cluster, and I have thousands of users around the country and all they have to have is a pc with a browser and an internet or intranet connection as some users reside outside our WAN and others within it.

Users do not need a local dsn at all.

Have you set up the SQL connector on your WEB server?

[ggarman]

Sorry, I misunderstood your message.

I've not been able to get this particular part to work before, I've tried so many combinations and mutations of this string that I wouldn't be able to fit them all in a message.

I don't control the web server and I didn't build the SQL Server. I can make some changes to SQL Server as needed, the only problem is that it is a semi-production server.

Do I make the registry changes just on my development machine? or on the Web server on the SQL Server?

For your data source, what is the 'Name_of_SQL_Instance'? I have no clue what this is.

I've pretty much done your string before, except for the 'instance' part.

Thanks,
Greg

[Stan]

First, you need to talk to whoever is responsible for the web server.
Is aspdb installed on the web server?
Is the data access connection from that web server to the sql server in place?

You don't need to make any changes whatsoever to the sql server.

The registry entries would be made only on the web server, but they infact may not be necessary, but you need to make sure the dataconnection is established via odbc, though with the string I gave you you will be connecting via oledb.


Get your SQL admin to explain what an instance is. He should also abe able to help you with the odbc on the web server.

[ggarman]

Thanks, I give it a try!

Greg