Reekod Posted December 14, 2011 Share Posted December 14, 2011 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) #include<sql.au3> #include<array.au3> $sqlCon = ObjCreate("ADODB.Connection") $driver = 'DRIVER={SQL Server}' $server = 'SERVER=servename' $DB1 = 'DATABASE=DBNAME' $user = 'UID=USERNAME' $TC = 'Trusted_Connection=Yes' $sqlCon.Open($driver & ';' & $server & ';' & $DB1 & ';' & $user & ';' & $TC & ';') $NameList="" if @error Then MsgBox(0, "ERROR", "Failed to connect to the database") Exit Else MsgBox(0, "Success!", "Connection to database successful!") EndIf $result = _SQLQuery($sqlCon,"select name0 from v_R_System") With $result While Not .EOF $NameList &= .Fields ("name0").value & @CRLF .MoveNext WEnd EndWith Thx for reading. Regards. ReekoD Link to comment Share on other sites More sharing options...
rodent1 Posted December 14, 2011 Share Posted December 14, 2011 (edited) Hi,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:SELECT table_name FROM INFORMATION_SCHEMA.TABLESHere is a function I wrote for another script that retrieves data from a SQL Server database:expandcollapse popupFunc 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") Exit EndIf if $ServerName = "." or $ServerName = "" Then $sqlCon.Open("Provider=SQLOLEDB; Data Source=MyDatabase; Initial Catalog=MyData; User ID=user; Password=password;") Else if StringLen($sServerIP) Then $sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $sServerIP & "MyData; User ID=user; Password=password;") Else $sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $ServerName & "MyDatabase; Initial Catalog=MyData; User ID=user; Password=password;") EndIf EndIf $oRS = ObjCreate ( "ADODB.Recordset" ) if $oRS = 0 then AddToLog ( "Failed to connect to the SQL database on server '" & $Servername & "'. Exiting...", "", 2 ) Exit(1) EndIf $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 ( "", " " ) $oRS.MoveFirst For $i = 1 To $intNbLignes if $i > 1 Then ReDimStr1Dim ( $RetAr, $i ) $RetAr [ $i ] = $oRS.Fields.Item(0).value $oRS.MoveNext Next $Ret = $RetAr [ 1 ] EndIf $oRS.close EndIf Return $Ret EndFunc Edited December 14, 2011 by rodent1 Link to comment Share on other sites More sharing options...
Reekod Posted December 15, 2011 Author Share Posted December 15, 2011 Thx a lot i'll test it asap Link to comment Share on other sites More sharing options...
rodent1 Posted December 15, 2011 Share Posted December 15, 2011 (edited) 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 December 15, 2011 by rodent1 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now