Query one single value from MSSQL Server 11.0.7469.6

[rudi]

Hi.

For retrieving the data on the Server itself this command is working fine, but I'd like to do the query over LAN:

 

Tool, included in the MS SQL Managment Studio:

 

C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe

 

I've searched and found several approaches (_SQL.UDF, MSSQL.UDF, the sqlite stuff), but are not really familiar with SQL.

 

What direction is best to take to do this query from a remote WS with sa auth against a mssql Server?

 

bcp.exe  "SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]" queryout "c:\temp\LastIterationID-JobSrv.TXT"  -d -Sgoe-vault01\autodeskvault -Usa -PMyPlainTextPassword -c -C1252


The Server Hostname is "Goe-Vault01"
The Database is "AutodeskVault"

 

[Nine]

I would recommend this :

 

 

[mLipok]

....
_ADO_Execute("SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]")
.....

 

[rudi]

Thanks to both for your reply.

In the ADO_EXAMPLE.au3 I found _Example_4_MSSQL(). I try and failed to use it with this modifications:

 

expandcollapse popup

#include "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\ADO.au3"


$Server="Goe-Vault01"
$Database="GoeVault01\AUTODESKVAULT"
$User="sa"
$Pass="MyPlainTextPassword"


; Jobserver
$Query="SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]"
$LastIterationJobSrv=MSSQL_Query($Server,$Database,$User,$Pass,$Query)



; Vault
$Query="SELECT max([FileIterationID]) from [Vault].[dbo].[FileIteration]"
$LastIterationVault=MSSQL_Query($Server,$Database,$User,$Pass,$Query)



Func MSSQL_Query($sServer, $sDatabase, $sUser, $sPassword, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID')
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')
EndFunc

 

Maybe because I didn't use the correct 'YourAppName' and 'YourProgram_UID', what values might be appropriate?

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\LastIterationID_Jobserver_Vault_Query.au3" /UserParams    
+>12:19:57 Starting AutoIt3Wrapper v.17.224.935.0 SciTE v.3.7.3.0   Keyboard:00000407  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64  Environment(Language:0407)  CodePage:0  utf8.auto.check:4
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\admin\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\admin\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.14.5)  from:C:\Program Files (x86)\AutoIt3  input:H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\LastIterationID_Jobserver_Vault_Query.au3
"H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\ADO.au3"(381,89) : error: _ArrayDisplay() called with wrong number of args.
        _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Program Files (x86)\AutoIt3\Include\Array.au3"(480,176) : REF: definition of _ArrayDisplay().
Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iMax_ColWidth = Default)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\ADO.au3"(385,90) : error: _ArrayDisplay() called with wrong number of args.
        _ArrayDisplay($aRecordset, $sTitle, "", 0, Default, Default, Default, $iAlternateColors)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Program Files (x86)\AutoIt3\Include\Array.au3"(480,176) : REF: definition of _ArrayDisplay().
Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iMax_ColWidth = Default)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\LastIterationID_Jobserver_Vault_Query.au3 - 2 error(s), 0 warning(s)
!>12:19:57 AU3Check ended. Press F4 to jump to next error.rc:2
+>12:19:57 AutoIt3Wrapper Finished.
>Exit code: 2    Time: 0.9177

 

 

 

[rudi]

Hi again.

I had to modify the ADO.AU3 UDF this way to get rid of this two Errors:

 

 

Then this script ...

expandcollapse popup

#include <Debug.au3>
#include "h:\daten\private\sysop\netz\batch\autoit3\snippets\ADO-2.1.15-BETA\ado.au3"
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Run_AU3Check=Y
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#AutoIt3Wrapper_Run_Au3Stripper=Y
#Au3Stripper_Parameters=/RM

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>


    Local $sDatabase = 'MuM_PSJobserver' ; change this string to YourDatabaseName
    Local $sServer = 'goe-vault01\AUTODESKVAULT' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'MyPlainTextPasswOrD' ; change this string to YourPassword
    Local $Query="SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]"
    _MSSQL_Query($sServer, $sDatabase, $sUser, $sPassword, $Query)

    Local $QueryTop10="SELECT TOP (10) [FI_FileIterationID] from [MuM_PSJobserver].[dbo].[FileIterationBuffer]"
    _MSSQL_Query($sServer, $sDatabase, $sUser, $sPassword, $QueryTop10)


