Connecting to SQL database

[bill ramsay]

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

 

[Gabriel Genellina]

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/

 

[bill ramsay]

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

 

[kyosohma]

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

 

[kyosohma]

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

 

[thebjorn]

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

 

[Rob Williscroft]

bill ramsay wrote in 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.