Jump to content
Sign in to follow this  

How to display SQL BD table

Recommended Posts


Hi all,

In this code i display all computername of my SCCM SQL Server. I need to do more complex query for some reports but i don't know how to do some basic query.

Can you help me to :

1st/ Display in an array each table of the data base

2nd/ Display * from a specific table

(ex: display all value rows/cols from v_R_system in an array)

$sqlCon = ObjCreate("ADODB.Connection")
$driver = 'DRIVER={SQL Server}'
$server = 'SERVER=servename'
$user = 'UID=USERNAME'
$TC = 'Trusted_Connection=Yes'
$sqlCon.Open($driver & ';' & $server & ';' & $DB1 & ';' & $user & ';' & $TC & ';')
if @error Then
MsgBox(0, "ERROR", "Failed to connect to the database")
MsgBox(0, "Success!", "Connection to database successful!")
$result = _SQLQuery($sqlCon,"select name0 from v_R_System")
With $result
While Not .EOF
$NameList &= .Fields ("name0").value & @CRLF

Thx for reading.



Share this post

Link to post
Share on other sites


There is a post that does something like what your're asking for, but for SQL Compact. It asks for the list of tables, posts the list in a listbox, then when a table is selected, it displays its contents.

It is located here:

To get a list of tables, this query should work:


Here is a function I wrote for another script that retrieves data from a SQL Server database:

Func RunQuery ( $Qry, $TestFile = "" )
Dim $sqlCon
Dim $oRS
Dim $intNbLignes
Dim $adLockOptimistic =3 ;Verrouillage optimiste, un enregistrement à la fois. Le fournisseur utilise le verrouillage optimiste et ne verrouille les enregistrements qu'à l'appel de la méthode Update.
Dim $adOpenKeyset = 1 ;Utilise un curseur à jeu de clés. Identique à un curseur dynamique mais ne permettant pas de voir les enregistrements ajoutés par d'autres utilisateurs (les enregistrements supprimés par d'autres utilisateurs ne sont pas accessibles à partir de votre Recordset). Les modifications de données effectuées par d'autres utilisateurs demeurent visibles.
Dim $Ret = ""
 $sqlCon = ObjCreate("ADODB.Connection")
 if $sqlCon = 0 Then
  MsgBox(0,"","failed to create a connection object")
 if $ServerName = "." or $ServerName = "" Then
  $sqlCon.Open("Provider=SQLOLEDB; Data Source=MyDatabase; Initial Catalog=MyData; User ID=user; Password=password;")
  if StringLen($sServerIP) Then
   $sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $sServerIP & "MyData; User ID=user; Password=password;")
   $sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $ServerName & "MyDatabase; Initial Catalog=MyData; User ID=user; Password=password;")
 $oRS = ObjCreate ( "ADODB.Recordset" )
 if $oRS = 0 then
  AddToLog ( "Failed to connect to the SQL database on server '" & $Servername & "'. Exiting...", "", 2 )
 $oRS.CursorLocation = 2 ;adUseServer
 if StringLeft ( StringLower ( $Qry ), 7 ) = "select " Then
  $oRS.open ( $Qry, $sqlCon, 3, -1) ; adOpenStatic, lock type unspecified
  $intNbLignes = $oRS.recordCount
  if $intNbLignes > 0 Then
   Dim $RetAr = StringSplit ( "", " " )
   For $i = 1 To $intNbLignes
    if $i > 1 Then ReDimStr1Dim ( $RetAr, $i )
    $RetAr [ $i ] = $oRS.Fields.Item(0).value
   $Ret = $RetAr [ 1 ]
 Return $Ret
Edited by rodent1

Share this post

Link to post
Share on other sites

Thx a lot

i'll test it asap

Share this post

Link to post
Share on other sites

I needed something similar to what you were asking for, so I wrote something fast that you may want to have a look at and just posted it here.

Edited by rodent1

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this