6

I have a local DB on my machine called 'Test' which contains a table called 'Tags'. I am able to access this DB and query from this table through SQL Server management studio 2008.

However, when using pyodbc I keep running into problems.

Using this:

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost:1433;DATABASE=Test')

yields the error:

pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection. (14) (SQLDriverConnectW); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid Instance()). (14)')

(with or without specifying the port)

Trying an alternative connection string:

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost\Test,1433')

yields no error, but then:

cur = conn.cursor()
cur.execute("SELECT * FROM Tags")

yields the error:

pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Tags'. (208) (SQLExecDirectW)")

Why could this be?

Alex Kulinkovich
  • 4,408
  • 15
  • 46
  • 50
tafi
  • 227
  • 1
  • 4
  • 11
  • What other combinations have you tried? – ktdrv Oct 02 '11 at 17:12
  • 1
    Well, I copied this DB from an external server, and when I tried to connect to that server, this connection string (without port specification) worked. i.e., "DRIVER={SQL Server};SERVER=DB-TEST-SRV;DATABASE=Test" yielded no error, and querying in the way I specified above yielded the expected result with no error. – tafi Oct 03 '11 at 07:50

5 Answers5

6

I tried changing your query to

SELECT * FROM Test.dbo.Tags

and it worked.

Rob Hruska
  • 118,520
  • 32
  • 167
  • 192
Wayne
  • 61
  • 1
  • 2
3

I don't see any authentication attributes in your connection strings. Try this (I'm using Windows authentication):

conn = pyodbc.connect('Trusted_Connection=yes', driver = '{SQL Server}',
                      server = 'localhost', database = 'Test')
cursor = conn.cursor()
# assuming that Tags table is in dbo schema
cursor.execute("SELECT * FROM dbo.Tags")
Bryan
  • 17,112
  • 7
  • 57
  • 80
1

Try replacing 'localhost' with either '(local)' or '.'. This solution fixed the problem for me.

David Doyle
  • 11
  • 1
1

For me, apart from maintaining the connection details (user, server, driver, correct table name etc.),

I took these steps:

  1. Checked the ODBC version here (Windows 10) ->
  2. (search for) ODBC ->
  3. Select 32/64 bit version ->
  4. Drivers ->
  5. Verify that the ODBC driver version is present there. If it is not, use this link to download the relevant driver: here

Reference Link: here

zx485
  • 28,498
  • 28
  • 50
  • 59
Anand Vamsee
  • 171
  • 2
  • 3
0

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost:1433;DATABASE=Test')

This connection lack of instance name and the port shouldn't be writen like this.

my connection is this:

cn=pyodbc.connect('DRIVER={SQL Server};SERVER=localhost\SQLEXPRESS;PORT=1433;DATABASE=ybdb;UID=sa;PWD=*****')

enter image description here

walkbob
  • 1
  • 1