Newbie getting connected

  • vonahsen

    SSC Rookie

    Points: 31

    I've got one MS 2000 server hosting MS Sql 2000, and another 2000 server running IIS 5.0. How do I connect my asp pages to the Sql server? I have a server client network utility server alias on the sql, and a system dsn using sql drivers on the iis box.

    Here is my global.asa (straight from MS, with my server name added):

    sub session_onstart

    set cn = server.createobject("ADODB.connection")

    cn.provider = "sqloledb"

    provstr = "SERVER=63.69.184.61;Database=Northwind;UID=sa;PWD=;"

    cn.open provstr

    set session("cnn") = cn

    end sub

    What am I doing wrong? Does server need to equal the dsn name? Is there a good newbie page around? I have had an exceedingly difficult time finding helpful information (maybe I don't know what I'm looking for).

    Any help would be much appreciated.

    Thanks,

    Barry

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

    Here's what I have in an include file for my ASP pages:

    Dim objConn, rsTable, strConnMatrix

    Set objConn = Server.CreateObject("ADODB.Connection")

    Set rsTable = Server.CreateObject("ADODB.RecordSet")

    strConnMatrixDBA = "provider=SQLOLEDB;Data Source=Matrix;User Id=dba;password=xxxxxx;Initial Catalog=dba"

    I have a strConnxxx for each server/db combination. Then in each ASP page I do the following:

    Dim strQuery

    strQuery = "Select * form MyTable"

    objConn.open strConnMatrixDBA

    Set rsTable = objConn.Execute( strQry)

    Steve Jones

    steve@dkranch.net

  • Andy Warren

    SSC Guru

    Points: 119694

    You're close, but you've got a few things to nail down. The first is how you're going to connect - DSN, DSN-Less, UDL, etc. I've got a couple articles posted here on SSC that you should find useful and not too tedious to read! You mentioned DSN, but you're not referencing one in your code sample.

    I'd recommend that you get your code working in plain VBScript (using Notepad to edit) or VB if you have it, then move it into ASP - that will help you troubleshoot I think. Then start on the ASP stuff.

    I usually build my connection like this:

    'use server.createobject when you're in ASP, use New ADODB.Connection in VB

    set cn = CreateObject("ADODB.Connection")

    cn.open "Provider=SQLOLEDB.1;Password=sqlservercentral;Persist Security Info=True;User ID=sa;Initial Catalog=Northwind;Data Source=MyServer;Application Name=TestApp"

    You'll note one thing I added to my connection string - the application name - its REALLY handy when you're using sp_who to figure out who is doing what.

    Im not a web/asp guy, but I don't think you want to put your connection in a session variable. Typically you want to think client server - when they post a page, open your connection, do whatever you need to do, close the connection. This would go in your .asp page, not global.asa.

    As far as the server utility, it just gives you a way to refer to your server using a "pretty" name like DataServer instead of the IP address. You can use the client utility to do the same thing on the client even if the server is not configured that way - one way or the other, it all comes down to resolving the name to the machine IP.

    Andy

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply