Jump to content

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.



Link to comment
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
Link to comment
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

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...