Sign in to follow this  
Followers 0
arcticpup

Retrieve SQL info

10 posts in this topic

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)

Share this post


Link to post
Share on other sites



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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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?

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  
Followers 0