• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

How do I access a SQL Server database

OK, the situation is this.  There is a company setting up a SQL Server database.  I am developing a Web site that needs access to this database using ASP. The database is held at site A, but the Web site is served from site B.  I am familiar with setting up DSN's and referencing them in ASP to connect to MS Access database.
However, when I try to create a DSN for a SQL server database, I get an empty drop-down box to specify the SQL server.  How do I specify the remote SQL server machine at site B.  Also, what do they need to have done at site B to make the SQL Server database accessible in this way.

Thanks for any help
1 Solution
robinMAuthor Commented:
I got my A's and B's mixed up in the above question.  Please assume the database is held at site B, and the Web site is served from site A.  It should be clearer that way.
robinMAuthor Commented:
Sorry, could you give me a bit more info.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Are you using InterDev to create the connection?  You should be able to specify the machine name in your connection string, e.g. Server=SQLservername.

Have you tried typing the name of the SQL server into the dialog box directly (instead of relying on the dropdown list)?

robinMAuthor Commented:
Sorry, I'm not using Visual Interdev.  This is how I specify a connection to a SQL server on the same machine after creating a FileDSN

  ' appears in each .asp file
  ' Create database connection object (dsnPath defined in global.asa)
  Set MyConn=Server.CreateObject("ADODB.Connection")
  MyConn.Open (Application("dsnPath"))

Application("dsnPath") is declared as follows in global.asa

Application("dsnPath") = "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\pathfinder.dsn"
Use the connection object of the ADO to connect to the SQL database. You have to set the provider as SQLOLEDB. See the syntax for the connection string.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now