"Query is too Complex"

OK, first let me say that I am no DB person.

But I have a user here who keeps getting this error
whenever she does, whatever it is she does, with
databases...

A google search takes me to various forums where
I am forced to sign up before I can read any answers.

Interesting note here is that the guy in the office next
to her can run this same query without any problems.

So, can this be a hardware issue?
If I threw more RAM at this problem would that do?

Or is this strickly a coding issue?
Why would one machine run this query ok, yet another
machine generates this error?

Any pointers that you guys could throw my way would
be greatly appreciated.
Thanks

*$Starbuck,

I am afraid you haven't provided enough information to be of help. What
error is she getting? What statement is she running? etc.

By the way, you can search the archive of the public Microsoft
SQL-Server forums on Google (without registration). Have a look at
http://groups.google.com/groups?hl=...ublic.sqlserver

Hope this helps,
Gert-Jan

Starbuck wrote:
> OK, first let me say that I am no DB person.
> But I have a user here who keeps getting this error
> whenever she does, whatever it is she does, with
> databases...
> A google search takes me to various forums where
> I am forced to sign up before I can read any answers.
> Interesting note here is that the guy in the office next
> to her can run this same query without any problems.
> So, can this be a hardware issue?
> If I threw more RAM at this problem would that do?
> Or is this strickly a coding issue?
> Why would one machine run this query ok, yet another
> machine generates this error?
> Any pointers that you guys could throw my way would
> be greatly appreciated.
> Thanks
> *$

--
(Please reply only to the newsgroup)|||The error message is "Query is too complex".

The Query that is being run combines 2 tables into
a table query. There is only one join.

The issue seems to be that this query runs on another
machine just fine... They have similar hardware,
and software, but it fails with a "Query is too complex"
error on her machine.

We dont feel that this is in fact a "complex" query.

Point is, I need this to run on her machine, but I am
unable to determine what the differences are.

Could this be a connection issue?

I'm really shooting in the dark here, so any suggestions
are greatly appreciated.

thanks again.
*$

On Thu, 05 Aug 2004 19:43:08 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:

>Starbuck,
>I am afraid you haven't provided enough information to be of help. What
>error is she getting? What statement is she running? etc.
>By the way, you can search the archive of the public Microsoft
>SQL-Server forums on Google (without registration). Have a look at
>http://groups.google.com/groups?hl=...ublic.sqlserver
>Hope this helps,
>Gert-Jan
>
>Starbuck wrote:
>>
>> OK, first let me say that I am no DB person.
>>
>> But I have a user here who keeps getting this error
>> whenever she does, whatever it is she does, with
>> databases...
>>
>> A google search takes me to various forums where
>> I am forced to sign up before I can read any answers.
>>
>> Interesting note here is that the guy in the office next
>> to her can run this same query without any problems.
>>
>> So, can this be a hardware issue?
>> If I threw more RAM at this problem would that do?
>>
>> Or is this strickly a coding issue?
>> Why would one machine run this query ok, yet another
>> machine generates this error?
>>
>> Any pointers that you guys could throw my way would
>> be greatly appreciated.
>> Thanks
>>
>> *$|||Starbuck (Starbuck@.BogusDomain.com) writes:
> The error message is "Query is too complex".
> The Query that is being run combines 2 tables into
> a table query. There is only one join.
> The issue seems to be that this query runs on another
> machine just fine... They have similar hardware,
> and software, but it fails with a "Query is too complex"
> error on her machine.
> We dont feel that this is in fact a "complex" query.
> Point is, I need this to run on her machine, but I am
> unable to determine what the differences are.
> Could this be a connection issue?
> I'm really shooting in the dark here, so any suggestions
> are greatly appreciated.

If you are in the dark, guess how dark we are in, who don't even know
what environment you are using, or how the query looks like.

