Entry

How do I connect my ASP server to SQL server on a serparate machine ?

How do I connect to SQL Server using ASP?

Jun 7th, 2003 16:33
Sven Glazenburg, Saravpreet Minhas, Chris Durkin, Jakub Soucek, cat father, http://www.able-consulting.com/ADO_Conn.htm

You can find a whole bunch of useful examples of connection strings, 
including many different ways to connect to SQL Servers at: 
http://www.able-consulting.com/ADO_Conn.htm
You can work with any data source in ASP provided that you create 
system data source on your web server. Go to ODBC Sources in Control 
Panel and create new System DSN (do not use User DNS). Choose ODBC 
driver, choose DSN name and enter further details (database host, 
default username etc.). Of course you must have appropriate ODBC 
driver 
and client software installed.
Then you can use this data source in your ASP script, like:
  ...
  Set objConnection = Server.CreateObject("ADODB.Connection")
  objConnection.ConnectionString 
= "DSN=my_new_data_source;UID=my_username;PWD=my_password"
  objConnection.Open
  ...
You can also create a DSN-less connection to SQL Server and connect 
using TCP/IP:
   dim conn
   dim str
   set conn = server.createobject("ADODB.Connection")
   str = "Provider=SQLOLEDB;"
   str = str & "Data Source=[IP Address,Port];" 'eg. 255.255.66.77,1533
   str = str & "Initial Catalog=[Database name];"
   str = str & "Network Library=dbmssocn"
   conn.open str, "username", "password"
This may also be possible using Provider=MSDASQL, I haven't tried it. 
Be 
careful using the SQLOLEDB provider, I've noticed that certain 
features 
of the Command object when dealing with stored procedures don't work 
properly.
-----------------------------------------------------------------------
From Saravpreet:
You can also use this
oConn.Open "Driver={SQL Server};" & _
           "Server=xxx.xxx.xxx.xxx;" & _
           "Address=xxx.xxx.xxx.xxx,1433;" & _
           "Network=DBMSSOCN;" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;"
Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named 
   Pipes
if still finding problems then email me at saravpreet@hotmail.com