Func _MSSQL_Query($sServer, $sDatabase, $sUser, $sPassword, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()   ; Open connection with $sConnectionString
    _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword) ; not specified:  , 'YourAppName', @ComputerName & '_' & 'YourProgram_UID')   If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)
    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
    _DebugArrayDisplay($aRecordset,UBound($aRecordset))
    for $i = 0 to UBound($aRecordset) - 1
        if IsArray($aRecordset[$i]) Then
            _DebugArrayDisplay($aRecordset[$i],"Array Element " & $i)
        Else
            MsgBox(0,"Array Element " & $i,$aRecordset[$i])
        EndIf
    Next

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')
EndFunc   ;==>_Example_4_MSSQL


Func _ErrDescription($sDescription = Default)
    Local Static $sDescription_static = ''
    If $sDescription <> Default Then $sDescription_static = $sDescription
    Return $sDescription_static
EndFunc   ;==>_ErrDescription

Func _ErrFunc($oError)
    ConsoleWrite( _
            @ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & _
            "$oError.description is: " & @TAB & $oError.description & @CRLF & _
            "$oError.windescription: " & @TAB & $oError.windescription & @CRLF & _
            "$oError.number is: " & @TAB & Hex($oError.number, 8) & @CRLF & _
            "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "$oError.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "$oError.source is : " & @TAB & $oError.source & @CRLF & _
            "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF _
            )
    _ErrDescription($oError.description) ; store description to use it outsided UDF in your own function
EndFunc   ;==>_ErrFunc

...  is working, but I don't get all of its Output:

For the first query, that is "asking" for just the max value, the results are this:

 

for the 2nd query, that is "asking" for the top ten values, the result is that one:

 

Questions:

 

1. What do I miss to receive the column Name as element 2 (Row 1) in the original result Array, when doing a query just for the (single) max value?
2. Why do I have to "comment out" parts of the line ...

_ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors)

... to ...

_ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader); , Default, $iAlternateColors)

... do I specify wrong Parameters?

 

CU, Rudi.

Edited May 13, 2019 by rudi

[rudi]

Hi, after quite some time I used a workaround with SSMS's BCP.EXE (output to txt file, then read that output. Quite ugly approach)

bcp.exe  "SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]" queryout "c:\full\path\bcp-output.txt"  -d -Sgoe-vault03\autodeskvault -Usa -PPlainTextPasswordHere -c -C1252

due to updates for our Vault Server I got in touch again with this issue, to directly place a SQL query on our SQL server:

 

 

@ChrisL @mLipok thanks for your ADO.au3, I noticed, that there is a new beta version:

• in 2019 I used ADO-2.1.15-BETA
• now there is ADO 2.1.19 BETA

Could someone please point me in the right direction to SELECT a single result value, using these specs?

 

This script is giving me no results, just these errors. 

; from example script ADO_EXAMPLE.au3, no output at all

Func _Example_MSSQL_SQLServerAuthorization()
    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'autodeskvault' ; change this string to YourDatabaseName
    Local $sServer = 'goe-vault03\autodeskvault' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'PlainTextPasswordHere' ; change this string to YourPassword

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $sMySelect='SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]'

    _Example_1_RecordsetToConsole($sConnectionString, $sMySelect)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example_2_RecordsetDisplay($sConnectionString, $sMySelect)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example_3_ConnectionProperties($sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword,$sMySelect)

EndFunc   ;==>_Example_MSSQL

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO 2.1.19 BETA\ADO_EXAMPLE.au3" /UserParams    
+>16:58:10 Starting AutoIt3Wrapper v.18.708.1148.0 SciTE v.4.1.0.0   Keyboard:00000407  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0407)  CodePage:0  utf8.auto.check:4
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\admin.AD\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\admin.AD\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.14.5)  params:-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7  from:C:\Program Files (x86)\AutoIt3  input:H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO 2.1.19 BETA\ADO_EXAMPLE.au3
+>16:58:11 AU3Check ended.rc:0
>Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3.exe "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO 2.1.19 BETA\ADO_EXAMPLE.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
! ---> @error=4  @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization()
@@ Debug(69) : $Result = -1
>Error code: 4
+>16:58:11 AutoIt3.exe ended.rc:0
+>16:58:11 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 1.158

 

