MSSQL: Get Databases and Tables with 2008 R2 SQL Native Client

Databases
1

For the first time, i am trying to access a MSSQL 2008 R2 Database.
My Clients have the required Native Client installed and i can pull the Databases with:

rs = myDatabase.SQLSelect("SELECT * FROM SYS.DATABASES")

When i now try to pull the Tables within a Database i’ve found using the above statement, the Programm crashes.
It crashes while doing:

rs = myDatabase.SQLSelect("USE " + Database_Name) //Database_Name is a String

If i perform a:

rs = myDatabase.TableSchema before i use rs = myDatabase.SQLSelect("USE " + Database_Name) //Database_Name is a String, i get a list of Tables, but i cannot access such Tables because of a “invalid objectname” error. I think i can handle the objectname name error as soon as i have figured out how to correctly pull the tables…

So, my main issue is, the crash at: rs = myDatabase.SQLSelect("USE " + Database_Name) //Database_Name is a String. And i think i’ve tried everything, but nothing works…

2

Have you tried SQLExecute instead of Select?

3

Try use this sql:
“select * from yourdbname.dbo.tablename” or if you dont know schema:
“select * from yourdbname…tablename”

4

No. But it shouldn’t crash in any case by simple Select or Execute Statements, or? :slight_smile:

5

[quote=52335:@Michal Dziubek]Try use this sql:
“select * from yourdbname.dbo.tablename” or if you dont know schema:
“select * from yourdbname…tablename”[/quote]

Thank you @Michal Dziubek

6

It shouldn’t crash, but you shouldn’t use SQLSelect, either. The fact that it crashes should be reported via feedback.

7

How can i pull data without a select?

[code] Dim rs As RecordSet
Dim TabellenNameAs String

Kundennummer = WAG4KHKKundennummer

TabellenName = "[SL_MARCHE].[dbo].[Kunden]"

'rs = WaWi_DB.SQLSelect("SELECT * FROM " + TabellenName + " WHERE Nummer='" + Kundennummer + "'")

Dim ps As MSSQLServerPreparedStatement
ps = WaWi_DB.Prepare("SELECT * FROM " + TabellenName + " WHERE Nummer=?")
rs = ps.SQLSelect(Kundennummer)

If rs<>Nil And Not rs.EOF Then
  Beep
End If

End If[/code]

crashes on rs = ps.SQLSelect(Kundennummer)

[SL_MARCHE].[dbo].[Kunden] is a VIEW not a TABLE.

With Tabels it’s working ‘fine’ now.

8

[quote=52202:@Sascha S]When i now try to pull the Tables within a Database i’ve found using the above statement, the Programm crashes.
It crashes while doing:

rs = myDatabase.SQLSelect("USE " + Database_Name) //Database_Name is a String[/quote]
I meant, you shouldn’t use Select here, This should be Execute, as it doesn’t return a value.

9

Ah! Got it. My fault. Thank you @Tim Hare :slight_smile:

10

Although I have not used the plug-in, I have spent 20 odd years working with SQL Server and I’d like to make one observation - you don’t normally issue a USE statement in your application. It should happen automatically either because it is the default database for the user account or more often it is the database name given in the connection string. So the fact that you are having to issue a USE statement and you are experiencing crashes makes me wounded if you have not got some kind of installation or configuration issue.

Also, be aware that in many large production environments the DBAs will have it locked down so that users can issue USE statements for security reasons.

11

Sorry that should read “can not issue” in the last statement.

12

Thank you @James Dooley.