Long SQL statements

[gritts]

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

[Clark]

Have you tried it without the embedded functions, e.g. convert?

[Zedna]

Try eliminating method. Step by step remove some pieces (columns/functions) from your query and test if it works or not.

[Melkor]

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

[gritts]

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 March 27, 2012 by gritts