Jump to content
Sign in to follow this  
gritts

Long SQL statements

Recommended Posts

Appologies if this is in the wrong place...

I am using the to connect to a MS SQL database. I can connect fine with simple queries but when I attempt to connect with a more complex query, I get no results. I know the query works as it has been tested in query analyzer.

Here is my query statement:

SELECT DATEADD(HOUR,datediff(hour, getutcdate(),getdate()),History.TRDateTime),CONVERT(VARCHAR(10), DATEADD(HOUR,datediff(hour, getutcdate(), getdate()),History.TRDateTime), 103) AS FaxDate,CONVERT(VARCHAR(8),DATEADD(HOUR,datediff(hour, getutcdate(), getdate()),History.TRDateTime),108) AS FaxTime,HistoryTRX.RemoteServer,Users.UserID,Users.UserDIDNumber,HistoryTRX.RemoteID,HistoryTRX.ElapsedTime,DocFiles.BodyFilename,Documents.UniqueID,DocFiles.NumPages, historytrx.termstat

FROM Documents

JOIN History ON History.Owner=Documents.handle

JOIN HistoryTRX ON History.handle=HistoryTRX.handle

JOIN Users ON Users.handle=Documents.OwnerID

JOIN DocFiles ON Documents.DocFileDBA=DocFiles.handle

where history.trdatetime > '2011/09/07 05:00:00.000' and Documents.CreationTime < '2011/09/08 05:00:00.000' AND users.userid LIKE '%WRF%'

ORDER BY TRDateTime

Is there a limit to what the number of characters in the query can be? This on is 849 characters in length. When it is part of my script, it is one long string. I broke it up here for readability. Any suggestions on how to better accomplish my query?

Thanks

Share this post


Link to post
Share on other sites

Good question! I have the same problem. Does anybody can help?

Now, I can advise to use tools of sql-server. For MsSQL it is sqlcmd.exe or osql.exe


Luck, Love and PatienceNikita

Share this post


Link to post
Share on other sites

Thought I would add a little more to this thread since it has been a while. Here is the query I am using now. I have been nibbling away at this project. I hope to make the variable items more flexable so that a user can make changes to the query. For example, the UserID, UniqueID, etc. This query seems to work well. For time/date cosmetics, I'll work with some of the AutoIT formatting.

"SELECT History.TRDateTime, HistoryTRX.RemoteServer,Users.UserID,Users.UserDIDNumber,HistoryTRX.RemoteID,DocFiles.BodyFilename,Documents.UniqueID,DocFiles.NumPages, historytrx.termstat FROM Documents JOIN History ON History.Owner=Documents.handle JOIN HistoryTRX ON History.handle=HistoryTRX.handle JOIN Users ON Users.handle=Documents.OwnerID JOIN DocFiles ON Documents.DocFileDBA=DocFiles.handle where history.trdatetime &amp;gt; '2012-03-05 06:00:00.000' and Documents.CreationTime &amp;lt; '2012-03-06 06:00:00.000' AND users.userid LIKE '%WRF%' ORDER BY TRDateTime;"

Edited by gritts

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       
      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       
       
      Have fun,
      mLipok
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
      FOR EXAMPLE DATABASE use AdventureWorksDW2016_EXT.bak from:
      https://github.com/microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2016_EXT.bak
      I will relay on this database in my examples.
       
      Here is link to post which shows how "ODBC Data Source Administrator" looks like.
       
    • By shino54
      Hello,
      I would like a query to know if an entry exists
      Thank you in advance.
      $sQuery = "SELECT Alger FROM garage where auto='BL1879'" $result1 = $result.Fields("Alger").Value if $result1="" Then MsgBox(0, "ERROR", "BAD not exist") Else MsgBox(0, "Success!", "OK exist") EndIf Exit  
    • By Nas
      Hi everyone,
      I am trying to make a script that runs a query and show it to me to see if everything is right and then decide if I finish it or not so I made a little script as below :
      #include <ADO.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> _ADO_EVENTS_SetUp(True) _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler) Local $sDriver = 'SQL Server' Local $sDatabase = 'DataBase' ; change this string to YourDatabaseName Local $sServer = 'Localhost' ; change this string to YourServerLocation Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & ';PWD=' & ';' ;~ Global $Query = _ ;~ "BEGIN TRAN" & @CRLF & _ ;~ "UPDATE Table" & @CRLF & _ ;~ "SET HOUR = 4" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "SELECT * FROM Table" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "ROLLBACK TRAN" Global $Query = _ "BEGIN TRAN" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" _Query_Display($sConnectionString, $Query) Func _Query_Display($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 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, 'Query Result') EndFunc ;==> _Query_Display When I ran this script it works great, but when I run the query below :
      Global $Query = _ "BEGIN TRAN" & @CRLF & _ "UPDATE Table" & @CRLF & _ "SET HOUR = 4" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" It doesn't show anything, when I take those begin and rollback it does what it should but still not showing me anything at all, is there a way around it that you know of?
      Thank you.
    • By Nas
      Hello Guys,
       
      I am running the Ado example script, if I run this line, it runs without errors :
      Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' But when I try the windows auth one , it failed everytime :
      Func _Example_MSSQL_WindowsAuthorization() Local $sDatabase = 'AdventureWorks2016' Local $sServer = 'SQLSRV' _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from Person.Address") EndFunc here is the error :
       
      ADO.au3 v.2.1.16 BETA (1204) : ==> COM Error intercepted !
      $oADO_Error.description is:     Provider cannot be found. It may not be properly installed.
      $oADO_Error.windescription:     Exception occurred.
      $oADO_Error.number is:     80020009
      $oADO_Error.lastdllerror is:     0
      $oADO_Error.scriptline is:     1204
      $oADO_Error.source is:     ADODB.Connection
      $oADO_Error.helpfile is:     C:\Windows\HELP\ADO270.CHM
      $oADO_Error.helpcontext is:     1240655
      By the way, I have 64x windows server 2016 with SQL Server , I already installed the odbc my sql connector, any thought on this will be greatly appreciate it.
    • By argumentum
      $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.
      $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.
      ..tho, I find that any 2nd argument in the ORDER BY is just slow.
      Is there a way to query this in a way to have a faster result back ?
      Thanks
      PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
×
×
  • Create New...