That much I can tell, that the message "Query is too complex" does not
appear in master..sysmessages, so it is not likely to be a message from
SQL Server. To be able to assist further we need to see the query,
we need to know what environment the user gets this error in, and we
need to know which database engine you are working with. (It should be
MS SQL Server, else you are posting to the wrong newsgroup.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Starbuck" <Starbuck@.BogusDomain.com> wrote in message
news:spk4h0t52bf29a93mcpvbb1pjs7sa3hqk5@.4ax.com...
> OK, first let me say that I am no DB person.
> But I have a user here who keeps getting this error
> whenever she does, whatever it is she does, with
> databases...
> A google search takes me to various forums where
> I am forced to sign up before I can read any answers.
> Interesting note here is that the guy in the office next
> to her can run this same query without any problems.
> So, can this be a hardware issue?
> If I threw more RAM at this problem would that do?
> Or is this strickly a coding issue?
> Why would one machine run this query ok, yet another
> machine generates this error?
> Any pointers that you guys could throw my way would
> be greatly appreciated.
> Thanks
> *$

I've never heard of this error before - as Erland pointed out, this is not a
native MSSQL error - however some Googling suggests that it is likely to be
either an MS Access issue, or something related to some very old data access
components (the most recent exact hit for this error in Google newsgroups is
in the year 2000). For example:

http://support.microsoft.com/defaul...&NoWebContent=1

http://support.microsoft.com/defaul...&NoWebContent=1

http://www.dotnet247.com/247referen.../43/216266.aspx

http://www.google.com/search?source...ery+too+complex

Without more information, however, it's very difficult for anyone to give
any good advice. What is the database (Access, MSSQL), what is the client
(Access, in-house app, third-party app), what are the operating systems, the
servicepacks, how is the query submitted, what is the definition of the
query and tables, what difference is there between the workstations where
the query works and does not (OS, hardware) etc.

My best guess is that you are querying an MS Access database, and the "guy
in the next office" has a more recent Windows version than the user with the
error (and therefore more recent data access components), but that's purely
speculation and very likely to be wrong. If you're not using MSSQL, then as
Erland said, you will get a better response in a forum dedicated to your
database platform.

Simon

"complex" SQL and bulk mailing

Well, you guys probably don't think this is very complex SQL :) , but it's taken me ages and it's STILL not working right. I submit the following:

CREATE PROCEDURE forum_readmail AS
Declare @.hMessage varchar(255),@.msg_id varchar(255),@.MessageText varchar(8000),@.message varchar(8000),@.MessageSubject varchar(8000),@.subject varchar(8000),@.Origin varchar (8000),@.originator_address varchar(8000)
EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN
exec master.dbo.xp_readmail @.peek='true',@.msg_id=@.hMessage,@.message=@.MessageTe xt OUT,@.subject=@.MessageSubject OUT,@.originator_address=@.Origin OUT
IF ((SELECT COUNT(*) FROM forum_users WHERE email=@.Origin)=1)
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF REPLYING TO EXISTING THREAD
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500), @.usr1 int, @.mySubject varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
SELECT @.mySubject=subject FROM forum_threads WHERE id=@.existingID
-- GET USERS ID FROM EMAIL
declare user_cursor1 cursor for
SELECT id from forum_users WHERE email=@.Origin
open user_cursor1
fetch next from user_cursor1
into @.usr1
while @.@.FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@.MessageText, CHAR(13)+CHAR(10), '<br>'), @.existingID, @.usr1)
fetch next from user_cursor1
into @.usr1
end
close user_cursor1
deallocate user_cursor1

--GET EMAIL LIST
declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1
while @.@.FETCH_STATUS=0
begin --CREATE EMAIL
select @.sbj1=@.Origin+': '+@.mySubject+' (ThreadID='+cast(@.existingID as varchar)+')'
select @.bdy1='User '+@.Origin+' has replied to a thread on the test forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messa geText
exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END
ELSE -- IF NEW THREAD
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500), @.usr2 int

-- INSERT INTO FORUM_THREADS
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY

-- GET USERS ID FROM EMAIL
declare user_cursor2 cursor for
SELECT id from forum_users WHERE email=@.Origin
open user_cursor2
fetch next from user_cursor2
into @.usr2
while @.@.FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@.MessageText, CHAR(13)+CHAR(10), '<br>'), @.newID, @.usr2)
fetch next from user_cursor2
into @.usr2
end
close user_cursor2
deallocate user_cursor2
--GET EMAIL LIST
declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2
while @.@.FETCH_STATUS=0
begin
select @.sbj2=@.Origin+': '+@.MessageSubject+' (ThreadID='+cast(@.newID as varchar)+')'
select @.bdy2='User '+@.Origin+' has started a new thread on the test forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messa geText
exec master.dbo.xp_sendmail
@.recipients=@.em2,
@.message=@.bdy2,
@.subject=@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END
END
SET @.hMessage = NULL
EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
END
GO

This runs as a scheduled job every 10 minutes. There are about 600 users in the 'forum_users' table - which our experiments indicate it should take about 3 minutes to email.

It seems to be having problems, though - it sits continually in an "Executing job" state, even sometimes when there aren't any emails in the mailbox to read. And sometimes just fails completely - won't read anything.

What could be causing this? Where should I start? Is the code as clean and swift as it can be? Should I be especially vigilant of invalid email addresses? Might we be having network problems? Should I not even be thinking of using SQLMail to deal with large (ie: absolute maximum of about 1500 users) volumes of email both to read and send out?

Help. My fingers are bleeding and my boss is going to beat me.Right, we've done some more testing. It seems that setting it to read all emails (not just the unread ones) and not to "peek" at them (ie: leave them as unread) stops it stitting there in an "executing" state. But there are still problems.

Firstly, it fails every other time. It's like this:
I delete every message in the mailbox
I send it a single email
I start the job - it immediately switches to "Failed".
I start the job again - it spends a few seconds on "Executing" (there are about 100 email addresses to send emails to) and finally switches to "Successful".

It only seems to do this when there's only new messages in the mailbox. I don't understand why.

Secondly - and this is the thing we're hoping to actually test - I have concerns about the volumes that SQLmail can actually cope with. It seems to be able to generate email at about 1000 per minute, but if I've got 1000 users in the database and there's 10 new forum posts for it to process, it's gotta send out 10,000 emails.

Is this a ridiculous thing to expect SQL mail to be able to do?|||Oh, there's one more thing. We've also got a problem with Exchange returning delivery failure notification emails to the mailbox that SQL mail is reading - which can't be helping. Now, we've tried setting Exchange to stop sending the blasted things, but to no avail. So I changed the SP above to include (immediately after the "exec master.dbo.xp_readmail" line), the following:

IF (CHARINDEX('Undeliverable', @.MessageSubject)>0)
BEGIN
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END

but it very blatantly isn't deleting anything with "Undeliverable" in the subject. Can anyone think why?|||OK, update:

I've got round the error message it was generating, but it's still ignoring this 'if' statement, and I've NO idea why. I've tried various checks, including:
IF (@.MessageSubject LIKE '%Undeliverable%')
and
IF (@.Origin LIKE '%System%')
(where @.Origin is the sender of the email; 'System Administrator')

It's just not deleting the email, and it SHOULD. Does anyone know why?