Connecting to SQL database

Discussion in 'Python' started by bill ramsay, Dec 15, 2007.

  1. bill ramsay

    bill ramsay Guest

    Hi

    I have successfully connected to SQL2000 and MSDEE databases in the
    past, however I have to move to SQL2005 and SQLEXPRESS databases now.

    I've tried the following but with no luck [this is what i used in the
    earlier incarnation except the Provider resource was SQLOLEDB.1]

    Conn = Dispatch('ADODB.Connection')
    Conn.ConnectionString = "Provider=SQLNCLI;Server=10.1.1.2;
    Database=csrctest;Uid=bill;Pwd=bill"
    print Conn.ConnectionString

    try:
    print 'trying to open'
    Conn.Open()


    etc.


    All I ever get to is the trying to openline, then it times out.

    I have tried all of the following combinations too:

    Provider=SQLNCLI;Server=localhost;Database=csrctest;Uid=bill;Pwd=bill;

    Provider=SQLNCLI;Server=localhost\SQLEXPRESS;Database=csrctest;Uid=bill;Pwd=bill;

    The SQLEXPRESS database for testing purposes is on my home PC
    [10.1.1.2]

    Any clues s to where I am going wrong?

    Look forward to hearing from someone, anyone!!

    Kind regards

    Bill
    bill ramsay, Dec 15, 2007
    #1
    1. Advertising

  2. En Fri, 14 Dec 2007 23:24:24 -0300, Unknown <>
    escribió:

    > I have successfully connected to SQL2000 and MSDEE databases in the
    > past, however I have to move to SQL2005 and SQLEXPRESS databases now.


    > Conn.ConnectionString = "Provider=SQLNCLI;Server=10.1.1.2;
    > Database=csrctest;Uid=bill;Pwd=bill"


    Look for the right spelling at http://www.connectionstrings.com/

    --
    Gabriel Genellina
    Gabriel Genellina, Dec 15, 2007
    #2
    1. Advertising

  3. bill ramsay

    bill ramsay Guest

    On Fri, 14 Dec 2007 23:35:00 -0300, "Gabriel Genellina"
    <> wrote:

    >En Fri, 14 Dec 2007 23:24:24 -0300, Unknown <>
    >escribió:
    >
    >> I have successfully connected to SQL2000 and MSDEE databases in the
    >> past, however I have to move to SQL2005 and SQLEXPRESS databases now.

    >
    >> Conn.ConnectionString = "Provider=SQLNCLI;Server=10.1.1.2;
    >> Database=csrctest;Uid=bill;Pwd=bill"

    >
    >Look for the right spelling at http://www.connectionstrings.com/



    looks like my spelling is ok, any other ideas?

    thanks for the suggestion above.

    kind regards

    bill
    bill ramsay, Dec 20, 2007
    #3
  4. bill ramsay

    Guest

    On Dec 14, 8:24 pm, bill ramsay <blah@blahdeblah> wrote:
    > Hi
    >
    > I have successfully connected to SQL2000 and MSDEE databases in the
    > past, however I have to move to SQL2005 and SQLEXPRESS databases now.
    >
    > I've tried the following but with no luck [this is what i used in the
    > earlier incarnation except the Provider resource was SQLOLEDB.1]
    >
    > Conn = Dispatch('ADODB.Connection')
    > Conn.ConnectionString = "Provider=SQLNCLI;Server=10.1.1.2;
    > Database=csrctest;Uid=bill;Pwd=bill"
    > print Conn.ConnectionString
    >
    > try:
    > print 'trying to open'
    > Conn.Open()
    >
    > etc.
    >
    > All I ever get to is the trying to openline, then it times out.
    >
    > I have tried all of the following combinations too:
    >
    > Provider=SQLNCLI;Server=localhost;Database=csrctest;Uid=bill;Pwd=bill;
    >
    > Provider=SQLNCLI;Server=localhost\SQLEXPRESS;Database=csrctest;Uid=bill;Pwd=bill;
    >
    > The SQLEXPRESS database for testing purposes is on my home PC
    > [10.1.1.2]
    >
    > Any clues s to where I am going wrong?
    >
    > Look forward to hearing from someone, anyone!!
    >
    > Kind regards
    >
    > Bill


    What Python module are you using? At first I thought you were using
    the adodb module for Python, but your syntax doesn't look right for
    that one.

    Mike
    , Dec 20, 2007
    #4
  5. bill ramsay

    Guest

    On Dec 14, 8:24 pm, bill ramsay <blah@blahdeblah> wrote:
    > Hi
    >
    > I have successfully connected to SQL2000 and MSDEE databases in the
    > past, however I have to move to SQL2005 and SQLEXPRESS databases now.
    >
    > I've tried the following but with no luck [this is what i used in the
    > earlier incarnation except the Provider resource was SQLOLEDB.1]
    >
    > Conn = Dispatch('ADODB.Connection')
    > Conn.ConnectionString = "Provider=SQLNCLI;Server=10.1.1.2;
    > Database=csrctest;Uid=bill;Pwd=bill"
    > print Conn.ConnectionString
    >
    > try:
    > print 'trying to open'
    > Conn.Open()
    >
    > etc.
    >
    > All I ever get to is the trying to openline, then it times out.
    >
    > I have tried all of the following combinations too:
    >
    > Provider=SQLNCLI;Server=localhost;Database=csrctest;Uid=bill;Pwd=bill;
    >
    > Provider=SQLNCLI;Server=localhost\SQLEXPRESS;Database=csrctest;Uid=bill;Pwd=bill;
    >
    > The SQLEXPRESS database for testing purposes is on my home PC
    > [10.1.1.2]
    >
    > Any clues s to where I am going wrong?
    >
    > Look forward to hearing from someone, anyone!!
    >
    > Kind regards
    >
    > Bill


    You might try pymssql:

    http://pymssql.sourceforge.net/

    I've only used it with MSSQL2000, but according to the website, it
    should work with 2005 too.

    If you wrapped your database interface with SQLObject or SQLAlchemy,
    then (as I understand it) the sql connector module can be swapped out
    with little to no work. I hope to move to one of those sooner or
    later:

    http://www.sqlobject.org/
    http://www.sqlalchemy.org/

    Mike
    , Dec 20, 2007
    #5
  6. bill ramsay

    thebjorn Guest

    On Dec 20, 10:01 pm, bill ramsay <blah@blahdeblah> wrote:
    > On Fri, 14 Dec 2007 23:35:00 -0300, "Gabriel Genellina"
    >
    > <> wrote:
    > >En Fri, 14 Dec 2007 23:24:24 -0300, Unknown <>
    > >escribió:

    >
    > >> I have successfully connected to SQL2000 and MSDEE databases in the
    > >> past, however I have to move to SQL2005 and SQLEXPRESS databases now.

    >
    > >> Conn.ConnectionString = "Provider=SQLNCLI;Server=10.1.1.2;
    > >> Database=csrctest;Uid=bill;Pwd=bill"

    >
    > >Look for the right spelling athttp://www.connectionstrings.com/

    >
    > looks like my spelling is ok, any other ideas?
    >
    > thanks for the suggestion above.
    >
    > kind regards
    >
    > bill


    This is working for me to the internal network (I'm using adodbapi,
    but the connection string should be the same)

    'Provider=sqloledb;Data Source=dbserv;Initial Catalog=ndb;User
    Id=foo;Password=bar;'

    I just tested, and this works well too

    'Provider=SQLNCLI;Data Source=dbserv;Initial Catalog=ndb;User
    Id=foo;Password=bar;'

    and to my local instance (Hermes is my machine)

    'Provider=SQLNCLI;Data Source=HERMES\\SQLEXPRESS;Initial
    Catalog=;User Id=sa;Password=pw666;'

    Can you connect to the server using SqlServer Management Studio? Can
    you create an odbc connection to the server? Unlike 2000, SqlServer
    2005 has tcp/ip connections turned off by default, so you might want
    to check that with the SqlServer Surface Area Configuration tool.

    Externally we're still running 2000, so I don't know how connecting
    over the interweb differs.

    When you've figured out how to connect, and if you're open to a
    suggestion, you might want to try out the adodbapi package (http://
    adodbapi.sourceforge.net/). It is a thin wrapper over ADO, and works
    quite well. Then you'll get to use the Python db-api v.2.0 syntax,
    which is oodles less typing than straight ADO.

    -- bjorn
    thebjorn, Dec 20, 2007
    #6
  7. bill ramsay wrote in news: in
    comp.lang.python:

    > Hi
    >
    > I have successfully connected to SQL2000 and MSDEE databases in the


    >
    > Conn = Dispatch('ADODB.Connection')
    > Conn.ConnectionString = "Provider=SQLNCLI;Server=10.1.1.2;
    > Database=csrctest;Uid=bill;Pwd=bill"


    By default SQL 2005 doesn't enable the TCP/IP protocol, if your app'
    is running on the same machine use either the machine name or a
    period (.) and it will use the Shared Memory protocol.

    You will also need the server instance name(*), so it would be:

    "Provider=SQLNCLI;Server=.\SQLEXPRESS;Datab..."

    *) IIRC you can't install express as the "Default" instance so
    this will be required.

    If you need to enable TCP/IP use the "SQL Server Configuration Manager"
    about 4 levels deep from the "Start" menu. Remember to enable it for
    the server and SQLNCLI.

    Rob.
    --
    http://www.victim-prime.dsl.pipex.com/
    Rob Williscroft, Dec 20, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ammar
    Replies:
    2
    Views:
    300
    Ammar
    Jul 8, 2004
  2. Ann Marinas
    Replies:
    12
    Views:
    741
    Guest
    May 5, 2005
  3. Jesse
    Replies:
    4
    Views:
    4,272
    Ryan Stewart
    Jun 6, 2004
  4. =?Utf-8?B?TmVpbCBQYWRkb2Nr?=

    Connecting to SQL 2000 database with SQL 2005 tools installed

    =?Utf-8?B?TmVpbCBQYWRkb2Nr?=, May 2, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    404
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    May 2, 2007
  5. David Thielen
    Replies:
    3
    Views:
    1,576
    Steven Cheng [MSFT]
    Sep 1, 2008
Loading...

Share This Page