Retrieve SQL info

[arcticpup]

Hi,

I'm writing an app to retrieve some data from a SQL database.

The first part works fine (getting the SQL version), but the second part refuses to retrieve the data, keeps flagging that the requested action with this object has failed.

Any ideas welcome!

$datasource = RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Apex Software\incTax\DataLocations", "Data Source")
$conn = ObjCreate("ADODB.Connection")
$oError = ObjEvent("AutoIt.Error", "ErrHandler")
$DSN = "DRIVER={SQL Server};" & "SERVER=" & $datasource & ";DATABASE=;UID=SA;PWD=$ageAdm1n;"
$conn.Open($DSN)
$rs = ObjCreate("ADODB.RecordSet")
$rs.Open("SELECT @@VERSION AS myVersion", $conn)
$sqlinfo = $rs.Fields("myVersion" ).Value
$conn.close
Global $adoCn = ObjCreate("ADODB.Connection")

$conn1 = ObjCreate("ADODB.Connection")
$oError1 = ObjEvent("AutoIt.Error", "ErrHandler")
$DSN1 = "DRIVER={SQL Server};" & "SERVER=" & $datasource & ";DATABASE=TaxMaster; -E;"
$conn1.Open($DSN1)
$rs1 = ObjCreate("ADODB.RecordSet")
$rs1.Open("SELECT value from MasterSettings where name = 'ZBkUpActionLatestBkUp:File' as bkp", $conn1)
$bkp = $rs1.Fields("bkp" ).Value
$conn1.close
Global $adoCn1 = ObjCreate("ADODB.Connection")

IniWrite(@DesktopDir & "\SQL.ini", "SQLINFO","SQL Version", $sqlinfo)
IniWrite(@DesktopDir & "\SQL.ini", "SQLINFO","Backup location", $bkp)

[jvanegmond]

You forgot to enter the password in $DSN1 the second time you connect to the database.

[arcticpup]

It's using -E which creates a trusted connection.

I've altered the first one to use the -E flag and that still works fine.

Any other ideas?

[jvanegmond]

To be honest, your code is a mess. I'd suggest going over everything again and tidying it up. Sloppiness often leads to mistakes!

Try using the first connection (keep it open) to execute the second query.

[ChrisL]

See if this http://www.autoitscript.com/forum/index.php?showtopic=51952 helps

[arcticpup]

Still get the same message with the amended code.

Is there a better / easier way to connect to SQL?

$datasource = RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Apex Software\incTax\DataLocations", "Data Source")
$conn = ObjCreate("ADODB.Connection")
$oError = ObjEvent("AutoIt.Error", "ErrHandler")
$DSN = "DRIVER={SQL Server};" & "SERVER=" & $datasource & ";DATABASE=TaxMaster; -E;"
$conn.Open($DSN)
$rs = ObjCreate("ADODB.RecordSet")
$rs.Open("SELECT @@VERSION AS myVersion", $conn)
$sqlinfo = $rs.Fields("myVersion" ).Value

$rs1 = ObjCreate("ADODB.RecordSet")
$rs1.Open("SELECT value from MasterSettings where name = 'ZBkUpActionLatestBkUp:File' as bkp", $conn)
$bkp = $rs1.Fields("bkp" ).Value
$conn.close
Global $adoCn = ObjCreate("ADODB.Connection")

IniWrite(@DesktopDir & "\SQL.ini", "SQLINFO","SQL Version", $sqlinfo)
IniWrite(@DesktopDir & "\SQL.ini", "SQLINFO","Backup location", $bkp)

[arcticpup]

See if this http://www.autoitscript.com/forum/index.php?showtopic=51952 helps

I looked at that but I can't create a trusted connection with it.

[arcticpup]

OK, it seems to be happy with this. I can't get it all into one connection but hey, it does what I want it to now!

$datasource = RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Apex Software\incTax\DataLocations", "Data Source")
$conn = ObjCreate("ADODB.Connection")
$oError = ObjEvent("AutoIt.Error", "ErrHandler")
$DSN = "DRIVER={SQL Server};" & "SERVER=" & $datasource & ";DATABASE=TaxMaster; UID=SA;PWD=$ageAdm1n;"
$conn.Open($DSN)
$rs = ObjCreate("ADODB.RecordSet")
$rs.Open("SELECT @@VERSION AS myVersion", $conn)
$sqlinfo = $rs.Fields("myVersion" ).Value

$conn = ObjCreate("ADODB.Connection")
$oError = ObjEvent("AutoIt.Error", "ErrHandler")
$DSN = "DRIVER={SQL Server};" & "SERVER=" & $datasource & ";DATABASE=TaxMaster; UID=SA;PWD=$ageAdm1n;"
$conn.Open($DSN)
$rs = ObjCreate("ADODB.RecordSet")
$rs.Open("SELECT     Value AS BKP FROM         MasterSettings WHERE     (Name = 'ZBkUpActionLatestBkUp:file')", $conn)
$bkp = $rs.Fields("bkp" ).Value
IniDelete(@DesktopDir & "\SQL.ini", "SQLINFO")

IniWrite(@DesktopDir & "\SQL.ini", "SQLINFO","SQL Version", $sqlinfo)
IniWrite(@DesktopDir & "\SQL.ini", "SQLINFO","Backup location", $bkp)

[jvanegmond]

I thought you wanted a trusted connection?

[arcticpup]

Ideally I do, but it was only a small app to prove a point for someone, rather than something that will be delevoped any further.

For future reference, how yould I get a trusted connection using _sql.au3?