Can Not Find Table In Connected Mssql-db

[ist1809]

Hi,

in Progress I connected to a MSSQL-DB via ADO. The Connection is ok. But if I want to open with a "select *..." the message "...invalid objectname 'AAusDatei1'. Error code: 0x80020009..." pops up.

This is my first try with SQL and I don't know where I have to look first. Is it a code problem or DB Problem?

Here comes the code.

def var gvch_dbconn  as com-handle no-undo.
def var gvch_dataset as com-handle no-undo.

def var gvc_provider as character  no-undo.
def var gvc_server  as character  no-undo.
def var gvc_dbname  as character  no-undo.
def var gvc_userid  as character  no-undo.
def var gvc_userpwd  as character  no-undo.
def var gvc_connect  as character  no-undo.
def var gvc_befehl  as character  no-undo.
def var gvi_zaehler  as integer  no-undo.

ASSIGN
  gvc_provider = 'SQLOLEDB':U
  gvc_dbname  = 'HOMAG':U
  gvc_server  = '.':U
  gvc_userid  = 'sa':U
  gvc_userpwd  = 'istist':U
  gvc_connect  = 'provider=':U  + gvc_provider + ';':U
                           + 'server=':U  + gvc_server  + ';':U
                           + 'datasource=':U + gvc_dbname.

create "ADODB.Connection" gvch_dbconn.
create "ADODB.RecordSet"  gvch_dataset.

gvch_dbconn:open (gvc_connect,gvc_userid,gvc_userpwd,0) no-error.

if error-status:error or error-status:num-messages > 0
then do:
  message "Connect-Fehler".
  do gvi_zaehler = 1 to error-status:num-messages:
    message error-status:get-number(gvi_zaehler) error-status:get-message(gvi_zaehler) view-as alert-box.
  end.
  return.
end.
message "Connect-OK".

/*
gvc_befehl = 'SELECT * FROM GetBestand()':U.
gvc_befehl = 'SELECT * FROM StornoAuslagerung':U.
*/
gvc_befehl = 'SELECT * FROM AAusDatei1':U.

gvch_dataset:OPEN(gvc_befehl,gvch_dbconn, 3, 1, 1) no-error.    ---> here comes the listed error
if error-status:error or error-status:num-messages > 0
then do:
message "Open-DS-Fehler".
  do gvi_zaehler = 1 to error-status:num-messages:
    message error-status:get-number(gvi_zaehler) error-status:get-message(gvi_zaehler) view-as alert-box.
  end.
  return.
end.
message "Open-DS-OK".

gvch_dataset:close.

release object gvch_dbconn  no-error.
release object gvch_dataset no-error.

And here comes the whole error and a picture of the database...




If I do this via a VBS, it works fine.

'*********************************************************************
' Connectionstring für Verbindung zum Server
'*********************************************************************
Const OdbcName = "Provider=SQLOLEDB;Datasource=.;DATABASE=HOMAG;UID=sa;PWD=istist"
Dim PathArgs(0) ' Programm-Argument

Set WSHShell = WScript.CreateObject("WScript.Shell")
Set ObjEnv = WSHShell.Environment("Process")
Set ObjArgs = WScript.Arguments
Set FSO = CreateObject("Scripting.FileSystemObject")

Verbindung zur Datenbank aufbauen
Set objData = CreateObject("ADODB.Connection")
objData.Open Odbcname

' ADO Recordset erzeugen
Set objRS = CreateObject("ADODB.RecordSet")

On Error Resume Next

'*********************************************************************
' Tabelle StornoAuslauslagerung auslesen
'*********************************************************************
objRS.ActiveConnection = objData
'objRS.Source = "SELECT * FROM StornoAuslagerung"
objRS.Source = "SELECT * FROM AAusDatei1"

'adOpenKeyset Type
objRS.CursorType = 1
objRS.Open
MsgBox " Anzahl Datensaetze in Tabelle:" & objRS.RecordCount

objRS.Close
objData.Close



Any ideas? What is wrong or what is missing?

Thanks a lot

Ingo

 

[jdpjamesp]

I have no idea and am therefore only guessing, but what happens if you quote the table name?
gvc_befehl ='SELECT * FROM ~'AAusDatei1~'':U.

 

[ist1809]

Hi Cringer,

thank you. But this does not work either. I have found some other code and all work without quotes...

 

[Stefan]

You may need to qualify the table with database and owner so:

select HOMAG.dbo.AAusDatei1

There may be a minor difference between what you are doing from VB vs what you are doing from Progress with respect to the connection.

 

[ist1809]

Hi Stefan,

that's it. Now it works

Thank you.

 

[TheMadDBA]

The VB Connection has DATABASE=HOMAG so that is setting your current database (schema). The ABL connection isn't doing that or setting the DefaultDatabase property.

That is why you have to prefix the tables with the database (not always a bad thing imo).

 

[Jeff Winchell]

As a general rule, it is always best to run an English version of your software when you need to debug an error message so you can get the English version of that error message, and then Google that message. There are FAR more people debugging errors in English than there are in German (or any other language). Even when I worked in Germany I used English language versions of the software for that reason. (Some like Office or Windows let you flip languages between English and some other language with a couple clicks)