tampi Posted November 5, 2021 Posted November 5, 2021 #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <ADO.au3> #Region ### START Koda GUI section ### Form= $Form1 = GUICreate("Form1", 384, 168, 192, 124) $connect_ = GUICtrlCreateButton("Connect", 144, 112, 91, 33) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### $objErr = ObjEvent("AutoIt.Error","MyErrFunc") While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $connect_ ms_sql_connection() EndSwitch WEnd Func ms_sql_connection() $sServer = '192.168.42.228, 1500' $sDatabase = 'usta' $sUID = 'exampleid' $sPWD = 'examplepass' $DSN = 'DRIVER={SQL Server};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUID &';PWD=' & $sPWD & ';' $oConn = ObjCreate ("ADODB.Connection") $oConn.ConnectionTimeout = 3 ; default is 15 s (must be supported by data provider DSN) $oConn.Open($DSN) If @error Then Exit MsgBox( 1, "Connection", "Successfully Connected!") EndFunc Func MyErrFunc() MsgBox(48, 'COM Error', $objErr.description) SetError(1) EndFunc Hi, I have a problem. I don't know how can I solve it. This script works very well on my computer. I am getting Successfull Connected! msgbox. But it's not working on my friends computer. He is getting this error : [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. What should I do? Where is the problem ?
mLipok Posted November 5, 2021 Posted November 5, 2021 On 11/5/2021 at 6:09 AM, tampi said: Where is the problem ? Expand Connection string Or Firewall Or MS SQL configuration tampi 1 Signature beginning:* Please remember: "AutoIt"..... * Wondering who uses AutoIt and what it can be used for ? * Forum Rules ** ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Code * for other useful stuff click the following button: Reveal hidden contents Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API * ErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 * My contribution to others projects or UDF based on others projects: * _sql.au3 UDF * POP3.au3 UDF * RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF * SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane * Useful links: * Forum Rules * Forum etiquette * Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * Wiki: * Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX IE Related: * How to use IE.au3 UDF with AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler * IE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related: * How to get reference to PDF object embeded in IE * IE on Windows 11 * I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions * EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *I also encourage you to check awesome @trancexx code: * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuff * OnHungApp handler * Avoid "AutoIt Error" message box in unknown errors * HTML editor * winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/ "Homo sum; humani nil a me alienum puto" - Publius Terentius Afer"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming" , be and \\//_. Anticipating Errors : "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty." Signature last update: 2023-04-24
robertocm Posted November 5, 2021 Posted November 5, 2021 (edited) ;$sServer = '192.168.42.228, 1500' ;try this: $sServer = '192.168.42.228' ;or use the server name as from the original example by Zedna, Aug 6, 2020 ;https://www.autoitscript.com/forum/topic/203542-ms-sql-connection/?do=findComment&comment=1461607 ;$sServer = 'server1' ;or try changing connection string: $DSN = 'SQLOLEDB;Data Source=' & $sServer & ';Initial Catalog=' & $sDatabase & ';User Id=' & $sUID &';Password=' & $sPWD & ';' See also: List all installed OLE DB providers: https://www.autoitscript.com/wiki/ADO_Tools A possible problem of your script is keeping open connections in presence of errors because of this: If @error Then Exit Better always checking for open connections on exit. This would be my version of your example: expandcollapse popup#include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> Opt("MustDeclareVars", 1) Opt("TrayIconDebug", 1) OnAutoItExitRegister("OnAutoItExit") Global $nMsg Global $oConn, $sSQL Global $sServer = '192.168.42.228' Global $sDatabase = 'tempdb' Global $sUID = 'sa' Global $sPWD = 'examplepass' #===== ADODB ===== ;Help: COM Error Handling ;_ErrADODB From spudw2k ;https://www.autoitscript.com/forum/topic/105875-adodb-example/ Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB") Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic Global Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable $oConn = ObjCreate("ADODB.Connection") ; Create a connection object ;~ ;https://accessexperts.com/blog/2011/03/24/sql-server-connections-strings-for-microsoft-access/ ;~ ;You should bypass the ODBC layer altogether when connecting to SQL Sever by using a connection string similar to this one in your code: ;~ ;stConnect = "Provider=SQLOLEDB;Data Source=... ;~ ;Or if you’re using native client: ;~ ;stConnect = "Provider=SQLNCLI10;Data Source=... Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=' & $sServer & ';Initial Catalog=' & $sDatabase & ';User Id=' & $sUID &';Password=' & $sPWD & ';' ;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp ;A Recordset object inherits this setting from the associated Connection object. ;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object. $oConn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient $oConn.CommandTimeout = 10 ;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode ;https://www.w3schools.com/asp/prop_rec_mode.asp $oConn.Mode = 1 ;Read-only #Region ### START Koda GUI section ### Form= Global $Form1 = GUICreate("Form1", 384, 168, 192, 124) Global $connect_ = GUICtrlCreateButton("Connect", 144, 112, 91, 33) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $connect_ ms_sql_connection() EndSwitch WEnd Func ms_sql_connection() $oConn.Open($sADOConnectionString) ; Open the connection MsgBox(1, "Connection String", $oConn.ConnectionString) ;Zedna, Aug 7, 2020 ;https://www.autoitscript.com/forum/topic/203542-ms-sql-connection/?do=findComment&comment=1461671 ;simple select returns 1 value (1 column and 1 row: count() max() TOP 1) $sSQL = "SELECT 'Version: ' + @@VERSION + CHAR(13) + 'Language: ' + @@LANGUAGE + CHAR(13) + 'ServerName: ' + @@SERVERNAME;" MsgBox(0, "", $oConn.Execute($sSQL).Fields(0).Value) $oConn.Close ;Close the connection $oConn = 0 ;Release the connection object EndFunc Func _ErrADODB() Msgbox(0,"ADODB COM Error","We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $errADODB.description & @CRLF & _ "err.windescription:" & @TAB & $errADODB.windescription & @CRLF & _ "err.number is: " & @TAB & hex($errADODB.number,8) & @CRLF & _ "err.lastdllerror is: " & @TAB & $errADODB.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $errADODB.scriptline & @CRLF & _ "err.source is: " & @TAB & $errADODB.source & @CRLF & _ "err.helpfile is: " & @TAB & $errADODB.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $errADODB.helpcontext, 5) Local $err = $errADODB.number If $err = 0 Then $err = -1 Local $sFilePath = @DesktopDir & "\error.txt" ;Open the file for write access. Local $hFileOpen = FileOpen($sFilePath, 2) ;If $hFileOpen = -1 Then ;MsgBox(0, "", "An error occurred when reading/writing the file.") ;EndIf FileWrite($hFileOpen, "ADODB COM Error" & Chr(1) & _ "err.description is: " & @TAB & $errADODB.description & Chr(1) & _ "err.windescription:" & @TAB & $errADODB.windescription & Chr(1) & _ "err.number is: " & @TAB & hex($errADODB.number,8) & Chr(1) & _ "err.lastdllerror is: " & @TAB & $errADODB.lastdllerror & Chr(1) & _ "err.scriptline is: " & @TAB & $errADODB.scriptline & Chr(1) & _ "err.source is: " & @TAB & $errADODB.source & Chr(1) & _ "err.helpfile is: " & @TAB & $errADODB.helpfile & Chr(1) & _ "err.helpcontext is: " & @TAB & $errADODB.helpcontext _ ) ;Close the handle returned by FileOpen. FileClose($hFileOpen) $oConn.Close $oConn = 0 Exit EndFunc Func OnAutoItExit() If IsObj($oConn) Then If $oConn.State > 0 Then $oConn.Close ;adStateOpen Close the connection $oConn = 0 ; Release the connection object EndIf EndFunc Edited November 5, 2021 by robertocm Leendert-Jan and tampi 2
Leendert-Jan Posted November 9, 2021 Posted November 9, 2021 (edited) In case your MS SQL Server is running on your own computer, you should configure MS SQL Server to allow remote connections. This is disabled by default. First, download and install SQL Server Management Studio from here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15. You have to click the first link in the article: Once it is installed, open it and log in to your SQL server. Right click on your server () and click Properties --> Connections --> Allow Remote Connections. Next, open SQL Server Configuration Manager and enable TCP/IP if you have not done that already. Restart your SQL Server. Make the firewall is configured correctly, so it does not block the port you are using. And lastly, I am assuming your friend is on the same network as you, otherwise you probably have to configure port fowarding on your router. More info: Allowing remote connections: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-ver15 Enable TCP/IP: https://www.habaneroconsulting.com/stories/insights/2015/tcpip-is-disabled-by-default-in-microsoft-sql-server-2014 Edited November 9, 2021 by Leendert-Jan Added more info robertocm, Zedna and tampi 3
tampi Posted November 17, 2021 Author Posted November 17, 2021 Yes, this was my problem. @Leendert-Jan I don't have router. I am using mobile phone network. I searched a little but it seems I can't port forwarding on mobile networks. I saw this : https://azure.microsoft.com/en-us/free/students/ They giving 100$ for 1 year so I bought SQL Server(4.99$ for month). I solved my problem that way. Thanks for everyone!
chkrishna Posted February 3, 2024 Posted February 3, 2024 Hi Did you get the solution for this i did not understand what others has replied for this can u please help me
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