SEARCH
Knowledgebase: ASP .NET / ASP
How do I connect to MSSQL using Classic ASP
Posted by Arvand Sabetian (Import) on 14 July 2009 09:00 PM

Please use the following connection string on our Windows 2008 servers to connect to the local MSSQL service:

"Provider=SQLOLEDB;Data Source=myServerAddress;UID=myUsername; PWD=myPassword; database=myDataBase;"

 


Another method is to use a driver like so:

"Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"

Note: The second method, with the driver, has shown some compatibility problems with some databases.

In the above two examples, substitute the variables where:

myDataBase and myUsername and myPassword are what you've already specified in the control panel under MSSQL databases. myServerAddress can be 'localhost' or you may need to specify '.\SQLEXPRESS'

Lastly, if you are not sure how to use that in context of the code, you may use this sample code:

Set strConnString=Server.CreateObject("ADODB.CONNECTION")
connect = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;UID=USERNAME; PWD=PASSWORD; database=DB;"
strConnString.ConnectionString = connect
strConnString.Open
(139 vote(s))
Helpful
Not helpful
Comments (7)
avesta.arvixe
24 November 2010 07:26 AM
I've noticed that the SQLOLEDB example(the second example using "Provider") works much better with MSSQL Server 2008. The first provider has shown incompatibilities with varchar fields and other items.
Dimitry
12 March 2011 02:59 AM
We use "Driver={SQL Server Native Client 10.0}" method, but first record from SQL DB coming with "indefined" value. Any comments welcome.
Arvand Sabetian
13 March 2011 03:03 AM
I would first make sure that you are connecting fine. If there is no error on connection then the issue is with the SQL command rather than the connection string.
Terry Horton
30 April 2011 01:37 AM
Using another website hosting company, I have the following ASP code to open an Access DB:

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "provider=Microsoft.Jet.OLEDB.4.0; data source=D:\accounts\mqtgolfer\database\mydb.mdb"

My question is what would I use to open the same file on Arvixe?
Arvand Sabetian
02 June 2011 11:30 PM
The first thing you would need to get right is the path to your mdb file. The root of your hosting account is C:\HostingSpaces\USERNAME\ so if your database is under domain.com\data\mydb.mdb then you would put c:\HostingSpaces\USERNAME\domain.com\data\mydb.mdb.

If you get further errors, generally googling the error will get you on your way.
Alkesh Shah
10 October 2011 06:40 AM
I'm using Arvixe as my host and I'm able to open up my MSSQL database and see my tables in the dropdown, but when I select a table get the following error:

An error occurred on the server when processing the URL. Please contact the system administrator.

If you are the system administrator please click here to find out more about this error.
Ryan C
18 October 2011 06:50 AM
Please ensure you are using this method to connect via management studios: http://blog.arvixe.com/how-to-use-microsoft-sql-server-2008-to-connect-to-your-database/

If you still face issues, please try creating a new SQL user and assigning it to your database using our control panel, then updating management studios to reflect this new user and try connecting.

If you continue to face issues, please submit a support ticket so we can better assist you.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).