Edited April 14, 2022 by rudi

[mLipok]

1 hour ago, rudi said:

! ---> @error=4  @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization()

DISP_E_EXCEPTION
0x80020009
Exception occurred.

Focus on:

    Local $sDatabase = 'autodeskvault' ; change this string to YourDatabaseName

I think this should be 'MuM_PSJobserver'

btw.
Please show to us  COMError handler message.

Edited April 14, 2022 by mLipok

[mLipok]

Also take a look here:

https://answers.microsoft.com/en-us/msoffice/forum/all/run-time-error-2147352567-the-value-you-entered/e676d41b-5ffb-49ad-8b65-5571f5dd17dc

 

[rudi]

@mLipok Thanks for your reply. You are absolutely right 👏

 

This is now working fine:

 

expandcollapse popup

#include "h:\daten\private\sysop\netz\batch\autoit3\snippets\ADO-2.1.15-BETA\ado.au3"



$OpenFileIterations = OpenFileIterationCount()
$Err = @error
$Icon = @extended
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $OpenFileIterations = ' & $OpenFileIterations & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
If $Err Then
    MsgBox($MB_ICONERROR, "Error", "Could not get the Vault Server and Job Server FileiterationIDs!" & @CRLF & _
            "SQL Query or ADO error.")
Else
    MsgBox($Icon, "Jobserver Check", $OpenFileIterations & " Vault File Iterations waiting to be processed.")
EndIf



Func OpenFileIterationCount()
    ; Success:      <int> = difference between Vault and Jobserver: Number of File Iterations not processed so far
    ; Failure:      false

    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'MuM_PSJobserver' ; change this string to YourDatabaseName
    Local $sServer = 'goe-vault03\autodeskvault' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'PlainTextPasswordHere' ; change this string to YourPassword
    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    Local $sMySelect = 'SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]'
    Local $VaultFI
    Local $JobSrvFI

    $JobSrvFI = SelectOneMaxValue($sConnectionString, $sMySelect)
    $err = @error
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') :     $JobSrvFI = ' & $JobSrvFI & @CRLF & '>Error code: ' & $err & @CRLF) ;### Debug Console
    If $err Then Return False

    $sDatabase = "Vault"
    $sMySelect = "SELECT max([FileIterationID]) from [Vault].[dbo].[FileIteration]"

    $VaultFI = SelectOneMaxValue($sConnectionString, $sMySelect)
    $err = @error
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $VaultFI = ' & $VaultFI & @CRLF & '>Error code: ' & $err & @CRLF) ;### Debug Console
    If $err Then Return False
    $OpenIterations = $VaultFI - $JobSrvFI
    Switch $OpenIterations
        Case 0 To 5
            Return SetError(0, $MB_ICONINFORMATION, $OpenIterations)
        Case 6 To 15
            Return SetError(0, $MB_ICONWARNING, $OpenIterations)
        Case 15 To 25
            Return SetError(0, $MB_ICONERROR, $OpenIterations)
        Case Else
            Return SetError(1, $MB_ICONERROR, $OpenIterations)
    EndSwitch
EndFunc   ;==>OpenFileIterationCount


Func SelectOneMaxValue($sConnectionString, $sQUERY)
    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Get recordset to array of arrays (Conent and ColumnNames)
    Local $aRecordsetAsArray = _ADO_Recordset_ToArray($oRecordset, False)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Get inner array - only conent of Recordset
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetAsArray)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    ; Clean Up
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
    If IsArray($aRecordsetContent) Then
        If ((UBound($aRecordsetContent, $UBOUND_DIMENSIONS) = 2) And (UBound($aRecordsetContent, $UBOUND_COLUMNS) = 1) And (UBound($aRecordsetContent, $UBOUND_ROWS) = 1)) Then ; Okey: 2D Array, one row, one collumn, valid result
            Return $aRecordsetContent[0][0]
        Else
            Return SetError(1, 0, $aRecordsetContent)
        EndIf
    Else
        Return SetError(1, 1, $aRecordsetContent)
    EndIf
EndFunc   ;==>SelectOneMaxValue